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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Tilton | 2012-07-23 21:12:39 | Efficiency of EXISTS? |
Previous Message | Robert Haas | 2012-07-23 19:07:10 | Re: postgres clustering interactions with pg_dump |