my.cnf MariaDB 配置 - 即使進行了優化，也達到了 CPU 上限

我正在嘗試解決 mySQL 在 CPU/伺服器負載方面達到上限的問題，因為伺服器規格如下：

• 項目清單
• 2 個 2.4GHz 八核 E5-2630 v3 Haswell Xeon
• 128GB 記憶體
• SSD 主驅動器
• 雲Linux 7.5
• MariaDB 10.2.16
• LiteSpeed 5.2.8

在下面的配置中，mySQL/MariaDB 不斷地達到 80-150% 的 CPU 並且給伺服器帶來了相當大的壓力。

關於我可以調整/啟用/禁用/添加/刪除的任何建議，以使 mySQL 減少 CPU 佔用，因為 RAM 使用率穩定在 25% 以下，但 CPU 經常通過屋頂。

[mysqld]
skip_name_resolve               = 1
default_storage_engine          = InnoDB
log_error                       = /var/lib/mysql/mysql_log.err
performance_schema              = 0
sql_mode                        = "NO_ENGINE_SUBSTITUTION"

max_allowed_packet              = 256M
max_connections                 = 400
open_files_limit                = 10000
wait_timeout                    = 120
connect_timeout                 = 120
interactive_timeout             = 180
tmpdir                          = /var/lib/mysql/tmp
tmp_table_size                  = 256M
max_heap_table_size             = 256M
max_tmp_tables                  = 300
max_statement_time              = 60
innodb_strict_mode              = OFF
#innodb_file_per_table          = ON

slow_query_log                  = ON
slow_query_log_file             = /var/lib/mysql/mysql_slow_queries.log
long_query_time                 = 10

table_open_cache                = 128
table_open_cache_instances      = 16
query_cache_type                = ON
query_cache_size                = 256M
thread_cache_size               = 40
query_cache_limit               = 300M 
key_buffer_size                 = 3G
join_buffer_size                = 512M
max_heap_table_size             = 16M

innodb_buffer_pool_size         = 64G  #80% of system memory?
innodb_log_file_size            = 2G
#innodb_log_buffer_size         = 1G
innodb_buffer_pool_instances    = 12
innodb_file_format              = Barracuda
#innodb_max_dirty_pages_pct     = 0
#innodb_io_capacity             = 400
#innodb_io_capacity_max         = 600
#innodb_flush_sync              = OFF
innodb_buffer_pool_dump_pct     = 80
innodb_flush_log_at_trx_commit  = 0 #or 2
sync_binlog                     = 0
innodb_flush_method             = O_DIRECT
innodb_thread_concurrency       = 8
innodb_read_ahead_threshold     = 8

concurrent_insert               = 1
local_infile                    = 0
group_concat_max_len            = 102400

以下是可能有助於更好地了解環境的所有相關閱讀材料：

• 顯示全域變數：https ://pastebin.com/xuwGb0Ec
• 顯示全球狀態：https ://pastebin.com/7XStDDQU
• MySQL 調諧器報告：https ://pastebin.com/N5595vA4
• 頂部-c： http: //prntscr.com/kh4f8b
• htop： http: //prntscr.com/kh4ec9
• ulimit，df -h，iostat -x： http: //prntscr.com/kh4nyz

為您的 my.cnf 考慮的建議

$$ mysqld $$每秒速率 = RPS

thread_handling=pool-of-threads  # from one-thread-per-connection for scalability
max_heap_table_size=512M  # from 16M to increase RAM capacity
tmp_table_size=512M # from 256K 2 be = max_heap_table_size and reduce created_tmp_disk_tables
innodb_io_capacity=10000  # from 200 limit for SSD possible RPS
read_buffer_size=256K  # from 128K to reduce handler_read_next RPS
read_rnd_buffer_size=192K  # from 256K to reduce handler_read_rnd_nxt RPS
aria_pagecache_division_limit=50  # from 100 for WARM cache
key_cache_division_limit=50  # from 100 for WARM cache
innodb_buffer_pool_instances=8  # from 12 for your current data volume
innodb_buffer_pool_size=24G  # from 64G to support 11G of data with room to grow
innodb_lru_scan_depth=100  # from 1024 to reduce CPU every SECOND see refman
innodb_thread_concurrency=0  # from 8 see dba.stackexchange Question 5666

如需其他建議，請查看個人資料、網路個人資料以獲取包括 Skype ID 在內的聯繫資訊。

還有更多的機會來提高您的系統性能。

ulimit -n 48000 會很有幫助。測試後，使其持久化。

記住每天只更改一次，監控，24 小時或更長時間後進行下一次更改。如果任何更改是有害的，請在將其從 my.cnf 中刪除後告訴我@wilsonhauck。

請在幾天后告訴我們您的進展情況。

引用自：https://serverfault.com/questions/925502