Thread: Tuning 9.3 for 32 GB RAM

Tuning 9.3 for 32 GB RAM

From
Alexander Farber
Date:
Hello,

do these changes please look okay for a PostgreSQL 9.3 running on CentOS 6.4 server with 32 GB RAM (with Drupal 7 and few custom PHP scripts)

postgresql.conf:

   shared_buffers = 4096MB
   work_mem = 32MB
   checkpoint_segments = 32
   log_min_duration_statement = 10000

sysctl.conf:

   kernel.shmmax=17179869184
   kernel.shmall=4194304

pgbouncer.ini:

   listen_port = 6432
   unix_socket_dir = /tmp
   pool_mode = session
   server_reset_query = DISCARD ALL
   server_check_delay = 10
   max_client_conn = 600
   default_pool_size = 50

I understand, that nobody can tell me the optimal settings - unless I provide full source code to everything. And if I provide "the full source code", nobody will look at it anyway.

So I am just asking, if the settings look okay or if they will waste gigabytes of RAM.

Thank you
Alex

Re: Tuning 9.3 for 32 GB RAM

From
Jayadevan M
Date:
Hi,

On Thu, Nov 14, 2013 at 7:14 PM, Alexander Farber <alexander.farber@gmail.com> wrote:
Hello,

do these changes please look okay for a PostgreSQL 9.3 running on CentOS 6.4 server with 32 GB RAM (with Drupal 7 and few custom PHP scripts)

postgresql.conf:

   shared_buffers = 4096MB
   work_mem = 32MB
   checkpoint_segments = 32
   log_min_duration_statement = 10000

sysctl.conf:

   kernel.shmmax=17179869184
   kernel.shmall=4194304

pgbouncer.ini:

   listen_port = 6432
   unix_socket_dir = /tmp
   pool_mode = session
   server_reset_query = DISCARD ALL
   server_check_delay = 10
   max_client_conn = 600
   default_pool_size = 50

I understand, that nobody can tell me the optimal settings - unless I provide full source code to everything. And if I provide "the full source code", nobody will look at it anyway.

So I am just asking, if the settings look okay or if they will waste gigabytes of RAM.

Thank you
Alex

You may want to look at effective_cache_size also. May be quickly go through
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and see what
http://pgfoundry.org/projects/pgtune/
says.
Jayadevan

Re: Tuning 9.3 for 32 GB RAM

From
Alexander Farber
Date:
Hello, my problem with
is that it never mentions larger amounts of RAM,
so while reading it I always wonder how up-to-date it is...



Re: Tuning 9.3 for 32 GB RAM

From
Alexander Farber
Date:
And pgtune is 4 years old...


Re: Tuning 9.3 for 32 GB RAM

From
Alexander Farber
Date:
pgtune has produced the following for my server (the specs: http://www.hetzner.de/en/hosting/produkte_rootserver/px60ssd ):

default_statistics_target = 50
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 22GB
work_mem = 192MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 7680MB
max_connections = 80

Is it really okay? Isn't 22GB too high?
And how does it know that max_connections =80 is enough in my case? (I use pgbouncer).

Regards
Alex






On Thu, Nov 14, 2013 at 4:04 PM, Alexander Farber <alexander.farber@gmail.com> wrote:
And pgtune is 4 years old...



Re: Tuning 9.3 for 32 GB RAM

From
Zev Benjamin
Date:
On 11/14/2013 10:09 AM, Alexander Farber wrote:
> pgtune has produced the following for my server (the specs:
> http://www.hetzner.de/en/hosting/produkte_rootserver/px60ssd ):
>
> default_statistics_target = 50
> maintenance_work_mem = 1GB
> constraint_exclusion = on
> checkpoint_completion_target = 0.9
> effective_cache_size = 22GB
> work_mem = 192MB
> wal_buffers = 8MB
> checkpoint_segments = 16
> shared_buffers = 7680MB
> max_connections = 80
>
> Is it really okay? Isn't 22GB too high?
> And how does it know that max_connections =80 is enough in my case? (I
> use pgbouncer).

It doesn't.  There is a static map between the "type" (the -T option)
pgtune is using and the max_connections value it sets.  You should
consider the output of pgtune as a guideline rather than "optimal settings."


Zev

>
> Regards
> Alex
>
>
>
>
>
>
> On Thu, Nov 14, 2013 at 4:04 PM, Alexander Farber
> <alexander.farber@gmail.com <mailto:alexander.farber@gmail.com>> wrote:
>
>     And pgtune is 4 years old...
>
>
>


Re: Tuning 9.3 for 32 GB RAM

From
Sergey Konoplev
Date:
On Thu, Nov 14, 2013 at 5:44 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> sysctl.conf:
>
>    kernel.shmmax=17179869184
>    kernel.shmall=4194304

You can also consult this document about kernel and OS settings:

https://github.com/grayhemp/pgcookbook/blob/master/database_server_configuration.md

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com


Re: Tuning 9.3 for 32 GB RAM

From
"Tomas Vondra"
Date:
On 14 Listopad 2013, 16:09, Alexander Farber wrote:
> pgtune has produced the following for my server (the specs:
> http://www.hetzner.de/en/hosting/produkte_rootserver/px60ssd ):
>
> default_statistics_target = 50
> maintenance_work_mem = 1GB
> constraint_exclusion = on
> checkpoint_completion_target = 0.9
> effective_cache_size = 22GB
> work_mem = 192MB
> wal_buffers = 8MB
> checkpoint_segments = 16
> shared_buffers = 7680MB
> max_connections = 80
>
> Is it really okay? Isn't 22GB too high?

effective_cache_size is a hint on how much memory is there for filesystem
cache, so that it can be considered when planning queries. PostgreSQL
relies on filesystem cache, so this is needed to answer questions like
'how probable it's the block is in cache and won't actually cause any
I/O"?

It does not allocate anything. You do have 32GB of RAM in total, so 22GB
for caches seems about right unless you're running other memory-intensive
applications on the same box (making less memory to be available for the
filesystem cache).

Tomas



Re: Tuning 9.3 for 32 GB RAM

From
Michael Paquier
Date:
On Thu, Nov 14, 2013 at 10:44 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> sysctl.conf:
>
>    kernel.shmmax=17179869184
>    kernel.shmall=4194304
You do not need those settings in sysctl.conf since 9.3 as consumption
of V shared memory has been reduced with this commit:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b0fc0df9364d2d2d17c0162cf3b8b59f6cb09f67
Regards,
--
Michael