我正在尝试解决 mySQL 在 CPU/服务器负载方面达到上限的问题,因为服务器规格如下:
项目清单
2 x 2.4GHz 八核 E5-2630 v3 Haswell Xeon
- 128GB 内存
- SSD 主驱动器
- CloudLinux 7.5
- MariaDB 10.2.16
- LiteSpeed 5.2.8
在以下配置下,mySQL/MariaDB 的 CPU 使用率会不断达到 80-150% 之间,给服务器带来很大压力。
关于我可以调整/启用/禁用/添加/删除什么来使 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
答案1
针对 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。
请在几天内让我们知道您的进展。