Database Reference
In-Depth Information
You also want to make sure that
log_duration
is set to
on
, so that you can actually see the
duration.
Another possibility to spot long queries is to look them up in system view
pg_stat_
activity
by repeatedly running the following:
select now()-query_start as running_for, current_query from pg_stat_
activity order by 1 desc limit 5;
This query looks up top five currently running queries ordered by how long they have been
running. You don't usually get the real run time this way, but spotting something here hints you
that they may need optimizing.
Finding queries that make the server slow
Sometimes, a single query execution is not slow in itself, but the aggregate effect of running
hundreds or even thousands of such queries per second has a net effect of making the server
slow.
These queries do not show up in logs with slow query logging turned on, but there are other
ways they can be found, which are as follows:
F
Watch
pg_stat_activity
:
do this by repeatedly running
select
now()-query_start
as
running_for,
current_
query
from
pg_stat_activity
. If the same query keeps coming up often, but the running
for time remains slow, then there is a good chance that this is the query that consumes a lot
of resources, and is at least partly responsible for general slow performance,
You can gather such statistics semi-automatically by running the following shell command
(on one line)
while psql -qt -c "select current_query, now()-query_start as running_for
from pg_stat_activity" >>query_stats.txt ; do sleep 1; done
This issues the select query at one second intervals and collects the output in file
query_stats.txt
after running it for a few seconds, you can stop it by pressing
Control-C
and then look
at the sorted output
sort query_stats.txt | less
