[Pdns-users] FW: Optimize Powerdns and Mysql for DB with 500K entries

Thu Feb 26 16:20:32 UTC 2015

Hi chris,(and AJ)
Thanks for the detailed response.
I now have 4GB of RAM available and looking at the size of my records_orig table, I have set innodb-buffer-pool-size = 950M
+----------------------------+---------+------------+----------+--------+------------+| schema_table               | data_MB | indexes_MB | total_MB | engine | row_format |+----------------------------+---------+------------+----------+--------+------------+| powerdns.records_orig      |  449.95 |     399.16 |   849.11 | InnoDB | Compact    |

In terms of my setup, I am using pdns 3.4.2.1 and I am running NAPTR queries from another server using the dig utility to test query time.
The powerdns database is made using the standard guide, however I have renamed the table to records_orig from records.
Structure below;

| records_orig | CREATE TABLE `records_orig` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `domain_id` int(11) DEFAULT NULL,  `name` varchar(255) DEFAULT NULL,  `type` varchar(10) DEFAULT NULL,  `content` varchar(64000) DEFAULT NULL,  `ttl` int(11) DEFAULT NULL,  `prio` int(11) DEFAULT NULL,  `change_date` int(11) DEFAULT NULL,  `disabled` tinyint(1) DEFAULT '0',  `ordername` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,  `auth` tinyint(1) DEFAULT '1',  `carrierrate` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `nametype_index` (`name`,`type`),  KEY `domain_id` (`domain_id`),  KEY `recordorder` (`domain_id`,`ordername`)) ENGINE=InnoDB AUTO_INCREMENT=14077920 DEFAULT CHARSET=latin1 |

Now that has 3.5 million entries in it, however there are particular time of day entries required, so I infact made a view called records that pdns will then query, and is shown below, and contains a new field I added called carrierrate.

| records | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `records` AS select `records_orig`.`id` AS `id`,`records_orig`.`domain_id` AS `domain_id`,`records_orig`.`name` AS `name`,`records_orig`.`type` AS `type`,`records_orig`.`content` AS `content`,`records_orig`.`ttl` AS `ttl`,`records_orig`.`prio` AS `prio`,`records_orig`.`change_date` AS `change_date`,`records_orig`.`disabled` AS `disabled`,`records_orig`.`ordername` AS `ordername`,`records_orig`.`auth` AS `auth`,`records_orig`.`carrierrate` AS `carrierrate` from `records_orig` where ((`records_orig`.`carrierrate` = 'BT-Peak') or (`records_orig`.`carrierrate` = 'BTI-Weekend') or (`records_orig`.`carrierrate` = 'Colt-OffPeak') or (`records_orig`.`carrierrate` = 'ColtI-OffPeak') or (`records_orig`.`carrierrate` = 'Gamma-OffPeak') or (`records_orig`.`carrierrate` = 'UPC-\n\nOffPeak') or (`records_orig`.`carrierrate` = 'Verizon-OffPeak') or (`records_orig`.`carrierrate` = 'Bandwidth-Allday') or (`records_orig`.`carrierrate` = 'BBCOM-Allday') or (`records_orig`.`carrierrate` = 'TATA-Allday') or (`records_orig`.`carrierrate` = 'SOA')) | latin1               | latin1_swedish_ci    |
Now as this is a view, no indexes are there, could this be causing me problems? And the pdns.conf is as standard, I haven't modifed it.Also I havent modified the query powerdns performs, as I am purely holding NAPTR records in a single domain, would changing the mysql query help, as I notice it goes through the SOA,NS and so on queries until it gets to NAPTR.
In terms of your question about performance, I can see that some query times are 0-8ms, however others are up to 4500ms, so I need to understand where I can optimize further on this current VM server, as the table view it is querying is around 500K rows.
Any help would be great.
Many thanks
Jon
Date: Thu, 26 Feb 2015 10:14:41 +0800
From: lists at shthead.com
To: pdns-users at mailman.powerdns.com
Subject: Re: [Pdns-users] Optimize Powerdns and Mysql for DB with 500K	entries

    Hi,

    I have 3 PowerDNS instances running with the MySQL backend across 4
    DNS servers. The largest has 1,883,763 domains with 9,736,133
    records (With all instances combined there is a total of 21M rows in
    the records table).

    The only things I have done for performance are:

    - All tables are InnoDB

    - All DNS servers have 16GB or more of memory, InnoDB buffer pool
    size is at least 10GB on each

    - MySQL 5.6 (actually running Percona, upgrading from 5.5 to 5.6
    gave me a slight performance increase)

    - InnoDB file format is barracuda, tables are compressed with 4KB
    page size

    With table compression my largest instance uses a total of 750mb on
    disk.

    The minimum specs for my DNS servers are:

    - 2 x E5-2620 CPU (6 cores + hyperthreading each)

    - 16GB of RAM

    - 2 x 15K SAS in RAID 1

    With the 3 power DNS instances + unbound instance for caching name
    server the load average on the servers is less than 1, there is no
    IO wait. Each DNS server is handling an average of 6,714 queries per
    second across the 3 PowerDNS instances and Unbound.

    Using dnsscope for my biggest instance I can see that I get these
    stats:

    0.01% of questions answered within 50 usec (0.01%)

    51.67% of questions answered within 100 usec (51.67%)

    60.11% of questions answered within 200 usec (8.44%)

    60.40% of questions answered within 300 usec (0.29%)

    60.70% of questions answered within 400 usec (0.30%)

    63.85% of questions answered within 800 usec (3.14%)

    67.78% of questions answered within 1000 usec (3.93%)

    97.93% of questions answered within 2.00 msec (30.15%)

    99.71% of questions answered within 4.00 msec (1.78%)

    99.97% of questions answered within 8.00 msec (0.26%)

    100.00% of questions answered within 32.00 msec (0.03%)

    100.00% of questions answered within 64.00 msec (0.00%)

    0 responses (0.00%) older than 2 seconds

    Average non-late response time: 569.60 usec

    What kind of statistics are you seeing? Do you get large amounts of
    I/O wait on the server? Is your mysql innodb buffer pool size large
    enough to hold the entire table in RAM?

    Chris

    On 26/02/2015 2:40 AM, Jonathan Hunter
      wrote:

      Hi Guys,

        I appreciate there are optimization tips on the website,
          however I wondered if there are any specific tips for
          optimization when dealing with a records table or associated
          view of 500K rows in a Mysql backend database on a Virtual
          Centos Machine with 2 x 3Ghz processors, 1GB RAM and 20GB
          Memory.

        I am seeing some slow responses in terms of using dig to
          perform NAPTR record lookups.

        Any help would be great.

        Many thanks

        Jon

      _______________________________________________
Pdns-users mailing list
Pdns-users at mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users

_______________________________________________
Pdns-users mailing list
Pdns-users at mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users 		 	   		   		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20150226/932c6864/attachment.html>