pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql-9.1 CentOS7 effective_cache_size issue
2+ messages / 2 participants
[nested] [flat]
* Postgresql-9.1 CentOS7 effective_cache_size issue
@ 2015-08-11 08:38 Michael H <michael@wemoto.com>
0 siblings, 1 reply; 2+ messages in thread
From: Michael H @ 2015-08-11 08:38 UTC (permalink / raw)
To: pgsql-admin
Hi All,
I've been performance tuning a new database server for the past couple
of weeks with very mixed results, I've read every guide to tuning I can
locate on Google aswell as Gregory Smiths - Postgresql 9.0 High
Performance book.
The server is a HP DL385P gen8, dual processor AMD Opteron 6386SE,
16core 2.8Ghz (32 cores total). 128GB DDR3 1600mhz, 8 x 16GB sticks. 4 x
300GB 6G SAS 10K in a RAID1+0 configuration.
We are using CentOS7.1 minimal with Postgresql-9.2.13.
I'm seeing good IOPS, memory throughput is good, the server is
benchmarking very well in comparison to it's predecessor.
I have left most of the configuration as defaults and tuned the
following parameters;
shared_buffers = 8GB
max_prepared_transactions = 5
work_mem = 32MB
max_stack_depth = 7MB
max_files_per_process = 1000000
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 128
wal_buffers=64MB
checkpoint_segments = 64
maintenance_work_mem=2GB
## note this is commented out
#effective_cache_size = 40GB
# increased logging levels for PGBADGER
track_activities = on
track_counts = on
track_functions = all
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
log_min_duration_statement = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 0
client_min_messages = notice
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = off
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u '
log_lock_waits = on
log_statement = 'none'
log_temp_files = 0
From my readings online I have configured shmmax and shmall in
/etc/sysctl.conf, the suggested settings were 50% of RAM dedicated to
shared_buffers.
kernel.shmmax = 67462680576 # roughly 62GB allowing 60GB for PGSQL.
kernel.shmall = 16470381 # shmmax/16 the same ratio as default values
and my previous server.
the shmmax and shamall can be reduced, this was my starting point.
Now, when I make changes with work_mem and shared_buffers I am seeing
performance increases / decreases as I would expect.
When I set effective_cache_size to anything other than the default
(comment out my setting) my TPS takes a huge nose dive, from 37TPS down
to 5TPS.
wal_buf wal_seg effective_cache_size shared_buffers work_mem
64MB 64 defaults 8GB 64MB
pgbench - my data on my database
TPS total transactions
37.324716 11224
34.353093 10337
19.832292 6003
10.010148 3120
5.859798 2073
changing effective_cache_size (tested from 1GB to 80GB) causes these
benchmark results
wal_buf wal_seg effective_cache_size shared_buffers work_mem
64MB 64 ***** 8GB 64MB
pgbench - my data on my database
TPS total transactions
5.86 1,770
3.78 1,168
1.34 430
0.66 258
0.37 512
looking at vmstat, free, top and ipcs I'm not seeing anything unusual,
nothing is being swapped to disk, cache is not flooding and I am only
consuming about 8GB of RAM no matter what configuration changes I make.
Are there known issues with Postgresql-9.2.13 and Centos7? I found one
article where a guy had the same kind of issues with memory consumption.
http://postgresql.nabble.com/PostgreSQL-9-3-abd-CentOS-7-memory-usage-td5822755.html
Can anybody point me in the right direction?! am I making some
fundamental mistakes with my configuration?
Any assistance would be great, I'm pushing to get this box into
production later this week!
Thank you in advance,
Michael
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgresql-9.1 CentOS7 effective_cache_size issue
@ 2015-08-11 16:26 John Scalia <jayknowsunix@gmail.com>
parent: Michael H <michael@wemoto.com>
0 siblings, 0 replies; 2+ messages in thread
From: John Scalia @ 2015-08-11 16:26 UTC (permalink / raw)
To: Michael H <michael@wemoto.com>; +Cc: pgsql-admin
Michael,
As nobody has yet to chime in, here's my $0.02 worth. First off, your
shared_buffers setting looks a little low. Did you actually calculate the
value to use or just use the 25% of RAM rule of thumb? Also, did you read
the section in the docs on setting effective_cache_size? Basically just
adding together the free and cache values returned by the free command.
Still, unless your shared buffers is properly being utilized, your mileage
may vary.
--
Jay
On Tue, Aug 11, 2015 at 3:38 AM, Michael H <michael@wemoto.com> wrote:
> Hi All,
>
> I've been performance tuning a new database server for the past couple of
> weeks with very mixed results, I've read every guide to tuning I can locate
> on Google aswell as Gregory Smiths - Postgresql 9.0 High Performance book.
>
> The server is a HP DL385P gen8, dual processor AMD Opteron 6386SE, 16core
> 2.8Ghz (32 cores total). 128GB DDR3 1600mhz, 8 x 16GB sticks. 4 x 300GB 6G
> SAS 10K in a RAID1+0 configuration.
>
> We are using CentOS7.1 minimal with Postgresql-9.2.13.
>
> I'm seeing good IOPS, memory throughput is good, the server is
> benchmarking very well in comparison to it's predecessor.
>
> I have left most of the configuration as defaults and tuned the following
> parameters;
>
>
> shared_buffers = 8GB
> max_prepared_transactions = 5
> work_mem = 32MB
> max_stack_depth = 7MB
> max_files_per_process = 1000000
> wal_level = hot_standby
> max_wal_senders = 3
> wal_keep_segments = 128
> wal_buffers=64MB
> checkpoint_segments = 64
> maintenance_work_mem=2GB
>
> ## note this is commented out
> #effective_cache_size = 40GB
>
> # increased logging levels for PGBADGER
> track_activities = on
> track_counts = on
> track_functions = all
> log_parser_stats = off
> log_planner_stats = off
> log_executor_stats = off
> log_statement_stats = off
> log_min_duration_statement = 0
> log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
> log_checkpoints = on
> log_connections = on
> log_disconnections = on
> log_lock_waits = on
> log_temp_files = 0
> log_destination = 'stderr'
> logging_collector = on
> log_directory = 'pg_log'
> log_filename = 'postgresql-%Y-%m-%d.log'
> log_rotation_age = 0
> client_min_messages = notice
> log_min_messages = warning
> log_min_error_statement = error
> log_min_duration_statement = 0
> log_checkpoints = on
> log_connections = on
> log_disconnections = on
> log_duration = off
> log_error_verbosity = verbose
> log_hostname = on
> log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u '
> log_lock_waits = on
> log_statement = 'none'
> log_temp_files = 0
>
> From my readings online I have configured shmmax and shmall in
> /etc/sysctl.conf, the suggested settings were 50% of RAM dedicated to
> shared_buffers.
>
> kernel.shmmax = 67462680576 # roughly 62GB allowing 60GB for PGSQL.
> kernel.shmall = 16470381 # shmmax/16 the same ratio as default
> values and my previous server.
>
> the shmmax and shamall can be reduced, this was my starting point.
>
> Now, when I make changes with work_mem and shared_buffers I am seeing
> performance increases / decreases as I would expect.
>
> When I set effective_cache_size to anything other than the default
> (comment out my setting) my TPS takes a huge nose dive, from 37TPS down to
> 5TPS.
>
>
> wal_buf wal_seg effective_cache_size shared_buffers work_mem
> 64MB 64 defaults 8GB 64MB
>
> pgbench - my data on my database
> TPS total transactions
> 37.324716 11224
> 34.353093 10337
> 19.832292 6003
> 10.010148 3120
> 5.859798 2073
>
>
> changing effective_cache_size (tested from 1GB to 80GB) causes these
> benchmark results
> wal_buf wal_seg effective_cache_size shared_buffers work_mem
> 64MB 64 ***** 8GB 64MB
>
> pgbench - my data on my database
> TPS total transactions
> 5.86 1,770
> 3.78 1,168
> 1.34 430
> 0.66 258
> 0.37 512
>
>
> looking at vmstat, free, top and ipcs I'm not seeing anything unusual,
> nothing is being swapped to disk, cache is not flooding and I am only
> consuming about 8GB of RAM no matter what configuration changes I make.
>
> Are there known issues with Postgresql-9.2.13 and Centos7? I found one
> article where a guy had the same kind of issues with memory consumption.
>
>
> http://postgresql.nabble.com/PostgreSQL-9-3-abd-CentOS-7-memory-usage-td5822755.html
>
> Can anybody point me in the right direction?! am I making some fundamental
> mistakes with my configuration?
>
> Any assistance would be great, I'm pushing to get this box into production
> later this week!
>
> Thank you in advance,
>
> Michael
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-08-11 16:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-11 08:38 Postgresql-9.1 CentOS7 effective_cache_size issue Michael H <michael@wemoto.com>
2015-08-11 16:26 ` John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox