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:

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

([email protected][local]:4448) [postgres] > create extension pg_stat_statements;
CREATE EXTENSION
Time: 281.765 ms
([email protected][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:

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

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

([email protected][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
([email protected][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.

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

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

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

Time: 0.215 ms
([email protected][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:

([email protected][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.:

([email protected][local]:4448) [postgres] > \x
Expanded display is on.
([email protected][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:

([email protected][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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure