MySQL query randomly takes "forever"

Answer 1

If id is a primary key, why are you adding the LIMIT clause?

Have you tried specifying the column names you want instead of using a *?

Also, is your Id column int? By specifying '1' instead of 1, you may not be using the index.

Try

SELECT * FROM Feeds WHERE id = 1

rather than

SELECT * FROM Feeds WHERE id = '1'

Edit for comment

It's better to specify the column names explicitly, in my opinion, because you may need to add columns to that table in the future that are not needed by your app. At that point, you start pulling more data than is needed.

Answer 2

If you're using MyISAM, you might be hitting concurrency problems.

Answer 3

If the table is larger than what can be kept in memory caches, then it could be that some of these queries needed to touch the disc at some unfortunate moments where something else were putting a lot of load on them?

MySQL tuning is sometimes a bit of a black art. High key-buffer cache contention can also give noticeable slow downs.

You could also try searching the mysql performance blog for clues and plausible theories.

Answer 4

Your query is very simple, and given that id is a primary key there's no way it should take that long even on a huge table, under normal circumstances. Just a guess here, but maybe the server is the problem? As I understand it (from 30 seconds of looking at their homepage), Slicehost is offering you a virtual machine "slice" of a more powerful server. Could it be that the other slices on the same server are doing heavy disk reads every now and then, temporarily stealing all your resources? Or maybe it happens when the administrators create/remove slices from the machine for other users.

Does this happen very frequently?

Answer 5

Looking at the mean & variance of slows, you have a problem with the VM host machine (which is not under your control unfortunately).

For those of you pointing out the memory/disk I/O, those numbers are just too big for that. Disk should return in 100ms, not several seconds.

Answer 6

I've gotten to get a lot of experience in this kind of thing over the last year, unfortunately.

I agree with others it could be a CPU/disk latency issue (due to the virtual hosting). Is there some way you can get disk latency numbers from the host? Maybe there are spikes.

I also agree that the query is a little weird, in specifying the limit clause and the quoting the index. The SELECT * bit I can totally understand.

I'd guess InnoDB doesn't have enough memory, but with so few rows and giving InnoDB 1 gig, that's not it.

I'd guess the query is wrong. I've seen MySQL do this kind of thing before. Some query takes too long or causes others to start to stack up. But the queries that you see taking too long are simple smaller things that should never take very long.

I have a few suggestions for you:

• Is there some sort of automated backup running that could be locking the table?
• Does this happen on any kind of regular or predictable interval?
• Have you ever been logged in and seen the full process list when this occurs?
• Does it coincide with anything specific (any time people run a certain report, etc)?
• Do you have any very large tables that could be tying up all your memory while they're working on queries, preventing this table from coming in (unlikely)?
• Has this always been the case? Did it start recently? Has the MySQL version changed? Are you able to try another build of MySQL (newer point release, Percona Performance build, etc)?

Some times looking at the full process list while this is going on can be the most helpful.

When we were running into this kind of thing last year, it was watching the process list that finally caught the real issue.

Answer 7

I have seen this problem before.

Your index is on an integer field, and your where clause key is a string. Your index is being defeated by the fact that you are causing a type conversion. Unquote your key in the where clause.

I was very surprised that mysql behaves this way, it's quite disappointing that it can't detect when this is happening.