Re: High activity short table and locks

Guillaume Bog wrote:
> On Wed, Jul 23, 2008 at 11:17 PM, Richard Huxton <dev@archonet.com> 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