Re: Postgresql 9.2, Memoy cache usage.

On Tue, Dec 16, 2014 at 4:18 PM, Cristian Iturrieta <citurrieta(at)bbr(dot)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(at)postgresql(dot)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
>
>
>
> [image: BBR_logotipo]
>
> *Cristian Iturrieta Olivares **| Área Ingeniería*
>
>
> *CONSULTOR **Luis Thayer Ojeda Nº 1145*
>
>
> *Providencia - Santiago de Chile **(** +56 2** 2**8404250 | www.bbr.cl
> <http://www.bbr.cl>*
>
>
>