[Pdns-users] mysql-tests
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','','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
Re: [Pdns-users] Documentation on populating backend database tables
On Fri, Apr 22, 2011 at 09:18:45PM +, Sebastian Tennant wrote:
>
> How, for example, do I set up pdns as an authoritative name server for foo.com
> and publish the addresses of two subdomains (ns1 and ns2):
>
> My guess would be something like this:
>
> INSERT INTO domains (name,type) VALUES ('foo.com', ???);
> -- what does type mean here?
>
> INSERT INTO records (domain_id, name, type, ttl)
>VALUES (1, '', 'NS', 259200);
>
> INSERT INTO records (domain_id, name, type, ttl)
>VALUES (1, 'ns1', 'A', 86400);
>
> INSERT INTO records (domain_id, name, type, ttl)
>VALUES (1, 'ns2', 'A', 86400);
>
> But wait... where do the IP addresses go?
>
> Any help/tips/advice/pointers much appreciated.
>
> sebyte
Here is a basic desciption of the fields and queries that PDNS
uses:
http://doc.powerdns.com/generic-mypgsql-backends.html#id587910
You can use that information to determine what to INSERT since
the queries used for sqlite are the same as the Generic MySQL and
PgSQL backends.
Consult your favorite DNS RFC/documentation to see what records
you will need to set up a zone.
Cheers,
Ken
___
Pdns-users mailing list
Pdns-users@mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users
[Pdns-users] Documentation on populating backend database tables
Hi all,
Can anyone point me to some documentation on how to populate the backend
database tables (assuming you're using a relational database backend), i.e.,
what values should you use in each column, and when?
I've chosen to use sqlite3 as my backend, so my schema looks like this:
--8<---cut here---start->8---
CREATE TABLE domains (
id INTEGER PRIMARY KEY,
nameVARCHAR(255) NOT NULL,
master VARCHAR(128) DEFAULT NULL,
last_check INTEGER DEFAULT NULL,
typeVARCHAR(6) NOT NULL,
notified_serial INTEGER DEFAULT NULL,
account VARCHAR(40) DEFAULT NULL );
CREATE UNIQUE INDEX name_index ON domains(name);
CREATE TABLE records (
id INTEGER PRIMARY KEY,
domain_id INTEGER DEFAULT NULL,
nameVARCHAR(255) DEFAULT NULL,
typeVARCHAR(6) DEFAULT NULL,
content VARCHAR(255) DEFAULT NULL,
ttl INTEGER DEFAULT NULL,
prioINTEGER DEFAULT NULL,
change_date INTEGER DEFAULT NULL );
CREATE INDEX rec_name_index ON records(name);
CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);
CREATE TABLE supermasters (
ip VARCHAR(25) NOT NULL,
nameserver VARCHAR(255) NOT NULL,
account VARCHAR(40) DEFAULT NULL );
--8<---cut here---end--->8---
How, for example, do I set up pdns as an authoritative name server for foo.com
and publish the addresses of two subdomains (ns1 and ns2):
My guess would be something like this:
INSERT INTO domains (name,type) VALUES ('foo.com', ???);
-- what does type mean here?
INSERT INTO records (domain_id, name, type, ttl)
VALUES (1, '', 'NS', 259200);
INSERT INTO records (domain_id, name, type, ttl)
VALUES (1, 'ns1', 'A', 86400);
INSERT INTO records (domain_id, name, type, ttl)
VALUES (1, 'ns2', 'A', 86400);
But wait... where do the IP addresses go?
Any help/tips/advice/pointers much appreciated.
sebyte
--
Eamcs' AlsaPlayer - Music Without Jolts
Lightweight, full-featured and mindful of your idyllic happiness.
http://home.gna.org/eap
___
Pdns-users mailing list
Pdns-users@mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users
