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: li...@shthead.com
To: pdns-users@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@mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users
_______________________________________________
Pdns-users mailing list
Pdns-users@mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users
_______________________________________________
Pdns-users mailing list
Pdns-users@mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users
