LOGQUERY
Logging slow SQL queries server-side in PostgreSQL
A first solution is to modify PostgreSQL postgresql.conf configuration file:
Connect to the database server, open postgresql.conf configuration file, enable query logging and set maximum execution time to 30 ms:
logging_collector = on log_directory = 'pg_log' log_min_duration_statement = 30
Restart PostgreSQL server for logging_collector and log_directory to take effect. The logs will be written in log_directory under PostgreSQL data directory.
You can decide to print the query plan using these variables (unckeck # to modify):
#log_checkpoints = off
Causes checkpoints and restartpoints to be logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off.
#log_connections = off
Causes each attempted connection to the server to be logged, as well as successful completion of client authentication. Only superusers can change this parameter at session start, and it cannot be changed at all within a session. The default is off.
#log_disconnections = off
Causes session terminations to be logged. The log output provides information similar to log_connections, plus the duration of the session. Only superusers can change this parameter at session start, and it cannot be changed at all within a session. The default is off.
#log_duration = off
Causes the duration of every completed statement to be logged. The default is off. Only superusers can change this setting. For clients using extended query protocol, durations of the Parse, Bind, and Execute steps are logged independently.
#log_hostname = off
By default, connection log messages only show the IP address of the connecting host. Turning this parameter on causes logging of the host name as well. Note that depending on your host name resolution setup this might impose a non-negligible performance penalty. This parameter can only be set in the postgresql.conf file or on the server command line.
log_line_prefix = '%t '
This is a printf-style string that is output at the beginning of each log line. % characters begin "escape sequences" that are replaced with status information as outlined below. Unrecognized escapes are ignored. Other characters are copied straight to the log line. Some escapes are only recognized by session processes, and will be treated as empty by background processes such as the main server process. Status information may be aligned either left or right by specifying a numeric literal after the % and before the option. A negative value will cause the status information to be padded on the right with spaces to give it a minimum width, whereas a positive value will pad on the left. Padding can be useful to aid human readability in log files. This parameter can only be set in the postgresql.conf file or on the server command line. The default is an empty string.
Escape Effect Session only
%a Application name yes
%u User name yes
%d Database name yes
%r Remote host name or IP address, and remote port yes
%h Remote host name or IP address yes
%p Process ID no
%t Time stamp without milliseconds no
%m Time stamp with milliseconds no
%i Command tag: type of session's current command yes
%e SQLSTATE error code no
%c Session ID: see below no
%l Number of the log line for each session or process, starting at 1 no
%s Process start time stamp no
%v Virtual transaction ID (backendID/localXID) no
%x Transaction ID (0 if none is assigned) no
%q Produces no output, but tells non-session processes to stop at this point in the string;
ignored by session processes no
%% Literal %
These settings apply to the whole database server.
A second solution is to log slow queries interactively using an SQL command.
Connect to the database server, open postgresql.conf file and enable query logging and set maximum execution time to 30 ms:
logging_collector = on log_directory = 'pg_log'
Restart PostgreSQL for settings to take effect.
Then connect to your SQL client and run:
SET log_min_duration_statement to 30; SET debug_print_plan to ON;
AutoExplain: https://www.postgresql.org/docs/9.6/static/auto-explain.html
- Add this row in postgresql.conf
session_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '3s'
- TEST CON PGBENCH
pgbench -i
