Infrastructure at your Service

Daniel Westermann

SQL Monitoring in PostgreSQL (2) – pg_stat_statements

The last post looked into how you can monitor queries using the logging system. This post will introduce pg_stat_statements.

pg_stat_statements is a module that needs to be loaded and is not available in the default configuration. Loading it is quite easy. Create the extension as usual:

postgres@oel7:/home/postgres/ [PG6] sqh
Null display is "NULL".
Timing is on.
psql (9.5alpha2)
Type "help" for help.

(postgres@[local]:4448) [postgres] > create extension pg_stat_statements;
CREATE EXTENSION
Time: 281.765 ms
(postgres@[local]:4448) [postgres] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 btree_gist         | 1.1     | public     | support for indexing common datatypes in GiST
 pg_stat_statements | 1.3     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw       | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(4 rows)

After the extension is available we need to adjust the shared_preload_libraries parameter:

(postgres@[local]:4448) [postgres] > show shared_preload_libraries;
 shared_preload_libraries 
--------------------------
 
(1 row)

(postgres@[local]:4448) [postgres] > alter system set shared_preload_libraries='pg_stat_statements';
ALTER SYSTEM
Time: 55.005 ms

(postgres@[local]:4448) [postgres] > select name,pending_restart 
                                       from pg_settings 
                                      where name in ('shared_preload_libraries');
           name           | pending_restart 
--------------------------+-----------------
 shared_preload_libraries | f
(1 row)

Time: 1.517 ms
(postgres@[local]:4448) [postgres] > select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Basically pg_stat_statements can be used from now own. But there are some parameters to look at if you want to fine tune. Check the documentation for the description.

(postgres@[local]:4448) [postgres] > show pg_stat_statements.max;
-[ RECORD 1 ]----------+-----
pg_stat_statements.max | 5000

Time: 0.230 ms
(postgres@[local]:4448) [postgres] > show pg_stat_statements.track;
-[ RECORD 1 ]------------+----
pg_stat_statements.track | top

Time: 0.211 ms
(postgres@[local]:4448) [postgres] > show pg_stat_statements.track_utility;
-[ RECORD 1 ]--------------------+---
pg_stat_statements.track_utility | on

Time: 0.215 ms
(postgres@[local]:4448) [postgres] > show pg_stat_statements.save;
-[ RECORD 1 ]-----------+---
pg_stat_statements.save | on

Time: 0.212 ms

When we installed the extension a view was created with the following columns:

(postgres@[local]:4448) [postgres] > \d pg_stat_statements
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers 
---------------------+------------------+-----------
 userid              | oid              | 
 dbid                | oid              | 
 queryid             | bigint           | 
 query               | text             | 
 calls               | bigint           | 
 total_time          | double precision | 
 min_time            | double precision | 
 max_time            | double precision | 
 mean_time           | double precision | 
 stddev_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 | 
 blk_write_time      | double precision | 

We can now query the view for information we are interested in, e.g.:

(postgres@[local]:4448) [postgres] > \x
Expanded display is on.
(postgres@[local]:4448) [postgres] > select userid,query,calls,total_time from pg_stat_statements;
-[ RECORD 1 ]
userid     | 10
query      | alter system set logging_collector=on;
calls      | 1
total_time | 30.13
-[ RECORD 2 ]
userid     | 10
query      | create extension pg_stat_statements;
calls      | 2
total_time | 250.54
-[ RECORD 3 ]
userid     | 10
query      | select name,pending_restart from pg_settings where name in (?,?,?,?,?);
calls      | 1
total_time | 0.627
-[ RECORD 4 ]
userid     | 10
query      | show log_rotation_size;
calls      | 1
total_time | 0.006

Additionally we can call a function which is named exactly the same as the view:

(postgres@[local]:4448) [postgres] > select * from pg_stat_statements(true);
-[ RECORD 1 ]
userid              | 10
dbid                | 13295
queryid             | 780340104
query               | alter system set logging_collector=on;
calls               | 1
total_time          | 30.13
min_time            | 30.13
max_time            | 30.13
mean_time           | 30.13
stddev_time         | 0
rows                | 0
shared_blks_hit     | 0
shared_blks_read    | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 2 ]
userid              | 10
dbid                | 13295
queryid             | 1392856018
query               | create extension pg_stat_statements;
calls               | 2
total_time          | 250.54
min_time            | 1.489
max_time            | 249.051
mean_time           | 125.27
stddev_time         | 123.781
rows                | 0
shared_blks_hit     | 1150
shared_blks_read    | 90
Time: 0.742 ms

On top of either the view or the function we can now start to troubleshoot issues with the queries the server executes. Hope this helps.

In the next post I’ll introduce pg_activity.

 

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure