Every couple of days, my server's CPU and memory usage spikes up to 100%, making our vBulletin forum almost unusable for 5-10 minutes. CPU is typically at 1-2, Memory at 3GB. During the spikes, CPU goes up past 40 and Memory at 5GB (max).
There's no correlation with traffic load or cron jobs. The access logs show that hits are consistent during these spikes. No increase in hits. These spikes can happen in the middle of the night when there are less than 20 users online. Pages go from loading in 0.3 seconds to 10+ seconds. When I run top, I see MySQL eating up all of the CPU and memory, with httpd in second place.
I'm on an SSD VPS with 5GB RAM, Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz, usually lightning fast for our mid-sized forum.
MySQL stats from past week:
Uptime: 7 days 3 hours 50 min 12 sec
Max Used Connections: 230/500 (46.00%)
Key Buffer Used: 95.71M/256.00M (37.39%)
Current Key Buffer Usage: 77.97M (30.46%)
InnoDB Buffer Pool: 79.41M/128.00M (62.04%)
Query Cache: 62.59M/90.00M (69.55%)
Table Locks Waited: 25.16k Rate: 0.0/s (0.04%)
--------------------
Query Cache Limit (bytes): 524288
Query Cache Min Unit (bytes): 4096
Query Cache Size (bytes): 94371840
Query Cache Free Mem (bytes): 28736608
Query Cache Used Mem (bytes): 65635232
Queries in Query Cache: 1
Query Cache Avg Size (bytes): 65635232
TOP during normal, fast time (I don't have one from a recent spike)
top - 12:19:21 up 9 days, 22:08, 3 users, load average: 0.92, 1.50, 2.10
Tasks: 112 total, 2 running, 109 sleeping, 0 stopped, 1 zombie
Cpu(s): 8.6%us, 0.4%sy, 0.0%ni, 90.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.1%st
Mem: 5242880k total, 2269752k used, 2973128k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 1034424k cached
Queries from the past 7 days:
Total 92.95M 150.3/s
QC Hits 39.96M 64.6/s %Total: 42.98
DMS 25.85M 41.8/s 27.81
Com_ 20.12M 32.5/s 21.65
COM_QUIT 7.03M 11.4/s 7.56
-Unknown 2.16k 0.0/s 0.00
Slow 10 s 90 0.0/s 0.00 %DMS: 0.00 Log: OFF
DMS 25.85M 41.8/s 27.81
SELECT 22.59M 36.5/s 24.30 87.39
INSERT 1.40M 2.3/s 1.50 5.40
UPDATE 1.30M 2.1/s 1.40 5.04
REPLACE 509.01k 0.8/s 0.55 1.97
DELETE 51.48k 0.1/s 0.06 0.20
Com_ 20.12M 32.5/s 21.65
set_option 13.09M 21.2/s 14.09
change_db 7.02M 11.3/s 7.55
show_fields 5.16k 0.0/s 0.01
Rows 5.53G 8.9k/s
Using idx 2.49G 4.0k/s %Index: 44.95
Rows/question 59.47
Table Locks
Waited 25.16k 0.0/s %Total: 0.04
Immediate 70.33M 113.7/s
Our my.cnf file:
[mysqld]
thread_cache_size=4
table_cache=512
local-infile=0
innodb_file_per_table=1
query_cache_limit=1M
max_connections=500
key_buffer_size=256M
join_buffer_size=1M
query_cache_limit = 512K
query_cache_size = 90M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
max_tmp_tables=1
default-storage-engine=MyISAM
open_files_limit=4974
max_allowed_packet=268435456
innodb_use_native_aio=0
innodb=on
innodb_buffer_pool_size=134217728
Do you see anything that looks like it could be causing these intermittent problems on an otherwise speedy website? We have around 1 million page views per month. 30-60 concurrent visitors at any given moment on a regular day. 60-130 on a busy day. But again, these spikes don't have any correlation with number of concurrent visitors.
Thanks!
query_cache_type=0andquery_cache_size=0and restart. See if that helps.show full processlistwhile server is slow several times, to see SQL queries are running. It could be random SQL injection attacks, or simply some expensive queries.