2

I'm using a docker postgres image, and for a perfomance measure I want to clear the cache. First of all I run this command:

docker run -it --rm postgres:9.6.4-alpine -c "sync && echo 3> /proc/sys/vm/drop_caches"

Then:

kubectl delete pod db

After that I restart the pod.I execute this query:

explain (analyze, buffers) select e.name
from table1 as e, table2 as prov
where e.provider_id = prov.id 
and e.status = true and prov.status = true 
and to_tsvector('simple', e.name) @@ to_tsquery('simple', 'a:*');

I got this query plan:

Hash Join  (cost=187.59..1114.19 rows=2727 width=30) (actual 
time=2.291..4.432 rows=3174 loops=1)
  Hash Cond: (e.provider_id = prov.id)
  Buffers: shared hit=77 read=121
  ->  Bitmap Heap Scan on table1 e  (cost=46.15..934.90 rows=2819 width=34) (actual time=0.619..2.086 rows=3180 loops=1)
        Recheck Cond: (to_tsvector('simple'::regconfig, (name)::text) @@ '''a'':*'::tsquery)'
        Filter: status
        Rows Removed by Filter: 62
       Heap Blocks: exact=138
        Buffers: shared hit=52 read=94
        ->  Bitmap Index Scan on table1_name_index  (cost=0.00..45.45 rows=2860 width=0) (actual time=0.603..0.603 rows=3242 loops=1)'
              Index Cond: (to_tsvector('simple'::regconfig, (name)::text) @@ '''a'':*'::tsquery)
              Buffers: shared hit=1 read=7
  ->  Hash  (cost=90.84..90.84 rows=4048 width=4) (actual time=1.639..1.639 rows=4048 loops=1)
        Buckets: 4096  Batches: 1  Memory Usage: 175kB
        Buffers: shared hit=22 read=27
        ->  Seq Scan on table2 prov  (cost=0.00..90.84 rows=4048 width=4) (actual time=0.005..1.037 rows=4048 loops=1)
              Filter: status
             Rows Removed by Filter: 136
              Buffers: shared hit=22 read=27
Planning time: 0.530 ms
Execution time: 4.600 ms

I don't understand why does the query uses the memory cache.Does the command doesn't clear the cache totalllt.if it is not the case, how can I flush the memory cache or it's not possible to do that?

Slim
  • 5,527
  • 13
  • 45
  • 81
  • 1
    That doesn't make sense. The cache plays a vital part in the performance of a database. Trying to "measure" performance without the cache is pretty much useless. –  Nov 27 '17 at 08:27
  • https://linux-mm.org/Drop_Caches *This is a non-destructive operation and will only free things that are completely unused. Dirty objects will continue to be in use until written out to disk and are not freeable. If you run "sync" first to flush them out to disk, these drop operations will tend to free more memory.* free more memory probably not includes flushing PG buffer cache?.. – Vao Tsun Nov 27 '17 at 08:31
  • @a_horse_with_no_name I want to measure the first execution time of query without using memory cache.Because using cache give a an execution time shorter and this did not reflect the original performance. – Slim Nov 27 '17 at 09:54
  • @VaoTsun so it is not possible to clear the memory of PG? – Slim Nov 27 '17 at 09:55
  • 2
    I dont remember such tool - only to look into buffer cache. And regarding your "original performance" - the benefit of working with not cached performance is questionable - because in real life it will be cached on the very first execution and furter most probably reused – Vao Tsun Nov 27 '17 at 09:57
  • So you only run that query once right after the server is started? –  Nov 27 '17 at 10:09
  • @VaoTsun I know that the cache is useful for performance but I'm using know a dev database and I want to know if the first execution of query gives me a query runtime greater than 1s because when I reexecute the query I got a time of 35msec.That's why I want to clear the cache to get the first execution time to compare the execution time of two queries.One contains a join then I added a new join with another table to the first query. I want to know if this will affect a lot the execution time. – Slim Nov 27 '17 at 10:10
  • @a_horse_with_no_name yes – Slim Nov 27 '17 at 10:10
  • You might want to look into [pg_prewarm](https://www.postgresql.org/docs/current/static/pgprewarm.html) to fill the cache right after startup. See also [here](http://raghavt.blogspot.de/2014/06/utilising-caching-contribs-pgprewarm.html) and [here](https://www.enterprisedb.com/blog/hibernating-and-restoring-postgres-buffer-cache) for more information on that module –  Nov 27 '17 at 10:12
  • Normally, people would repeat the query a few times (with different search parameters) until the response stabilizes. The *cold* execution time measures nothing (except footprint * disk-bandwidth). – joop Nov 27 '17 at 10:13
  • @para008 pages are cached, not queries. if you add join that will be used once - you will affect 1 second time, so no worries on affecting more. and if same join will be reused, you should measure impact with cache, not without it – Vao Tsun Nov 27 '17 at 10:13
  • https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7413988573867 It's for oracle, not postgres, but idea is same – Vao Tsun Nov 27 '17 at 10:16

0 Answers0