Обсуждение: Tuning 9.3 for 32 GB RAM
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
Hi,
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and see what
http://pgfoundry.org/projects/pgtune/
On Thu, Nov 14, 2013 at 7:14 PM, Alexander Farber <alexander.farber@gmail.com> wrote:
You may want to look at effective_cache_size also. May be quickly go throughHello,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 = 4096MBwork_mem = 32MBcheckpoint_segments = 32log_min_duration_statement = 10000sysctl.conf:kernel.shmmax=17179869184kernel.shmall=4194304pgbouncer.ini:listen_port = 6432unix_socket_dir = /tmppool_mode = sessionserver_reset_query = DISCARD ALLserver_check_delay = 10max_client_conn = 600default_pool_size = 50I 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 youAlex
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and see what
http://pgfoundry.org/projects/pgtune/
says.
Jayadevan
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...
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:
And pgtune is 4 years old...
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... > > >
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
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
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