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.
