Re: Postgresql-9.1 CentOS7 effective_cache_size issue

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(at)wemoto(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>