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

Re: PostgreSQL 8.4 performance tuning questions

From: tv(at)fuzzy(dot)cz
To: "Rauan Maemirov" <rauan(at)maemirov(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL 8.4 performance tuning questions
Date: 2009-07-30 13:39:42
Message-ID: 5683.193.179.187.70.1248961182.squirrel@sq.gransy.com (view raw or flat)
Thread:
Lists: pgsql-performance
> Hi, list. I've just upgraded pgsql from 8.3 to 8.4. I've used pgtune
> before and everything worked fine for me.
>
> And now i have ~93% cpu load. Here's changed values of config:
>
> default_statistics_target = 50
> maintenance_work_mem = 1GB
> constraint_exclusion = on
> checkpoint_completion_target = 0.9
> effective_cache_size = 22GB
> work_mem = 192MB
> wal_buffers = 8MB
> checkpoint_segments = 16
> shared_buffers = 7680MB
> max_connections = 80
>
>
> My box is Nehalem 2xQuad 2.8 with RAM 32Gb, and there's only
> postgresql working on it.
>
> For connection pooling i'm using pgbouncer's latest version with
> pool_size 20 (used 30 before, but now lowered) and 10k connections.
>
> What parameters i should give more attention on?
>

All the values seem quite reasonable to me. What about the _cost variables?

I guess one or more queries are evaluated using a different execution
plan, probably sequential scan instead of index scan, hash join instead of
merge join, or something like that.

Try to log the "slow" statements - see "log_min_statement_duration". That
might give you slow queries (although not necessarily the ones causing
problems), and you can analyze them.

What is the general I/O activity? Is there a lot of data read/written to
the disks, is there a lot of I/O wait?

regards
Tomas

PS: Was the database analyzed recently?


In response to

Responses

pgsql-performance by date

Next:From: Rauan MaemirovDate: 2009-07-30 14:07:59
Subject: Re: PostgreSQL 8.4 performance tuning questions
Previous:From: parimalaDate: 2009-07-30 12:54:30
Subject: Why is PostgreSQL so slow on Windows ( Postgres 8.3.7) version

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