Обсуждение: questions regarding shared_buffers behavior
Question regarding the operation of the shared_buffers cache and implications of the pg_X_stat_tables|pg_X_stat_indexes stats.
( I am also aware that this is all complicated by the kernel cache behavior, however, if, for the purpose of these questions, you wouldn’t mind assuming that we don’t have a kernel cache, and therefore just focus on the behavior of the db cache as an isolated component, it will help – thanks in advance).
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?
Are there any particular types of *table* access operations that will cause postgres to choose not to retain the table pages in shared_buffers at the end of the operation?
In particular, the activity tracked by:
- Seq_scan
- Seq_tup_read
- Idx_tup_read
- Idx_tup_fetch
Are there any particular types of *index* access operations that will cause postgres to choose not to retain the index pages in shared_buffers at the end of the operation?
In particular, the activity tracked by:
- idx_scan
- Idx_tup_read
- Idx_tup_fetch
Mark Rostron wrote: > > 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? > There is no such procedure. When a table or index page is used, its usage count goes up, which means it's more likely to stay in the cache for longer afterwards. Processing trying to allocate pages are constantly circling the buffer cache looking for pages where the usage count is at 0 to re-use. 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. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
> > > > 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 buffercache 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 interestedin. I don't suppose you would be able to tell me how large that re-useable area might be? Now, with regard to the behavior of table sequential scans: do the stat values in seq_scan and seq_tup_read reflect actualbehavior. 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? Obviously, the reason I am asking this is that I am noticing high machine io levels that would only result from sequentialscan activity. The explain output says otherwise, but the seq_scan stat value for the table kinda correlates. Hence my enquiry. Thanks in advance. Mr
2010/11/8 Mark Rostron <mrostron@ql2.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 buffercache 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 interestedin. > 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 actualbehavior. > 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 sequentialscan 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
On Sun, Nov 7, 2010 at 10:03 PM, Cédric Villemain <cedric.villemain.debian@gmail.com> wrote: > 2010/11/8 Mark Rostron <mrostron@ql2.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 buffercache 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 interestedin. >> 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 Note that there is a different, higher limit for the "bulk write" strategy when using COPY IN or CTAS. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company