Postgres Pro
Downloads
Home   >   mailing lists

Optimize the database performance - Mailing list pgsql-performance

From Micka
Subject Optimize the database performance
Date
Msg-id CAF+MRtnRr_8QqZ5ZGz0_H8jzvn8KND=K7cVzX8aywiumN+UJwA@mail.gmail.com
Whole thread Raw
Responses Re: Optimize the database performance
Re: Optimize the database performance
Re: Optimize the database performance
List pgsql-performance
Tree view
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,

pgsql-performance by date:

Previous
From: Robins Tharakan
Date:
Subject: Bad plan by Planner (Already resolved?)
Next
From: MirrorX
Date:
Subject: Re: Optimize the database performance