1

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
Prem
  • 578
  • 1
  • 5
  • 12
linuxpro
  • 11
  • 1
  • 2
    How much RAM? Please find the 'worst' query via the slowlog -- [_SlowLog_](http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog) – Rick James Nov 21 '22 at 23:38
  • 1
    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? – paulsm4 Nov 22 '22 at 00:39
  • 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 Nov 26 '22 at 16:11
  • 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. – Wilson Hauck Nov 26 '22 at 16:12

1 Answers1

0

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.

Wilson Hauck
  • 472
  • 5
  • 11