Thread: Server Configuration

Hi, I have a Server with Fedora Core 11, Tomcat and Postgresql 8.3.
With Hardware:
* 8GB RAM
* 8 processors Intel Xeon E5520 @2.27GHz
* 250GB SATA DISK

Actually, it serves at most 250 connections.
The problem happends when it serves many many connections at a time, tables and queries began to get blocked, then I have to kill some processes
in order to allow other people continue working.

Wich recommendations could you give me for to configure postgresql.conf, and could it be eficcient to buy another server with almost same hardware
in order to use pgPool2 with replication, load balance and parallel query?.

On Wed, Feb 02, 2011 at 03:15:22PM -0300, Cesar Arrieta wrote:
> Hi, I have a Server with Fedora Core 11, Tomcat and Postgresql 8.3.
> With Hardware:
> * 8GB RAM
> * 8 processors Intel Xeon E5520 @2.27GHz
> * 250GB SATA DISK
>
> Actually, it serves at most 250 connections.
> The problem happends when it serves many many connections at a time, tables
> and queries began to get blocked, then I have to kill some processes
> in order to allow other people continue working.
>
> Wich recommendations could you give me for to configure postgresql.conf, and
> could it be eficcient to buy another server with almost same hardware
> in order to use pgPool2 with replication, load balance and parallel query?.

It sounds like you may just need a connection pooler (pgpool, pgbouncer)
and it might work just fine.

Cheers,
Ken

I would personally highly recommend using pgBouncer!  I have been using
this in production migrating from MySQL and have had phenomenal success
with it combined with lighttpd and php as an internal information
system.  I am getting on average 300 requests per second very low load
average as it is maintaining between 10 and 20 DB connections throughout
the day.

I would also recommend splitting the application server and the DB
server if at all possible.  Doing this would make it possible to do
horizontal scaling by way of a reverse proxy in front of the application
server.

The overhead I experienced of natively running Postgres with PHP was a
performance killer.  Connecting to the DB and disconnecting was killing
the CPU - as it seems with your problem.

What is the nature of the application which will be running on this
server?

Best of Luck!
Richard Carnes


-----Original Message-----
From: Kenneth Marshall [mailto:ktm@rice.edu]
Sent: Wednesday, February 02, 2011 1:32 PM
To: Cesar Arrieta
Cc: pgsql-performance@postgresql.org
Subject: Re: Server Configuration


On Wed, Feb 02, 2011 at 03:15:22PM -0300, Cesar Arrieta wrote:
> Hi, I have a Server with Fedora Core 11, Tomcat and Postgresql 8.3.
> With Hardware:
> * 8GB RAM
> * 8 processors Intel Xeon E5520 @2.27GHz
> * 250GB SATA DISK
>
> Actually, it serves at most 250 connections.
> The problem happends when it serves many many connections at a time,
tables
> and queries began to get blocked, then I have to kill some processes
> in order to allow other people continue working.
>
> Wich recommendations could you give me for to configure
postgresql.conf, and
> could it be eficcient to buy another server with almost same hardware
> in order to use pgPool2 with replication, load balance and parallel
query?.

It sounds like you may just need a connection pooler (pgpool, pgbouncer)
and it might work just fine.

Cheers,
Ken

The information contained in this electronic message from CCH Small Firm Services, and any attachments, contains
informationthat may be confidential and/or privileged. If you are not the intended recipient, you are hereby notified
thatany disclosure, copying, distribution or use of this information is strictly prohibited. If you have received this
communicationin error, please notify CCH Small Firm Services immediately by e-mail or by telephone at 770-857-5000, and
destroythis communication. Thank you. 

On Wed, 2011-02-02 at 15:15 -0300, Cesar Arrieta wrote:
> Hi, I have a Server with Fedora Core 11, Tomcat and Postgresql 8.3.
> With Hardware:
> * 8GB RAM
> * 8 processors Intel Xeon E5520 @2.27GHz
> * 250GB SATA DISK
>
> Actually, it serves at most 250 connections.
> The problem happends when it serves many many connections at a time,
> tables and queries began to get blocked, then I have to kill some
> processes
> in order to allow other people continue working.
>
> Wich recommendations could you give me for to configure
> postgresql.conf, and could it be eficcient to buy another server with
> almost same hardware
> in order to use pgPool2 with replication, load balance and parallel
> query?.
My first recommedation is to update your PostgreSQL version to 9.0 and
later you can use the PgPool-II version 3.0 in order to use the Hot
standby/Streaming Replication features with it.

Here is a example of the configuration of PgPool-II 3.0.1 and
PostgreSQL-9.0.2
http://lists.pgfoundry.org/pipermail/pgpool-general/2011-February/003338.html

Regards



--
Ing. Marcos Luís Ortíz Valmaseda
System Engineer -- Database Administrator

Centro de Tecnologías de Gestión de Datos (DATEC)
Universidad de las Ciencias Informáticas
http://postgresql.uci.cu