I ran into an issue the last few days, I have a database server 2 Quad Core Processors and 24 gb of ram, recently we have come across a big issue the server is running normally at about 130% cpu then it will just randomly spike to 750% pretty much maxing out all of the cores and it brings our site to a super crawl. I restarted the mysql process and it resolves it then about 10 mins later it happens again. The last time it happened i let it sit at 750% then after a few minutes it goes back down. I did a process dump as it was happening and it has about 4,000 queries in queue that says copying/sending to tmp table.

If anyone knows about this issue or are experts at mysql innodb databases and php let me know I'm even willing to pay to get this fix, price is not a issue, just want the issue resolved.


Do not restart MySQL just like that. Usually it doesn't help at all -- the troublesome query or situation will come back sooner or later and after restart MySQL needs anyway to warm up. Restarting will flush its caches and so on.

What I suspect that is going on is that there is some kind of abnormal activity on your web site (such as DoS attack or Slashdot/Reddit effect), or perhaps a recent update included a new fancy database-killing bug. Check your http logs, or for more visual outlook, run the Apache logs through Webalizer or similar program.

If your problem is not due web activity or you would like to prevent problems like this in the future, typical situations for the spikiness you just described:

  • Non-optimally tuned my.cnf -- have you fine-tuned your InnoDB settings? Could we take a look at your my.cnf?

  • Missing indexes from some heavily used table.

  • Table type as MyISAM and then some long-running SELECTs combined with lots of UPDATE/INSERT/DELETE activity leads to huge query queues. This is actually what I think might be your problem: are you ABSOLUTELY sure your tables are in InnoDB format and this one table is not accidently (or even by purpose) in MyISAM?

  • Too small tmp_table_size value in my.cnf; this can be the situation if your database runs queries with lots of sorts, big results sets or similar. Too small tmp_table_size leads to situation where MySQL creates the tmp table it needs for the query to hard disk instead of storing it to RAM. For one query this is not essentially a bad thing, but if many queries are doing this at the same time, your hard disk performance will be a big bad bottleneck. This is another thing I suspect might your problem at the moment.

  • Databases are located on SAN or some other storage and for any reason the SAN itself slows down; maybe some other server is heavily using it.

  • Filesystem and/or I/O elevator is killing the performance. For example, if you have a typical Linux distribution, they are nowadays bundled with CFQ as the default I/O elevator. That might not be anywhere near optimal for database use - deadline or anticipatory are much better for that, I usually use deadline. I can guide you how to check out and/or change the current I/O elevator if you are in doubt - the operation is safe and can be done online. When it comes to filesystems, ext3 might not be the most performant one with huge database files, especially if concurrency is high.

And then some questions for you:

  • If it is InnoDB, what does SHOW GLOBAL INNODB STATUS tell you during the spikes?

  • Are the tables your web site needs to access huge? Are we talking about thousands of rows, millions of rows...? And storage-wise, do they consume lots of disk space?

  • What operating system you have in use? And what is the filesystem? Is the filesystem tuned at all? Are the databases lying on local disks or some kind of shared storage such as SAN?

  • You have 24 GB of memory, yes. But what does free report to you during spikes?

  • What kind of web site you run? Is it easily cacheable (such as a news site, where the content changes relatively rarely), or a Facebook-like über-dynamic site?

| improve this answer | |
  • Here is my my.cnf pastie.org/1292985 – William Nov 12 '10 at 16:19
  • I will run the show innodb status next time it happens to see what it outputs . The query that seems to always get stuck has over 1,265,112 rows in that table. Around 130 MB of data and it is join with a table that is 79,000 around 29MB . It is on ext3 not to sure about the tuned part. and its local. Its a uber dynamic site. – William Nov 12 '10 at 16:24
  • On the memory part i believe when it was in spiked stated memory was only at 3% don't quote me I was going crazy when it happened lol ? Is there a way i can contact you through instant messenger or something? – William Nov 12 '10 at 16:25
  • and CentOS is the operating system – William Nov 12 '10 at 16:28
  • See my contact details from my home page (can be found in my profile) and email me, let's get started there. – Janne Pikkarainen Nov 12 '10 at 18:40

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.