Thread: Postgresql 9.2, Memoy cache usage.

Postgresql 9.2, Memoy cache usage.

From
"Cristian Iturrieta"
Date:

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 | www.bbr.cl

 

Attachment

Re: Postgresql 9.2, Memoy cache usage.

From
Gabriel Sánchez
Date:
On Tue, Dec 16, 2014 at 4:18 PM, Cristian Iturrieta <citurrieta@bbr.cl> wrote:

Dear pgadmin-hackers,

 

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


Hi Cristian.  Since your question concerns the PostgreSQL database and not the pgAdmin client (they are separate efforts), it would be more appropriately asked in the Postgres General list: <pgsql-general@postgresql.org>.  I believe you will have greater success getting answers there.
 

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 | www.bbr.cl

 

Attachment