Database Reference
In-Depth Information
Performance tuning is unfortunately still not an exact science, so you may also encounter a
performance problem not found by any of the given methods.
And, we show how to get help in the final chapter, Reporting Performance Problems, in case
none of the other recipes here work.
Finding slow SQL statements
There are two main kinds of slowness which can manifest themselves in a database.
The first kind is a single query that can be too slow to be really useable, such as a customer
information query in a CRM running for minutes, a password check query running in tens
of seconds, or a daily data-aggregation query running for more than a day. These can be found
by logging queries that take over a certain amount of time, either at the client end,
or in the database.
The second kind is a query that is run frequently (say a few thousand times a second), and which
used to run in single digit milliseconds, but now is running in several tens or even hundreds of
milliseconds, and is slowing down the system. This kind of slowness is much harder to find.
Here, we will show several ways to find the statements that are either slow, or although not
being slow by themselves, cause the database as a whole to slow down.
Getting ready
Connect to the database as the user whose statements you want to investigate, or as a
superuser to investigate all users' queries.
Get access to PostgreSQL log files. They are usually located together with other log files, for
example, on Debian/Ubuntu Linux, they are in directory /var/log/postgresql/ .
You should also set up logging of queries taking over x seconds, or if you are not swamped with
thousands of small and fast queries, you can also up logging all queries at least for some period
of time, so you can get an overview of full database activity, and not just individual slow queries.
How to do it...
The easiest way for finding single, slow queries is to set up PostgreSQL to log them all. So, if
you decide to monitor a query taking over 10 seconds, then set up logging queries over 10
seconds by defining the following:
log_min_duration_statement = 10000;
Remember that the duration is in milliseconds.
After doing this, and reloading PostgreSQL conf, all slow queries are logged.
 
Search WWH ::




Custom Search