☰

如何减少MySQL服务器的CPU使用率或者负载？

tag-icon • tag-icon linux mysql cpu-usage optimization
如何减少MySQL服务器的CPU使用率或者负载？

如何减少 MySQL 服务器 CPU 使用率或负载？

这是my.cnf：

skip-name-resolve
disable_log_bin

innodb_buffer_pool_size=200GB

innodb_log_file_size=25GB
innodb_log_buffer_size=8M
innodb_buffer_pool_instances=64
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120
innodb_file_per_table=1


innodb_io_capacity=5000
innodb_write_io_threads=32
innodb_read_io_threads=32
innodb_thread_concurrency=512
innodb_autoinc_lock_mode=0

slow_query_log=1
slow_query_log_file="/var/lib/mysql/mysql-slow.log"
long_query_time=1

transaction-isolation=READ-COMMITTED

bulk_insert_buffer_size=256M
max_allowed_packet=64M
max_heap_table_size=256M
ft_min_word_len=2
ft_stopword_file=/var/lib/mysql/stopwords
max_connections=1024
max_user_connections=1024
max_connect_errors=999999999

tmp_table_size=128M
table_open_cache=5000
wait_timeout=10800
thread_cache_size=400
group_concat_max_len=4194304

join_buffer_size=32M
key_buffer_size=2GB
read_buffer_size=8M
sort_buffer_size=8M
read_rnd_buffer_size=16M
myisam_sort_buffer_size=600M

delayed_insert_limit=100
delayed_insert_timeout=300
delayed_queue_size=1000000

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

答案1

每秒速率 = RPS

建议考虑减少 my.cnf 中 [mysql] 部分的 CPU 使用率或负载

read_rnd_buffer_size=32K  # from 16M to reduce handler_read_rnd_next RPS
read_buffer_size=1M  # from 8M to reduce handler_read_next RPS
innodb_thread_concurrency=0  # from 512 to allow OS to manage the limit
max_connect_errors=10  # from 999999999 to aggravate hackers/crackers after 10 break in attempts

查看个人资料以获取联系信息，发布所要求的附加信息将允许提供更多建议。

相关内容