Re: High CPU Usage

On Thu, Jun 14, 2012 at 11:15 AM, Siddharth Shah
<siddharth(dot)shah(at)elitecore(dot)com> 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