Summary

Pg_stat_states is an extension plug-in of PostgreSQL database, which is used to collect the running information of SQL in the database, such as the total execution time of SQL, the number of calls, the hit rate of shared memory and so on. Used to monitor the performance of database is an important expansion module of database performance monitoring.
By default, pg_stat_states are available in the source package of PG without downloading, but you need to configure and install them manually. Here's a demonstration.

Download and install

If you are compiling and installing, go directly to the contrib/pg_stat_states directory in the source package and execute the compilation and installation actions.
(If you are installing yum, you need to download the source packages to find extensions, preferably in the same version as the database you installed)
There are some built-in plug-ins in the source package of pg

[root@stephen contrib]# pwd
/opt/postgresql-11.4/contrib
[root@stephen contrib]# ls pg_stat_statements
expected pg_stat_statements--1.3--1.4.sql pg_stat_statements.conf
Makefile pg_stat_statements--1.4--1.5.sql pg_stat_statements.control
pg_stat_statements--1.0--1.1.sql pg_stat_statements--1.4.sql pg_stat_statements--unpackaged--1.0.sql
pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.5--1.6.sql sql
pg_stat_statements--1.2--1.3.sql pg_stat_statements.c

Compile and install

make && make install

Creating and Expanding

postgres=# create extension pg_stat_statements ;
CREATE EXTENSION

Setting parameters
After installation, add the following parameters to the postgres.conf file in the PG configuration file

shared_preload_libraries='pg_stat_statements'     
track_io_timing = on
track_activity_query_size = 2048
pg_stat_statements.max = 1000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

Introduction of parameters

• shared_preload_libraries: Dynamic libraries can be loaded to set up multiple shared libraries with comma style among them. If you set up unsupported shared libraries, the database will restart and fail to start
• pg_stat_statements.max: Set the maximum number of SQL records by default of 5000. If the set value is reached, the least frequently executed SQL will be discarded.
• pg_stat_statements.track: What kind of SQL records are set, top refers to the outermost SQL, all contains the SQL involved in the function
• pg_stat_statements.track_utility: Sets whether to record SQL commands other than select, update, delete, insert
• pg_stat_statements.save = on: Whether to record SQL information in a file when the database is closed, usually on

After the installation is complete, you can see a view under the database

postgres=# \d
List of relations
-[ RECORD 1 ]--------------
Schema | public
Name | pg_stat_statements
Type | view
Owner | postgres

This is the view where pg_stat_states record the execution of SQL

Use

After opening, any statements executed in the database are recorded.

For example, I made several queries in the database.

stephen=# select * from datax_test limit 10;

Then you can see the execution of this SQL in the view pg_stat_states.

stephen=# select * from pg_stat_statements where queryid = 2362179633810610809;
-[ RECORD 1 ]-------+----------------------------------
userid        | 10        #User oid to execute this SQL
dbid        | 16384        #oid of the database executed by this SQL
queryid        | 2362179633810610809        #Number of this SQL
query        | select * from datax_test limit $1        #Content of this SQL
calls        | 6        #Number of calls to this SQL
total_time        | 17.551195        #Total execution time (ms)
min_time        | 0.030288        #Minimum time        
max_time        | 17.390669        #Maximum time
mean_time        | 2.92519916666667        #average time
stddev_time        | 6.46915489671277        #Table deviations in SQL time-consuming
rows        | 60        #Number of rows returned
shared_blks_hit        | 5        #Number of shared memory data blocks hit
shared_blks_read        | 1        #Shared Memory Degree
shared_blks_dirtied        | 0        #Number of dirty blocks generated in shared memory
shared_blks_written        | 0        #Number of shared memory data blocks written
local_blks_hit        | 0        #Number of blocks hit by temporary table
local_blks_read        | 0        # Number of blocks to read for temporary tables
local_blks_dirtied        | 0        #Number of dirty blocks on the temporary Watch
local_blks_written        | 0        #Number of blocks written to temporary tables
temp_blks_read        | 0        #Number of blocks read by temporary files
temp_blks_written        | 0        #Number of blocks written to temporary files
blk_read_time        | 17.332569        #Reading from disk takes time
blk_write_time        | 0        #Writing from disk takes time

Reset statistics
As the database runs, the pg_stat_states view of statistics becomes larger and larger, and the acquired SQL can be cleaned up through the pg_stat_statements_reset function.

stephen=# select count(*) from pg_stat_statements;
 count 
-------
   106
(1 row)

stephen=# select pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------
 
(1 row)

stephen=# select count(*) from pg_stat_statements;
 count 
-------
     2
(1 row)