Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1072413
  • 博文数量: 380
  • 博客积分: 2147
  • 博客等级: 大尉
  • 技术积分: 3655
  • 用 户 组: 普通用户
  • 注册时间: 2012-02-06 10:47
文章分类

全部博文(380)

文章存档

2019年(1)

2017年(31)

2016年(15)

2015年(17)

2014年(182)

2013年(16)

2012年(118)

我的朋友

分类: Mysql/postgreSQL

2019-01-24 17:10:47

原文地址:PostgreSQL之analyze监控 作者:Bean_lee


   最近一段时间,自己比较懒惰,自己老婆快生了,自己各种压力,加上照顾老婆,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很有学问的一个分支,这我不细说,这不是本文的主题,因为展开讲的话,这个可不是一篇博客可以讲清楚的,我后面的博客应该会讲到,但是急性子的筒子,可以看Explaining the Unexplainable系列文章还有官方文档How the Planner Uses Statistics。决策由代价决定,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。 
  1. import os
  2. import time
  3. import commands
  4. import sys

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



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

  9.         for record in current_output.split('\n'):
  10.                 tmp = record.split("|")
  11.                 if len(tmp) == 2:
  12.                         key = tmp[0].strip()
  13.                         value = tmp[1].strip()
  14.                         if key not in mydict:
  15.                                 print "%-40s|%-30s|%-30s" % (key, "", value)
  16.                                 print "%-40s+%-30s+%-30s" % ('-'*40, '-'*30, '-'*30)
  17.                                 mydict[key] = value
  18.                         if key in mydict and mydict[key] != value:
  19.                                 print "%-40s|%-30s|%-30s" % (key, mydict[key], value)
  20.                                 print "%-40s+%-30s+%-30s" % ('-'*40, '-'*30, '-'*30)
  21.                                 mydict[key] = value

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

  25. def monitor_analyze_forever():
  26.         print_title()
  27.         while True:
  28.                 check_loop()
  29.                 time.sleep(30)

  30. def monitor_analyze_once():
  31.         print_title()
  32.         check_loop()
  33.         


  34. if __name__ == "__main__":
  35.         input = sys.argv
  36.         if len(input) > 1 and input[1] == 'single':
  37.                 monitor_analyze_once()
  38.         else:
  39.                 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都显示出来了.

阅读(5523) | 评论(0) | 转发(0) |
0

上一篇:3. Git与TortoiseGit基本操作

下一篇:没有了

给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册