MySQL Query Randomly Lags

Question

I have a query that looks like this:

SELECT id FROM user WHERE id='47' 

The ID is indexed and reads for this query are always fast when using profiling data, like this.

SET profiling = 1; SHOW PROFILES; 

The queries always execute in around 0.0002 seconds.

However, if I profile the query from the PHP side, like this:

$current = microtime(true); $data = $conn->query($full_query); $elapsed = microtime(true) - $current; 

Then occasionally maybe 1 out 50 of these queries will take something like .2 seconds. However, in my test script I have code to test this that profiles the query using SET profiling = 1; and even though the PHP round trip through PDO might be .2 seconds the query time was still 0.0002.

Things I know, or know that aren't causing the issue:

1. The query isn't slow. When I look at the same query, from the same query run, profiled in PHP and profiled using SET PROFILING the query is always fast and never logged in the slow query log even when it shows taking .2 seconds from the PHP side.
2. This is not skip-name-resolve related - this is inconsistent and I have skip-name-resolve already on
3. This is not query cache related, the behavior exists in both
4. This behavior happens even on queries coming out of the cache.
5. The query doesn't actually select the ID, but I use this query for testing to show that it isn't a disk access issue since that field is definitely indexed.
6. This tables is only 10-20 megs with something like a 1 meg index. The machine shows very little load and innodb is not using all of its buffers.
7. This is tested against a table that has no other activity against it other than my test queries.

Does anyone have any ideas of what else to check? This seems to me to be a networking issue, but I need to be able to see it and find the issue to fix it and I'm running out of places to check next. Any ideas?

Answer 1

I would profile the machine.

You say this occurs ~1 per 50 times, and that each query has a 0.2 sec benchmark. You should be able to put top in a screen, and then run a loop of queries in PHP to load-test the RDBMS and gather performance stats.

You will probably have to run more than 50 * 0.2 =10 seconds, since your "1 out of 50" statistic is probably based on hand-running individual queries - based on what I read in your description. Try 30-second and 90-second load tests.

During this time, watch your top process screen. Sort it by CPU by pressing P. Each time you press 'P' it will change the sort order for process-CPU-consumption, so make sure you have the most-consuming on top. (pressing M sorts by memory usage. check the man page for more)

Look for anything that bubbles to the top during the time(s) of your load-test. You should see something jump higher - however momentarily.
(note, such a process may not reach the top of the list — it need not, but could still introduce enough disk load or other activity to lag the MySQL server)

Answer 2

I have noticed the same phenomenon on my systems. Queries which normally take a millisecond will suddenly take 1-2 seconds. All of my cases are simple, single table INSERT/UPDATE/REPLACE statements --- not on any SELECTs. No load, locking, or thread build up is evident.

I had suspected that it's due to clearing out dirty pages, flushing changes to disk, or some hidden mutex, but I have yet to narrow it down.

Also Ruled Out

1. Server load -- no correlation with high

2. load Engine -- happens with InnoDB/MyISAM/Memory MySQL Query

3. Cache -- happens whether it's on or off

4. Log rotations -- no correlation in events