pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

High CPU Usage
2+ messages / 2 participants
[nested] [flat]

* High CPU Usage
@ 2012-06-14 15:15  Siddharth Shah <siddharth.shah@elitecore.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Siddharth Shah @ 2012-06-14 15:15 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: High CPU Usage
@ 2012-07-23 19:38  Robert Haas <robertmhaas@gmail.com>
  parent: Siddharth Shah <siddharth.shah@elitecore.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Robert Haas @ 2012-07-23 19:38 UTC (permalink / raw)
  To: Siddharth Shah <siddharth.shah@elitecore.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2012-07-23 19:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-14 15:15 High CPU Usage Siddharth Shah <siddharth.shah@elitecore.com>
2012-07-23 19:38 ` Robert Haas <robertmhaas@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox