PostgreSQL之analyze监控

   最近一段时间，自己比较懒惰，自己老婆快生了，自己各种压力，加上照顾老婆，PostgreSQL源码阅读工作陷于停滞阶段。但是自己PostgreSQL的功力还是有增长的。最近在监控线上系统的数据库操作，分析比较慢的查询，找到优化SQL或者提升PostgreSQL性能的途径。总体来说，真刀真枪的搞，stackoverflow，blog，官方文档看了不少，对DB的查询计划有了初步的理解，这也吸引我开始看查询计划，查询优化部分的代码。
   analyze是获取统计信息。PostgreSQL中有张pg_statistics的系统表，记录着用户各个表的统计信息，输出不友好，不是给人看的，是给PostgreSQL另外提供pg_stats表，可以查看用户DB里面各个relation各个字段上的统计信息。一条SQL的执行，可能有多种方法，条条大路通罗马，是用串行扫描Seq Scan还是Index Scan还是Bitmap Heap Scan，对于SQL中的union是用Nested Loop还是使用Hash Join，这需要PostgreSQL决策。暂时看不懂这些术语也没关系，毕竟我也只是看了几篇博客，看了点官方文档，自己理解的也不深。总之了PostgreSQL需要评估代价，Rows Estimation是PostgreSQL很有学问的一个分支，这我不细说，这不是本文的主题，因为展开讲的话，这个可不是一篇博客可以讲清楚的，我后面的博客应该会讲到，但是急性子的筒子，可以看系列文章还有官方文档。决策由代价决定，PostgreSQL根据代价，选择代价小的执行计划。那么代价又是在怎么计算的呢？根据统计信息。统计信息提供的越准确，那么代价估算的越准确，那么选择的执行计划就越合理。
   analyze就起到一个更新统计信息的作用，我最近优化一个SQL，优化前采用Nested Loop，花费时间是6秒多，analyze之后，stat信息得到更新，PostgreSQL采用了更加合理的Hash Join来处理SQL中的union，花费的时间降到了560ms。优化前，之所以选择了错误的执行计划，就是因为长时间没有执行analyze，统计信息不够新。由此可见，合理的analyze频率是很关键的。随着relation中数据集合的变化，postgres会自动执行analyze，PostgreSQL是如何触发autoanalyze的呢？
   
   这是vacumm相关的配置参数，除了analyze，还有vacuum相关的，我们不关心，我们目前只关心ANALYZE。  
   何时发生auto analyze？  
   假设A为update的tuples计数值,B 为delete的tuples计数值 ,C为insert的tuples的计数值 .那么
   （A+B+C）>(0.1*tuples+50)时，将会发生自动autoanalyze。
   网易的德哥有篇文章how many tuples updated and or deleted will trigger auto vacuum or analyze非常厉害，实验证明了这个公式。根据这个公式，可以很清楚的知道，数据表比较小的时候，auto anaylze会比较频繁，数据表越大，0.1×tuples的值就越大，就越难trigger auto analyze。当然，这个参数是可以配置的，具体配置成多少，自己根据业务需要配置。对于我们公司的产品，数据库的autovacuum_analyze_scale_factor默认配置成0.1是不合适的，具体原因是业务层面的东西，我就不多唧唧歪歪了。
   如何获取某用户表的何时进行的analyze呢？pg_stat_user_tables中有个字段叫last_autoanalyze,当然还有字段叫last_analyze,记录的是上一次手动ANALYZE的时间。
   
   当然了我这张图不是auto analyze，属于manual analyze。如何trigger auto analyze，可以看德哥的PostgreSQL daily maintenance-vacuum，触发auto analyze的例子写的非常详细，不所说。我们虽然能够获取到上一次的时间，但是很不幸无法获取到每一次的时间，尤其是根据业务模型，你修改了autovacuum_analyze_scale_factor的时候，你可能比较希望关注每一次的analyze time，判断是否满足业务需求。我写了脚本monitor这件事，每发生一次，添加一笔记录，也可以只执行1次，查看所有表的last auto analyze time。 

import os
import time
import commands
import sys

cmd = '/usr/bin/psql XXXXX -t -c "select relname, last_autoanalyze from pg_stat_user_tables" |sort'
mydict = {}



def check_loop():
        current_output = commands.getoutput(cmd)

        for record in current_output.split('\n'):
                tmp = record.split("|")
                if len(tmp) == 2:
                        key = tmp[0].strip()
                        value = tmp[1].strip()
                        if key not in mydict:
                                print "%-40s|%-30s|%-30s" % (key, "", value)
                                print "%-40s+%-30s+%-30s" % ('-'*40, '-'*30, '-'*30)
                                mydict[key] = value
                        if key in mydict and mydict[key] != value:
                                print "%-40s|%-30s|%-30s" % (key, mydict[key], value)
                                print "%-40s+%-30s+%-30s" % ('-'*40, '-'*30, '-'*30)
                                mydict[key] = value

def print_title():
        print "%-40s %-30s %-30s" % ("tablename", "last_autoanalyze", "current_autoanalyze")
        print "%-40s+%-30s+%-30s" % ('-'*40, '-'*30, '-'*30)

def monitor_analyze_forever():
        print_title()
        while True:
                check_loop()
                time.sleep(30)

def monitor_analyze_once():
        print_title()
        check_loop()



if __name__ == "__main__":
        input = sys.argv
        if len(input) > 1 and input[1] == 'single':
                monitor_analyze_once()
        else:
                monitor_analyze_forever()

    将XXXXX改成自己DB的name就行了。带single参数，表示只查看，不monitor，否则moniotor，没发生一次analyze，就插入一条记录。这个是检查周期，默认是1分钟，每一分钟检查一次（A+B+C）>(0.1*tuples+50).可以配置:
   
   我默认sleep 30s,是不合适的,更好的方法是获取naptime,根据naptime,确定sleep的时间,我就不改了.
   输出如下:
   
     为了信息安全,我输出的是系统表,而不是用户表的auto analyze信息,为了计算两次analyze的间隔,我把上一次和这一次analyze time都显示出来了.