On 2018-01-21 12:45:54 -0800, Neto pr wrote:
> I need to know the actual execution time of a query, but considering that the
> data is already cached. I also need to make sure that cached data from other
> queries is cleared.
> I believe that in order to know the real time of a query it will be necessary
> to "warm up" the data to be inserted in cache.
>
> Below are the steps suggested by a DBA for me:
>
> Step 1- run ANALYZE on all tables involved before the test;
> Step 2- restart the DBMS (to clear the DBMS cache);
> Step 3- erase the S.O. cache;
Did you mean "OS cache" (operating system cache)?
> Step 4- execute at least 5 times the same query.
>
> After the actual execution time of the query, it would have to take the time of
> the query that is in the "median" among all.
If you do this, clearing the caches before the tests will probably have little
effekt. The first query will fill the cache with the data needed for
your query (possibly evicting other data) and the next 4 will work on
the cached data. Whether the cache was empty or full before the first
query will make little difference to the median, because the first query
will almost certainly be discarded as an outlier.
Flushing out caches is very useful if you want to measure performance
without caches (e.g. if you want to determine what the performance
impact of a server reboot is).
> Example:
>
> Execution 1: 07m 58s
> Execution 2: 14m 51s
> Execution 3: 17m 59s
> Execution 4: 17m 55s
> Execution 5: 17m 07s
Are these real measurements or did you make them up? They look weird.
Normally the first run is by far the slowest, then the others are very
similar, sometimes with a slight improvement (especially between the 2nd
and 3rd). But in your case it is just the opposite.
> [cleardot]
Sending Webbugs to a mailinglist?
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
