How to find current and historical queries in Greenplum database
Article ID: 296977
Updated On:
Products
Issue/Introduction
This KB article covers how to find current and historical queries in our database.
Environment
Product Version: Other
Resolution
The initial port of call would be to create the "gpperfmon" database in your environment. Steps to do so can be found in the following Greenplum Documentation.
The "gpperfmon" database is a dedicated database where data collection agents on Greenplum segment hosts save query and system statistics.
Important Note: The default capture time in the "queries_history" table in the gpperfmon database are queries over 20 minutes. You will need to edit the gpperfmon.conf file and set the following guc "min_query_time = value" to capture historical queries running more than "x" minutes. The gpperfmon.conf file can be found in the $MASTER_DATA_DIRECTORY
Capture Current Running Queries using "pg_stat_activity":
SELECT * FROM pg_stat_activity;
Specifying queries running longer than 15 minutes:
SELECT * FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '15 minutes';
Capturing Historical Queries using the "queries_history" table:
gpperfmon=# \x Expanded display is on. gpperfmon=# select * from queries_history; -[ RECORD 1 ] ctime | 2020-01-24 09:57:15 tmid | 1579859709 ssid | 28 ccnt | 2 username | gpmon db | gpperfmon cost | 0 tsubmit | 2020-01-24 09:57:00 tstart | 2020-01-24 09:57:00 tfinish | 2020-01-24 09:57:00 status | done rows_out | 0 cpu_elapsed | 40 cpu_currpct | 0 skew_cpu | 0 skew_rows | 0 query_hash | 0 query_text | SELECT sess_id, current_query FROM pg_stat_activity; query_plan | application_name | rsqname | pg_default rqppriority | medium -[ RECORD 2 ]----+--------------------------------
Capturing a historical query greater the "x" seconds (Depended on the interval set in the gpperfmon.conf):
gpperfmon=# select * from queries_history where tfinish - tstart > interval '59 second';
ctime | tmid | ssid | ccnt | username | db | cost | tsubmit | tstart | tfinish | status | rows_out | cpu_elapsed | cpu_currpct | skew_cpu | skew_rows | query_hash | query_text
| query_plan | application_name | rsqname | rqppriority
---------------------+------------+------+------+----------+-----------+------+---------------------+---------------------+---------------------+--------+----------+-------------+-------------+----------+-----------+------------+----------------------
+------------+------------------+------------+-------------
2020-02-05 12:46:45 | 1580904472 | 21 | 23 | gpadmin | gpperfmon | 0 | 2020-02-05 12:45:23 | 2020-02-05 12:45:23 | 2020-02-05 12:46:25 | done | 0 | 0 | 0 | 0 | 0 | 0 | select pg_sleep(62);
| | psql | pg_default | medium
2020-02-05 12:45:30 | 1580904472 | 21 | 21 | gpadmin | gpperfmon | 0 | 2020-02-05 12:44:17 | 2020-02-05 12:44:17 | 2020-02-05 12:45:17 | done | 0 | 0 | 0 | 0 | 0 | 0 | select pg_sleep(60);
| | psql | pg_default | medium
(2 rows)
gpperfmon=#
Further information on gpperfmon query tables can be found in the Greenplum Documentation here.
More information pertaining the the gpperfmon database can also be found here.
Feedback
