I did one final test of increasing the shared_buffers=32GB. It seems to be having no impact on TPS (in fact, if I look closely there is a 10-15% **negative** impact on the TPS compared to shared_buffers=2G)
I can confirm that **almost** the entire DB has been cached in the shared_buffers: relname | buffered | buffers_percent | percent_of_relation -------------------------+------------+-----------------+--------------------- pgbench_accounts | 24 GB | 74.5 | 93.9 pgbench_accounts_pkey | 4284 MB | 13.1 | 100.0 pgbench_history | 134 MB | 0.4 | 95.8 pg_aggregate | 8192 bytes | 0.0 | 50.0 pg_amproc | 32 kB | 0.0 | 100.0 pg_cast | 16 kB | 0.0 | 100.0 pg_amop | 48 kB | 0.0 | 85.7 pg_depend | 96 kB | 0.0 | 18.8 pg_index | 40 kB | 0.0 | 125.0 pg_namespace | 8192 bytes | 0.0 | 100.0 pg_opclass | 24 kB | 0.0 | 100.0 pg_operator | 96 kB | 0.0 | 75.0 pg_rewrite | 24 kB | 0.0 | 25.0 pg_statistic | 176 kB | 0.0 | 75.9 pg_aggregate_fnoid_index | 16 kB | 0.0 | 100.0 pg_trigger | 40 kB | 0.0 | 500.0 pg_amop_fam_strat_index | 24 kB | 0.0 | 60.0 pg_amop_opr_fam_index | 32 kB | 0.0 | 80.0 pg_amproc_fam_proc_index | 24 kB | 0.0 | 75.0 pg_constraint | 24 kB | 0.0 | 150.0 And I think now I give up. I don't think I understand how PG perf tuning works and what impact shared_buffers has on perf. I'll just run my DB in production with default settings and hope no one complains about the system being slow! -- Saurabh. On Tue, Jan 29, 2019 at 11:40 PM Saurabh Nanda <saurabhna...@gmail.com> wrote: > That is likely correct, but the data will likely be stored in the OS file >> cache, so reading it from there will still be pretty fast. >> > > Right -- but increasing shared_buffers won't increase my TPS, right? Btw, > I just realised that irrespective of shared_buffers, my entire DB is > already in memory (DB size=30GB, RAM=64GB). I think the following output > from iotop confirms this. All throughout the benchmarking > (client=1,4,8,12,24,48,96), the *disk read* values remain zero! > > Total DISK READ : 0.00 B/s | Total DISK WRITE : 73.93 M/s > Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 43.69 M/s > > > > Could this explain why my TPS numbers are not changing no matter how much > I fiddle with the Postgres configuration? > > If my hypothesis is correct, increasing the pgbench scale to get a 200GB > database would immediately show different results, right? > > -- Saurabh. > -- http://www.saurabhnanda.com
