[Pdns-users] SLAVE refresh performance

[Klaus Darilion]

Am 12.09.2011 15:45, schrieb Peter van Dijk:
> Hello Klaus,
> 
> On Sep 12, 2011, at 3:27 PM, Klaus Darilion wrote:
> 
>> Seems like PDNS' slave zone checking is quite inefficient:
>> 1. get the list of configured slave zones: "info-all-slaves-query"
>> select id,name,master,last_check,type from domains where type='SLAVE'
>> 2. get "refresh" from SOA of all these domains:
>> select content,ttl,prio,type,domain_id,name, case when auth then 1 else
>> 0 end as auth from records where type='SOA' and name=E'foo.bar'
>> 3. calculate for every zone if zone should be refreshed
>> if (last_check + refresh < time(0)) ...
>>
>> IMO it would be much more efficient if the calculation would be done
>> when updating the domains table (instead of updating last_check also
>> update a additional 'next_check' column).
>>
>> Thus, changing the "info-all-slaves-query" from:
>>
>>  select id,name,master,last_check,type from domains where type='SLAVE'
>>
>> to (Postgresql syntax)
>>
>>  select id,name,master,last_check,type from domains where type='SLAVE'
>> and last_check!=NULL and next_check < EXTRACT(EPOCH FROM NOW());
> 
> I would, pedantically, suggest <= instead of < there ;)
> 
>> Of course the next_check column must be updated too on SOA-Checks/AXFR.
>> As long as PDNS doesn't to that, we can work around it by having an
>> update-trigger on the domains table, which updates 'next_check' on
>> 'last_check'-updates.
> 
> The delta between last_check and next_check would have to come from somewhere; officially, it should come from the SOA. In your case, are you suggesting some static interval? Or some SQL magic to parse a SOA?

We thought of some SQL magic:

on update:
  get SOA from records table
  extract refresh
  update: next_check = new.last_check + refresh

parsing the refresh out of the SOA is probably not nice in a trigger,
that's why I would like to see that happening in PDNS.

> 
>> I think this should work fine without any side-effects.
> 
> As far as I can see, this could work well.
> 
> Please let us know when you try anything like the above in production. It sounds like a decent change.


OK. We keep you updated.

regards
Klaus

> 
>> Of course it would be more efficient if this could be implemented in the
>> "update-lastcheck-query" (you can see this as a feature request :-)
> 
> I have made a note of that, but I'll await further news about any experiences you get with changing this schema.
> 
> Kind regards,
> Peter van Dijk
> _______________________________________________
> Pdns-users mailing list
> Pdns-users at mailman.powerdns.com
> http://mailman.powerdns.com/mailman/listinfo/pdns-users