Re: 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(at)archonet(dot)com> wrote:

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