From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | Micka <mickamusset(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimize the database performance |
Date: | 2011-10-17 18:35:09 |
Message-ID: | 4E9C755D.4070608@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Cédric Villemain | 2011-10-17 20:31:13 | Re: Optimize the database performance |
Previous Message | Kevin Grittner | 2011-10-17 16:02:12 | Re: Bad plan by Planner (Already resolved?) |