Hi, effective_cache_size is used only for query planing - you will not see it in vmstat etc. the default is 128mb, meaning you'd expect to see major differences when running with 128mb vs 80GB of effective cache.
I'd take a look at your execution plans - I think you would find them very different between the 2 settings. could you share your pgbench configuration? BTW, have you considered upgrading to a newer PG version - you are missing out on quite a few performance improvements (for large memory clusters as well) Regards, - Jony On Tue, Aug 11, 2015 at 11:52 AM, Michael H <mich...@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 > > ----------------- > I just sent this message to psql-admin and realised this may be the more > appropriate location to ask. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
