Re: Optimize the database performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux