Thread: Optimize the database performance

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,

hello Micha,

i think that noone can tell you much without more information about your
system. roughly i would say that you could change the following parameters:
shared_buffers = 1024MB -> 6GB
work_mem = 256MB -> 30-50 MB
effective_cache_size = 5120MB -> 16GB (depends on whether its a dedicated db
server or not)
kernel.shmmax=5368709120 : now its 5GB, probably you need more here, i would
put 50% of ram
kernel.shmall=5368709120 you need less here. check he shmsetup.sh script for
more info
autovacuum off -> on


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Optimize-the-database-performance-tp4909314p4909422.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

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
thisbox. 

> --> autovacuum = off

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

-Andy

2011/10/17 Micka <mickamusset@gmail.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