[Bind-dlz-testers] Bind with mysql backend
Mike Ragusa
mragusa at gmail.com
Thu Mar 6 21:01:27 UTC 2008
Also this is the performance I got using dnsperf.
MyISAM
Statistics:
Parse input file: once
Ended due to: reaching end of file
Queries sent: 19349 queries
Queries completed: 19348 queries
Queries lost: 1 queries
Avg request size: 38 bytes
Avg response size: 221 bytes
Percentage completed: 99.99%
Percentage lost: 0.01%
Started at: Wed Mar 5 13:07:36 2008
Finished at: Wed Mar 5 13:10:58 2008
Ran for: 201.879686 seconds
Queries per second: 95.839261 qps
InnoDB
Statistics:
Parse input file: once
Ended due to: reaching end of file
Queries sent: 19349 queries
Queries completed: 19349 queries
Queries lost: 0 queries
Avg request size: 38 bytes
Avg response size: 221 bytes
Percentage completed: 100.00%
Percentage lost: 0.00%
Started at: Wed Mar 5 13:27:45 2008
Finished at: Wed Mar 5 13:30:26 2008
Ran for: 160.849914 seconds
Queries per second: 120.292262 qps
On Tue, Mar 4, 2008 at 4:14 PM, Graham Weldon <graham at fluidlino.com.au>
wrote:
> I am personally using a non-standard DB Structure, although its closely
> modelled off the sample one.
> Reason for doing this alternate version is to bring the database structure
> in line with naming conventions for CakePHP, to ensure we can manage the
> data within the database easily, and in a useful manner.
>
> mysql> show tables;
> +---------------------+
> | Tables_in_dns |
> +---------------------+
> | dns_queries |
> | dns_query_summaries |
> | dns_records |
> | dns_transfers |
> | dns_zones |
> +---------------------+
> 8 rows in set (0.00 sec)
>
> mysql> desc dns_zones;
> +-------------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+------------------+------+-----+---------+----------------+
> | id | int(11) unsigned | NO | PRI | NULL | auto_increment |
> | domain_id | int(11) | NO | | NULL | |
> | domain_name | varchar(255) | NO | MUL | NULL | |
> +-------------+------------------+------+-----+---------+----------------+
> 3 rows in set (0.00 sec)
>
> mysql> desc dns_records;
>
> +-------------+---------------------+------+-----+---------------------+----------------+
> | Field | Type | Null | Key | Default |
> Extra |
>
> +-------------+---------------------+------+-----+---------------------+----------------+
> | id | int(11) unsigned | NO | PRI | NULL |
> auto_increment |
> | dns_zone_id | int(11) unsigned | NO | MUL | NULL
> | |
> | ttl | int(11) unsigned | NO | | 21600
> | |
> | type | varchar(10) | NO | | NULL
> | |
> | host | varchar(255) | NO | | @
> | |
> | mx_priority | int(11) unsigned | YES | | NULL
> | |
> | data | text | YES | | NULL
> | |
> | contact | varchar(255) | YES | | NULL
> | |
> | serial | bigint(20) unsigned | YES | | NULL
> | |
> | refresh | int(11) unsigned | YES | | NULL
> | |
> | retry | int(11) unsigned | YES | | NULL
> | |
> | expire | int(11) unsigned | YES | | NULL
> | |
> | minimum | int(11) unsigned | YES | | NULL
> | |
> | updated | timestamp | NO | | 0000-00-00 00:00:00
> | |
>
> +-------------+---------------------+------+-----+---------------------+----------------+
> 14 rows in set (0.00 sec)
>
> mysql> desc dns_queries;
>
> +-------------+------------------+------+-----+-------------------+----------------+
> | Field | Type | Null | Key | Default |
> Extra |
>
> +-------------+------------------+------+-----+-------------------+----------------+
> | id | int(11) unsigned | NO | PRI | NULL |
> auto_increment |
> | domain_name | varchar(255) | NO | | NULL
> | |
> | timestamp | timestamp | NO | | CURRENT_TIMESTAMP
> | |
>
> +-------------+------------------+------+-----+-------------------+----------------+
> 3 rows in set (0.00 sec)
>
> mysql> desc dns_transfers;
> +-------------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+------------------+------+-----+---------+----------------+
> | id | int(11) unsigned | NO | PRI | NULL | auto_increment |
> | dns_zone_id | int(11) unsigned | NO | MUL | NULL | |
> | client | int(11) unsigned | NO | MUL | NULL | |
> +-------------+------------------+------+-----+---------+----------------+
> 3 rows in set (0.01 sec)
>
> And finally, my DLZ config... (coloured to match the coloring used on the
> MySQL section of the DLZ website
> ---------------------------
> dlz "MySQL Zone" {
> database "mysql
> {host=localhost dbname=dns_db user=dns_user pass=dns_pass}
> {SELECT DISTINCT 'DOMAIN_EXISTS' FROM `dns_zones` WHERE
> `domain_name` = '%zone%'}
> {SELECT `ttl`,`type`,`mx_priority`,IF (`type` = 'TXT',
> concat('\"', `data`, '\"'), `data`) FROM `dns_records` LEFT JOIN `dns_zones`
> ON `dns_records`.`dns_zone_id` = `dns_zones`.`id` WHERE
> `domain_name`='%zone%' AND `host`='%record%' AND NOT (`type`='SOA' OR
> `type`='NS')}
> {SELECT
> `ttl`,`type`,`mx_priority`,`data`,`contact`,`serial`,`refresh`,`retry`,`expire`,`minimum`
> FROM `dns_records` LEFT JOIN `dns_zones` ON
> `dns_records`.`dns_zone_id`=`dns_zones`.`id` WHERE `domain_name`='%zone%'
> AND (`type`='SOA' OR `type`='NS') ORDER BY `type` DESC}
> {SELECT `ttl`,`type`,`mx_priority`,IF (`type` = 'TXT',
> concat('\"', `data`, '\"'),
> `data`),`contact`,`serial`,`refresh`,`retry`,`expire`,`minimum` FROM
> `dns_records` LEFT JOIN `dns_zones` ON
> `dns_records`.`dns_zone_id`=`dns_zones`.`id` WHERE `domain_name`='%zone%'
> AND NOT (`type`='SOA' OR `type`='NS')}
> {SELECT DISTINCT 'ALLOW_TRANSFER' FROM `dns_transfers` LEFT JOIN
> `dns_zones` ON `dns_transfers`.`dns_zone_id` = `dns_zones`.`id` WHERE
> `domain_name`='%zone%' AND `client`='%client%'}
> {INSERT INTO `dns_queries` SET `domain_name`='%zone%'}";
> };
> ---------------------------
>
>
> Cheers,
>
> Graham Weldon
>
> *Fluid Lino
> *(02) 4927 5337
> (0407) 017 293
> graham at fluidlino.com.au
> www.fluidlino.com.au
>
>
> ------------------------------
> *From:* bind-dlz-testers-bounces at lists.sourceforge.net [mailto:
> bind-dlz-testers-bounces at lists.sourceforge.net] *On Behalf Of *Mike Ragusa
> *Sent:* Wednesday, 5 March 2008 3:25 AM
> *To:* bind-users at isc.org; bind-dlz-testers at lists.sourceforge.net
> *Subject:* [Bind-dlz-testers] Bind with mysql backend
>
> 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.
>
> Default Schema:
> +-------------+------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+------------+------+-----+---------+-------+
> | zone | text | YES | MUL | NULL | |
> | host | text | YES | MUL | NULL | |
> | type | text | YES | MUL | NULL | |
> | data | text | YES | | NULL | |
> | ttl | int(11) | NO | | 3600 | |
> | mx_priority | text | YES | | NULL | |
> | refresh | int(11) | YES | | NULL | |
> | retry | int(11) | YES | | NULL | |
> | expire | int(11) | YES | | NULL | |
> | minimum | int(11) | YES | | NULL | |
> | serial | bigint(20) | YES | | NULL | |
> | resp_person | text | YES | | NULL | |
> | primary_ns | text | YES | | NULL | |
> +-------------+------------+------+-----+---------+-------+
>
>
> Default MySQL for named.conf:
>
> dlz "Mysql zone" {
> database "mysql
> {host=localhost dbname=dns user=dns pass=bind-dlz-test}
> {select zone from dns_records where zone = '%zone%'}
> {select ttl, type, mx_priority, case when lower(type)='txt' then
> concat('\"', data, '\"')
> when lower(type) = 'soa' then concat_ws(' ', data, resp_person,
> serial, refresh, retry, expire, minimum)
> else data end from dns_records where zone = '%zone%' and host =
> '%record%'}
> {}
> {select ttl, type, host, mx_priority, case when lower(type)='txt' then
> concat('\"', data, '\"') else data end, resp_person, serial,
> refresh, retry, expire,
> minimum from dns_records where zone = '%zone%'}
> {select zone from xfr_table where zone = '%zone%' and client =
> '%client%'}";
> };
>
> -------------------------------------------------------------------------
> 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/
> _______________________________________________
> Bind-dlz-testers mailing list
> Bind-dlz-testers at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/bind-dlz-testers
>
>
More information about the bind-users
mailing list