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 <saurabhnanda@xxxxxxxxx> 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/sActual DISK READ: 0.00 B/s | Actual DISK WRITE: 43.69 M/sCould 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.
