Mysql
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。
請在幾天后告訴我們您的進展情況。