On Thu, Jun 14, 2012 at 11:15 AM, Siddharth Shah
> I have around 1000 schema in database, Each schema having similar data
> structure with different data
> Each schema has few tables which never updates (Read only table) and other
> tables rewrites almost everyday so I prefer to TRUNCATE those tables and
> restores with new data
> Now facing issue on high CPU & IO on database primarily of Stats Collector &
> Vacuuming, size of statfile is almost 28MB
How many tables do you have across all the schemas?
> and when I manually vacuum
> analyze complete database it takes almost 90 minutes though auto vacuum is
There's no real reason to run vacuum analyze manually if you have
> Restoring dump on each schema may minor data variations
> Executing SQL statements on schema are few , Affecting less than 50 touple /
> My Questions :
> Increasing Maintainace_Work_Mem improves auto / manual vacuum performance ?
It can, but mostly if there are a lot of updates or deletes. If the
tables aren't changing much it isn't going to do anything.
> If it improves will it require more IO / CPU resource ?
> If I stops Stats Collector process & auto vaccuming & Execute manual vaccum
> based on schema restoration with major change what performance parameter I
> need to consider ? (Restoring data has vary few changes)
> Is Vacuuming & Stats required here for Metadata for improving performance ?
> (Table structures remain same)
> Any more on this which can help to reduce IO without affecting major
The Enterprise PostgreSQL Company
In response to
pgsql-performance by date
|Next:||From: Kenneth Tilton||Date: 2012-07-23 21:12:39|
|Subject: Efficiency of EXISTS?|
|Previous:||From: Robert Haas||Date: 2012-07-23 19:07:10|
|Subject: Re: postgres clustering interactions with pg_dump|