开发者社区> shy丶gril> 正文

PostgreSQL模板 pg_stat_statements的官方介绍/简单安装步骤

简介:
+关注继续查看


标题:PostgreSQL模板 pg_stat_statements的官方介绍/简单安装步骤 

作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]


PostgreSQL 9.3.1 官方中文手册介绍pg_stat_statements

F.28. pg_stat_statements

pg_stat_statements模块提供一种跟踪执行统计服务器执行的所有SQL语句的手段。

该模块必须通过在postgresql.conf中添加pg_stat_statements 到shared_preload_libraries来加载,因为它需要额外的共享内存。 这意味着添加或删除这个模块都需要重启服务器。

F.28.1. pg_stat_statements 视图

该模块聚集的统计通过一个名为pg_stat_statements的系统视图使其可用。 这个模块为每个不同的查询、数据库ID和用户ID(取决于该模块可以追踪的不同语句的最大值) 包含一行。视图的字段显示在表 F-20中。

表 F-20. pg_stat_statements 字段

名字 类型 参考 描述
userid oid pg_authid.oid 执行该语句的用户的OID
dbid oid pg_database.oid 执行该语句的数据库的OID
query text   有代表性的语句的文本 (多达 track_activity_query_size 字节)
calls bigint   执行的次数
total_time double precision   该语句花费的总时间,以毫秒计
rows bigint   该语句恢复或影响的行的总数
shared_blks_hit bigint   该语句命中的共享块缓存的总数
shared_blks_read bigint   该语句读取的共享块的总数
shared_blks_dirtied bigint   该语句弄脏的共享块的总数
shared_blks_written bigint   该语句写入的共享块的总数
local_blks_hit bigint   该语句命中的本地块缓存的总数
local_blks_read bigint   该语句读取的本地块的总数
local_blks_dirtied bigint   该语句弄脏的本地块的总数
local_blks_written bigint   该语句写入的本地块的总数
temp_blks_read bigint   该语句读取的临时块的总数
temp_blks_written bigint   该语句写入的临时块的总数
blk_read_time double precision   该语句读取块花费的总时间,以毫秒计 (如果启用了track_io_timing,否则为0)
blk_write_time double precision   该语句写入块花费的总时间,以毫秒计 (如果启用了track_io_timing,否则为0)

这个视图和函数pg_stat_statements_reset,只有在通过安装 pg_stat_statements扩展特别安装到的数据库中可用。 不过,当pg_stat_statements模块加载到服务器中时, 统计跟踪该服务器中的所有数据库,不管该视图是否存在。

为了安全起见,不允许非超级用户查看其它用户执行的查询的文本。不过, 如果视图已经安装到他们的数据库中,那么他们可以看到统计。

可计划的查询(也就是,SELECT, INSERT, UPDATE, 和 DELETE)组合成为一个pg_stat_statements, 当它们根据一个内部哈希计算有相同的查询结构时。典型的,如果两个查询语义上相等, 除了查询中字面常量的值之外,我们认为这两个查询相同。工具命令(也就是,所有其他命令) 是直接基于它们的文本查询字符串比较的。

当一个常量的值为了匹配其他查询而忽略时,该常量在pg_stat_statements 的显示中被?替代。查询文本的剩余部分是第一个查询特定散列值与 pg_stat_statements相关条目。

在一些情况下,带有明显不同文本的查询可能合并到一个pg_stat_statements。 通常这只在语义相等的查询上发生,但是有很小的可能哈希冲突导致不相关的查询被合并到一个条目。 (不过,这对于属于不同用户或数据库的查询来说是不会发生的。)

因为哈希值是基于分析查询的表示法之后来计算的,相反的也是可能的: 带有相同文本的查询可能表现为单独的条目,如果它们因为一个因素的结果有不同的含义, 比如不同的search_path设置。

F.28.2. 函数

pg_stat_statements_reset() returns void   【执行select pg_stat_statements_reset(); 相当于清空pg_stat_statements系统视图

pg_stat_statements_reset抛弃所有pg_stat_statements 到目前为止收集的统计。缺省的,这个函数只能被超级用户执行。

F.28.3. 配置参数

pg_stat_statements.max (integer)

pg_stat_statements.max是该模块追踪语句的最大值 (也就是,pg_stat_statements视图中的最大行数)。 如果观察了比这更多的不同的语句,则会抛弃执行最少的语句的信息。 缺省值是1000。这个参数只能在服务器启动时设置。

pg_stat_statements.track (enum)

pg_stat_statements.track控制哪个语句可以被该模块计数。 声明top来跟踪顶级的语句(直接通过客户端发出的语句)。all 也跟踪嵌套的语句(比如包含在函数中的语句),或none禁用语句状态收集。 缺省值是top。只有超级用户可以更改这个设置。

pg_stat_statements.track_utility (boolean)

pg_stat_statements.track_utility控制该模块是否追踪工具命令。 工具命令是除了SELECT, INSERT, UPDATE 和 DELETE的所有命令。缺省值是on。只有超级用户可以更改这个设置。

pg_stat_statements.save (boolean)  

pg_stat_statements.save指定在服务器关闭时是否保存语句状态。 如果是off,那么在服务器关闭时不保存状态,在服务器启动时也不重新加载。 缺省值是on。这个参数只可以在postgresql.conf 文件中或者服务器命令行中设置。

该模块需要额外的共享内存总计大约为 pg_stat_statements.max * track_activity_query_size字节10000*1024/1024/10249.7M。请注意, 这个内存在该模块加载时被消耗,即使pg_stat_statements.track 设置为none
track_activity_query_size (integer)指定跟踪当前执行命令的预留字节数, 为了每个活动会话,以及pg_stat_activity.query字段。 默认值是1024。这个参数只能在服务器启动时设置。

这些参数必须在postgresql.conf中设置。典型的用法是:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'      # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

F.28.4. 示例输出

bench=# SELECT pg_stat_statements_reset();

$ pgbench -i bench
$ pgbench -c10 -t300 bench

bench=# \x
bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_time DESC LIMIT 1;
-[ RECORD 1 ]---------------------------------------------------------------------
query       | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
calls       | 3000
total_time  | 9609.00100000002
rows        | 2836
hit_percent | 99.9778970000200936


简单安装步骤:

1、编写$PGDATA/postgresql.conf

       shared_preload_libraries = 'pg_stat_statements' 

       pg_stat_statements.max = 1000

     pg_stat_statements.track = all
2、编译安装pg_stat_statements模块

     进入postgresql的源码目录:

     cd ...postgresql-9.1.3/contrib/pg_stat_statements  【find / -name 找下...路径】

     make

     make install

     #如果..../share/extension目录下存在pg_stat_statements--1.*.sql,说明安装成功了

3、加载pg_stat_statements模块

     #启动postgresql服务

     pg_ctl start 

     #加载sql文件

     $ psql -f pg_stat_statements--1.2.sql

     Use "CREATE EXTENSION pg_stat_statements" to load this file.

    #查看版本
    postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'; 
             name               | default_version | installed_version |                          comment                          
     --------------------+-----------------+-------------------+-----------------------------------------------------------
     pg_stat_statements | 1.2                     | 1.2                        | track execution statistics of all SQL statements executed

     postgres=# SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';
             name        | version | installed | superuser | relocatable | schema | requires |                          comment                          
     --------------------+---------+-----------+-----------+-------------+--------+----------+--------------------------
     pg_stat_statements | 1.2     | t         | t         | t           |        |    | track execution statistics of all SQL statements executed
     postgres=# 

     #进入DB创建系统视图:

     postgres=# create extension pg_stat_statements;

     CREATE EXTENSION
     postgres=# select * from pg_stat_statements;  --不报错就可以使用了,随意DDL/DML下~再查看pg_stat_statements会有跟踪记录

4、本人使用pg_stat_statements用来监控是否有drop index的情况.和慢sql...等,如下是查看drop index 情况;
     SELECT PA.ROLNAME,PD.DATNAME,P.QUERY,CALLS,TOTAL_TIME,ROWS
     FROM  PG_STAT_STATEMENTS P
     JOIN PG_AUTHID PA ON PA.OID = P.USERID
     JOIN PG_DATABASE PD ON PD.OID = P.DBID
     -- AND DATNAME LIKE 'BI_EDW_SHOES%'
     AND UPPER(QUERY) LIKE '%DROP %INDEX%'
     -- 过滤掉存储/函数中包含的drop index关键字
     and  UPPER(QUERY) not like '%=%DROP %INDEX%' ;



    【源于本人笔记】 若有书写错误,表达错误,请指正...

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
CentOS 6.5安装二进制版本的MongoDB 2.6
CentOS 6.5安装二进制版本的MongoDB 2.6 MongoDB的国外镜像访问非常慢,以至于选择MongoDB官网的在线安装很不靠谱。那么,我们可以选择安装MongoDB 2.6的二进制发布包。
804 0
菜鸟入门【ASP.NET Core】7:WebHost的配置、 IHostEnvironment和 IApplicationLifetime介绍、dotnet watch run 和attach到进程调试
WebHost的配置 我们用vs2017新建一个空网站HelloCore 可以使用ConfigureAppConfiguration对配置进行更改,比如说添加jsonfile和commandline配置。
1876 0
ActiveReports 报表控件官方中文入门教程 (1)-安装、激活以及产品资源
原文:ActiveReports 报表控件官方中文入门教程 (1)-安装、激活以及产品资源 本系列文章主要是面向初次接触 ActiveReports 产品的用户,可以帮助您在三天之内轻松的掌握ActiveReports控件的基本使用方法,包括安装、激活、创建报表、绑定数据源以及发布等内容。
1010 0
oracle中的 exists 和 in 简单用法介绍
document.body.oncopy = function () { setTimeout( function () { var text = clipboardData.
476 0
DataHub:流行的元数据架构介绍
时至今日,我们正生活在数据的黄金时代。当数据科学家加入数据驱动型公司时,他们希望找到一种数据发现工具(即数据目录),可以用来找出公司中存在哪些数据集,以及如何使用这些数据集来测试新假设和产生新见解。大多数数据科学家并不真正关心这个工具在幕后是如何工作的,只要它能使他们富有成效。
464 0
TimesTen学习(一)安装篇
TimesTen学习(一)安装篇 一.Linux平台安装准备工作。 1.大页设置 1).修改HugePage配置值: echo 32 > /proc/sys/vm/nr_hugepages 2).
916 0
js正则函数match、exec、test、search、replace、split使用介绍集合
match 方法 使用正则表达式模式对字符串执行查找,并将包含查找的结果作为数组返回。 stringObj.match(rgExp) 参数 stringObj 必选项。对其进行查找的 String 对象或字符串文字。
596 0
+关注
1878
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载