Re: Postgres Performance Tuning

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.