MySQL Forums
Forum List  »  Performance

Periodic SELECT query slowdowns
Posted by: Dale Lampson
Date: March 31, 2009 08:22PM

My RoR / mySQL app is stable and has been running for weeks. But I've noticed that about 10 times per 24-hour period, mySQL will log to mysql-slow.log select queries that normally take < 100 ms. During these transient slow-down periods, things get really ugly--the same queries can take 50 seconds or longer. Then, as mysteriously as the problem appears, it goes away. This condition typically lasts anywhere from 30 seconds to several minutes.

Other processes on the slice remain responsive.

The query is a simple 1-table select in the form

SELECT * FROM `wind_speeds` WHERE ((lat between 37.26999893 and 37.46999893) AND (lng between -96.55999908 and -96.35999908));

The table structure is:

+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| lat | float(9,6) | NO | MUL | NULL | |
| lng | float(9,6) | NO | MUL | NULL | |
| wind_speed | float(7,3) | NO | | NULL | |
| x | int(11) | NO | | NULL | |
| y | int(11) | NO | | NULL | |

The engine is Innodb.


Hardware: 2 VM slices, Dual Core running Ubuntu 8.04. The app servers run on slice 1, and only the mySQL DB instance is running on slice2.

I can find nothing out of the ordinary in any of my application stack logs. With enhanced logging, everything points to the execution path being very fast until mySQL gets it and reports the long execution in the mysql-slow log file. There's never anything in the mysql.error log.

Right now I'm thinking it's either something screwed up in my my.cnf file (5.0.56), or mySQL is fine, but something's wonky with my VPN / SAN. But I'm flat out of ideas on what to look for next. Any help will be greatly appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Periodic SELECT query slowdowns
3804
March 31, 2009 08:22PM
1884
March 31, 2009 09:44PM
1882
April 02, 2009 08:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.