Re: Will higher shared_buffers improve tpcb-like benchmarks?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux