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

Sat Jan 9 17:26:40 UTC 2021

Hi Kevin,

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.

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. 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.

I would start looking at your database configuration and/or database
client connection settings. Perhaps it hits a connection limit of some
sort and the connection fails for some attempts. Try raising global
connection limits (you seem to have raised that one indeed) as well as
powerdns-user specific ones. Causes for the error to happen still
could be quite broad, from network issues to database configuration
settings or OS limiting the resources.

With 7k queries *per second* on 800k domains all receiving periodic
zone transfer requests and max-tcp-connections=5000, I could totally
imagine it will be causing the hit the default 1024 limit for a user
in Debian Linux by default. Try to see if that's actually raised to >
5000.

  # su - pdns --shell /bin/bash -c "ulimit -n"

(This may also be required for the MariaDB server/user on your other server.)

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).

HTH,

Gert

[1]: https://github.com/PowerDNS/pdns/blob/auth-4.1.14/modules/gmysqlbackend/smysql.cc#L447-L488

On Sat, Jan 9, 2021 at 2:42 PM Kevin via Pdns-users
<pdns-users at mailman.powerdns.com> wrote:
>
> Dear Community,
>
> I am running against an issue i like to fix.
> So now and then we are getting the following error in the logs:
> TCP nameserver had error, cycling backend: Unable to launch gmysql
> connection: Please add '(gmysql-)innodb-read-committed=no' to your
> PowerDNS configuration, and reconsider your storage engine if it does
> not support transactions.
>
> At that exact moment we are getting a error report from Neustar with the
> error:
> Premature closure of connection.
>
> I have a PowerDNS 4.1.14 Authorive (hidden master server) (Debian9)
> - VPS 6 cores, 16gb ram.
> MariaDB 10.1.44 (Debian 9)
> - VPS 8 cores, 8gb ram
>
> PowerDNS Config:
> allow-axfr-ips=204.74.97.97, 156.154.63.123, 23.21.200.163,
> 23.21.206.251, 50.112.240.144, 50.112.240.145, 176.34.183.208,
> 54.75.253.83, 54.217.202.161, 107.21.214.87, 54.245.253.13
> only-notify=54.217.202.161, 107.21.214.87, 54.245.253.13
> also-notify=54.217.202.161, 107.21.214.87, 54.245.253.13
> allow-notify-from=0.0.0.0/0,::/0
> daemon=yes
> default-soa-name=ns1.example.com
> default-soa-edit=INCEPTION-INCREMENT
> soa-refresh-default=86400
> default-ttl=86400
> disable-axfr=no
> disable-tcp=no
> distributor-threads=1
> do-ipv6-additional-processing=yes
> logging-facility=0
> loglevel=9
> guardian=yes
> launch=gmysql
> gmysql-host=10.21.0.254
> gmysql-user=username
> gmysql-password=password
> gmysql-dbname=database
> gmysql-innodb-read-committed=yes
> gmysql-dnssec
> local-address=x.x.x.x
> local-port=53
> log-dns-queries=no
> master=yes
> max-tcp-connections=5000
> query-local-address=x.x.x.x
> receiver-threads=5
> retrieval-threads=5
> signing-threads=5
> slave=no
> soa-minimum-ttl=3600
> version-string=anonymous
> prevent-self-notification=yes
> webserver=yes
> webserver-allow-from=x.x.x.x
> api=yes
> webserver-address=x.x.x.x
> webserver-port=8853
> api-key=apikey
> expand-alias=yes
> resolver=8.8.8.8:53
> reuseport=yes
>
> MariaDB Config: (Default with the following adjustments)
> innodb_buffer_pool_size = 4G
> innodb_log_file_size = 1G
> innodb_buffer_pool_instances = 1
> key_buffer_size         = 16M
> max_allowed_packet      = 16M
> thread_stack            = 192K
> thread_cache_size       = 8
> myisam_recover_options  = BACKUP
> max_connections        = 5000
> query_cache_limit       = 1M
> query_cache_size        = 16M
>
> mysql > status:
> Threads: 15  Questions: 342316599  Slow queries: 0  Opens: 188  Flush
> tables: 1  Open tables: 182  Queries per second avg: 6897.372
>
> Nobody is doing a lookup on this server, its completly hidden from the
> internet, and only open for Neustar to do AXFR/IXFR requests:
> Jan  9 14:28:52 hidden-master pdns[24457]: AXFR of domain 'example.com'
> allowed: client IP 23.21.206.251 is in allow-axfr-ips
> Jan  9 14:28:52 hidden-master pdns[24457]: gmysql Connection successful.
> Connected to database 'database' on '10.21.0.254'.
> Jan  9 14:28:52 hidden-master pdns[24457]: IXFR of domain 'example.com'
> to 23.21.206.251 finished
>
> Thats why i think the mysql server has around 7k query's since we are
> running almost 800k domains on this server.
> An error in the log:
>
> Jan  9 13:00:21 hidden-master pdns[24457]: gmysql Connection failed:
> Please add '(gmysql-)innodb-read-committed=no' to your PowerDNS
> configuration, and reconsider your storage engine if it does not support
> transactions.:
> Jan  9 13:00:21 hidden-master pdns[24457]: Caught an exception
> instantiating a backend: Unable to launch gmysql connection: Please add
> '(gmysql-)innodb-read-committed=no' to your PowerDNS configuration, and
> reconsider your storage engine if it does not support transactions.:
> Jan  9 13:00:21 hidden-master pdns[24457]: Cleaning up
>
> Are we asking too much from Powerdns? Do we have a misconfiguration? Or
> can we adjust some settings to avoid this problem?
> Changing the config from "yes" to "no" does not solve the issue, it
> feels it even makes it worse.
>
> No errors shown in /var/log/myql/error.log
>
> I think we are hitting a cap somewhere.
> Any help and or advice is welcome.
>
> Kind regards,
>
> Kevin
> _______________________________________________
> Pdns-users mailing list
> Pdns-users at mailman.powerdns.com
> https://mailman.powerdns.com/mailman/listinfo/pdns-users