PostgreSQL源码分析之shared buffer状态信息及性能测量

   在前面提到过shared_buffers是可以配置的，也给出了配置方法，但是配置多大才合理。网上资料众说纷纭，有的说总RAM的10%～15%，有的说是30～45%,有的很具体，说8G内存情况下512M是合理的。本文通过shared buffer相关的统计量和状态信息，希望可以加深对shared buffer的理解，同时给出大家一点启发，如果设定shared buffer的大小。
   PostgreSQL提供了pg_buffercache来帮助我们获取shared buffer的状态，这个扩展展示了每一个shared buffer的信息。
   首先是安装，首先要编译contrib/pg_buffercache目录下的内容，我比较懒，将整个contrib都编译了。然后通过执行：  

    create extension pg_buffercache ;

就可以生成这个pg_buffercache这个视图（view）了。这个pg_buffercache给出了所有8192个8KB buffer的相关信息：
   
   利用这个pg_buffercache，可以得到很多的有用的统计信息：比如按照dirty和usagecount进行分类，统计下shared buffers中相关信息

        select usagecount,count(*),isdirty
        from pg_buffercache
        group by isdirty, usagecount
        order by isdirty, usagecount ;

    输出如下：（下面是默认1024个shared buffer时的统计信息）：

               usagecount | count | isdirty
              ------------+-------+---------
                        0 |    119| f
                        1 |     84| f
                        2 |     91| f
                        3 |     11| f
                        4 |    131| f
                        5 |    360| f
                        0 |     41| t
                        1 |     86| t
                        2 |     44| t
                        3 |     22| t
                        4 |     16| t
                        5 |     19| t
              (12 rows)

   Greg Smith大神在slides中指出，如果大量的share buffer的usagecount都是4或者5,那么这表明你的shared buffer不够多，你应该扩大你的shared buffer的个数。
   如果你的shared buffer大多数的usagecount是0或者1, 你就可以尝试减少你的shared buffer，具体减少到多少，可以分析缓存换粗命中率。
   OK终于到了缓存命中率。shared buffer本质也是缓存，既然是缓存，分析缓存性能的指标不外乎就是命中率。何为命中率呢？
   如果你访问relation的某个8K block，你调用BufferAlloc，发现你要找的block正好在shared buffers中（hash查找中找到了对应的block），这叫命中，英文叫做hit，如果你要找的block不在shared buffer中，这叫缺失 ，英文为miss（注意，对于PostgreSQL，miss虽然会发生read，可是不一定发生磁盘读写，因为还有OS的cache，也许落在了OS的cache中，从而不需要触发磁盘IO）。衡量缓存设计的优劣的标准不外乎就是high hit，and low miss。如何查看当前数据库的命中情况呢？
   PostgreSQL提供了一些统计视图pg_stat_database,里面有好多统计信息，今天对我们关系的就是blks_read和blks_hit,顾名思义，blks_reads表示没命中，miss，需要read的那些alloc，而blks_hit表示命中的那些Alloc。
   命中率 = hit/（hit+miss）

    select     blks_read,blks_hit,
               blks_hit::numeric / (blks_read + blks_hit ) as ratio
    from       pg_stat_database
    where      datname = 'manu_db' ;

   输出如下图所示：   
       
    这个基本表达了当前数据库miss 和 hit情况，计算了命中率，但是这个包括了PostgreSQL系统表的一些信息，并且统计信息略显粗略，我们可以更具体的查看：    这就需要用到了pg_statio_user_tables，这个也是系统提供统计信息的一个view，废话不多说：  

SELECT (sum(heap_blks_hit) + sum(heap_blks_read)) AS TABLE_CNT,
         round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 ,3)as TABLE_RATIO,
         (sum(idx_blks_hit) + sum(idx_blks_read)) AS INDEX_CNT,
         round(sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read)) * 100,3) as INDEX_RATIO,
         (sum(toast_blks_hit) + sum(toast_blks_read)) AS TOAST_CNT,
         round(sum(toast_blks_hit) / (sum(toast_blks_hit) + sum(toast_blks_read)) * 100,3) as TOAST_RATIO,
         (sum(tidx_blks_hit) + sum(tidx_blks_read)) AS TOASIDX_CNT ,
         round(sum(tidx_blks_hit) / (sum(tidx_blks_hit) + sum(tidx_blks_read))*100,3) as TOASTIDX_RATIO
FROM pg_statio_user_tables ;

    输出如下：
   
   当然了，某些清空下，用户表中并没有idx或者toast，上述语句可能会报错division by zero。去除相应的统计即可。输出的含义很明显了，就是分别统计table，inx等各个部分的命中率。
   有些时候，我们不仅关心总的命中率，还关心各个relation各自的命中率情况，我们依然用pg_statio_user_tables,只不过，我们不再累加了，如下面的例子： 

  SELECT relname, heap_blks_read, heap_blks_hit,
         round(heap_blks_hit::numeric/(heap_blks_hit + heap_blks_read), 3)
  FROM   pg_statio_user_tables
  WHERE  heap_blks_read > 0
  ORDER  BY 2 DESC ;

      输出如下：
    
   当然了，如果需要index的统计，只需要将上面sql中的heap替换idx即可。

   最后，很多资料说这个缓存命中率不可低于99%,如果低于了99%，表明，cache效率太低了，需要增大shared buffer。总之了，当你的shared buffer命中率太低，比如60%，基本就需要检查下你配置的shared buffers是否太小了，导致你cache利用率如此之低。