I have enabled mysql slow query in my server to log queries that takes over 5 seconds. When I run the same query from the mysql>, the queries that reported over 10 seconds took less than 1 second. Why is that?
TIA, -peter
Asked
Active
Viewed 144 times
1
-
First thing that comes to mind is that the query cache might get a hit for it that time around. Other than that, could be a lot of things. – Corbin May 09 '12 at 16:23
-
Or you have a huge load on the server when the long execution time is logged, and you re-run the queries when the load is smaller. As an option you can include a server load measure in your logs. – Hari May 09 '12 at 16:28
1 Answers
1
It's likely due to caching.
Databases cache some data in memory while leaving most data on disk. When data is fetched by a sql query that data is loaded -- and then kept in memory in case it gets requested again in the near future. Eventually if that data is not requested again, it will be overwritten in the cache by new data coming into the cache from later queries.
On some systems there is additional caching on the hard disk itself -- data fetched from disk once may be kept in cache by the disk controllers. This is because data fetched from disk recently is likely to be fetched again.
So once data has been requested from the query prompt it will be cached -- potentially by the disk controller and by the database itself.
On your application, this is less likely to happen since new users come on at different times and access their own data (which is not likely to be cached before they arrive).
One of the most common ways to speed up a database is to increase the amount of memory the server has available so more data can be cached.
For more information, here is some reading from MySQL's docs on the MySQL Query Cache
Kevin Bedell
- 13,254
- 10
- 78
- 114