Thread: Disk caching

Hi to all.

I am trying to see how PostgreSQL performance changes on the basis of 
work_mem. So, I am going to execute the 22 queries of TPCH 
(http://www.tpc.org/tpch/) again and again, each time for a different 
value of work_mem.
Since I am interested just in work_mem variations, I should prevent each 
query to take advantages from revious executions of the 22 queries them 
selves. For example, taking cache advantages. So, taking into account 
that the 22 queries are those http://pastebin.com/7Dg50YRZ and are 
executed on tables of hundreds of MB and

1) Is it sufficient to run change the values of work_mem through psql 
and running the queries again without restarting postgres?

2) Or, should I restart postgres?

3) Or, shoud I restart the machine each time I execute the 22 queries?

Thanks for your time.
Regards.

Manolo.

mac_man2008@yahoo.it wrote:
> Since I am interested just in work_mem variations, I should prevent 
> each query to take advantages from revious executions of the 22 
> queries them selves. For example, taking cache advantages.

work_mem has nothing to do with disk caching; it controls whether larger 
sorts are done using disk or memory.  The main parameter that controls 
the size of the disk cache is shared_buffers.  If you found the 
descriptions in the manual confusing, 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server is another 
guide to what the settings mean that may help you out.

If you want to eliminate caching effects from your test, you will need 
to both restart the database server and flush the operating system 
cache.  You can do this easily on Linux:  
http://linux-mm.org/Drop_Caches but it's not as simple to do on most 
other platforms without rebooting.

P.S. The right list for this discussion if you want to talk about it 
more is pgsql-performance.  This one is focused at things involving 
PostgreSQL development, not questions related to using the database.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us