Re: [GENERAL] High activity short table and locks

Hello,

My first impression is that vacuuming the offending table very often helps a lot. I'm doing it by hand for now but I will have a cronjob for this. By the way, it seems I don't need thoses indexes anymore. Thanks a lot for your helpful advices.

On Thu, Jul 24, 2008 at 6:19 PM, Richard Huxton <dev@archonet.com> wrote:

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