DB‎ > ‎Postgres‎ > ‎

PostgreSQL database buffer cache and OS cache

Thank you for visiting this page, this page has been update in another link PostgreSQL database buffer cache and OS cache
Unlike many traditional database products, PostgreSQL does not assume or even prefer that the majority of the memory on the system be allocated for its use. Most reads and writes from the database are done using standard operating system calls that allows the operating system's cache to work in its usual fashion. Only few exception, for example WAL in some configuration.
In PostgreSQL, there are two layers, PostgreSQL shared buffers and OS Page cache. Lots of articles talked about how they work and how to tune them, here I'd rather to just share with you how to get information from both layers. Tune the sizes of them according to your system.

To get information, there are two extensions need to be installed.

pg_buffercache
Using pg_buffercache, you can look inside the current contents of the PostgreSQL shared_buffers database cache. It's one of optional contrib modules available that ships with PostgreSQL. A introduction documentation to pg_buffercache is at 
http://www.postgresql.org/docs/current/static/pgbuffercache.html
Installation is simple:

    postgres=# CREATE EXTENSION pg_buffercache; 

pgfincore

A set of functions to handle low-level management of relations using mincore to explore cache memory in OS.
More info is in the project README file, with examples, very handy. Project link:
http://pgfoundry.org/projects/pgfincore/

      Installation(this installation needs postgresql-devel module):  
  1. Download the latest version: http://pgfoundry.org/frs/download.php/3186/pgfincore-v1.1.1.tar.gz
  2. As root user:  
  3. export PATH=/usr/local/pgsql91/bin:$PATH     //Set the path to point pg_config.
  4. tar -xvf pgfincore-v1.1.1.tar.gz  
  5. cd pgfincore-1.1.1  
  6. make clean  
  7. make   
  8. make install 
  9. Now connect to PG and run below command
  10. postgres=# CREATE EXTENSION pgfincore; 

Other than using external languages like perl or python to put information together, I made it by myself using SQL only, it's a long SQL, but doesn't cost much time to run. It tells you how much data buffered in database share_buffer, how much data buffered in OS cache, how much data the relation has and the percentage of data buffered in shared_buffer and OS cache.

select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered, round(100.0 * count(*) / ( select setting from pg_settings where name='shared_buffers')::integer,1) as pgbuffer_percent, round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,( select round( sum(pages_mem) * 4 /1024,0 ) from pgfincore(c.relname::text) ) as os_cache_MB , round(100 * ( select sum(pages_mem)*4096 from pgfincore(c.relname::text) ) / pg_table_size(c.oid),1) as os_cache_percent_of_relation,pg_size_pretty(pg_table_size(c.oid)) as rel_size from pg_class c inner join pg_buffercache b on b.relfilenode=c.relfilenode inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database() and c.relnamespace=(select oid from pg_namespace where nspname='public')) group by c.oid,c.relname order by 3  desc limit 30;

    relname    | pg_buffered | pgbuffer_percent | percent_of_relation | os_cache_mb | os_cache_percent_of_relation |  rel_size 
---------------+-------------+------------------+---------------------+-------------+------------------------------+------------
 t_acl         | 2326 MB     |             75.7 |               100.0 |          29 |                          1.2 | 2326 MB
 t_dirs        | 209 MB      |              6.8 |                 5.0 |        2159 |                         52.2 | 4134 MB
 t_inodes_pkey | 70 MB       |              2.3 |                 4.5 |        1219 |                         78.3 | 1557 MB
 t_dirs_pkey   | 58 MB       |              1.9 |                 1.7 |        1084 |                         32.3 | 3354 MB
 i_t_acl_rs_id | 52 MB       |              1.7 |                 3.8 |        1045 |                         76.7 | 1363 MB
 i_dirs_ipnfsid| 52 MB       |              1.7 |                 2.4 |        1373 |                         63.1 | 2176 MB


Where:
pg_buffered             stands for how much data buffered in PostgreSQL buffer cache
pgbuffer_percent      stands for  pg_buffered/total_buffer_size *100
percent_of_relation   stands for pg_buffered/total_relation_size * 100
os_cache_mb          stands for how much a relation cached in OS

I guess other two columns not need to mention.
As a DBA, once you get to know how your production data cached, you should know where to start to tune.

Here are only thumb of rules:

Don't give all the RAM to the OS, the PostgreSQL shared buffer cache can do better than the OS is the way it keeps a usage count of buffers. OS typically use some form of LRU algorithm which will give any buffer one or two chances before that data is evicted. Most important, you lose track of io statistics.

Also, someone says that "Don't make the shared buffer cache too large, OS cache is being used anyway for reads and writes, it's extremely likely that there's going to be some wasted overlap there", but I'd like to see, from my experience, not used data evicted out of memory for long run, there is not much data overlapped in both buffer area.
It's still worth to have some OS cache for postgreSQL, for better i/o purpose.

Everything mentioned above has been tested for postgresql91-9.1.9 and 9.2.4 on Redhat 6 or SL6. Other linux kinds of platform should work same way.Drop me an e-mail if you see problem.




Comments