MySQL CPU & Memory Spikes

MySQLperformance

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!

Best Answer

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.

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

MySQL using too much CPU

You need to unleash two things

innodb_read_io_threads (Default is 4, Max is 64, raise it 8 or 16)
innodb_write_io_threads (Default is 4, Max is 64, raise it 8 or 16)
innodb_thread_concurrency (Default is 0. Since you don't have it, 0 is just fine)

I have this discussed this over the years. Please see some of my earlier posts on getting InnoDB to engage more CPU/Core activity.

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?

Give it a Try !!!

UPDATE 2013-06-20 02:39 EDT

Your last comment

Hey Rolando, I added read_io and write_io at 8 and read through your other posts but it doesn't seem to be bringing down the CPU usage. I even dropped the 4 buffer pool instances down to 1 in hopes that it would have better performance, but still no go. Do you have any more suggestions that I could implement that will help drop CPU usage?

I would suggest raising innodb_buffer_pool_instances to 8 to match the number of cores. The MySQL Documentation suggests 1G per buffer pool instance. Then, you would raise to 16 (since you have innodb_buffer_pool_size at 16G). Try 8 first, then 16.

Even with this, I have some rather distressing news: Here is a Press Release From FusionIO from 2 years ago. Paragraph 2 says:

The StatSoft white paper concluded that Fusion ioDrives significantly increased the I/O performance in the STATISTICA suite of analytics software products and solutions, greatly increasing CPU utilization and efficiency. With ioMemory, StatSoft achieved 300 and 500 percent data performance and latency reduction improvements when compared to legacy disk-based storage. With the increased I/O performance enabled by the Fusion ioDrives, CPU utilization increased to 90 percent in tests of large data sets, versus the 32 percent CPU utilization observed with the disk-based technology.

If you were using RAID 10 SAS or even SSDs for /var/lib/mysql, InnoDB would have its day with the CPUs. However, from the looks of this Press Release, InnoDB is competing with FusionIO for CPU Utilization.

SUGGESTION

Find out what the IOPs are for the FusionIO disk and set innodb_io_capacity to that number. The default value for innodb_io_capacity is 200. If the IOPs are greater than 200, then please set it. If the IOPs are in the 1000's, then InnoDB has a chance to be on a level playing field with the FusionIO.