Since last couple of days, mysql is causing huge CPU spikes for no reason on my server…
I am running virtualmin on a cent OS 64 BIT with 12 GB RAM
These are load avg
CPU load averages 35.53 (1 min) 47.24 (5 mins) 32.57 (15 mins)
when i search TOP
Its showing mysql causing above 100% CPU uage with MYSQLD in command
can any one help to find the root cause of issue or suggest some solutions…
If you didnt fine tune your MySQL i would start from here.
i have already installed tuning-primer and updated my.cnf
Following is my current my.cnf based on tuning-primer recommendation
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 4M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 1800
table_definition_cache = 1800
table_open_cache = 768
max_allowed_packet = 4M
key_buffer_size = 1G
wait_timeout = 5
interactive_timeout = 2
long_query_time = 1
open_files_limit = 3200
innodb_buffer_pool_size = 26M
join_buffer_size = 4M
tmp_table_size = 128M
max_heap_table_size = 72M
max_connections = 80
With this settings your MySQL will max at 2.7GB. How much memory you have on that server and how much is free?
I have 12GB memory
Also it seems that some wordpress queries are taking more than 1 second. I have enabled the slow query log and its already 300mb in 2 days /
How many hosts are working with the same MySQL service ?
I recommend to execute MySQLTunner to check what is going wrong with the queries, configuration and something more.
Also, you need to consider to use SSD, NVME or faster Hard Disk to run MySQL databases with heavy load, with tweaking database does not guarantee you the hoped speed atleast without upgrading HardWare.