MySQL CPU & Memory Spikes 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!
I remember to have the same problems and it has something to do with how vBulletin is programmed. Please check regular "cronjobs" (not talking about system cronjobs you have already mentioned not to be the culprit) run by vBulletin which are triggered by visting users.

You can find them in version 4 of vBulletin in your admin panel, just below the settings for RSS-Feeds.

Another point to check is wether your vBulletin tables are in MyISAM format. vBulletin (at least until version 4) is optimized for MyISAM - if you happen to use InnoDB then some queries on vBulletin's tables (especially with the post table) will literally grind your system down to a halt.

Hope this helps.