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@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@mailman.powerdns.com > https://mailman.powerdns.com/mailman/listinfo/pdns-users _______________________________________________ Pdns-users mailing list Pdns-users@mailman.powerdns.com https://mailman.powerdns.com/mailman/listinfo/pdns-users
