Re: Postgresql 9.2, Memoy cache usage.

Dear pgadmin-hackers,

 

   I have the following query: What could be due to a single query sql one day run fast and another slow.

The database has massive loads overnight and the next day serves datawarehouse. This causes the first query is slower by having to go find all the information to disk. With a new process is achieved load data from disk cache operating system that is able to improve response times. I have doubts as to refresh cache and as the LRU (least recently used) works. One way to optimize the query was generating "Common Table Expressions", here is my other question, where are CTE's stored?, Work_mem?, Buffer cache?, Temporary tablespace ?, etc ..

 

 

Deputy some parameter values postgresql :

 

max_connections = 40

checkpoint_completion_target = 0.9

random_page_cost=20

seq_page_cost = 20

default_statistics_target = 100 # pgtune wizard 2014-11-20

maintenance_work_mem = 1GB # pgtune wizard 2014-11-20

constraint_exclusion = on # pgtune wizard 2014-11-20

effective_cache_size = 30GB # pgtune wizard 2014-11-20

work_mem = 800MB # pgtune wizard 2014-11-20

wal_buffers = 32MB # pgtune wizard 2014-11-20

checkpoint_segments = 64 # pgtune wizard 2014-11-20

shared_buffers = 5GB # pgtune wizard 2014-11-20

 

 

Summary charging procedure cache operating system:

 

select distinct relfilenode from pg_buffercache > /tmp/cacheprecarga.lst

 

cat /tmp/cacheprecarga.lst | while read line; do find /var/lib/pgsql/9.2/data/base/ -name $line -print -exec cat '{}' > /dev/null \; ; done

 

 

 

 

 

regards

 

Cristian Iturrieta Olivares | Área Ingeniería

CONSULTOR
Luis Thayer Ojeda Nº 1145

Providencia - Santiago de Chile
( +56 2 28404250 | www.bbr.cl