DB‎ > ‎Postgres‎ > ‎

Useful SQLs to check contents of PostgreSQL shared_buffer

Thank you for visiting this page, this page has been update in another link Useful SQLs to check contents of PostgreSQL shared buffer
In another article
https://sites.google.com/site/itmyshare/database-tips-and-examples/postgresql-buffering
I discussed overall postgreSQL buffering situation, if you want to check detail, here are some useful SQLs for sharing.

Of course, pg_buffercache has to be insalled.

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; 

#1 This SQL show you the relations buffered in database share buffer, ordered by relation percentage taken in shared buffer. It also shows that how much of the whole relation is buffered.
 
select c.relname,pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) / ( select setting from pg_settings where name='shared_buffers')::integer,1) as buffer_percent, round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation 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()) group by c.oid,c.relname order by 3 desc limit 10;

        relname         | buffered | buffer_percent | percent_of_relation
------------------------+----------+----------------+---------------------
 t_acl                  | 1869 MB  |           60.8 |               100.0
 t_inodes_pkey          | 128 MB   |            4.2 |                 9.8
 t_dirs                 | 129 MB   |            4.2 |                 3.3
 t_locationinfo_pkey    | 95 MB    |            3.1 |                 3.6
 i_t_acl_rs_id          | 96 MB    |            3.1 |                 8.3
 i_dirs_ipnfsid         | 86 MB    |            2.8 |                 5.1
 t_dirs_pkey            | 85 MB    |            2.8 |                 3.2
 t_inodes_checksum_pkey | 74 MB    |            2.4 |                 6.5
 t_inodes               | 64 MB    |            2.1 |                 2.1
 t_tags_pkey            | 63 MB    |            2.1 |                 6.1


#2 relation usage count in PostgreSQL database shared buffer
select c.relname,count(*) as buffers,usagecount 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()) group by c.relname,usagecount order by c.relname,usagecount;

relname        | buffers | usagecount

---------------+---------+------------

i_dirs_iparent | 1047    | 0

i_dirs_iparent | 1620    | 1

i_dirs_iparent | 471     | 2

i_dirs_iparent | 389     | 3

i_dirs_iparent | 194     | 4

i_dirs_iparent | 110     | 5


#3 disk usage

select nspname,relname,pg_size_pretty(pg_relation_size(c.oid)) as "size" from pg_class c left join pg_namespace n on ( n.oid=c.relnamespace) where nspname not in ('pg_catalog','information_schema') order by pg_relation_size(c.oid) desc limit 30;

 nspname |          relname          |  size  
---------+---------------------------+---------
 public  | t_access_latency          | 5263 MB
 public  | t_locationinfo            | 3919 MB
 public  | t_dirs                    | 3880 MB
 public  | t_inodes                  | 3087 MB
 public  | t_level_2                 | 2906 MB
 public  | t_dirs_pkey               | 2683 MB
 public  | t_locationinfo_pkey       | 2678 MB


#4 top relation in cache
select c.relname,count(*) as buffers 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()) group by c.relname order by 2 desc limit 20;
         relname         | buffers
-------------------------+---------
 t_acl                   |  239219
 t_inodes_pkey           |   16417
 t_dirs                  |   16391
 i_t_acl_rs_id           |   12160
 t_locationinfo_pkey     |   11666
 t_dirs_pkey             |   11223
 i_dirs_ipnfsid          |   10736
 t_inodes_checksum_pkey  |    9231

#5 summary of buffer usage count

 usagecount | count  | isdirty
------------+--------+---------
          0 |  24075 | f
          1 | 262086 | f
          2 |  23744 | f
          3 |  23788 | f
          4 |  24135 | f
          5 |  19386 | f
          1 |   1971 | t
          2 |   1206 | t
          3 |   1128 | t
          4 |   4209 | t
          5 |   7488 | t

#6 lock information in postgreSQL

select locktype,virtualtransaction,transactionid,nspname,relname,mode,granted,cast(date_trunc('second',query_start) as timestamp) as query_start, substr(current_query,1,60) as query from pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) left outer join pg_namespace on (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity where not pg_locks.pid=pg_backend_pid() and pg_locks.pid = pg_stat_activity.procpid order by virtualtransaction;





Comments