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
>
>
