Wie kann die CPU-Auslastung bzw. Belastung des MySQL-Servers reduziert werden?
Hier ist 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
Antwort1
Rate pro Sekunde = RPS
Vorschläge zur Reduzierung der CPU-Auslastung oder -Last im Abschnitt [mysql] von my.cnf
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
Sehen Sie sich das Profil für Kontaktinformationen an. Durch das Posten der angeforderten zusätzlichen Informationen sind weitere Vorschläge möglich.