MariaDB and PHP-FPM high cpu load

Question

we have a dedicated server running only 1 domain (Wordpress) with 500 users per minute and have very often timeouts. Server Specs: Intel(R) Xeon(R) CPU E5-1650 v3 @ 3.50GHz (12 core(s)) 64GB RAM 2 x 200GB SSD Raid 0 OS: Debian 9.6 , Plesk Onyx 17.8, MariaDB 10, PHP-FPM 7.2

We just tuned some DB settings but still have the timeouts/breaks. MariaDB CPU Usage is 200% , PHP-FPM 50% CPU per Instance Can anyone help ?

Here is the my.cnf
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam_recover_options = BACKUP
max_connections = 200
#table_cache = 64
#thread_concurrency = 10

#query_cache_limit = 1M
#query_cache_size = 16M
skip-name-resolve = 1
table_cache = 8192
table_definition_cache = 8192
table_open_cache = 16384
sort_buffer_size = 16M
query_cache_limit = 24M
query_cache_size = 256M
join_buffer_size = 10M
tmp_table_size = 512M
max_heap_table_size = 512M
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_flush_method = O_DSYNC
innodb_flush_log_at_trx_commit = 0
innodb_lru_scan_depth = 256

Answer 1

Some of your tuning options are dangerous. Most are horrendously counterproductive. Leave the following and remove the rest:

innodb_buffer_pool_size=24G skip_name_resolve=1 

Then set:

slow_log=1 long_query_time=0 

Capture a few hours of slow log and put it through mysqldumpslow or pt-query-digest, and fix the queries that are eating most of your CPU, through better indexing or rewiring them more efficiently.

Answer 2

For more detailed analysis, see: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

Off hand: query_cache_size = 256M is too high, leading to CPU activity to keep it purged. In most cases, it is best to turn off the query cache.

WordPress? Here's another tip, but it involves changing tables there: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

My suggestions on the setting up the slowlog:

log_output = FILE slow_query_log = ON slow_query_log_file = (fullpath to some file) long_query_time = 1 log_slow_admin_statements = ON log_queries_not_using_indexes = OFF 

http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog