Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Scott Marlowe
On Mon, 2006-08-28 at 08:47, Guillaume Cottenceau wrote: > Markus Schaber writes: > > > > VACUUM ANALYZE is normally run overnight (each night). Is it not > > > regular enough? There can be hundreds of thousands of statements > > > a day. > > > > Which PostgreSQL version are you using? Maybe you

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Guillaume Cottenceau
Markus Schaber writes: > > VACUUM ANALYZE is normally run overnight (each night). Is it not > > regular enough? There can be hundreds of thousands of statements > > a day. > > Which PostgreSQL version are you using? Maybe you should consider > autovacuum (which is a contrib module at least since

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Markus Schaber
Hi, Guillaume, Guillaume Cottenceau wrote: >> So I assume that you never UPDATE, but only INSERT new entries and >> sometimes DELETE a big bunch of entries from the beginning. > > Actually, in the version of software where we have the problem, > that's exactly the case. But in newer versions, UP

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Markus Schaber
Hi, Guillaume, Guillaume Cottenceau wrote: > About REINDEX: is it ok to consider that REINDEX is to indexes > what VACUUM FULL is to table data, because it cleans up unused > index pages? Yes, roughly speaking. >> And AFAICS you're not running it on a regular basis so your database >> was proba

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Guillaume Cottenceau
> > We have a couple of logs files which get larger over time > > (millions of rows). As they are log files, they can be trimmed > > from older values. > > Ah, ok, you DELETEd the old rows. Yes. > So I assume that you never UPDATE, but only INSERT new entries and > sometimes DELETE a big bunch

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Guillaume Cottenceau
Guillaume, Thanks for your help. > On 28 Aug 2006 11:43:16 +0200, Guillaume Cottenceau <[EMAIL PROTECTED]> wrote: > > max_fsm_pages is 2 > > max_fsm_relations is 1000 > > Do they look low? > > Yes they are probably too low if you don't run VACUUM on a regular > basis and you have a lot of

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Markus Schaber
Hi, Guillaume, Guillaume Cottenceau wrote: > We have a couple of logs files which get larger over time > (millions of rows). As they are log files, they can be trimmed > from older values. Ah, ok, you DELETEd the old rows. So I assume that you never UPDATE, but only INSERT new entries and somet

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Guillaume Smet
Guillaume, On 28 Aug 2006 11:43:16 +0200, Guillaume Cottenceau <[EMAIL PROTECTED]> wrote: max_fsm_pages is 2 max_fsm_relations is 1000 Do they look low? Yes they are probably too low if you don't run VACUUM on a regular basis and you have a lot of UPDATE/DELETE activity. FSM doesn't take

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Guillaume Cottenceau
Hi Markus, Thanks for your message. > Guillaume Cottenceau wrote: > > > We noticed a slowdown on our application while traffic was kinda > > heavy. The logics after reading the docs commanded us to trim the > > enlarged tables, run VACUUM ANALYZE and then expect fast > > performance again; but i

Re: [PERFORM] perf pb solved only after pg_dump and restore

2006-08-28 Thread Markus Schaber
Hi, Guillaume Guillaume Cottenceau wrote: > We noticed a slowdown on our application while traffic was kinda > heavy. The logics after reading the docs commanded us to trim the > enlarged tables, run VACUUM ANALYZE and then expect fast > performance again; but it wasn't the case[1]. What exactly