This is the most puzzling MySQL problem that I've encountered in my career as an administrator. Can anyone with MySQL mastery help me a bit on this?:

Right now, I run an application that queries my MySQL/InnoDB tables many times a second. These queries are simple and optimized -- either single row inserts or selects with an index.

Usually, the queries are super fast, running under 10 ms. However, once every hour or so, all the queries slow down. For example, at 5:04:39 today, a bunch of simple queries all took more than 1-3 seconds to run, as shown in my slow query log.

Why is this the case, and what do you think the solution is?

I have some ideas of my own: maybe the hard drive is busy during that time? I do run a cloud server (rackspace) But I have flush_log_at_trx_commit set to 0 and tons of buffer memory (10x the table size on disk). So the inserts and selects should be done from memory right?

Has anyone else experience something like this before? I've searched all over this forum and others, and it really seems like no other MySQL problem I've seen before.

  • Please should the CREATE TABLE statement(s) for the table(s) that run fast now and slow down without warning. Feb 15 '11 at 17:20

It's incredibly unlikely that MySQL would just slow down on it's own. The question is what else is running on that server and is there any chance that this might be influencing the performance? I would also time the slowness if possible, for example if the slowness happens exactly on the same interval there might be some CRON that is slowing it down. Something completely random could be your hard disk failing, but since this is a VM, and in theory the hard drive is isolated and 100%, this should not be a problem.

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.