Hello,
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 and when I
manually vacuum analyze complete database it takes almost 90 minutes
though auto vacuum is configured
Restoring dump on each schema may minor data variations
Executing SQL statements on schema are few , Affecting less than 50
touple / day
My Questions :
Increasing Maintainace_Work_Mem improves auto / manual vacuum
performance ? 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
performance
regards,
Siddharth