Date: Tue, 12 Aug 2008 08:59:37 +0100 From: Chris Hastie <lists@oak-wood.co.uk> To: freebsd-questions@freebsd.org Subject: Re: mysql and BIND 9.4.2 Message-ID: <48A142E9.2090906@oak-wood.co.uk> In-Reply-To: <A649D807B7293E4B8FEE66605F84E7AD028AEEA4@emrldsdex01.emerald.fiserv.net> References: <A649D807B7293E4B8FEE66605F84E7AD028AEE4C@emrldsdex01.emerald.fiserv.net> <48A0B763.4020305@oak-wood.co.uk> <A649D807B7293E4B8FEE66605F84E7AD028AEEA4@emrldsdex01.emerald.fiserv.net>
next in thread | previous in thread | raw e-mail | index | archive | help
On 11/08/08 23:23, Johnson, James wrote: > Thanks Chris, I'll look into this. Have you or anyone you know ever set > something like this before? What I'm trying to do is replace our name > servers, they will be Virtualized. > > > I have several nameservers running from a replicated MySQL database, on both Ubuntu and FreeBSD, and in the past on Debian. It's not really a FreeBSD issue this, so you might be better off with the bind-dlz-testers mailing list (http://bind-dlz.sourceforge.net/mailing_list.html). There's a good deal of information on configuring bind-dlz on the bind-dlz site, and a good deal of information on configuring replication in MySQL on the MySQL site. Here are a few pointers from my experience: Like any database project, spend some time thinking carefully about your database schema before you start. As I recall, the examples on the bind-dlz site place SOA records and other RRs in the same table. I prefer to separate these out, eg -- -- Table structure for table `dns_rr` -- CREATE TABLE `dns_rr` ( `rr_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `zone` varchar(255) NOT NULL DEFAULT '', `host` varchar(255) NOT NULL DEFAULT '@', `ttl` mediumint(8) unsigned NOT NULL DEFAULT '86400', `rr_type` enum('A','AAAA','MX','PTR','NS','TXT','CNAME','RP','SRV') NOT NULL DEFAULT 'A', `mx_priority` smallint(5) unsigned DEFAULT NULL, `rr_data` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`rr_id`), KEY `zone` (`zone`), KEY `zone_host` (`zone`(250),`host`(250)), KEY `rr_type` (`rr_type`), KEY `zone_host_type` (`zone`(245),`host`(245),`rr_type`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `dns_soa` -- CREATE TABLE `dns_soa` ( `zone` varchar(255) NOT NULL DEFAULT '', `ttl` mediumint(8) unsigned NOT NULL DEFAULT '86400', `primary_ns` varchar(255) NOT NULL DEFAULT 'ns0.example.com.', `resp_person` varchar(255) DEFAULT 'hostmaster', `serial` bigint(20) unsigned DEFAULT '1', `refresh` mediumint(8) unsigned DEFAULT '3600', `retry` mediumint(8) unsigned DEFAULT '600', `expire` int(10) unsigned DEFAULT '2419200', `minimum` mediumint(8) unsigned DEFAULT '1800', `owner` varchar(30) NOT NULL DEFAULT '', `active` enum('yes','no') NOT NULL DEFAULT 'no', PRIMARY KEY (`zone`), KEY `owner` (`owner`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; If you plan on running bind chroot bear in mind that if you connect to MySQL with a unix socket, you need the socket to be in the chroot. An alternative, with slightly more overhead, is to connect with TCP. But don't forget that if you specify the host as 'localhost' the mysql client will try to use unix sockets - you need to specify it as 127.0.0.1 to force TCP. Spend some time tuning MySQL. In particular, make sure the query cache is adequate. Query responses are quite small, so I've found reducing query_cache_min_res_unit to 1k useful. It may be worth considering running a separate instance of MySQL just for bind so that it can be tuned to that specific purpose and the query cache isn't filled with non bind related queries. Bear in mind that storing your zone data in MySQL is considerably slower than bind's default in memory system, so on a busy system you need to get every ounce of performance out of MySQL. Don't use a bind-dlz installation as a caching nameserver. Apart from the usual reasons to keep caching resolvers and authoritative name servers apart, firing recursive queries at bind massively increases the number of sql queries - bind-dlz will have to execute queries in order to work out that it is not authoritative for the requested domain. All these queries on random domains will fill MySQL's query cache with stuff that might not be asked again and you'll see a reduction in query cache hit rate. HTH -- Chris Hastie Find tree care advice at http://www.tree-care.info/
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?48A142E9.2090906>