Moin I just played with the MySQL-Backend hardware: DL380G6 12GB memory pdns: pdns-3.0-rc2 MySQL: 5.2.5-MariaDB-log/XtraDB(InnoDB-Branch)
So Im not into powerdns. As I tried to have a look into mysql only I disabled the cache as good as I thought it is: cache-ttl=0 negquery-cache-ttl=0 query-cache-ttl=0 recursive-cache-ttl=0 As Im missing any good data I created 6*10^6 entries for domains and for every domain some entries in the records-table (about 66*10^6) So here is the size of the two tables: +------------+--------------+-------------+ | TABLE_NAME | INDEX_LENGTH | DATA_LENGTH | +------------+--------------+-------------+ | domains | 475004928 | 431898624 | | records | 11372855296 | 5813305344 | +------------+--------------+-------------+ I was running about 500.000 queries with dnsperf against pdns. As there is an idea to simulate hot data I counted allways the 2. run. And even if the database data exceeds the memory in total. This does not count here, as we are working with hot data (the 500.000). Parse input file: once Ended due to: reaching end of file Queries sent: 494969 queries Queries completed: 494969 queries Queries lost: 0 queries Avg request size: 55 bytes Avg response size: 81 bytes Percentage completed: 100.00% Percentage lost: 0.00% Started at: Fri Apr 22 20:45:29 2011 Finished at: Fri Apr 22 20:46:18 2011 Ran for: 48.938326 seconds Queries per second: 10114.138354 qps Next we get rid of an index: drop index `rec_name_index` on records; There is no need for that index. +------------+--------------+-------------+ | TABLE_NAME | INDEX_LENGTH | DATA_LENGTH | +------------+--------------+-------------+ | domains | 475004928 | 431898624 | | records | 6116343808 | 5813305344 | +------------+--------------+-------------+ yeah see index-size dropping. Queries per second: 10822.316691 qps Ok not faster for *hot* data. But more important not *slower* Next there is an ALTER TABLE records MODIFY `type` enum('A','AAAA','SOA','NS','MX','CNAME','PTR','TXT') NOT NULL; why? (ok there is only the subset of types I used for my test) BUT: 1. The index is smaller and faster 2. You can check for the correct types. +------------+--------------+-------------+ | TABLE_NAME | INDEX_LENGTH | DATA_LENGTH | +------------+--------------+-------------+ | domains | 475004928 | 431898624 | | records | 5816451072 | 5696913408 | +------------+--------------+-------------+ Queries per second: 10918.386216 qps Just to make sure I run all this tests with distributor-threads=32. The default isn't that effective: Queries per second: 5656.248028 qps So nearly the half with distributor-threads=3 :( The last step for today was not indexing the full name-column. So after dropping nametype_index: CREATE INDEX `nametype_index` on records(name(100),type); None of my records had nearly 100 chars. This saves again: +------------+--------------+-------------+ | TABLE_NAME | INDEX_LENGTH | DATA_LENGTH | +------------+--------------+-------------+ | domains | 475004928 | 431898624 | | records | 3547332608 | 5696913408 | +------------+--------------+-------------+ Queries per second: 10923.212970 qps So no performance drop while doing all that stuff. Now it is easier to put more data into memory. So also queries that are not hot should benefit etc. Take care of distributor-threads. There are maybe some things to check i.e. more NOT NULL, UNSIGNED INT instead INT etc. Im going to check against PBXT also and MySQL 5.5. Also looking into MySQL-Cluster should be fun. As I miss live/real data I would like to get into contact with some live/real-data. Comments welcome! Regards Erkan -- über den grenzen muß die freiheit wohl wolkenlos sein _______________________________________________ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users