pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimize the database performance
4+ messages / 4 participants
[nested] [flat]
* Optimize the database performance
@ 2011-10-17 09:48 Micka <mickamusset@gmail.com>
2011-10-17 10:39 ` Re: Optimize the database performance MirrorX <mirrorx@gmail.com>
2011-10-17 18:35 ` Re: Optimize the database performance Andy Colson <andy@squeakycode.net>
2011-10-17 20:31 ` Re: Optimize the database performance Cédric Villemain <cedric.villemain.debian@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Micka @ 2011-10-17 09:48 UTC (permalink / raw)
To: pgsql-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,
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimize the database performance
2011-10-17 09:48 Optimize the database performance Micka <mickamusset@gmail.com>
@ 2011-10-17 10:39 ` MirrorX <mirrorx@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: MirrorX @ 2011-10-17 10:39 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimize the database performance
2011-10-17 09:48 Optimize the database performance Micka <mickamusset@gmail.com>
@ 2011-10-17 18:35 ` Andy Colson <andy@squeakycode.net>
2 siblings, 0 replies; 4+ messages in thread
From: Andy Colson @ 2011-10-17 18:35 UTC (permalink / raw)
To: Micka <mickamusset@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimize the database performance
2011-10-17 09:48 Optimize the database performance Micka <mickamusset@gmail.com>
@ 2011-10-17 20:31 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Cédric Villemain @ 2011-10-17 20:31 UTC (permalink / raw)
To: Micka <mickamusset@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-17 20:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-17 09:48 Optimize the database performance Micka <mickamusset@gmail.com>
2011-10-17 10:39 ` MirrorX <mirrorx@gmail.com>
2011-10-17 18:35 ` Andy Colson <andy@squeakycode.net>
2011-10-17 20:31 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox