mysqld taking 800% CPU since traffic increased on my site, how to fix

Solution 1:

Suggestions to consider for your my.cfg-ini [mysqld] section

max_connections=400  # from 2000 - max_used_connections was 109 in 2 hours
thread_cache_size=100  # from autocalc CAP at 100 to avoid OOM & reduce threads_created
query_cache_size=0  # to disable QC - it will not be available in V8
log_warnings=2  # for addl aborted_connection information

For starters, these will help, multiple CPU's makes > 100% CPU used reasonable. Additional suggestions will be proposed after May 4 request for more details can be posted and analyzed.

Solution 2:

Suggestions to consider for your my.cnf-ini [mysqld] section with current information analyzed.

key_cache_age_threshold=64800  # from 300 second discard, only to RD again
innodb_lru_scan_depth=100  # from 1024 to reduce CPU load see v8 refman
innodb_log_buffer_size=1G  # from 16M to better support 3G per hour
innodb_log_file_size=4G  # from 50M before rotation to next logfile.
innodb_io_capacity=1000  # from 200 to 'let it run'
innodb_change_buffer_max_size=10  # from 25 to reduce BP set-aside RAM
sort_buffer_size=2M  # from 256K to reduce sort_merge_passes of 1m+
max_write_lock_count=16  # from a HUGE number to allow RD after nn lcks

handler_rollback appears to average ONE every 11 seconds.

For 3 minutes, SET GLOBAL innodb_print_all_deadlocks=1; then 3 minutes later, turn it OFF with 0. Any detected deadlocks will be in your error log. If prevention is possible, significant workload will go away.