Re: Bind with mysql backend

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Mar 04, 2008 at 11:25:26AM -0500, Mike Ragusa wrote:

>   I have been using the default schema with bind-dlz and mysql as noted on
>   their website. I was wondering what people use to manage this information
>   and if they used another database schema that offered better performance
>   or ablity to manage the data better.

We have not rolled this live yet, so I do not know what kind of
performance I'll be able to achieve, but I do know that our busiest mysql
servers typically achieve around 1400 queries per second, so I don't
expect mysql to be any kind of limiting factor for us.  (Our current DNS
servers are running at about 40 qps, so I don't expect the load at mysql
to be much above 100 qps at the peak of the day...and these are simpler
queries than our busy servers are doing...and on beefier servers).

We use Rose::DB here, so we modeled a database that fits into Rose's
idea of mapping and ended up with this:

mysql> show create table domain \G
*************************** 1. row ***************************
       Table: domain
Create Table: CREATE TABLE `domain` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `disabled_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `expires_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` tinyint(1) default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `domain_name_idx` (`name`),
  KEY `user_id_idx` (`user_id`),
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table domain_record \G
*************************** 1. row ***************************
       Table: domain_record
Create Table: CREATE TABLE `domain_record` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `domain_id` int(15) unsigned NOT NULL,
  `type_id` smallint(5) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `mx_priority` tinyint(3) unsigned default NULL,
  `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_by_user_id` bigint(20) unsigned default NULL,
  `active` tinyint(1) default '1',
  PRIMARY KEY  (`id`),
  KEY `domain_id_idx` (`domain_id`),
  KEY `type_id_idx` (`type_id`),
  KEY `name_idx` (`name`),
  CONSTRAINT `domain_record_domain_id` FOREIGN KEY (`domain_id`)
REFERENCES `domain` (`id`),
  CONSTRAINT `domain_record_type_id` FOREIGN KEY (`type_id`) REFERENCES
`domain_record_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table domain_record_type \G
*************************** 1. row ***************************
       Table: domain_record_type
Create Table: CREATE TABLE `domain_record_type` (
  `id` smallint(5) unsigned NOT NULL,
  `type` varchar(10) NOT NULL,
  `description` varchar(255) NOT NULL default '',
  `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `active_idx` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from domain_record_type \G
*************************** 1. row ***************************
         id: 1
       type: A
description: A record is an IP address
 created_at: 2008-02-13 12:32:47
 updated_at: 0000-00-00 00:00:00
     active: 1
*************************** 2. row ***************************
         id: 2
       type: NS
description: NS record is the hostname of DNS servers for domains
 created_at: 2008-02-13 12:32:47
 updated_at: 0000-00-00 00:00:00
     active: 1
*************************** 3. row ***************************
         id: 3
       type: MX
description: MX record is the hostname of mail servers for domains
 created_at: 2008-02-13 12:32:47
 updated_at: 0000-00-00 00:00:00
     active: 1
*************************** 4. row ***************************
         id: 4
       type: TXT
description: TXT record is miscellaneous text entries for domains with a few specific uses such as SPF or DKIM message signing
 created_at: 2008-02-13 12:32:47
 updated_at: 0000-00-00 00:00:00
     active: 1
*************************** 5. row ***************************
         id: 5
       type: CNAME
description: CNAME is an alias that points to a different hostname
 created_at: 2008-02-13 12:32:47
 updated_at: 0000-00-00 00:00:00
     active: 1
*************************** 6. row ***************************
         id: 6
       type: AAAA
description: AAAA is an ipv6 IP address
 created_at: 2008-02-13 12:32:47
 updated_at: 0000-00-00 00:00:00
     active: 0
*************************** 7. row ***************************
         id: 7
       type: SOA
description: SOA is the Start of Authority, basic information about your domain
 created_at: 2008-02-14 11:17:34
 updated_at: 0000-00-00 00:00:00
     active: 1
7 rows in set (0.00 sec)

# cat /var/named/chroot/etc/named.dlz 
dlz "Mysql zone" {
   database "mysql
   {host=127.0.0.1 user=USERNAME pass=PASSWORD dbname=bind ssl=false}
   {SELECT name AS zone FROM domain WHERE name = '%zone%' AND active=1}
   {SELECT '86400' AS ttl,domain_record_type.type,
        case when lower(domain_record_type.type) = 'mx' then domain_record.mx_priority end, 
        case when lower(domain_record_type.type) = 'txt' then concat('\"', domain_record.value, '\"') else domain_record.value end AS data
        FROM domain_record,domain,domain_record_type 
        WHERE domain_record_type.id=domain_record.type_id AND domain_record.active=1
            AND domain.name='%zone%'
            AND domain_record.name='%record%'}";
};
- -- 
Regards...		Todd
The greatest shortcoming of the human race is our inability to
understand the exponential function.   --Albert Bartlett, physicist
Linux kernel 2.6.22-14-generic   3 users,  load average: 0.06, 0.05, 0.00
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHzaFiY2VBGxIDMLwRAmELAJ0UTGzk7iHYdeAAmXog6HFbIt/R5ACfY7A8
ZlvCjwcTEKbr0PHWQMSi8S0=
=U53j
-----END PGP SIGNATURE-----

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/