Message-ID: <4E9C755D.4070608@squeakycode.net>
Date: Mon, 17 Oct 2011 13:35:09 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.21) Gecko/20110903 Lightning/1.0b2 Thunderbird/3.1.13
MIME-Version: 1.0
To: Micka <mickamusset@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Optimize the database performance
References: 
 <CAF+MRtnRr_8QqZ5ZGz0_H8jzvn8KND=K7cVzX8aywiumN+UJwA@mail.gmail.com>
In-Reply-To: 
 <CAF+MRtnRr_8QqZ5ZGz0_H8jzvn8KND=K7cVzX8aywiumN+UJwA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 10/17/2011 04:48 AM, Micka wrote:
> Hi,
>
> I've a postgres 9.1 database used for map generating ( tiles ).
> The system has 24Go RAM and 5 processors.
> I'm using geoserver to generate the tiles.
>
> My data used 8486 MB  => psql -d gis -c "SELECT pg_size_pretty(pg_database_size('gis'))"
>
> I've carefully indexes the table by the "the_geom" column.
>
> Here is my database config :
>
> --> change :
> --> listen_addresses = '*'
> --> max_connections = 50
> --> tcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;
> --> shared_buffers = 1024MB                 # 10% of available RAM
> --> work_mem = 256MB                        # min 64kB
> --> maintenance_work_mem = 256MB            # min 1MB
> --> effective_cache_size = 5120MB
> --> autovacuum = off
>
> sudo nano /etc/sysctl.conf
> -->   kernel.shmmax=5368709120
> -->   kernel.shmall=5368709120
>
> I wanted to have your opinion about this config ?  What can I do to optimize the performance ?
>
> Thank you,
>

Yeah... We're gonna need some more details.  Whats slow?  Are you CPU bound or IO bound?  How many concurrent db connections?  What does vmstat look like?  And 10% of 24 gig is 2.4 gig, not 1 gig.

Or is this box doing something else.  I noticeeffective_cache_size is only 5 gig, so you must be doing other things on this box.

> --> autovacuum = off

Are you vacuuming by hand!?   If not this is a "really bad idea" (tm)(c)(r)

-Andy