Skip site navigation (1) Skip section navigation (2)

Re: High CPU Usage

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Siddharth Shah <siddharth(dot)shah(at)elitecore(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: High CPU Usage
Date: 2012-07-23 19:38:35
Message-ID: CA+TgmoaBZ=w0uC7r01FxXi+t1FW1UwtgzXWnEyWLS9otrPmWwg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Kenneth TiltonDate: 2012-07-23 21:12:39
Subject: Efficiency of EXISTS?
Previous:From: Robert HaasDate: 2012-07-23 19:07:10
Subject: Re: postgres clustering interactions with pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group