On Thu, Jun 14, 2012 at 11:15 AM, Siddharth Shah <siddharth.shah@xxxxxxxxxxxxx> wrote: > 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 > configured There's no real reason to run vacuum analyze manually if you have autovacuum 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 ? 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 > performance -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
