October 21, 2023
Summary: in this tutorial, you will learn how to setup slow query logging in PostgreSQL.
Table of Contents
PostgreSQL allows configuring the settings of log queries that are running for more than a certain duration using some parameters. This helps admins in understanding the queries that need to be optimized to improve the overall application performance. In this tutorial, we shall discuss the steps involved in enabling the logging of time-consuming queries in PostgreSQL.
Getting ready
It is always important to know that logging additional information to PostgreSQL logs causes more disk writes and uses a lot of disk space. For this reason, it is important to monitor the disk usage and the IOPS increase when any extensive logging has been enabled. It is also important to segregate logs and data across different disks. This way, none of the logging-specific writes cause any I/O saturation for the data directory that contains the database objects.
How to do it…
We will initiate the logging process using the following steps:
1. Ensure that logging_collector is set to ON to enable logging in PostgreSQL.
The following steps can be used to set logging_collector to ON:
$ psql -c "show logging_collector"
$ psql -c "ALTER SYSTEM SET logging_collector TO 'ON'"
It is definitely wise to isolate the database logs from the syslogs so that it is easy to differentiate kernel-level messages and PostgreSQL-level events.
So, we should set the log_destination parameter to ‘stderr’ instead of ‘syslog’.
2. Set log_line_prefix appropriately so that enough information is visible in each line of the log file:
$ psql -c "ALTER SYSTEM SET log_line_prefix TO '%t [%p]: [%l-1] user=%u,db=%d'"
3. Set log_min_duration_statement to the time after which a query is said to be time-consuming.
We will check out various examples for specific time ranges as follows:
The following command can be used to log all queries running for more than 10 milliseconds:
-- To log all the queries running for more than 10 milliseconds
$ psql -c "ALTER SYSTEM SET log_min_duration_statement TO '10ms'"
The following command can be used to log all queries running for more than 15 seconds:
$ psql -c "ALTER SYSTEM SET log_min_duration_statement TO '15s'"
The following command can be used to log all queries running for more than 20 minutes:
$ psql -c "ALTER SYSTEM SET log_min_duration_statement TO '20min'"
The following command can be used to log all queries running for more than 1 hour:
$ psql -c "ALTER SYSTEM SET log_min_duration_statement TO '1h'"
4. Restart or reload to get the changes into effect. Changes to logging_collector require a restart:
The following command may be used to perform a restart:
$ pg_ctl -D $PGDATA restart -mf
The following command may be used to perform a reload:
$ pg_ctl -D $PGDATA reload
Thus, we have learned how to start logging data.
How it works…
There is only one type of log file in PostgreSQL. What this means is, there is no separate log file for recording errors or warnings and no separate log file to log slow queries.
For this reason, to log errors or slow queries, logging_collector must be set to ON.
Otherwise, we would not see any information logged to logs.
Once this has been confirmed as enabled, it is important to make sure that all the details, such as the application or user running SQL, are also visible.
For this purpose, we could use the log_line_prefix parameter.
This can be set to the value seen in step 2 or using the runtime configs for logging as seen in step 2.
To enable the logging of slow queries, we must set log_min_duration_statement to a time after which a SQL query is said to be performing slow.
This is a global setting. As seen in the examples in step 3, it can be set to a few milliseconds, seconds, minutes, or a few hours.
If it’s just log_min_duration_statement or log_line_prefix that have been changed, we can just perform a reload as seen in step 4.
There’s more…
Some more logging parameters we may want to look into are the following:
log_connections: Every new connection (received and authorized) is logged to PostgreSQL logs.log_disconnections: Every disconnection of a PostgreSQL connection is logged to logs.log_duration: Enable logging of the duration of every query running in the database server.