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.