Mysql
如何為使用過多 CPU 的 Mysql 5.1 配置 my.cnf？
我是新的數據庫工程師。我的伺服器在數據庫查詢（簡單計數語句）上使用過多 CPU 時遇到問題。我已經搜尋過它，所以我發現它取決於系統配置。所以我發布同樣的內容。以下是我的伺服器的以下統計資訊：
32 GB Ram 2.7 TB hard drive 150 GB database size(with 2 myisam tables contains billions of record) mysql version 5.1 Centos這是我的 my.cnf 文件：
#datadir=/var/lib/mysql datadir=/home/mysql socket=/var/lib/mysql/mysql.sock #socket=/home/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid沒有太多細節我認為my.cnf沒有根據系統配置進行配置。
請讓我知道在 my.cnf 中設置參數的最佳方法是什麼，以便 mysql 正常工作。
顯示變數的輸出；
SHOW VARIABLES; | Variable_name | Value | +-----------------------------------------+-------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | / | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | bulk_insert_buffer_size | 8388608 | | character_sets_dir | /usr/share/mysql/charsets/| | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 10 | | datadir | /home/mysql/ | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | engine_condition_pushdown | ON | | error_count | 0 | event_scheduler | OFF | expire_logs_days | 0 | flush | OFF | flush_time | 0 | foreign_key_checks | ON | general_log | OFF | general_log_file | /home/mysql/localhost.log | | group_concat_max_len | 1024 | identity | 0 | ignore_builtin_innodb | OFF | innodb_adaptive_hash_index | ON | innodb_additional_mem_pool_size | 1048576 | innodb_autoextend_increment | 8 | innodb_autoinc_lock_mode | 1 | innodb_buffer_pool_size | 8388608 | innodb_checksums | ON | innodb_commit_concurrency | 0 | innodb_concurrency_tickets | 500 | innodb_data_file_path | ibdata1:10M:autoextend | innodb_doublewrite | ON | innodb_fast_shutdown | 1 | innodb_file_io_threads | 4 | innodb_file_per_table | OFF | innodb_flush_log_at_trx_commit | 1 | innodb_flush_method | | innodb_force_recovery | 0 | innodb_lock_wait_timeout | 50 | innodb_locks_unsafe_for_binlog | OFF | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | innodb_log_files_in_group | 2 | innodb_log_group_home_dir | ./ | innodb_max_dirty_pages_pct | 90 | innodb_max_purge_lag | 0 | innodb_mirrored_log_groups | 1 | innodb_open_files | 300 | innodb_rollback_on_timeout | OFF | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | innodb_support_xa | ON | innodb_sync_spin_loops | 20 | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | innodb_thread_sleep_delay | 10000 | innodb_use_legacy_cardinality_algorithm | ON | insert_id | 0 | interactive_timeout | 28800 | join_buffer_size | 131072 | keep_files_on_create | OFF | key_buffer_size | 8384512 | key_cache_age_threshold | 300 | key_cache_block_size | 1024 | key_cache_division_limit | 100 | long_query_time | 10.000000 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 151 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_long_data_size | 1048576 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 18446744073709551615 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | OFF | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | open_files_limit | 1024 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on | | pid_file | /home/mysql/localhost.localdomain.pid | | plugin_dir | /usr/lib64/mysql/plugin | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | pseudo_thread_id | 18 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | rpl_recovery_rank | 0 | | secure_auth | OFF | |skip_external_locking | ON | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /home/mysql/localhost-slow.log | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 2097144 | | sql_auto_is_null | ON | | sql_big_selects | ON | | sql_big_tables | OFF | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_log_update | ON | | sql_low_priority_updates | OFF | | sql_max_join_size | 18446744073709551615 | | sql_mode | | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | | | sql_warnings | | | storage_engine | MyISAM | | table_definition_cache | 256 | | table_lock_wait_timeout | 50 | | table_open_cache | 64 | | table_type | MyISAM | | thread_cache_size | 0 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | | | timestamp | 1401967364 | | tmp_table_size | 16777216 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ| 版本 | 5.1.66-社區
首先，對於大數據，您應該考慮升級 MySQL 5.6 並將您的表轉換為 InnoDB，因為即使 MyISAM 在讀取方面更快，但隨著數據庫的增長，此功能也會失去。而且您的數據庫比該限制要大得多。MyISAM 表使用表級鎖定。根據您的流量估計，您每秒有接近 200 次寫入。使用 MyISAM，任何時候都只能進行其中的一項。您必須確保您的硬體能夠跟上這些事務以避免超限，即，單個查詢可能需要不超過 5 毫秒。
作為 my.cnf，這可能是您的系統檢查性能的起點。
$$ mysqld $$
open-files-limit=65535
expire-logs-days=14
max-allowed-packet=64M
max-connect-errors=10000
tmp-table-size=512M
max-heap-table-size=256M
query-cache-type= 0
query-cache-size=0
max-connections=1000
thread-cache-size=100
open-files-limit=655350
table-definition-cache=1024
table-open-cache=2048
innodb-lock-wait-timeout=240
慢查詢日誌=0
長查詢時間=10
日誌錯誤=/var/log/mysql-error.log
innodb-purge-batch-size=10000
innodb-rollback-on-timeout
$$ mysqldump $$
快速
單筆交易
max_allowed_packet = 16M
$$ mysql $$
no_auto_rehash
$$ myisamchk $$
key_buffer = 512M sort_buffer_size =
512M
read_buffer = 64M
write_buffer = 64M
$$ mysqld_safe $$
打開文件限制 = 32768
myisam_recover = 備份，強制
很抱歉我沒有馬上回答這個問題。它是這樣的：
由於 MySQL 版本是 MySQL 5.1.66，因此可以採取一些措施以正確的方式提高 CPU 性能。從歷史上看，MySQL 5.1.38 是 MySQL 5 的第一個版本，引入了innodb_read_io_threads和innodb_write_io_threads作為可配置選項。
我寫過很多次
Sep 26, 2013:為什麼在 Mysql 5.1 中使用 Innodb 外掛而不是內置的 Innodb？(GPL)Jun 01, 2012:我有 16GB 的記憶體，我應該如何配置 MySQL 伺服器？May 17, 2012: MySQL - 具有不同伺服器規格的主/從複製主要想法是安裝InnoDB Plugin 1.0。開始使用這些選項。安裝InnoDB 外掛後，我會向您推薦以下有關調整的文章：
Mar 16, 2012:在 Debian 上使用多個核心進行單個 MySQL 查詢Sep 20, 2011:多核和 MySQL 性能Sep 12, 2011:可以讓 MySQL 使用多個核心嗎？May 26, 2011:關於單執行緒與多執行緒數據庫的性能離開時間扭曲，我想說簡單地升級到 MySQL 5.6 並使用我的調整建議。我還將所有 MyISAM 轉換為 InnoDB。