如何在greenplum开源版本中使用pg_stat_statements扩展? [英] How do I use pg_stat_statements extension in greenplum open source version?

问题描述

我正在尝试使用经过修改的greenplum开源版本进行开发. greenplum版本是Greenplum Database 6.0.0-beta.1 build dev(基于PostgreSQL 9.4.24).

I am trying to use a modified greenplum open source version for development. The greenplum version is Greenplum Database 6.0.0-beta.1 build dev (based on PostgreSQL 9.4.24).

我想将pg_stat_statements扩展名添加到数据库中，并且确实按照

I wanted to add pg_stat_statements extension to my database, and I did manage to install it on the database, following https://www.postgresql.org/docs/9.5/pgstatstatements.html. However, this extension doesn't work as expected. It only records non-plannable queries and utility queries. For all plannable queries which modify my tables, there is not a single record.

我的问题是，pg_stat_statements与greenplum兼容吗?由于我没有使用正式版本，因此我想确保原始版本可以与pg_stat_statements一起使用.如果是这样，我如何使用它来跟踪greeplum中的所有sql查询?谢谢.

My question is, is pg_stat_statements compatible with greenplum? Since I am not using the official release, I would like to make sure the original one can work with pg_stat_statements. If so, how can I use it to track all sql queries in greeplum? Thanks.

下面是一个不记录我的选择查询的示例.

Below is a example of not recording my select query.

postgres=# select pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------

(1 row)

postgres=# select query from pg_stat_statements;
               query                
------------------------------------
 select pg_stat_statements_reset();
(1 row)

postgres=# select * from test;
 id | num 
----+-----
  1 |   2
  3 |   4
(2 rows)

postgres=# select query from pg_stat_statements;
               query                
------------------------------------
 select pg_stat_statements_reset();
(1 row)

推荐答案

这是我从@ leskin-in那里得到的greenplum松弛物品:

Here's what I get from @leskin-in in greenplum slack:

我想这目前是不可能的. 当Greenplum执行常规"查询时，每个Greenplum段都充当一个独立的PostgreSQL实例，该实例执行由GPDB主服务器创建的计划. pg_stat_statements跟踪单个PostgreSQL实例的资源使用情况；结果，在GPDB中，它能够独立跟踪每个段的资源消耗. PostgreSQL pg_stat_statements没有处理一些复杂问题.一个示例是GPDB使用切片.在GPDB段上，它们是计划树的独立部分，并作为独立查询执行. 我想在当前版本的GPDB主数据库上对pg_stat_statemens进行查询时，检索到的结果仅用于主数据库.由于正常"查询大部分是按段执行的，因此结果与查询所消耗的实际资源不一致. 在开源Greenplum 5和6有一个Greenplum专用的实用程序gpperfmon.它提供了pg_stat_statements的某些功能，并且具有群集感知能力(显示了整个群集的实际资源消耗，以及几个特定于群集的指标).

I suppose this is currently not possible. When Greenplum executes "normal" queries, each of Greenplum segments acts as an independent PostgreSQL instance which executes a plan created by GPDB master. pg_stat_statements tracks resources usage for a single PostgreSQL instance; as a result, in GPDB it is able to track the resources consumption for each segment independently. There are several complications PostgreSQL pg_stat_statements does not deal with. An example is that GPDB uses slices. On GPDB segments, these are independent parts of plan trees and are executed as independent queries. I suppose when a query to pg_stat_statemens is made on GPDB master in current version, the results retrieved are for master only. As "normal" queries are executed in most part by segments, the results are inconsistent with actual resources' consumption by the query. In open-source Greenplum 5 & 6 there is a Greenplum-specific utility gpperfmon. It provides some of the pg_stat_statements features, and is cluster-aware (shows actual resources consumption for the cluster as a whole, and also several cluster-specific metrics).

这篇关于如何在greenplum开源版本中使用pg_stat_statements扩展?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！