I use a VPS with 56 CPU cores and 64 GB of memory, a with MySQL database, but the CPU usage is always high for my apps - user 10.000. This is in my mysqld.cnf. What's wrong with my settings?

innodb_log_file_size = 3125M
innodb_log_buffer_size = 3125M
innodb_file_per_table = ON
innodb_log_files_in_group =4
innodb_flush_method = O_DSYNC
innodb_lock_wait_timeout = 50
innodb_buffer_pool_instances = 50
innodb_flush_log_at_trx_commit = 0
innodb_stats_on_metadata = OFF
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_io_capacity = 5000
key_buffer_size         = 4G
max_allowed_packet      = 1G
thread_stack            = 5M
sort_buffer_size = 50M
read_buffer_size = 50M
read_rnd_buffer_size = 20M
myisam_sort_buffer_size = 20M
join_buffer_size = 1G   
myisam-recover-options  = BACKUP
max_connections        = 1000
max_user_connections = 500
thread_cache_size      = 1000
query_cache_limit       = 0
query_cache_size        = 0
long_query_time = 10
expire_logs_days        = 5
max_binlog_size   = 200M
  • So what mysql version? What is your app doing? Why do you say its wrong? Can we see a show global status and show processlist?
    – danblack
    Feb 2 at 7:59
  • 1
    What makes you say CPU usage always high? Are you just looking at load average? Is it IO related, as opposed to CPU? What are the slow query log and performance schema telling you?
    – nnichols
    Feb 2 at 8:24
  • What do you mean by user 10.00 ? Is it 100% cpu usage ? Feb 2 at 12:04
  • What Version of MySQL?
    – Rick James
    Feb 2 at 18:58
  • mysql version 5.7.4 when peak season max_used_connections = 155 thread_connection = 153 Feb 8 at 0:23

1 Answer 1


innodb_log_buffer_size - I would keep that under 1% of RAM. (innodb_log_file_size looks OK.)

innodb_log_files_in_group there is some evidence that more that "2" degrades performance.

innodb_flush_method -- this depends on MySQL version and disk filesystem type. (Your choice is rarely picked by other DBAs.)

Did you also set innodb_adaptive_max_sleep_delay? I see that MariaDB abandoned innodb_thread_sleep_delay in 10.5 -- either it was obviated by some improvement, or it was deemed not useful. I don't know MySQL's stand on the two.

innodb_io_capacity_max=15000 -- Do you have a super-duper SSD?

key_buffer_size = 4G: Unless you are using MyISAM (you should not be), set this to only 50M.

thread_stack -- Leave at the default value

long_query_time = 1 to make better use of the slowlog. After a day, analyze it with pt-query-digest. SlowLog

But the real way to deal with high CPU (or Load Average) is to find the 'worst' queries (according to the slowlog) and work on improving them. (Random tuning of settings can only get you into trouble.)

For further analysis of the settings, please provide SHOW GLOBAL STATUS; and SHOW VARIABLES; after running at least a day. ( http://mysql.rjweb.org/doc.php/mysql_analysis#tuning )


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.