1

What is my goal?

To measure actual query time, so that optimisation can be done if the time consumed is not satisfactory.

What have I done so far?

We have been using MySQL and in there we used to measure by using SQL_NO_CACHE.While this does not make MySQL to use the cache as mentioned here it somewhat better than using the cache.

The actual question is not that MySQL time is actually correct, in fact it is not since it does not disable the OS's cache, but on how to measure time for a query taking the cache out of the picture in Postgres.

From the same SO answer above, it is evident that there is no equivalent for Postgres and the cache in Postgres works very differently.

So if we take a query and run it several times, it may take some time in the beginning, but further calls mostly would come from the cache(from using explain (analyze,buffers).

With the above facts established,how I can measure query time.

Problem 1 : We cannot straightaway say that a query is slow for the first time since lot of warmup is required

Problem 2 : If we run the query say for 5 times and take the average, the results are not accurate since it uses the cache

Questions

  • Taking problem 1,2 into consideration, my goal is to ascertain whether a query needs performance tuning, not actual performance/load testing in which case the buffer cache would come into the picture
  • From my understanding the cache contains pages in 8KB blocks, and it is just getting off the cache as in a quick read from memory. Is my understanding correct here?
Community
  • 1
  • 1
Greedy Coder
  • 1,256
  • 1
  • 15
  • 36
  • Does `SQL_NO_CACHE` also disable the file system cache of the operating system on MySQL? –  Oct 21 '15 at 14:58
  • Nope it doesn't.It just disables caching from MySQL's side.But in a way using that to understand query time is flawed too. – Greedy Coder Oct 21 '15 at 14:59
  • Well then you had the same problem there: the first query will read the data into the cache of the file system, the second will get it from the cache. –  Oct 21 '15 at 15:01
  • Yeah thats true, apparently, I came in during the migration to PgSQL and was curious that the whole thing is flawed,but since we are moving to PGSQL anyway, I was curious if there was any established of doing this in Postgres.Maybe ill update the question as it seems to directly compare with MySQL – Greedy Coder Oct 21 '15 at 15:03
  • Why do you want to measure something that you don't know how to measure? – wildplasser Oct 21 '15 at 15:08
  • 1) I want to measure so that if the query needs any tuning say it takes 200ms without the cache under consideration 2) Thats the point of the question.How to measure? – Greedy Coder Oct 21 '15 at 15:10
  • 1
    I don't understand the whole thing. Caching is one of the most important aspects in tuning a database server. In reality you would never run without a decent cache in production. If you "disable" the Postgres cache (e.g. by setting `shared_buffers` to a ridiculously low number) you also influence the optimizer. The plan that the optimizer chooses in this situation will be greatly different than the one chosen if there _is_ a cache e.g. without a cache the planner will prefer a full table scan over an index scan –  Oct 21 '15 at 17:13
  • @a_horse_with_no_name : I completely agree, but my situation is something like [this](http://stackoverflow.com/questions/1216660/see-and-clear-postgres-caches-buffers).A query takes 10 seconds on the first run, and when its cached it takes around 1.5 seconds.So if i take the average of say four runs - avg comes to around 3.6 seconds which is not.My concern is to reduce the 10 second runtime in the first place not the cached time. – Greedy Coder Oct 22 '15 at 08:03
  • If you want the performance of the cached query right after e.g. starting Postgres, you might want to look into the module pg_prewarm to "warm up" the cache after a restart: http://www.postgresql.org/docs/current/static/pgprewarm.html –  Oct 22 '15 at 08:04

1 Answers1

0

You can set lowest possible value for cache to reduce cache effects

You also can stop pg an run echo 3 /proc/sys/vm/drop_caches to remove os cache

But there is no need to real cache Your test environment should be nearest possible conditions to production and in prod u definitely have cache! It's better to compare different ways of getting a query or different ways of designing your schema and choose the best

Ehsan Mohammadi
  • 111
  • 8