Re: Bind with mysql backend

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 <[email protected]>
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
> [email protected]
> www.fluidlino.com.au
>
>
>  ------------------------------
> *From:* [email protected] [mailto:
> [email protected]] *On Behalf Of *Mike Ragusa
> *Sent:* Wednesday, 5 March 2008 3:25 AM
> *To:* [email protected]; [email protected]
> *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
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/bind-dlz-testers
>
>

-------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/bind-dlz-testers