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:

  1. git checkout main
  2. Add prepared_statements = true to the minimal config
  3. RUST_LOG=info cargo run --release -- pgcat.minimal.toml
  4. 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 image

With commit right before: 73500c0c961a57ae3bac0618046775a43ea78129 image

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

Most upvoted comments

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.