[Pdns-users] TCP nameserver had error, cycling backend: innodb-read-committed=no

[Peter van Dijk]

Hello,

On Sat, 2021-01-09 at 18:26 +0100, Gert van Dijk via Pdns-users wrote:
> It seems that this error message is triggered whenever PowerDNS cannot
> connect to the database at the first attempt, but it succeeds a second
> time. [1] The second time it tries to connect without transaction
> isolation enabled which may suggest that is the cause but that does
> not seem a causality necessarily. Since you report the same error with
> that turned off completely (recommended to leave it on by the way),
> this indicates a different (more generic) connection error and thus a
> misleading message.

Excellent insight, indeed likely correct.

> I'm not totally sure here, but I've seen log output of AXFR transfers
> that indicate a new connection for each zone transfer rather than a
> limited size connection pool.

Yes!

> If true, that means with
> max-tcp-connections=5000 (seems like a huge value to me for an
> isolated server) you need to accommodate for the same amount of
> connections from PowerDNS to your database backend.

Plus headroom. When a client disconnects from MySQL, the connection
slot is not immediately freed, and any connection that then comes in
'over the limit' is rudely rejected.

(Also, in previous pdns versions, you might see two DB connections per
AXFR client, but I think we fixed that.)

(In the past I've deployed haproxy in front of MySQL, with haproxy set
to a slightly lower concurrency - this works because haproxy holds on
to 'over limit' client connections until there is room again.)

> Having to accommodate for > 1024 simultaneous MySQL connections from
> one service seems like a design error for your use case in a broader
> sense or you may want to look at using a mysql-proxy service that
> pools the connections for you (in case PowerDNS in fact does open a
> new connection for each zone transfer).

Indeed.

Kind regards,
-- 
Peter van Dijk
PowerDNS.COM BV - https://www.powerdns.com/