[Pdns-users] mysql-tests

[erkan yanar]

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