-1

I noticed that when I execute a query more than one time consecutively on my mysql machine, the execution time drops and changes significantly (40/50%) after the first execution, and that WITHOUT query cache activated and no other processes interfering.

I suspect that there is some "hidden" caching involved, like temporary tables, specially when the query involves several subqueries, or maybe some disk IO cache, but I don't understand it really.

Since sometimes I need to fine tune some queries, I don't know how to deal with this problem. What I do usually, is to run the query several times and get rid of the peaks

I'd like to know if there is a better solution...

Glasnhost
  • 541
  • 3
  • 10
  • 19
  • well if it's a stupid question (it's possible...), let me know why...it will solve my problem...http://meta.stackexchange.com/questions/135/encouraging-people-to-explain-downvotes – Glasnhost Oct 13 '14 at 12:41

2 Answers2

1

The operating system uses unused memory as cache. Why is this a problem?

Edit

To get times spent with the different part of a query operation, you can set on profiling with 

set profiling=1;

and show the profile with show profiles and show profile for query <n> after running a query.

Sven
  • 97,248
  • 13
  • 177
  • 225
  • well, I'm changing the query in order to optimize it, so during the process I don't want anything to interfere...I want to optimize SQL only, independently from the operating system (it's not the same as to productive system) and anything that is not just SQL. – Glasnhost Oct 13 '14 at 12:39
  • So, you want to optimize a system in a very artificial way that doesn't resemble a usual production system with caches and all? Anyway, look at my edit. – Sven Oct 13 '14 at 12:49
  • not really, I'm trying to understand the effect of changes in the SQL query only. I agree that's artificial,and it's exactly what I want now...This "set profiling" seems to me very good for this, I didn't know it, thanks! – Glasnhost Oct 13 '14 at 15:58
1

Be careful with your optimization targets, as already mentioned: When you optimize your queries for repeated runs, you will get optimal results for queries which are run several times in short succession (but you might use query cache for that).

By repeating the queries, you "warm up" OS disk caches, so disk seeks are not accounted into your timing any more. This may worsen your results in real scenarios significantly, when several seek operations on busy disks are needed.

side note: You might also want to try "explain select..." which will tell you about used and usable indexes. In most cases, this will help you in determining which additonal indexes to create or how to rewrite your queries.

matt_gnu
  • 516
  • 3
  • 4
  • actually I want to optimize for NOT repeated runs. I see some queries that take 1200ms to execute the first time and 200ms if they are repeated shortly after. This is my usage pattern, the query is executed maybe once every few hours, when any cache is lost and that is the moment I want fast performance. Of course explain can give useful hints, but I don't know very well how to test the "cold" disk cache situation – Glasnhost Oct 13 '14 at 16:29
  • I guess this is the most complete answer I found: Query profiling Bypassing caches (percona.com) [link](http://www.percona.com/blog/2007/09/12/query-profiling-with-mysql-bypassing-caches/) – Glasnhost Oct 13 '14 at 21:09