[Pdns-users] Performance issues

[Martijn Reening]

Hello,

This is an update in our attempts to debug the performance issues on our
nameservers. We have found the main source of the timeouts: it was
caused by a somewhat larger zone (12699 records, 4236 unique names). The
zone was secured using DNSSEC.

Let's Encrypt certificates are automatically requested for many of these
domains, which causes a lookup for a CAA record. The record does not
exist, so an NSEC3 answer is generated. This causes queries of this form:

    select ordername, name
    from records
    where ordername <= 'hash'
      and domain_id=12345
      and disabled=0
      and ordername is not null
    order by 1 desc
    limit 1;

We have found out that these queries can take up anywhere from 4 to 15
seconds. The issue seems to be that MySQL 5.1 (or 5.5 for that matter)
cannot use the index on `ordername` in reverse (`ordername <= 'hash'`),
so it searches all records for the given domain. This is not a problem
for small zones, but causes delays and timeouts on larger zones.

The domain that caused most problems was only used for testing purposes.
Disabling DNSSEC for it was therefore the easiest temporary solution.
However, several customer domains cause similar problems.

Over the course of the next weeks we will replace the old MySQL 5.1
backend for a new database server (either MySQL 8 or PostgreSQL) that
can handle reverse indexes. We will monitor if the performance improves
and I will send an update with the results.

We are also interested in hearing whether there are others that have had
this same issue and how it was solved.


Martijn Reening