Postgres's Performance degrades after heavy db operation

On Mon, Jun 8, 2009 at 9:38 AM, Preeti
Khurana<preeti.khurana@globallogic.com> wrote:

Any ideas/pointers on what could be making the DB slow after all these heavy
operations being performed?

It seems autovacuum is not running often enough. You may wish to
include vacuum in your data update/delete programs so that it runs
after some number of those operations.

On Mon, Jun 8, 2009 at 2:38 PM, Preeti
Khurana<preeti.khurana@globallogic.com> wrote:

Hi,

   I am using Postgresql 8.1.2 and performing quite a huge number of db
operations ( insert/update/ delete) in around 4 tables. Around 3 million of
db is inserted and then 600 K rows are updated for around 5-6 times during a
day. Its observed that running this process for few days ( 5-6 days) makes
the db access quite slower. Auto-Vacuum is set to ON with other parameters
set to default.

Slony 1.1.5 is being used for the db replication simultaneously.

Any ideas/pointers on what could be making the DB slow after all these heavy
operations being performed?

check if vacuum analyze says anythihing about FSM size being too small.

--
GJ

oh, and take the time to upgrade to newest 8.1.X as the matter of urgency!.

Vick Khera <vivek@khera.org> writes:

On Mon, Jun 8, 2009 at 9:38 AM, Preeti
Khurana<preeti.khurana@globallogic.com> wrote:

Any ideas/pointers on what could be making the DB slow after all these heavy
operations being performed?

It seems autovacuum is not running often enough.

8.1's autovacuum isn't very aggressive by default. You might want to
play around with its parameters.

regards, tom lane

I checked the release notes till 8.1.17, but could not come across any such issue which leads to db performance degradation after doing heavy db operations. AutoVacuum is also enabled parallel.

-----Original Message-----
From: Grzegorz Jaśkiewicz [mailto:gryzman@gmail.com]
Sent: Monday, June 08, 2009 9:59 PM
To: Preeti Khura
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres's Performance degrades after heavy db operation

oh, and take the time to upgrade to newest 8.1.X as the matter of urgency!.

2009/6/10 Preeti Khurana <preeti.khurana@globallogic.com>:

I checked the release notes till 8.1.17, but could not come across any such issue which leads to db performance degradation after doing heavy db operations. AutoVacuum is also enabled parallel.

Note that you might be much better served moving to 8.3 if HOT can
help with your usage patterns. A database I work with dropped in load
by about 1/2 (maybe a bit more) just by switching to 8.3. But it's
not magic pixie dust and if your usage patterns preclude HOT helping
(i.e. updating every row in a table) then I don't expect any miracles.

In response to "Preeti Khurana" <preeti.khurana@globallogic.com>:

I checked the release notes till 8.1.17, but could not come across any such issue which leads to db performance degradation after doing heavy db operations.

I don't think that was his point. I expect it was something more along the
lines of crashing bugs and data corruption bugs that you might want to
have fixed.

AutoVacuum is also enabled parallel.

Which means virtually nothing if we're trying to help you tune. What are
your autovacuum settings? Simply turning it on is not always enough (if
it were, there wouldn't be a need to have other settings, now would there)

And I'll reiterate something that was said on this thread earlier ... it's
likely that autovacuum isn't going to be enough for your usage pattern.

Have you posted the output of VACUUM VERBOSE yet?

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/