Pdns: Make prepared statements against the gpgsql backend optional

[jacobbunk]

• Program: PowerDNS Authoritative 4.3
• Issue type: Feature request

Short description

PowerDNS 4.3 abandoned the use of prepared statements for the gpgsql backend. This has a bigger impact on performance in some situations.

Usecase

If you are not using a connection pooler such as pgbouncer it would be great to get the better performance by being able to use prepared statements.

Description

I recently upgraded from PowerDNS auth 4.2.1 to PowerDNS auth 4.3, and was a bit surprised to see latency reported by PowerDNS go up from ~2.5 ms to around 4.2 ms. I know we are talking milliseconds, but they matter when it comes to DNS.

Here's a graph of the latency of one of our DNS servers before and after the upgrade to PowerDNS 4.3:

We don't use pgbouncer, so it would be great to be able to enable prepared statements again.

[rgacogne]

Related to #8627.

[Habbie]

@sfrost I want to make this configurable now, as discussed-and-then-rejected in #8627. Because most users don't use pgbouncer, I'd also like to default to the more performant option, and of course, document this in the upgrade notes. As this would be in a minor release, it's a rude breaking change. Would a pgbouncer user notice immediately after startup, or could the trouble delay itself until '3AM the next morning'?

[Habbie]

https://builder.powerdns.com/#/buildrequests/124970?redirect_to_build=true will, in some hours, offer a bionic package of 4.3.0 with #8627 reverted.

[sfrost]

If you're changing it to depend on prepared statements working across multiple transactions then the upgrade would pretty quickly break pgbouncer setups which use transaction-mode connection pooling... How long it takes someone to notice that is difficult to say.

What might make more sense would be to check if a prepared statement exists or not before trying to use it and then if it doesn't, create it, and then use it. That can be done by querying pg_prepared_statements.

[Habbie]

What might make more sense would be to check if a prepared statement exists or not before trying to use it and then if it doesn't, create it, and then use it. That can be done by querying pg_prepared_statements.

Hmm, but that's another roundtrip.. per transaction, though.

[sfrost]

You could also just try to execute the prepared statement and, if it fails because it doesn't exist, then prepare and run it... That's not exactly ideal either, of course, but maybe it's better for some folks and at least avoids breaking others.

[sfrost]

(and then have an option to not bother trying to prepare it initially)

[Habbie]

Makes sense, but that's a lot more complexity for a minor release. I'll have to think about it.

[rgacogne]

You could also just try to execute the prepared statement and, if it fails because it doesn't exist, then prepare and run it...

It's unfortunately not easy to detect. PQexecPrepared() will fail and return PGRES_FATAL_ERROR, and the only way I see of knowing it's because the statement has not been prepared on that connection yet is to parse the result of PQresultErrorMessage():

ERROR:  prepared statement "foobar" does not exist

[jacobbunk]

I have now tested the package that @Habbie was kind enough to prepare for me using prepared statements. The graph below shows the latency first running 4.2.1, then running 4.3 and then running 4.3 with prepared statements. To me it is clear that disabling prepared statements is expensive, at least in my setup.

[Habbie]

Loud and clear, thanks :)

[zeha]

Do we know if the extra latency here is caused by parse and bind+execute being in separate tcp packets? If so, could everybody live with moving "back" to sending statements with interpolated data?
If it's not the separate tcp packet - what is causing the latency for you?

[rgacogne]

Minimal test out of PowerDNS, 100k queries to a local PG server over a unix socket:

• PQexec (no statement): 0m2.955s
• PQprepare + PQexecPrepared (full statement prepared once, executed 100k times): 0m1.426s
• PQexecParams (parameterized query): 0m3.956s

[zeha]

Okay, made my own tests, and it's really the planning time. Oh my.

[jacobbunk]

@zeha, thank you very much! Much appreciated. This also takes us back to being compatible with the documentation :-)
https://github.com/PowerDNS/pdns/blob/master/docs/backends/generic-sql.rst claims "From version 4.0.0 onward, the generic SQL backends use prepared statements for their queries."