There are
great resources to help tune
PostgresQL for performance, but say you've got a Postgres database which suddenly becomes very slow. How can you figure out what's going on?
General system stateA slow database server will typically be running out of:
So the first thing you should do is to get an overview of the system state using common UNIX tools such as:
- top (press c to see the full command line): look at CPU and memory usage
- iostat -x -m 5: look at i/o wait and service time
If you notice that memory is exhausted, you could play with the following
postgresql.conf settings:
shared_buffers
work_mem
effective_cache_size
If you see a lot of I/O, then try adjusting these settings:
checkpoint_segments
autovacuum_naptime
wal_buffers
Finding slow queries
On the database side, start by determining whether:
- there is one large query holding up all of the resources or
- the number of queries is the main problem.
To do this, make sure that the following setting is turned on in your
postgresql.conf:
stats_command_string = on
Then fire up
psql dbname and look at the
currently executing queries:
SELECT usename, current_query, query_start
FROM pg_stat_activity;
(You can also add the
procid column to the query if you want to map a query to the process ID you see in
top.)
If you can't get anything useful out of
pg_stat_activity, you may want to enable slow query logging by adding this to your
postgresql.conf:
log_min_duration_statement = 100
(All queries which take more than 100 ms to execute will be printed to the main Postgres log file.)
Examining a particular queryOnce you have identified a slow query, you can time it by running this command before executing the query manually:
\timing
To get an idea of where Postgres spends its time when executing that query, look at the query plan:
EXPLAIN your_query;
The numbers you see there are
estimates. To run the query and get actual numbers, use this instead:
EXPLAIN ANALYZE your_query;
If you still can't figure out why a certain query takes so long, have a look at the
ratio between live and dead rows in the relevant tables:
ANALYZE VERBOSE tablename;
Having too many dead rows is often a sign of insufficient vacuuming. You might want to turn autovacuum on if it isn't already, or to make it a bit more aggressive by tweaking these settings:
autovacuum_vacuum_scale_factor
autovacuum_analyze_scale_factor
Troubleshooting database performance is a bit of a black art, many thanks to Mark Kirkwood for sharing his Postgres wisdom with me.
If there's anything else you have successfully used to find the cause of your performance woes, please feel free to leave a comment.
A quick nine days after
release 0.7.2
of Rcpp,
our R / C++ interface classes,
Romain and I are happy to roll out a new
version 0.7.3.
It has been uploaded to
CRAN and
Debian, and mirrors should have the new
versions shortly. As before, my local page is
also available for downloads and some more details.
This release combines a number of under-the-hood fixes and enhancements with
one bug fix:
According to the 
