pgcat: Enormous performance regression by PR #482 when prepared_statements is set to true
Describe the bug
I wanted to try out prepared statement support and measure its performance in pgcat (essentially reproducing the blog). But when running pgbench --select-only
on the main branch I was only able to get ~50 TPS.
To Reproduce Steps to reproduce the behavior:
git checkout main
- Add
prepared_statements = true
to the minimal config RUST_LOG=info cargo run --release -- pgcat.minimal.toml
pgbench --select-only -T 10 -P 1 -p 6433 --protocol prepared
Expected behavior
Get somewhere in the order of 2000-20000 TPS. But instead I get between 20-50TPS. The worst part is that this even happens when using --protocol simple
. Only if you enable prepared_statements = true
from the config, performance of --protocol simple
returns to normal levels.
Screenshots
I narrowed the regression down commit: 4b78af9676596135bfcf16b049f4b1649a9064ed
With commit: 4b78af9676596135bfcf16b049f4b1649a9064ed
With commit right before: 73500c0c961a57ae3bac0618046775a43ea78129
Desktop (please complete the following information):
- OS: Linux
Additional info The reason I was testing pgcat its performance was because I wanted to compare PgBouncer its upcoming prepared statement support (https://github.com/pgbouncer/pgbouncer/pull/845) to pgcat and Odyssey. But pgcat its perf was so much lower than expected that something obviously seemed wrong.
About this issue
- Original URL
- State: closed
- Created a year ago
- Comments: 22
Commits related to this issue
- Fix for #534 and #535 — committed to postgresml/pgcat by levkk a year ago
Oh wow. This was really a blunder. Fixed in https://github.com/postgresml/pgcat/commit/aefcf4281c6c33532d73c0578d3fba1c8f9a5d11. Could you confirm on your end? Thanks!
The cause was the prepared statement maintenance cache loop issuing a Flush to Postgres on each iteration (each transaction). This only happened in transaction mode, which explains why the performance tanked in transaction mode but not in session mode.
Oh hmm, that was my mistake. Let me test with
transaction
mode.