PostgreSQL module-pg_stat_statements detailed explanation and installation test

PostgreSQL module-pg_stat_statements detailed explanation and installation test

In fact, it is very simple. Recently, postgresql may need to be monitored, so I have been exposed to many related monitoring commands and tools. This article mainly records the work process, and I am afraid I will forget it later.

The function I want is to record the execution time of each SQL statement and query the top 10 execution times per day.

The following describes pg_stat_statements: (Translation)

The pg_stat_statements module provides a method to track statistics of all SQL statements executed.

This module must rewrite the shared_preload_libraries variable in the configuration file postgresql. conf (later I will explain how to configure it) because it requires additional shared memory. It also means that the service needs to be restarted.

1-first look at the pg_stat_statements View

Field name	Type	Reference	Description
Userid	Oid	Pg_authid. Oid	Performer id
Dbid	Oid	Pg_database. Oid	Execution Database id
Query	Text		Executed statement
CILS	Bigint		Number of executions
Total_time	Double precision		Total execution time (average = total_time/CILS)
Rows	Bigint		Total number of affected rows
Shared_blks_hit	Bigint		Number of shared block hits
Shared_blks_read	Bigint		Number of shared block reads
Shared_blks_written	Bigint		Number of shared block writes
Local_blks_hit	Bigint		Number of Local block hits
Local_blks_read	Bigint		Number of Local block reads
Local_blks_written	Bigint		Number of Local block writes

The above view and function pg_stat_statements_reset will take effect only after the database has been correctly installed and the pg_stat_statements. SQL script has been executed. All database operations on the server will be tracked as long as pg_stat_statements is successfully added.

For security reasons, normal users are not allowed to view the statement information (query) executed by other users. If the view is installed in its database, then you can view the relevant statistical information (Zijian's experiment is really a Super User: S user postgres database)

Note: If the statement information (query) is the same, the statements are considered to be the same no matter whether the value of any out-of-line variable is used. Using the out-of-line variable can help organize statements and make statistical data more useful.

2-Functions

Pg_stat_statements_reset () returns void

Pg_stat_statements_reset discards all information currently counted by pg_stat_statements. By default, this function can only run under a Super User.

3-configuration variables

Pg_stat_statements.max (integer)

Pg_stat_statements.max is the maximum number of Statistics records (that is, the maximum number of rows in the view ). If the data volume is greater than the maximum value, the statement that executes the minimum value will be discarded (in my test, if the statement is executed for 1 times, followed by the data that has been discarded for a long time ), the default value is 1000. This variable is set before the service starts.

Pg_stat_statements.track (enum)

Pg_stat_statements.track controls statistical data rules. top is used to track top-level statement (directly sent by the client). all also traces nested statements (such as statements called in functions)

Pg_stat_statements.track_utility (boolen)

Pg_stat_statements.track_utility controls whether to track public program commands (utility commands). The public program command is a command other than SELECT/INSERT/UPDATE/DELETE. The default value is enable. Only Super Users can change this setting.

Pg_stat_statements.save (boolean)

Pg_stat_statements.save specifies whether to save statistics when the server is shut down. If off is set, statistics are not saved when the service is disabled. The default value is on. This value can only be set in postgresql. conf or the command line.

1 2 Next Page