PostgreSQL之analyze监控

栏目: 数据库 · PostgreSQL · 发布时间: 4年前

最近一段时间,自己比较懒惰,自己老婆快生了,自己各种压力,加上照顾老婆,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的呢?
   PostgreSQL之analyze监控     这是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的时间。

    PostgreSQL之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).可以配置:

    PostgreSQL之analyze监控

我默认sleep 30s,是不合适的,更好的方法是获取naptime,根据naptime,确定sleep的时间,我就不改了.

输出如下:

    PostgreSQL之analyze监控

      为了 信息安全,我输出的是系统表,而不是用户表的auto analyze信息 ,为了计算两次analyze的间隔,我把上一次和这一次analyze time都显示出来了.


以上所述就是小编给大家介绍的《PostgreSQL之analyze监控》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Ruby on Rails实践之路

Ruby on Rails实践之路

沃哈 / 科学 / 2010-5 / 48.00元

《Ruby on Rails实践之路:写给PHP和Java开发者的书》内容简介:Ruby on Rails是基于MVC模式的Web框架,用于开发基于数据库的Web应用。Ruby on Rails中内含了所需的Web服务器WEBrick。该框架配置的数据库除了缺省的MySQL外,还可以是Oracle、SQL Server等其他数据库。《Ruby on Rails实践之路:写给PHP和Java开发者的......一起来看看 《Ruby on Rails实践之路》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

随机密码生成器
随机密码生成器

多种字符组合密码

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器