Re: [Again] Postgres performance problem
От | Scott Marlowe |
---|---|
Тема | Re: [Again] Postgres performance problem |
Дата | |
Msg-id | dcc563d10709121128o1e5883a9n96b1f9ed74901be9@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [Again] Postgres performance problem (ruben@rentalia.com) |
Ответы |
Re: [Again] Postgres performance problem
Re: [Again] Postgres performance problem Re: [Again] Postgres performance problem |
Список | pgsql-performance |
On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote: > > Decibel! escribió: > > On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- > >> Hash: SHA1 > >> > >> db@zigo.dhs.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) Then, like Jim said, you're doing it wrong. Regular vacuum full is like rebuiling a piece of equipment every night when all it needs is the filter changed. > 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? Yep. ( I assume) > > (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. Then you're vacuum full is wasted. A reindex accomplishes the same thing, plus shrinks indexes (vacuum full can bloat indexes). Just run regular vacuums, preferably by autovacuum, and keep an eye on the vacuum analyze you run each night to see if your fsm is big enough. Occasionally vacuum full is absolutely the right answer. Most the time it's not. I'm getting more and more motivated to rewrite the vacuum docs. I think a rewrite from the ground up might be best... I keep seeing people doing vacuum full on this list and I'm thinking it's as much because of the way the docs represent vacuum full as anything. Is that true for you?
В списке pgsql-performance по дате отправления:
Предыдущее
От: Rafael Barrera OroДата:
Сообщение: Re: random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1