Обсуждение: Ideal configuration for postgresql 9.3 config
Hi,
I have POstgresql Server 9.3 setup, below are the Hardware specs
CPU: 16 cores
RAM: 48GB
Disk for Data is at a fiber channel SAN storage.
Below are the config
#########################################
listen_addresses = '*'
max_connections = 1024
shared_buffers = 8000MB
temp_buffers = 8MB
work_mem = 4096MB
maintenance_work_mem = 512MB
checkpoint_segments = 512
checkpoint_completion_target = 0.9
enable_hashjoin = on
enable_indexscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = off
enable_sort = on
enable_tidscan = on
effective_cache_size = 16000MB
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 10000
log_line_prefix = '%t %u %d %h '
log_statement = 'ddl'
autovacuum = off
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 2000
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
max_locks_per_transaction = 96
#####################################################
The config are originally from 9.2, we just copy and paste replacing the default of 9.3. We have based some parameters based on the current specs but still, there might be some changes need to be set. Also the autovacuum for some reason was turned off, is it advisable to turn it off or should this be ideal to be turned on? Hope someone can provide advise on the proper or optimized parameters.
Thanks in advance
Oliver
Hi Oliver,
I have suggested a few changes below.On Tue, Feb 16, 2016 at 7:15 PM, Oliver Jagape <oliver.jagape@concentrix.com> wrote:
CPU: 16 cores
RAM: 48GB
Disk for Data is at a fiber channel SAN storage.
max_connections = 1024
If you need that many concurrent connections, invest in a connection pooler. If no connection pooler is used, knock off 800 connections if you can.
shared_buffers = 8000MB
Test with a lower setting like 4GB, depending on your workload, that may or may not perform better.
work_mem = 4096MB
This magnitudes too high. It is per query * sort and aggregation steps. If you expect queries with large result sets (or distinct, group by, etc), 100MB is probably a reasonable starting point.
maintenance_work_mem = 512MB
Depending on your relation size, you might want to double this.
checkpoint_segments = 512
Is that a reason you chose such a high number?
enable_nestloop = off
Unless you have a clear reason to disable this (it applies to all queries), leave this enabled.
autovacuum = off
autovacuum should be on unless you know your data churn patterns and have scheduled manual vacuums.
autovacuum_naptime = 60
You probably want that more aggressive, like 5s.
Thanks for the reply,
I tried some of suggested changes and some are modified based on this Link I stumbled on the web..
Though I'll be able to observe the outcome on the next few days when most of the users will rush on using the apps that depend on this db.
Regards,
Oliver
From: bricklen <bricklen@gmail.com>
Sent: Wednesday, February 17, 2016 12:59 PM
To: Oliver Jagape
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Ideal configuration for postgresql 9.3 config
Sent: Wednesday, February 17, 2016 12:59 PM
To: Oliver Jagape
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Ideal configuration for postgresql 9.3 config
Hi Oliver,
I have suggested a few changes below.On Tue, Feb 16, 2016 at 7:15 PM, Oliver Jagape <oliver.jagape@concentrix.com> wrote:
CPU: 16 cores
RAM: 48GB
Disk for Data is at a fiber channel SAN storage.
max_connections = 1024
If you need that many concurrent connections, invest in a connection pooler. If no connection pooler is used, knock off 800 connections if you can.
shared_buffers = 8000MB
Test with a lower setting like 4GB, depending on your workload, that may or may not perform better.
work_mem = 4096MB
This magnitudes too high. It is per query * sort and aggregation steps. If you expect queries with large result sets (or distinct, group by, etc), 100MB is probably a reasonable starting point.
maintenance_work_mem = 512MB
Depending on your relation size, you might want to double this.
checkpoint_segments = 512
Is that a reason you chose such a high number?
enable_nestloop = off
Unless you have a clear reason to disable this (it applies to all queries), leave this enabled.
autovacuum = off
autovacuum should be on unless you know your data churn patterns and have scheduled manual vacuums.
autovacuum_naptime = 60
You probably want that more aggressive, like 5s.