[Pdns-users] patch to improve SQL db AXFR performance
Kenneth Marshall
ktm at rice.edu
Thu May 31 14:04:54 UTC 2007
Dear PDNS Users:
I have posted this patch previously but would like to post
this new version against 2.9.21. The changes are identical
modulo code positions. Its motivation was initially to reduce
the overhead and performance problems when using PDNS as the
SLAVE DNS server with large domains. Pre-patch, it would take
on the order of 200 minutes to transfer a frequently updated
large DNS zone into PDNS. The patched version take less than
9 minutes for the same AXFR zone transfer to occur.
We have recently moved a web based PDNS management tool to
production and in order to provide logging and error recovery
procedures we log all DB changes to another table. With the
patched version, the historical log table contains the actual
changes and is very small. Without the patch, the log table
would grow tremendously after any and all zone transfers.
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 have attached the patch and a couple of PostgreSQL related
pdns.conf files as starting points.
Regards,
Ken
-------------- next part --------------
--- pdns-2.9.21/pdns/docs/pdns.sgml 2007-04-21 08:56:35.000000000 -0500
+++ pdns-2.9.21_AXFR/pdns/docs/pdns.sgml 2007-05-30 16:16:59.948727000 -0500
@@ -10337,6 +10337,17 @@
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>commit-zone-axfr-query</term>
+ <listitem>
+ <para>
+ Called to commit an incoming AXFR. The domain_id is available in the query using %d.
+ Default: <command>
+ commit
+ </command>
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</sect2>
--- pdns-2.9.21/pdns/dnsbackend.hh 2007-04-21 08:56:36.000000000 -0500
+++ pdns-2.9.21_AXFR/pdns/dnsbackend.hh 2007-05-30 16:17:49.489087000 -0500
@@ -97,7 +97,7 @@
}
//! commits the transaction started by startTransaction
- virtual bool commitTransaction()
+ virtual bool commitTransaction(uint32_t domain_id)
{
return false;
}
--- pdns-2.9.21/pdns/backends/gsql/gsqlbackend.cc 2007-04-21 08:56:35.000000000 -0500
+++ pdns-2.9.21_AXFR/pdns/backends/gsql/gsqlbackend.cc 2007-05-30 16:19:57.910049000 -0500
@@ -238,6 +238,7 @@
d_UpdateLastCheckofZoneQuery=getArg("update-lastcheck-query");
d_InfoOfAllMasterDomainsQuery=getArg("info-all-master-query");
d_DeleteZoneQuery=getArg("delete-zone-query");
+ d_CommitZoneAXFRQuery=getArg("commit-zone-axfr-query");
}
@@ -413,10 +414,12 @@
return true;
}
-bool GSQLBackend::commitTransaction()
+bool GSQLBackend::commitTransaction(uint32_t domain_id)
{
+ char output[1024];
+ snprintf(output,sizeof(output)-1,d_CommitZoneAXFRQuery.c_str(),domain_id);
try {
- d_db->doCommand("commit");
+ d_db->doCommand(output);
}
catch (SSqlException &e) {
throw AhuException("Database failed to commit transaction: "+e.txtReason());
--- pdns-2.9.21/pdns/backends/gsql/gsqlbackend.hh 2007-04-21 08:56:35.000000000 -0500
+++ pdns-2.9.21_AXFR/pdns/backends/gsql/gsqlbackend.hh 2007-05-30 16:32:21.175632000 -0500
@@ -28,7 +28,7 @@
bool isMaster(const string &domain, const string &ip);
bool startTransaction(const string &domain, int domain_id=-1);
- bool commitTransaction();
+ bool commitTransaction(uint32_t domain_id);
bool abortTransaction();
bool feedRecord(const DNSResourceRecord &r);
bool createSlaveDomain(const string &ip, const string &domain, const string &account);
@@ -66,5 +66,6 @@
string d_UpdateLastCheckofZoneQuery;
string d_InfoOfAllMasterDomainsQuery;
string d_DeleteZoneQuery;
+ string d_CommitZoneAXFRQuery;
};
--- pdns-2.9.21/pdns/backends/bind/bindbackend2.cc 2007-04-21 08:56:36.000000000 -0500
+++ pdns-2.9.21_AXFR/pdns/backends/bind/bindbackend2.cc 2007-05-30 16:33:53.716335000 -0500
@@ -159,7 +159,7 @@
return true;
}
-bool Bind2Backend::commitTransaction()
+bool Bind2Backend::commitTransaction(uint32_t domain_id)
{
delete d_of;
d_of=0;
--- pdns-2.9.21/pdns/backends/bind/bindbackend2.hh 2007-04-21 08:56:36.000000000 -0500
+++ pdns-2.9.21_AXFR/pdns/backends/bind/bindbackend2.hh 2007-05-30 16:34:30.616601000 -0500
@@ -109,7 +109,7 @@
bool startTransaction(const string &qname, int id);
// bool Bind2Backend::stopTransaction(const string &qname, int id);
bool feedRecord(const DNSResourceRecord &r);
- bool commitTransaction();
+ bool commitTransaction(uint32_t domain_id);
bool abortTransaction();
typedef map<string, int, CIStringCompare> name_id_map_t;
--- pdns-2.9.21/modules/gsqlitebackend/gsqlitebackend.cc 2007-04-21 08:56:36.000000000 -0500
+++ pdns-2.9.21_AXFR/modules/gsqlitebackend/gsqlitebackend.cc 2007-05-30 16:48:52.683318000 -0500
@@ -75,6 +75,7 @@
declare( suffix, "update-lastcheck-query", "", "update domains set last_check=%d where id=%d");
declare( suffix, "info-all-master-query", "", "select id,name,master,last_check,notified_serial,type from domains where type='MASTER'");
declare( suffix, "delete-zone-query", "", "delete from records where domain_id=%d");
+ declare( suffix, "commit-zone-axfr-query", "", "commit");
}
//! Constructs a new gSQLiteBackend object.
--- pdns-2.9.21/modules/gsqlite3backend/gsqlite3backend.cc 2007-04-21 08:56:36.000000000 -0500
+++ pdns-2.9.21_AXFR/modules/gsqlite3backend/gsqlite3backend.cc 2007-05-30 16:48:45.683269000 -0500
@@ -75,6 +75,7 @@
declare( suffix, "update-lastcheck-query", "", "update domains set last_check=%d where id=%d");
declare( suffix, "info-all-master-query", "", "select id,name,master,last_check,notified_serial,type from domains where type='MASTER'");
declare( suffix, "delete-zone-query", "", "delete from records where domain_id=%d");
+ declare( suffix, "commit-zone-axfr-query", "", "commit");
}
//! Constructs a new gSQLite3Backend object.
--- pdns-2.9.21/modules/goraclebackend/goraclebackend.cc 2007-04-21 08:56:36.000000000 -0500
+++ pdns-2.9.21_AXFR/modules/goraclebackend/goraclebackend.cc 2007-05-30 16:42:17.880367000 -0500
@@ -68,6 +68,7 @@
declare(suffix,"update-lastcheck-query","", "update domains set last_check=%d where id=%d");
declare(suffix,"info-all-master-query","", "select id,name,master,last_check,notified_serial,type from domains where type='MASTER'");
declare(suffix,"delete-zone-query","", "delete from records where domain_id=%d");
+ declare( suffix, "commit-zone-axfr-query", "", "commit");
}
--- pdns-2.9.21/modules/gmysqlbackend/gmysqlbackend.cc 2007-04-21 08:56:36.000000000 -0500
+++ pdns-2.9.21_AXFR/modules/gmysqlbackend/gmysqlbackend.cc 2007-05-30 16:41:36.740059000 -0500
@@ -74,6 +74,7 @@
declare(suffix,"update-lastcheck-query","", "update domains set last_check=%d where id=%d");
declare(suffix,"info-all-master-query","", "select id,name,master,last_check,notified_serial,type from domains where type='MASTER'");
declare(suffix,"delete-zone-query","", "delete from records where domain_id=%d");
+ declare( suffix, "commit-zone-axfr-query", "", "commit");
}
--- pdns-2.9.21/modules/gpgsqlbackend/gpgsqlbackend.cc 2007-04-21 08:56:36.000000000 -0500
+++ pdns-2.9.21_AXFR/modules/gpgsqlbackend/gpgsqlbackend.cc 2007-05-30 16:47:28.922697000 -0500
@@ -74,6 +74,7 @@
declare(suffix,"update-lastcheck-query","", "update domains set last_check=%d where id=%d");
declare(suffix,"info-all-master-query","", "select id,name,master,last_check,notified_serial,type from domains where type='MASTER'");
declare(suffix,"delete-zone-query","", "delete from records where domain_id=%d");
+ declare( suffix, "commit-zone-axfr-query", "", "commit");
}
-------------- next part --------------
## The address we bind to and the port on which we listen
#local-address=10.128.92.32
#local-port=53
## Which backends to launch and order to query them in
#launch=bind
#bind-example-zones
launch=gpgsql
gpgsql-host=168.7.56.48
#gpgsql-socket=5432
gpgsql-dbname=pdns
gpgsql-user=postgres
gpgsql-password=""
#Update AXFR queries to use stage table for staging
gpgsql-delete-zone-query=DELETE FROM stage WHERE domain_id = %d;
gpgsql-insert-record-query=INSERT INTO stage (content,ttl,prio,type,domain_id,name) VALUES ('%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;
## Logging
#logfile=p
logging-facility=5
## Act as a master? slave?
master=yes
slave=yes
## If recursion is desired, IP address of a recursing nameserver
#recursor=127.0.0.1:552
#recursor=128.42.5.4
## Allow recursion only from Rice IPs
allow-recursion=127.0.0.0/8, 10.0.0.0/8, 192.168.0.0/16, 172.16.0.0/12, 128.42.0.0/16, 168.7.0.0/16
## If set, change user and/or group id for more security
#setgid=pdns
#setuid=pdns
## If set, chroot to the specified directory
chroot=./
## Start a webserver for monitoring
#webserver=yes
#webserver-address=127.0.0.1
#webserver-address=0.0.0.0
#webserver-port=8081
-------------- next part --------------
## The address we bind to and the port on which we listen
#local-address=10.128.92.32
#local-port=53
## Which backends to launch and order to query them in
#launch=bind
#bind-example-zones
launch=gpgsql
gpgsql-host=168.7.56.48
#gpgsql-socket=5432
gpgsql-dbname=pdns
gpgsql-user=postgres
gpgsql-password=""
#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;
gpgsql-insert-record-query=INSERT INTO stage (content,ttl,prio,type,domain_id,name) VALUES ('%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;
## Logging
#logfile=p
logging-facility=5
## Act as a master? slave?
master=yes
slave=yes
## If recursion is desired, IP address of a recursing nameserver
#recursor=127.0.0.1:552
#recursor=128.42.5.4
## Allow recursion only from Rice IPs
allow-recursion=127.0.0.0/8, 10.0.0.0/8, 192.168.0.0/16, 172.16.0.0/12, 128.42.0.0/16, 168.7.0.0/16
## If set, change user and/or group id for more security
#setgid=pdns
#setuid=pdns
## If set, chroot to the specified directory
chroot=./
## Start a webserver for monitoring
#webserver=yes
#webserver-address=127.0.0.1
#webserver-address=0.0.0.0
#webserver-port=8081
-------------- next part --------------
## The address we bind to and the port on which we listen
#local-address=10.128.92.32
#local-port=53
## Which backends to launch and order to query them in
#launch=bind
#bind-example-zones
launch=gpgsql
gpgsql-host=168.7.56.48
#gpgsql-socket=5432
gpgsql-dbname=pdns
gpgsql-user=postgres
gpgsql-password=""
#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;
## Logging
#logfile=p
logging-facility=5
## Act as a master? slave?
master=yes
slave=yes
## If recursion is desired, IP address of a recursing nameserver
#recursor=127.0.0.1:552
#recursor=128.42.5.4
## Allow recursion only from Rice IPs
allow-recursion=127.0.0.0/8, 10.0.0.0/8, 192.168.0.0/16, 172.16.0.0/12, 128.42.0.0/16, 168.7.0.0/16
## If set, change user and/or group id for more security
#setgid=pdns
#setuid=pdns
## If set, chroot to the specified directory
chroot=./
## Start a webserver for monitoring
#webserver=yes
#webserver-address=127.0.0.1
#webserver-address=0.0.0.0
#webserver-port=8081
More information about the Pdns-users
mailing list