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 <[EMAIL PROTECTED]> wrote: > Guillaume Bog wrote: > >> On Wed, Jul 23, 2008 at 11:17 PM, Richard Huxton <[EMAIL PROTECTED]> >> 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 >
