Score:1

How to reduce MySQL Server CPU usage or load?

zw flag

How to reduce MySQL Server CPU usage or load ?

Here is 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
ua flag
How much RAM? Please find the 'worst' query via the slowlog -- [_SlowLog_](http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog)
paulsm4 avatar
in flag
Q: What's your current CPU utilization (have you looked in "top", for example)? Q: If CPU "spikes", have you identified the query? Q: Have you looked at [EXPLAIN](https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html} for that query? Q (per linuxpro): How much RAM do you have?
Wilson Hauck avatar
jp flag
Additional DB information request, please. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions.
Wilson Hauck avatar
jp flag
Post TEXT data on justpaste.it and share the links. Additional very helpful OS information includes - please, htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused, for server workload tuning analysis to provide suggestions.
Score:0
jp flag

Rate Per Second = RPS

Suggestions to consider to reduce cpu usage or load in [mysql] section of 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

View profile for contact info, posting requested additional information will allow additional suggestions.

I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.