[Pdns-users] Odd master/slave behavior for large domains

[thomas morgan]

Ken--

Thanks much for your detailed reply.

I am quite intrigued by what you've done on the slave side and the  
corresponding performance benefit in consuming AXFRs. I will be going  
to dig up that patch and consider applying it here.

--t



On Sep 11, 2009, at 7:07 AM, Kenneth Marshall wrote:

> Hi Thomas,
>
> I have read your post and your transfer problem is caused by
> the OOM killer. You should not be running out of memory on
> a database server. That is the first problem. Regarding
> the AXFR transfers when PDNS is a slave, the process used
> is to delete all the records in the zone and then re-insert
> the new set of records retrieved from the PDNS master. For
> an MVCC database like PostgreSQL this is a very expensive
> process, particularly for a large zone since IXFR transfers
> are not yet supported by PDNS.
>
> We also use PostgreSQL as the backend database for PDNS. To
> work around the lack of support for incremental updates, I
> wrote a patch to the PDNS software to add an additional hook
> to the database interface called gpgsql-commit-zone-axfr-query.
> I posted a patch to the PDNS list several years ago and it is
> still valid for the MySQL and PostgreSQL DB backends. The
> purpose of this hook is to allow a user-defined SQL command
> to be used to commit the records that have been received by
> the PDNS slave to the DB backend. The default value of this
> parameter is "COMMIT;", which is the hard-coded command that
> is used in the plugins.
>
> With the ability to control the SQL statement used to commit
> the new zone information and custom definitions for gpgsql-
> elete-zone-query and gpgsql-insert-record-query you can cause
> the AXFR transfers to use a temp table for staging and then
> only update the changed records in the actual table. Here are
> the definitions that we are currently using in our pdns.conf:
>
> #Update AXFR queries to use stage table for staging
> gpgsql-delete-zone-query=CREATE TEMPORARY TABLE stage (id INT,  
> domain_id INT, name VARCHAR(255), type VARCHAR(6), content VARCHAR 
> (255), ttl INT, prio INT) ON COMMIT DROP; PREPARE axfrinsert  
> (varchar, int, int, varchar, int, varchar) AS INSERT INTO stage  
> (content,ttl,prio,type,domain_id,name) VALUES ($1,$2,$3,$4,$5,$6);
>
> gpgsql-insert-record-query=EXECUTE axfrinsert ('%s',%d,%d,'%s', 
> %d,'%s');
>
> gpgsql-commit-zone-axfr-query=CREATE TEMPORARY TABLE axfrvars (name  
> VARCHAR(6), value INT) ON COMMIT DROP; \
> INSERT INTO axfrvars (name, value) VALUES ('domain', %d); \
> DELETE FROM records \
> WHERE domain_id = \
>    (SELECT value FROM axfrvars WHERE name = 'domain') AND \
>      records.id NOT IN \
>          (SELECT records.id FROM records INNER JOIN stage \
>           USING (domain_id, name, type, content, ttl, prio)); \
> INSERT INTO records (domain_id, name, type, content, ttl, prio) \
> SELECT domain_id, name, type, content, ttl, prio FROM stage \
> WHERE stage.domain_id = \
>    (SELECT value FROM axfrvars WHERE name = 'domain') EXCEPT \
>        SELECT domain_id, name, type, content, ttl, prio from records \
>        WHERE records.domain_id = \
>            (SELECT value FROM axfrvars WHERE name = 'domain'); \
> COMMIT; DEALLOCATE axfrinsert;
>
> I included a couple of simpler variations in my original post
> as well as the version we use in production. I was unable to
> make a convincing case with the developer to get this commit-hook
> added in to the default code so I have been re-patching ever since
> because of the huge performance improvements offered in the absence
> of IXFR support. My C++ coding ability is not at the level needed
> to add that support. The use of this patch increased the speed of
> large zone AXFRs by more than an order of magnitude. Let me know
> if you have any questions. I think that shutting your OOM off will
> solve your AXFR transfer failure, but you will need my patch to
> get "reasonable" zone AXFR performance.
>
> Regards,
> Ken
>
> On Thu, Sep 10, 2009 at 11:14:18PM -0600, thomas morgan wrote:
>> I'm seeing some odd behavior in a traditional master and slave setup.
>>
>> Both the master and slave are PowerDNS. I've tried 2.9.21.2 and  
>> 2.9.22 and
>> seem to get the same results. Memory usage and other figures below  
>> are from
>> 2.9.22.
>>
>> The backends are Postgres 8.3. Servers are amd64 Debian Lenny.
>>
>> I created a single zone on the server and added 2 million host  
>> records. I
>> know that's a bunch, but it is a specific use case, not just an  
>> attempt to
>> break things.
>>
>> Oddity #1:
>> The master seems to send 3-4 NOTIFYs when the zone is updated -- at  
>> least
>> the slave is reporting in the logs to have received multiple NOTIFYs.
>> They're pretty consistently spaced: in several instances, 4 NOTIFYs  
>> with
>> intervals 3sec, 5sec, 9sec.
>>
>> Oddity #2:
>> The slave, upon receiving multiple NOTIFYs, initiates multiple  
>> AXFRs for
>> the same zone. For a small zone this wouldn't be a big deal, but  
>> for a
>> large zone it's fatal.
>>
>> Oddity #3:
>> The master consumes a *huge* amount of RAM to do each AXFR: 283 MB  
>> worth
>> per AXFR. Memory usage on the slave seems tolerable though.
>>
>> Oddity #4:
>> If an AXFR doesn't finish properly, the memory is never released.  
>> I've
>> managed to reproduce this using dig to perform the AXFR as well.
>>
>> Strangely dig dies after 8600 records when piped to less, but makes  
>> it
>> through all 2 million when piped to wc or just output to the  
>> terminal. This
>> might be dig's problem, but it does make it easy to cause #4. The  
>> final
>> line of dig's output when it fails is ";; communications error: end  
>> of
>> file". When the AXFR completes, the log includes "AXFR of domain  
>> 'h0.com'
>> to 64.<client> finished". When it doesn't (and memory isn't freed),  
>> this
>> message never shows up.
>>
>> I cannot tell, but it's possible the slave is eventually figuring  
>> out it
>> has multiple transfers for the same zone going and aborts them. The  
>> logs
>> (included at the bottom) give mixed readings on this (between the  
>> master
>> and slave). It may also be the master puking and closing the  
>> connection.
>>
>>
>> On my test servers things usually end when the oomkiller rears its  
>> ugly
>> head. ;)
>>
>> The combination of these becomes a significant problem. #1 causes  
>> #2 when
>> then multiplies the effect of #3. #2 also increases the likelihood  
>> of #4.
>>
>>
>> Anything I'm missing or that I can do to help figure out what's  
>> wrong? Some
>> logs are included below; they are not identical in every case, but  
>> this is
>> representative.
>>
>> --t
>>
>>
>> Master server:
>>
>> Sep 11 02:07:45 pdns-master-test pdns[4156]: 1 domain for which we  
>> are
>> master needs notifications
>> Sep 11 02:07:45 pdns-master-test pdns[4156]: Queued notification of  
>> domain
>> 'h0.com' to 64.<master>
>> Sep 11 02:07:45 pdns-master-test pdns[4156]: Queued notification of  
>> domain
>> 'h0.com' to 64.<slave>
>> Sep 11 02:07:45 pdns-master-test pdns[4156]: Received NOTIFY for h0.com
>> from 64.<master> which is not a master
>> Sep 11 02:07:46 pdns-master-test pdns[4156]: Received unsuccesful
>> notification report for 'h0.com' from 64.<master>, rcode: 5
>> Sep 11 02:07:46 pdns-master-test pdns[4156]: Removed from  
>> notification
>> list: 'h0.com' to 64.<master>
>> Sep 11 02:07:46 pdns-master-test pdns[4156]: AXFR of domain 'h0.com'
>> initiated by 64.<slave>
>> Sep 11 02:07:46 pdns-master-test pdns[4156]: gpgsql Connection  
>> succesful
>> Sep 11 02:07:48 pdns-master-test pdns[4156]: No master domains need
>> notifications
>> Sep 11 02:08:13 pdns-master-test pdns[4156]: AXFR of domain 'h0.com'
>> initiated by 64.<slave>
>> Sep 11 02:08:13 pdns-master-test pdns[4156]: gpgsql Connection  
>> succesful
>> Sep 11 02:08:23 pdns-master-test pdns[4156]: AXFR of domain 'h0.com'
>> initiated by 64.<slave>
>> Sep 11 02:08:25 pdns-master-test pdns[4156]: gpgsql Connection  
>> succesful
>> Sep 11 02:08:33 pdns-master-test pdns[4156]: AXFR of domain 'h0.com'
>> initiated by 64.<slave>
>> Sep 11 02:08:34 pdns-master-test pdns[4156]: gpgsql Connection  
>> succesful
>> Sep 11 02:09:21 pdns-master-test pdns[4156]: Communicator thread died
>> because of error: GSQLBackend unable to retrieve list of slave  
>> domains:
>> PostgreSQL failed to execute command: server closed the connection
>> unexpectedly#012#011This probably means the server terminated
>> abnormally#012#011before or while processing the request.
>> (The last line is where oomkiller decided to kill off postgres.)
>>
>>
>> Slave server:
>>
>> Sep 11 02:07:45 pdns-slave-test pdns[4379]: Received valid NOTIFY for
>> h0.com (id=1) from master 64.<master>: 1252634826 > 1252632174
>> Sep 11 02:07:46 pdns-slave-test pdns[4379]: gpgsql Connection  
>> succesful
>> Sep 11 02:07:48 pdns-slave-test pdns[4379]: Received valid NOTIFY for
>> h0.com (id=1) from master 64.<master>: 1252634826 > 1252632174
>> Sep 11 02:07:51 pdns-slave-test pdns[4379]: AXFR started for  
>> 'h0.com',
>> transaction started
>> Sep 11 02:07:53 pdns-slave-test pdns[4379]: Received valid NOTIFY for
>> h0.com (id=1) from master 64.<master>: 1252634826 > 1252632174
>> Sep 11 02:08:02 pdns-slave-test pdns[4379]: Received valid NOTIFY for
>> h0.com (id=1) from master 64.<master>: 1252634826 > 1252632174
>> Sep 11 02:08:13 pdns-slave-test pdns[4379]: Unable to AXFR zone  
>> 'h0.com':
>> Remote nameserver closed TCP connection
>> Sep 11 02:08:13 pdns-slave-test pdns[4379]: Aborting possible open
>> transaction for domain 'h0.com' AXFR
>> Sep 11 02:08:13 pdns-slave-test pdns[4379]: gpgsql Connection  
>> succesful
>> Sep 11 02:08:23 pdns-slave-test pdns[4379]: Unable to AXFR zone  
>> 'h0.com':
>> Timeout waiting for answer from 64.<master> during AXFR
>> Sep 11 02:08:23 pdns-slave-test pdns[4379]: gpgsql Connection  
>> succesful
>> Sep 11 02:08:33 pdns-slave-test pdns[4379]: Unable to AXFR zone  
>> 'h0.com':
>> Timeout waiting for answer from 64.<master> during AXFR
>> Sep 11 02:08:33 pdns-slave-test pdns[4379]: gpgsql Connection  
>> succesful
>> Sep 11 02:08:43 pdns-slave-test pdns[4379]: Unable to AXFR zone  
>> 'h0.com':
>> Timeout waiting for answer from 64.<master> during AXFR
>> (nothing more until the master's postgres daemon was restarted)
>>
>> The correlation between the "AXFR initated" on the master and the  
>> "Timeout
>> waiting for answer" on the slaves is strange too.
>>
>> --
>>
>> _______________________________________________
>> Pdns-users mailing list
>> Pdns-users at mailman.powerdns.com
>> http://mailman.powerdns.com/mailman/listinfo/pdns-users
>>