Re: Will higher shared_buffers improve tpcb-like benchmarks?

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(at)gmail(dot)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