How to optimize MySQL CPU utilization is going very high sometimes?
This is my my.ini file
slow-query-log=1
long_query_time=10
server-id=1
max_connections=2000
table_open_cache=2000
tmp_table_size=391M
thread_cache_size=10
key_buffer_size=512M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=256M
innodb_buffer_pool_size=10G
innodb_log_file_size=512M
innodb_thread_concurrency=25
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=256M
max_connect_errors=100
open_files_limit=4161
query_cache_type=1
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
query_cache_size = 2084M
Please Suggest any changes required in this to reduce high cpu utilization?
Suggestions to consider for your my.ini [mysqld] section (RPS = Rate Per Second)
You may verify these DYNAMIC Global Variables function for your version of MySQL after login as root with:
SET GLOBAL variable_name=variable_value; (note 128K would be 128*1024 and 391M would be 391*1024*1024 - K & M not allowed for DYNAMIC changes)
Just prior to changing your my.ini each day. If your request is rejected with 'read_only' message, skip it and let me know, please.
# 20180904 1137 from mysqlservertuning.com
# read_rnd_buffer_size=128K # from 256K to reduce handler_read_rnd_next RPS
# innodb_io_capacity=1000 # from 200 to allow additional HD RD and WD RPS
# read_buffer_size=128K # from 64K to reduce handler_read_next RPS
# max_connections=1000 # from 2000 because max_used_connections in 2 days was 244
# thread_cache_size=100 # from 10 to conserve CPU and reduce threads_created of 339 in 2 days
# max_heap_table_size=391M # from 16M to be = tmp_table_size and reduce created_tmp_disk_tables count
# innodb_lru_scan_depth=100 # from 1024 to reduce CPU busy every second see refman
# key_buffer_size=64M # from 512M because only 11% used today
# innodb_change_buffer_max_size=15 # from 25 percent because less than 1% used
# innodb_flushing_avg_loops=10 # from 30 to reduce flushing loop delay
Each DAY, save your current my.ini in \history with DATED timed filename such as 20180904-1137-my.ini to allow going back to last working my.ini quickly.
First time, copy this BLOCK (including the leading date and our web site name)to END of your [mysqld] section and ENABLE one change per DAY by removing leading # and spacecharacter, monitor before moving on to next change.
Disable EARLIER same NAMED variable(s) with leading # and space bar, to avoid confusion. In 5 years you will still have history of my.ini changes with approximate dates.
Normally only ONE change per day, monitor before moving to next change. If a change seems detrimental, go back to last working my.ini and let us know, please.
Looking forward to you posting a Review after implementation + 7 days to mysqlservertuning.com, select SHOP, select your item purchased - even if it was FREE, scroll down to Reviews and add yours, please. Account login is NOT required to post a review.