DB Slowing Down

[Alex]

Hi,
we have a database. not to big about 50 tables, 20m records. On a daily
basis we update/insert/delete between 500k to 1M records in total. We
run a full vacuum every night. The db grows probably by 200k records
each day and at the end of the month it gets cleaned up.
We run 7.4.1 and 7.4.6

Now, after a few weeks the db starts to slow down after about 2 months
same operations take 2-3 times as long as originally. The only way to
speed it up again is to drop the entire database; run an initdb, create
and restore the database.

Is that an expected behavior?
Any help or suggestions are appreciated.

Thanks
Alex


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Richard Huxton]

Alex wrote:

> Hi,
> we have a database. not to big about 50 tables, 20m records. On a daily
> basis we update/insert/delete between 500k to 1M records in total. We
> run a full vacuum every night. The db grows probably by 200k records
> each day and at the end of the month it gets cleaned up.
> We run 7.4.1 and 7.4.6
>
> Now, after a few weeks the db starts to slow down after about 2 months
> same operations take 2-3 times as long as originally. The only way to
> speed it up again is to drop the entire database; run an initdb, create
> and restore the database.

Information, Alex, information.
Can you give a specific example of where a query gets slower?
Is this a gradual effect or sudden?
Do you know if your indexes are growing unexpectedly? (shouldn't be, but
worth keeping an eye on)
Are you happy PG is tuned

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email]majordomo@postgresql.org[/email] so that your
message can get through to the mailing list cleanly

[Alex]

It a gradual process. For example, we have 3 reference tables that get
updated very day. they have between 3,5 and 7M records. All we do is
simple inserts, deletes. The number of records is different each day so
its a bit difficult to say.
Another table is a price database. This is where we actually see the
biggest difference. The 2 tables have about 2M records each, each day we
add about 60-80k records and update about the same number. At the end of
the day about 80% of these will be removed. So the tables grow by 500k
records a month, but at the end of the month again we remove about
300-400k of these records. What we see is that this load of 60-80
records at the beginning of a month and after re creating the db takes
in the are of 8min or so. after 2-3 month the whole process will take up
to 20-25 minutes. even though the two tables have only grown max. 500k.
Even the cleanup at the end of the month does not help a lot. it speeds
things up but nowhere close to what it would be after recreating the db.

Alex

Richard Huxton wrote:

> Alex wrote:
>

>> Hi,
>> we have a database. not to big about 50 tables, 20m records. On a
>> daily basis we update/insert/delete between 500k to 1M records in
>> total. We run a full vacuum every night. The db grows probably by
>> 200k records each day and at the end of the month it gets cleaned up.
>> We run 7.4.1 and 7.4.6
>>
>> Now, after a few weeks the db starts to slow down after about 2
>> months same operations take 2-3 times as long as originally. The only
>> way to speed it up again is to drop the entire database; run an
>> initdb, create and restore the database.

>
>
> Information, Alex, information.
> Can you give a specific example of where a query gets slower?
> Is this a gradual effect or sudden?
> Do you know if your indexes are growing unexpectedly? (shouldn't be,
> but worth keeping an eye on)
> Are you happy PG is tuned
>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

[Richard Huxton]

Alex wrote:

> It a gradual process. For example, we have 3 reference tables that get
> updated very day. they have between 3,5 and 7M records. All we do is
> simple inserts, deletes. The number of records is different each day so
> its a bit difficult to say.
> Another table is a price database. This is where we actually see the
> biggest difference. The 2 tables have about 2M records each, each day we
> add about 60-80k records and update about the same number. At the end of
> the day about 80% of these will be removed. So the tables grow by 500k
> records a month, but at the end of the month again we remove about
> 300-400k of these records. What we see is that this load of 60-80
> records at the beginning of a month and after re creating the db takes
> in the are of 8min or so. after 2-3 month the whole process will take up
> to 20-25 minutes. even though the two tables have only grown max. 500k.
> Even the cleanup at the end of the month does not help a lot. it speeds
> things up but nowhere close to what it would be after recreating the db.

Well, if you're not getting index bloat (and I don't think you should be
in 7.4.x) then that suggests you're doing a VACUUM not VACUUM FULL (and
don't have enough free-space-map allocated. Try adding a VERBOSE to your
VACUUMs and see what figures come out, and check the manuals for the
fsm... settings.

Alternatively, it could be that the physical order of records is better
when you dump/restore. It might be looking into what CLUSTER might do
for you if run overnight.

Basically, it sounds like one of 3 things:
1. The indexes are continually growing. (REINDEX)
2. Increasing numbers of "gaps" in the tables due to updates/deletes.
(VACUUM)
3. The dump/restore puts the data in a useful physical ordering which
gets shuffled as you update. (CLUSTER)

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Alex]

Thanks for the suggestions.
I run a vaccum analyze every night. I will look into all the options you
suggested.

Thanks
Alex

Richard Huxton wrote:

> Alex wrote:
>

>> It a gradual process. For example, we have 3 reference tables that
>> get updated very day. they have between 3,5 and 7M records. All we
>> do is simple inserts, deletes. The number of records is different
>> each day so its a bit difficult to say.
>> Another table is a price database. This is where we actually see the
>> biggest difference. The 2 tables have about 2M records each, each day
>> we add about 60-80k records and update about the same number. At the
>> end of the day about 80% of these will be removed. So the tables grow
>> by 500k records a month, but at the end of the month again we remove
>> about 300-400k of these records. What we see is that this load of
>> 60-80 records at the beginning of a month and after re creating the
>> db takes in the are of 8min or so. after 2-3 month the whole process
>> will take up to 20-25 minutes. even though the two tables have only
>> grown max. 500k. Even the cleanup at the end of the month does not
>> help a lot. it speeds things up but nowhere close to what it would be
>> after recreating the db.

>
>
> Well, if you're not getting index bloat (and I don't think you should
> be in 7.4.x) then that suggests you're doing a VACUUM not VACUUM FULL
> (and don't have enough free-space-map allocated. Try adding a VERBOSE
> to your VACUUMs and see what figures come out, and check the manuals
> for the fsm... settings.
>
> Alternatively, it could be that the physical order of records is
> better when you dump/restore. It might be looking into what CLUSTER
> might do for you if run overnight.
>
> Basically, it sounds like one of 3 things:
> 1. The indexes are continually growing. (REINDEX)
> 2. Increasing numbers of "gaps" in the tables due to updates/deletes.
> (VACUUM)
> 3. The dump/restore puts the data in a useful physical ordering which
> gets shuffled as you update. (CLUSTER)
>
> --
> Richard Huxton
> Archonet Ltd
>
>

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Geoffrey]

Alex wrote:

> Thanks for the suggestions.
> I run a vaccum analyze every night. I will look into all the options you
> suggested.

And please post back your results as there are others who are interested
in this thread. :)

--
Until later, Geoffrey

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]