[Pdns-users] Round Robin Via MYSQL rand()

[Brandon Phillips]

Hi ,
I just downloaded and started investigating powerdns this evening.  I am
using the generic mysql backend.  I noticed form the archives, that
Round Robin is a *NOT* a current function, but a lot of people seem to
want it.  That got me thinking, since the queries are looked up in the
DB, cannot the DB (mysql in my case) randomize its results?  Along those
lines. I tried specifying custom queries to the pdns.conf file that look
like:


gmysql-basic-query=select content,ttl,prio,type,domain_id,name,rand() as
rnd from records where qtype='%s' and name='%s' order by rnd

gmysql-id-query=select content,ttl,prio,type,domain_id,name,rand() as
rnd  from records where qtype='%s' and name='%s' and domain_id=%d order
by rnd

gmysql-any-query=select content,ttl,prio,type,domain_id,name,rand() as
rnd  from records where name='%s' order by rnd

gmysql-any-id-query=select content,ttl,prio,type,domain_id,name,rand()
as rnd  from records where  name='%s' and domain_id=%d order by rnd

These pretty much just add a random field, and set the sort order to the
random field.  Using dig I get these results over time:

Try 1:


;; QUESTION SECTION:
;www.test.com.                  IN      A

;; ANSWER SECTION:
www.test.com.           120     IN      A       199.198.197.196
www.test.com.           120     IN      A       111.222.111.222
www.test.com.           120     IN      A       222.111.222.111
www.test.com.           120     IN      A       111.111.111.111
www.test.com.           120     IN      A       222.222.222.222


Try 2: 

;; QUESTION SECTION:
;www.test.com.                  IN      A

;; ANSWER SECTION:
www.test.com.           120     IN      A       111.111.111.111
www.test.com.           120     IN      A       199.198.197.196
www.test.com.           120     IN      A       222.111.222.111
www.test.com.           120     IN      A       222.222.222.222
www.test.com.           120     IN      A       111.222.111.222


Try 3:
;; QUESTION SECTION:
;www.test.com.                  IN      A

;; ANSWER SECTION:
www.test.com.           120     IN      A       199.198.197.196
www.test.com.           120     IN      A       111.111.111.111
www.test.com.           120     IN      A       222.222.222.222
www.test.com.           120     IN      A       222.111.222.111
www.test.com.           120     IN      A       111.222.111.222

I do have to wait a few seconds in between requests, but it does appear
to be randomizing results, such that the requests would be balanced.  

Before I ask my questions let me state that I am pretty ignorant when it
comes to setting up DNS servers, (frankly, I am surprised I got this
far).  

Anyway, can some experiences users, comment on whether this strategy
will work in the long term, without causing problems.  I recently took
over operations of a small ISP, about 2000 domains.  I know it would add
a bit of overhead to the DB, but I can live with that.  I don't have
enough DNS experience to know how those queries and setting will effect 
normal  operations.

If I can manipulate the queries like that, could I not also write a
monitoring script, that could also update the DB, with a "health" value,
so my where clause could also specify only hosts that the database
thinks are alive.   Or possibly a "paid" field so if the customer is a
deadbeat, the domains can swiftly be "deactivated" by simply setting a
flag in the database.

I must admit I am pretty excited about the possibilities, but I want to
get some feedback, before barking up the wrong tree.

Thanks in advance.
Brandon