Tuning 9.3 for 32 GB RAM

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

Hello, my problem with
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
is that it never mentions larger amounts of RAM,
so while reading it I always wonder how up-to-date it is...

And pgtune is 4 years old...

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:

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

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

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

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

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

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

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

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