Re: Optimize the database performance

2011/10/17 Micka <mickamusset(at)gmail(dot)com>:
> 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 ?
>

as other poeple said, you need to give more information on your
hardware and usage of it to get more accurate answers.

Assuming that all your db can stay in RAM, I would start with
random_page_cost = 1 and seq_page_cost = 1.

effective_cache_size should be the sum of all cache space (linux and
postgresql), any number larger than 10GB should be fine, there is no
risk other than bad planning to set it too large (and it won't affect
you here I think)

You have memory available? you can increase the maintenance_work_mem
(and you probably want to do that if you have a maintenance window
when you do the vacuum manually - why not autovacum ?)

For shared_buffers, you should use pg_buffercache to see what's
happening and maybe change the value to something higher (2GB, 4GB,
...) . You can also just test and find the best size for your
application workload.

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation