Re: [Pdns-users] TCP nameserver had error, cycling backend: innodb-read-committed=no
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/ ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com https://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] TCP nameserver had error, cycling backend: innodb-read-committed=no
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 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
[Pdns-users] TCP nameserver had error, cycling backend: innodb-read-committed=no
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
