[Pdns-users] patch to improve SQL db AXFR performance

[Kenneth Marshall]

On Thu, May 31, 2007 at 04:27:18PM +0200, Norbert Sendetzky wrote:
> Hi Ken
> 
> > That is enough motivation. Please let me know if you have any
> > questions. I would like to have this functionality rolled into
> > PDNS, but it seems that I am one of the few who would take
> > advantage of it. In our system it is extremely useful so I will
> > continue to post updated versions as new releases of PDNS occur.
> 
> I think there are more people who would like to reduce the load on their 
> servers. Unfortunately, your patch looks a little bit like a workaround for a 
> problem already standardized via IXFR (incremental zone transfers).
> 
> If you would like to contribute to the missing implementation of IXFR in 
> PowerDNS, your IXFR related patches will surely be accepted and you wouldn't 
> have to patch your installation whenever you upgrade to a new version.
> 

Hi Norbert,

I had originally looked into fleshing out the IXFR functionality. It
turned out that the IXFR functionality seemed to only be possible when
used in conjunction with Dynamic DNS for the Bind nameserver. We are only
slave servers to system that are not using DDNS so IXFR would not have
helped. The performance problems with AXFR into a SQL database backend
would exist with or without the presence of the IXFR option. The AXFR
semantics work well but implicitly assume that they are using something
analogous to a flat-file zone storage process.

The entire "throw out the old information, replace it with the new info"
provides absolutely abysmal performance when mapped to an MVCC SQL engine.
The table results in the commingling of all domains within the same records
table. All that is needed to improved the performance is to allow the SQL
some latitude in what is actually changed in the table as a result of the
AXFR process. PDNS already allows you to set the delete-zone-query and the
instert-record-query. The only missing piece was to allow a user settable
commit-new-zone-query with a default value of "COMMIT;" just as now. Without
that functionality, it is difficult to perform the clean-ups needed for a
more complicated set of delete-zone-query and instert-record-query. Maybe
it would be better to have the queries be called begin-axfr-query,
insert-axfr-record-query, and commit-axfr-query but I was trying to change
as little of the existing code as possible when adding this functionality.

In any event, adding the final user settable query required a trivial change
and provides functionality that is not available even were the IXFR support
to be completed. Given that even as minor a change as this patch makes and
its only function is to allow the user to specify a commit-zone query along
with the delete-zone query that they can already specify and its lukewarm
reception, there is little chance that a more involved change to the codebase
would be well-received. Thank you for your comments and suggestions.

Regards,
Ken