Guillaume Bog wrote:
On Wed, Jul 23, 2008 at 11:17 PM, Richard Huxton <dev@xxxxxxxxxxxx> wrote:
I tried a vacuum full and had to stop it as it was blocking the server for
too long. Below is the partial results I got. It seems you are right:
enormous amount of dead space and rows. I did the same vacuum later and it
seems to have improved a lot the performance. I need to check again
tomorrow.
We don't have autovacuum, but as it seems autovacuum cannot target a
specific table, I may prefer do it by cron every minute, as you suggest.
There's a pg_autovacuum system table that lets you tune things
table-by-table. See the manual for details. In your case, a manual
vacuum once a minute will be a huge step forward anyway.
vf_cn2fr=# VACUUM FULL VERBOSE lockers ;
INFO: vacuuming "public.lockers"
INFO: "lockers": found 4228421 removable, 107 nonremovable row versions in
64803 pages
Well, that table is at least 1000 times larger than it needs to be.
If you've run a VACUUM FULL, you'll want to run REINDEX on all the
indexes on that table too.
64803 pages containing 512643700 free bytes are potential move destinations.
Ouch! that's a 500MB table holding 100 live rows.
You could fiddle around setting up ramdisks and pointing tablespaces there,
but I'm not sure it's worth it.
If it is possible to have no WAL at all on this table, I'd prefer to try it.
It seems completely useless and is probably taking a fair amount of i/o.
It's a bit early to be sure if the solution is there, but I feel you already
did throw some good light on my dark path, I have to thank you for that.
Afraid not. The synchronous_commit setting can reduce the disk I/O though.
--
Richard Huxton
Archonet Ltd
