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

[kjansen at wijhosten.nl]

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