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
BBR_logotipo
Cristian Iturrieta Olivares | Área Ingeniería
CONSULTOR
Luis Thayer Ojeda Nº 1145
Providencia - Santiago de Chile
( +56 2 28404250 | <http://www.bbr.cl> www.bbr.cl
