Skip site navigation (1) Skip section navigation (2)

Re: [Again] Postgres performance problem

From: ruben(at)rentalia(dot)com
To: "Decibel!" <decibel(at)decibel(dot)org>
Cc: db(at)zigo(dot)dhs(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: [Again] Postgres performance problem
Date: 2007-09-12 07:42:29
Message-ID: 46E79865.906@rentalia.com (view raw or flat)
Thread:
Lists: pgsql-performance

Decibel! escribió:
> On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> db(at)zigo(dot)dhs(dot)org escribi?:
>>>> Last time I had this problem i solved it stopping website,  restarting
>>>> database, vacuumm it, run again website. But I guess this is going to
>>>> happen again.
>>>>
>>>> I would like to detect and solve the problem. Any ideas to detect it?
>>> Do you have very long transactions? Maybe some client that is connected
>>> all the time that is idle in transaction?
>> There should not be long transactions. I ll keep an eye on Idle transactions
>>
>> I m detecting it using:
>>
>> echo 'SELECT current_query  FROM pg_stat_activity;' |
>> /usr/local/pgsql/bin/psql vacadb  | grep IDLE | wc -l
> 
> If you're using VACUUM FULL, you're doing something wrong. :) 

I do a VACUUM FULL VERBOSE ANALYZE each day. I save all logs so I can
check if vacuum is done properly.(it is)

Run lazy
> vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
> autovac parameters in half), and make sure your FSM is big enough

I checked that there is no warnings about FSM in logs. (also in logs
from vacuum). Is it reliable?

What do u mean for "cut all of 8.1's autovac parameters in half" Maybe
default autovac parameters?

> (periodic vacuumdb -av | tail is an easy way to check that).

I ll keep an eye on it.

> 
> Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> easy for them to seriously bloat.

Reindex is  done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
the output averyday and save it into a log, and I can check that it is
done properly.



In response to

Responses

pgsql-performance by date

Next:From: El-LotsoDate: 2007-09-12 08:09:33
Subject: Re: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hoursand still running
Previous:From: Claus GuttesenDate: 2007-09-12 07:17:23
Subject: Re: Hardware spec

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group