Slow ddns performance

[cyclops1982]

Hi, On the pdns-users mailling list, we got a report for slow DDNS updates. As reported by the user: On Tue, Mar 08, 2016 at 09:40:57PM +0100, Thomas Mieslinger wrote:

Hi,

maybe you remember my message regarding suboptimal ddns update performance. A colleague helped me to identify the list_subzone_query as the performance killer.

The original where clause is

disabled=0 and (name='%s' OR name like '%s') and domain_id='%d'

with

%.

as second argument.

When like is used with a search argument that starts with a wildcard the index can not be used.

Because I know that my records to update have no subzones I short circuit query with

SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and (name='%s' OR 'x'='%s') and domain_id='%d'

Cheers

Thomas

The analysis makes sense, needs to be double checked and then see if there is a better way of finding out the subzones. I know we had issues with this when writing the implementation.

[miesi]

I have further tested this and found out that it breaks prerequists checking of ddns queries. I suggest not to implement this.

[miesi]

To enhance the rfc2136 ddns performance I suggest to document this for the users who need it.

• create a column to hold the reversed name

alter table records add column name_rev varchar(255)  DEFAULT NULL after name, add KEY rec_name_rev_index (`name_rev`);

• fill it

update records set name_rev = REVERSE(name); 

• create a trigger

delimiter //
CREATE TRIGGER ins_records_spool BEFORE INSERT ON records
FOR EACH ROW
BEGIN
   SET NEW.name_rev=REVERSE(NEW.name);
END;
delimiter ; 

• modify gmysql-list-subzone-query

gmysql-list-subzone-query=SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and (name='%s' OR name_rev like REVERSE('%s')) and domain_id=%d 

This gives me a 20x better performance. (90k records zone)

[atosatto]

Might this be related to #6982?

[miesi]

We've implemented the schema change in our largest tenant database because it helps to speed up the queries for generating NSEC3 records.

As a side note, the database grew from 32GB to 67GB.

You not only need the space on your blockdevice, you need it as RAM.