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
