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

[Chris]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20150226/6b505d32/attachment-0001.html>