3

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!

  • offtopic. not a programming question. This is server configuration/debugging. Maybe try the DBA site. – Marc B Aug 29 '14 at 16:32
  • sounds like a brute force login attack. – Rufinus Aug 29 '14 at 16:33
  • @Rufinus, I've looked at the access logs, and the hits are consistent during these spikes. No increase in hits. – PF Billing Aug 29 '14 at 16:34
  • 2
    CPU spikes in MySQL are not uncommon, depending on the version. It could be a variety of causes. To be sure, you'd want to use oprofile or something to find out exactly which functions within the mysqld process account for the most execution time during the CPU spikes. My first guess is the query cache, which has been known to spend time resorting itself internally - try setting query_cache_type=0 and query_cache_size=0 and restart. See if that helps. – Bill Karwin Aug 29 '14 at 17:08
  • do a show full processlist while server is slow several times, to see SQL queries are running. It could be random SQL injection attacks, or simply some expensive queries. – Matija Nalis Dec 7 '14 at 21:25
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy