Thread: Will higher shared_buffers improve tpcb-like benchmarks?
Hi,
I'm going crazy trying to optimise my Postgres config for a production setting [1] Once I realised random changes weren't getting my anywhere, I finally purchased PostgreSQL 10 - Higher Performance [2] and understood the impact of shared_buffers.
IIUC, shared_buffers won't have any significant impact in the following scenario, right?
-- DB size = 30GB
-- shared_buffers = 2GB
-- workload = tpcb-like
This is because the tpcb-like workload selects & updates random rows from the DB [3]. Therefore, with a 2GB shared buffer, there is only a 6-7% chance (did I get my probability correct?) that the required data will be in the shared_buffer. Did I understand this correctly?
If nothing else becomes the bottleneck (eg. periodically writing dirty pages to disk), increasing the shared_buffers to 15GB+ should have a significant impact, for this DB-size and workload, right? (The system has 64 GB RAM)
[1] Related thread at https://www.postgresql.org/message-id/flat/CAPz%3D2oGdmvirLNX5kys%2BuiY7LKzCP4sTiXXob39qq6eDkEuk2Q%40mail.gmail.com
-- Saurabh.
Please remove me from this list Serv. I do not use this db anymore and fills my alerts daily.
On Tue, Jan 29, 2019 at 06:39 Saurabh Nanda <saurabhnanda@gmail.com> wrote:
Hi,I'm going crazy trying to optimise my Postgres config for a production setting [1] Once I realised random changes weren't getting my anywhere, I finally purchased PostgreSQL 10 - Higher Performance [2] and understood the impact of shared_buffers.IIUC, shared_buffers won't have any significant impact in the following scenario, right?-- DB size = 30GB-- shared_buffers = 2GB-- workload = tpcb-likeThis is because the tpcb-like workload selects & updates random rows from the DB [3]. Therefore, with a 2GB shared buffer, there is only a 6-7% chance (did I get my probability correct?) that the required data will be in the shared_buffer. Did I understand this correctly?If nothing else becomes the bottleneck (eg. periodically writing dirty pages to disk), increasing the shared_buffers to 15GB+ should have a significant impact, for this DB-size and workload, right? (The system has 64 GB RAM)-- Saurabh.
Ethical axioms are found and tested not very differently from the axioms of science. Truth is what stands the the test if experience.
On Tue, Jan 29, 2019 at 6:39 AM Saurabh Nanda <saurabhnanda@gmail.com> wrote:
Hi,I'm going crazy trying to optimise my Postgres config for a production setting [1] Once I realised random changes weren't getting my anywhere, I finally purchased PostgreSQL 10 - Higher Performance [2] and understood the impact of shared_buffers.IIUC, shared_buffers won't have any significant impact in the following scenario, right?-- DB size = 30GB-- shared_buffers = 2GB-- workload = tpcb-likeThis is because the tpcb-like workload selects & updates random rows from the DB [3]. Therefore, with a 2GB shared buffer, there is only a 6-7% chance (did I get my probability correct?) that the required data will be in the shared_buffer. Did I understand this correctly?
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.
If nothing else becomes the bottleneck (eg. periodically writing dirty pages to disk), increasing the shared_buffers to 15GB+ should have a significant impact, for this DB-size and workload, right? (The system has 64 GB RAM)
About the only way to know for sure that writing dirty data is not the bottleneck is to use a read only benchmark, such as the -S flag for pgbench. And at that point, the IPC overhead between pgbench and the backend, even when both are running on the same machine, is likely to be the bottleneck. And after that, the bottleneck might shift to opening and closing transactions and taking and releasing locks[1].
If you overcome that, then you might reliably see a difference between 2GB and 15GB of shared buffers, because at 2GB each query to pgbench_accounts is likely to fetch 2 pages into shared_buffers from the OS cache: the index leaf page for pgbench_accounts_pkey, and the table page for pgbench_accounts. At 15GB, the entire index should be reliably in shared_buffers (after enough warm-up time), so you would only need to fetch 1 page, and often not even that.
Cheers,
Jeff
[1] I have a very old patch to pgbench that introduces a new query to overcome this, https://www.postgresql.org/message-id/BANLkTi%3DQBYOM%2Bzj%3DReQeiEKDyVpKUtHm6Q%40mail.gmail.com . I don't know how much work it would be to get it to compile against newer versions--I stopped maintaining it because it became too much work to rebase it past conflicting work, and because I lost interest in this line of research.
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.
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@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/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.