Detailed explanation and installation test of PostgreSQL module pg_stat_statements

This module requires additional shared memory. The memory size is roughly pg_stat_statements.max * track_activity_query_size. Note that once a module is loaded, that is

This module requires additional shared memory. The memory size is roughly pg_stat_statements.max * track_activity_query_size. Note that once a module is loaded, that is

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 modify 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.