Re: Postgres Performance Tuning

Also you can try to take the help of pgtune before hand.

pgfoundry.org/projects/*pgtune*/

On Mon, Apr 4, 2011 at 12:43 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
> wrote:
> > Dear all,
> >
> > I have a Postgres database server with 16GB RAM.
> > Our application runs by making connections to Postgres Server from
> different
> > servers and selecting data from one table & insert into remaining tables
> in
> > a database.
> >
> > Below is the no. of connections output :-
> >
> > postgres=# select datname,numbackends from pg_stat_database;
> > datname | numbackends
> > -------------------+-------------
> > template1 | 0
> > template0 | 0
> > postgres | 3
> > template_postgis | 0
> > pdc_uima_dummy | 107
> > pdc_uima_version3 | 1
> > pdc_uima_olap | 0
> > pdc_uima_s9 | 3
> > pdc_uima | 1
> > (9 rows)
> >
> > I am totally confused for setting configuration parameters in Postgres
> > Parameters :-
> >
> > First of all, I research on some tuning parameters and set mu
> > postgresql.conf as:-
> >
> > max_connections = 1000
>
> That's a little high.
>
> > shared_buffers = 4096MB
> > work_mem = 64MB
>
> That's way high. Work mem is PER SORT as well as PER CONNECTION.
> 1000 connections with 2 sorts each = 128,000MB.
>
> > [root(at)s8-mysd-2 ~]# free total used free
> shared
> > buffers cached
> > Mem: 16299476 16202264 97212 0 58924 15231852
> > -/+ buffers/cache: 911488 15387988
> > Swap: 16787884 153136 16634748
>
> There is nothing wrong here. You're using 153M out of 16G swap. 15.x
> Gig is shared buffers. If your system is slow, it's not because it's
> running out of memory or using too much swap.
>
> >
> > I think there may be some problem in my Configuration parameters and
> change
> > it as :
>
> Don't just guess and hope for the best. Examine your system to
> determine where it's having issues. Use
> vmstat 10
> iostat -xd 10
> top
> htop
>
> and so on to see where your bottleneck is. CPU? Kernel wait? IO wait?
> etc.
>
> log long running queries. Use pgfouine to examine your queries.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>