From: | Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com> |
---|---|
To: | Mark Rostron <mrostron(at)ql2(dot)com> |
Cc: | Greg Smith <greg(at)2ndquadrant(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: questions regarding shared_buffers behavior |
Date: | 2010-11-08 03:03:37 |
Message-ID: | AANLkTinYy00+PaDCTdhq0urGJdJtiKUAFPmBdiC3t5Br@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2010/11/8 Mark Rostron <mrostron(at)ql2(dot)com>:
>> >
>> > What is the procedure that postgres uses to decide whether or not a
>> > table/index block will be left in the shared_buffers cache at the end
>> > of the operation?
>> >
>>
>> The only special cases are for sequential scans and VACUUM, which use continuously re-use a small section of the buffer cache in some cases instead.
>
> Thanks - the part about sequential scans and the re-use of a small section of shared_buffers is the bit I was interested in.
> I don't suppose you would be able to tell me how large that re-useable area might be?
There are 256KB per seqscan and 256KB per vacuum.
I suggest you to go reading src/backend/storage/buffer/README
>
> Now, with regard to the behavior of table sequential scans: do the stat values in seq_scan and seq_tup_read reflect actual behavior.
> I assume they do, but I'm just checking - these would be updated as the result of real I/O as opposed to fuzzy estimates?
They represent the real stat for hit/read from shared_buffers, *not*
from OS buffers.
Getting real statistic from OS has a cost because postgresql don't use
(for other reason) mmap to get data.
>
> Obviously, the reason I am asking this is that I am noticing high machine io levels that would only result from sequential scan activity
You may want to start inspect your postgresql buffer cache with the
contrib module pg_buffercache.
http://www.postgresql.org/docs/9.0/static/pgbuffercache.html
Then if it is not enough you can inspect more precisely your OS cache
with pgfincore but it migh be useless in your situation.
http://villemain.org/projects/pgfincore
> The explain output says otherwise, but the seq_scan stat value for the table kinda correlates.
Starting with 9.0, the contrib module pg_stat_statements provide a lot
of information about buffer access (from shared buffers usage, but
still very valuable information) you should have a look at it if you
have such postgresql version installed.
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
From | Date | Subject | |
---|---|---|---|
Next Message | shaiju.ck | 2010-11-08 06:16:56 | Select * is very slow |
Previous Message | Marti Raudsepp | 2010-11-08 02:35:46 | Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1? |