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.
