Thread: Re: Subtransactions + a long-running transaction leading to performance degradation on standbys
Re: Subtransactions + a long-running transaction leading to performance degradation on standbys
From
Andrey Borodin
Date:
> 20 авг. 2021 г., в 09:45, Nikolay Samokhvalov <nik@postgres.ai> написал(а): > > The patches changing the SLRU algorithm and adding GUCs to control the buffer sizes look really promising. Optimizationin this area would really help those who use SAVEPOINTs. I suspect, we have a lot of such users – ORMs like Railsand Hibernate tend to use them for fancy nesting transaction processing. Awesome analysis, Nik, thanks! Yes, I think there are many workloads that would benefit a lot from bigger SLRU caches. I just want to note, that on your screenshot unpatched version runs 400K tps, while patched runs 280K tps. I see the datesare different and this effect is not observed in [0]. Probably, you run tests on different machines. While your experiments clearly shows that patch can save DB from degradation under pathological workload it would be greatto ensure patch does not incur penalty on normal workload. Thank you! Best regards, Andrey Borodin. [0] https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/20
Re: Subtransactions + a long-running transaction leading to performance degradation on standbys
From
Nikolay Samokhvalov
Date:
On Thu, Aug 19, 2021 at 10:04 PM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
I just want to note, that on your screenshot unpatched version runs 400K tps, while patched runs 280K tps. I see the dates are different and this effect is not observed in [0]. Probably, you run tests on different machines.
Indeed, patched Postgres I was testing on 2x smaller EC2 instances, it is documented in the GitLab issue. But I added an additional note here: https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/21#note_655731979
While your experiments clearly shows that patch can save DB from degradation under pathological workload it would be great to ensure patch does not incur penalty on normal workload.
Makes sense. I'll try to find time to make a direct comparison.
I documented all the steps in detail in the GitLab issue, so anyone interested can reproduce it and explore the problem at different angles.