Postgres cluster function

Postgres cluster function

by John Coughlin -
Number of replies: 5

Our production moodle database (postgres 8.1.18) has grown to 38 GIG in size. The database is currently showing intermittently poor performance.

We find that when we backup and restore the database, it restores to a 22 GIG database. So apparently, we have a lot of gas in the production database and we think this is perhaps contributing to the problem.

We do have autovacuum turned on, and have implemented many of the standard tuning options. We have not run a full vacuum in a long time, mainly because it takes so long (on the order of 10 hours, I believe).

We would like to try running the cluster function instead. According to postgres documentation, this is a better, faster option than full vacuum and should offer some performance improvements as well as allow us to recover some disk space.

The cluster option is explained here : http://www.postgresql.org/docs/8.1/interactive/sql-cluster.html

First, has anyone run this function and what has your experience been with it?

Second, when I run this option on our server, it completes instantaneously. Specifically, if I go into phppgadmin, then go into the db, then click on ADMIN, then click on the cluster button, it immediately returns a message saying "cluster complete". I've also tried running the "cluster" SQL command with the same basic result. I'm assuming that nothing is really happening. Does anyone know why?

Thanks, -- john

Average of ratings: -
In reply to John Coughlin

Re: Postgres cluster function

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
I don't know whether we really saw anything like the benefits claimed, but the 'typical' performance improvements claimed for each version of Postgres between 8.1 and current 8.4 or whatever combine to over 40%. If there's any truth in the claims, it might be worth upgrading - as an added benefit, when you upgrade, this will mean you'll have to transfer all the tables and therefore they'll get written out without any gaps again...

About cluster command - from documentation, if you run it on its own, that just does: 'Cluster all tables in the database that have previously been clustered:' - so you need to run it on each table first to tell it which index to use. Also, I don't really see any logical reason why it should be faster (in terms of how long it locks the table, I mean) than VACUUM FULL, but I'm hardly an expert.

With our database we do see dramatic performance improvements after recreating tables such as with backup/restore etc. (Or to put it another way - if certain things get really slow, it's time to recreate the tables.) You might want to check specific tables to see which ones have problems, as not all tables do. It might save downtime if you only VACUUM FULL (or CLUSTER or whatever) on the problem tables and not everything.

Looking ahead to the future, I notice that Postgres 9 claims 'As part of our decade-long effort to eliminate the pain of VACUUM, VACUUM FULL is now substantially faster by rewriting the entire table and indexes, rather than moving around single rows to compact space.' - unfortunately, 'substantially faster' than an unacceptable period of downtime, is probably still an unacceptable period of downtime, but hey. smile

--sam
Average of ratings: -
In reply to sam marshall

Re: Postgres cluster function

by John Coughlin -

Thanks Sam, this is very helpful.

Your advice about running cluster per table was right on the money. This was definitely my problem insofar as "cluster" not doing anything when I'd run it. As soon as I ran cluster against a few of my tables, the global cluster started working as expected (i.e., it now takes an expected amount of time to run). I'm going to do as you suggest and try to identify the problem tables, and target those for clustering.

There's an interesting thread here that talks a little about why cluster runs faster than vacuum full. I'm not a DBA so I can't comment on how reliable this article is, but it's worth mentioning anyway:

http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959

Regardless, it look like PG9 will have a whole different method of performing a vacuum full, so that's good news.

I found your comment about rebuilding tables (and the performance increase it offers) very interesting. How big is your moodle DB, typically? How often do you typically rebuild your database? I'm thinking that if the cluster function doesn't give us any performance benefit, we may try to just drop our database and restore from backup.

Thanks again, -- john

Average of ratings: -
In reply to John Coughlin

Re: Postgres cluster function

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Our database is pretty big - I don't have the figures (pgadmin only seems to give per-table statistics and I didn't want to add them up by hand) but I presume it's a bit larger than yours. Same general area anyhow. I don't think it all fits in the db server's 64GB RAM but I don't think it's orders of magnitude away from fitting in that, either.

The article you linked says that CLUSTER now works the same way that VACUUM FULL will in 9, i.e. writing new files. Sounds good. I'll suggest that to our team (if they didn't already see these posts) because I don't think it's been mentioned, however of course they may already have considered it.

We don't rebuild regularly because it takes too long - it's difficult to give a typical case because in the past we have not had a suitable regular vacuum (non-FULL) regime which means the database has been in a right old mess. But on occasions when we have moved to a new database server or whatever and we transfer the file (I guess that would be about yearly) we did usually notice a big performance jump. Hopefully this would be not so significant now that we are doing vacuum properly.

Last time we transferred the whole database (and filesystem maybe?) I think we had like half a day of downtime or something stupid, so it's not something that is acceptable frequently! There were lots of other changes at the time though (this was the Postgres upgrade from 8.1 to 8.3 also, and other things) so I don't know how much of the time was taken up just with the database backup/restore.

--sam






Average of ratings: -
In reply to John Coughlin

Re: Postgres cluster function

by Matt Clarkson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
It definitely sounds like you're suffering from dead row bloat, which unfortunately can only be fixed with a VACUUM FULL. To confirm, run this query http://pastebin.com/DXEnYtWv

If you're getting dead row bloat then it probably means that autovacuum can't keep up and needs to be tuned to run more often. These settings fixed worked for us:

autovacuum_max_workers = 5 # max number of autovacuum subprocesses
autovacuum_naptime = 5s # time between autovacuum runs
autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before vacuum
autovacuum_vacuum_cost_delay = 60 # default vacuum cost delay for

Upgrading to 8.4 is also well worth it as autovacuum does its job more efficiently due to visibility maps.

-Matt.
Average of ratings: -
In reply to Matt Clarkson

Re: Postgres cluster function

by John Coughlin -

Thanks everybody for your help. We upgraded our postgres to the most recent version and reloaded the database, and have seen a huge performance increase. Our moodle installation is running better now than ever. September is typically our busiest month, and with over 16,000 users we haven't had a single complaint about performance.

We clustered a number of our high-use tables/indexes, and will probably recluster every few weeks.

We also implemented a number of postgres tuning changes which may or may not have helped, but they certainly didn't hurt. Here are the notable settings that everyone seems to talk about when it comes to performance, and how we have them set on our server with 8GIG RAM.

work_mem 20MEG
cpu_index_tuple_cost 0.001
cpu_operator_cost 0.0005
cpu_tuple_cost 0.003
effective_cache_size 6GB
checkpoint_segments 15
wal_buffers 4MB
maintenance_work_mem 1GB
shared_buffers 3GB

Thanks again, -- john

Average of ratings: -