before this, only A records were using the weight configured. I had updated the stored procedure to return both id for chosen A record and AAAA record. The sdb driver would then use this 2 formulate a result for Bind to return. In this way, both A and AAAA will follow the weight.

updated stored procedure:

CREATE PROCEDURE selectMirror(IN tableName varchar(255), IN lookupName varchar(255), OUT idV4 int, OUT idV6 int)
BEGIN
DECLARE totalWeight,mirrorWeight,mirrorID,selectedID,r int;
DECLARE mirrorType varchar(255);
DECLARE mirrorsV4 CURSOR FOR SELECT * FROM auto_geo_TEMP WHERE Upper(rdtype)=’A';
DECLARE mirrorsV6 CURSOR FOR SELECT * FROM auto_geo_TEMP WHERE Upper(rdtype)=’AAAA’;
DECLARE EXIT HANDLER FOR NOT FOUND SET selectedID=-1;

DROP table IF EXISTS auto_geo_TEMP;
set @tempQuery = concat(
‘CREATE TEMPORARY TABLE auto_geo_TEMP ENGINE=MEMORY SELECT id,rdtype,weight FROM ‘,
tableName,
‘ WHERE (Upper(rdtype)=\’A\’ OR Upper(rdtype)=\’AAAA\’)
and Upper(name)=Upper(\”,lookupName,’\')
and enabled
‘);
PREPARE stmt FROM @tempQuery;
EXECUTE stmt;

SELECT SUM(weight) into totalWeight from auto_geo_TEMP where Upper(rdtype)=’A';
set selectedID=-1;
IF totalWeight>-1 THEN
OPEN mirrorsV4;
selection: REPEAT
FETCH mirrorsV4 into mirrorID,mirrorType,mirrorWeight;
set r = FLOOR(1 + (RAND() * (totalWeight-1)));
IF r<= mirrorWeight THEN SET selectedID = mirrorID;
END IF;
SET totalWeight=totalWeight – mirrorWeight;
UNTIL ((totalWeight<1)OR(selectedID!=-1)) END REPEAT;
CLOSE mirrorsV4;
END IF;
set idV4=selectedID;

SELECT SUM(weight) into totalWeight from auto_geo_TEMP where Upper(rdtype)=’AAAA’;
set selectedID=-1;
IF totalWeight>-1 THEN
OPEN mirrorsV6;
selection: REPEAT
FETCH mirrorsV6 into mirrorID,mirrorType,mirrorWeight;
set r = FLOOR(1 + (RAND() * (totalWeight-1)));
IF r<= mirrorWeight THEN SET selectedID = mirrorID;
END IF;
SET totalWeight=totalWeight – mirrorWeight;
UNTIL ((totalWeight<1)OR(selectedID!=-1)) END REPEAT;
CLOSE mirrorsV6;
END IF;
set idV6=selectedID;

END

storedprocedure-220609

geodns.pl is the perl script that is used to administer the records in the database. It removes the need for access the database directly using tools like mysql to modify and add new mirrors in day to day operations.

Most of the modification are not too complex. Functions are instructed to handle the weight parameter where necessary.

Summary of changes:

- cmd_add: Require weight parameter. New mirror is inserted with the configured weight.

- cmd_edit: Enabled the script to change the weight parameter of a specified resource ID.

- cmd_list: Enabled the script to display the configured weight.

cmd_check seems to be creating the view correctly. However, new zones i created are not propagated to the correct view. Or rather, the view is not selecting the new zone I created. Need to investigate on this.

Another problem i discovered is that there is no protection against duplicate zone being inserted. The solution i intend to use is not alter the resource_geo table. to enforce that the combination of (name,rdata,CC,region) will need to be unique. However, i am not sure if this is done on purpose. Will need to check with Matthew.

*** Update (1500hrs): it was not shown in the auto_geo_US table because i had forgot to enable the record. My bad. Problem solved.

for the past few days i had been trying to test the weight distribution for the implementation.

For several time, the tests failed after a multiple lookups. multiple being ~50000 lookup. By trying to do a simple test using “dig” and “mysql”, the fault quickly pointed to the DNS server. The SQL server was still responding to queries, but dig was returning SERVFAIL message.

I decided to uncomment the diagnostics printf statement in the MySQL BIND SDB Driver. Running the test again with the BIND DNS Server printing the messages to console shows the problem almost immediately.

———————-

09-Jun-2009 09:07:03.841 client 127.0.0.1#53792: view other: query: releases.geo.mozilla.com IN A –
59
36
33
34
09-Jun-2009 09:07:03.851 client 127.0.0.1#53792: view other: query: releases.geo.mozilla.com IN A –
13
Out of memory (Needed 8164 bytes)
Out of memory (Needed 8164 bytes)
09-Jun-2009 09:07:04.512 client 127.0.0.1#53792: view other: query: releases.geo.mozilla.com IN A –
Out of memory (Needed 8164 bytes)
Out of memory (Needed 8164 bytes)

———————-

Looking at the system status using “top”, named was taking up memory as more queries are made. And there was definitely a memory leak. After looking at my codes again, i found that there was a resultset that was not freed after the results were used.

The fix was quite easy, it was to free up the memory allocation after consuming the results.

snprintf(str, sizeof(str),"call selectMirror(‘%s’,'%s’,@resID)", dbi->table, name);
mysql_query(&dbi->conn, str);
snprintf(str, sizeof(str),"select @resID");
if( mysql_query(&dbi->conn, str) != 0 )
{
        return (ISC_R_FAILURE);
}
res = mysql_store_result(&dbi->conn);
while ((row = mysql_fetch_row(res)))
{
        rid = atoi(row[0]);
}
mysql_free_result(res);

Testing this new build solved the memory leak problem. The memory usage remained constant at 1.2%.

Another bug i found while checking the code shows that i had for some reason (probably accidental) added an extra totalWeight reduction per loop cycle. Meaning in each iterration,the totalWeight of the available resources were getting reduced twice. This is probably the reason why the previous test results i sent to Matthew showed that only a subset of the total resources available were used. (Hopefully, this is the reason)

I had also added the SQL directive for the temporary table to be created in memory. The difference might be small because only a subset (based on region, resource type and resource name) of the mirrors are loaded into the temporary table. These data should fall below MySQL limit before loading the temporary table into disks. But the new addition should enforce that the table is always in memory.

The previous selectMirror procedure is dropped and the updated procedure is created.

CREATE PROCEDURE selectMirror(IN tableName varchar(255), IN lookupName varchar(255), OUT id int)
BEGIN
DECLARE notFound INT DEFAULT 0;
DECLARE totalWeight,mirrorWeight,mirrorID,selectedID,r int;
DECLARE mirrors CURSOR FOR SELECT * FROM auto_geo_TEMP;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET notFound=1;

DROP table IF EXISTS auto_geo_TEMP;
set @tempQuery = concat(
‘CREATE TEMPORARY TABLE auto_geo_TEMP ENGINE = MEMORY SELECT id,weight FROM ‘,
tableName,
‘ WHERE Upper(rdtype)=\’A\’
and Upper(name)=Upper(\”,lookupName,’\')
and enabled
‘);
PREPARE stmt FROM @tempQuery;
EXECUTE stmt;
SELECT SUM(weight) into totalWeight from auto_geo_TEMP;
set selectedID=-1;
OPEN mirrors;

selection: REPEAT
FETCH mirrors into mirrorID,mirrorWeight;
set r = FLOOR(1 + (RAND() * (totalWeight-1)));
IF r<= mirrorWeight THEN SET selectedID = mirrorID;
END IF;
if selectedID != -1 THEN LEAVE selection;
END IF;
SET totalWeight=totalWeight – mirrorWeight;
UNTIL (totalWeight-1)OR(notFound=1) END REPEAT;
CLOSE mirrors;
set id=selectedID;
END

The test for 1000000 lookups is going on as i am posting this entry. It is going on well. Will analyze the data and post the results soon.

After 1 week of looking high and low, finally solved the problem. Specifically with the cursor using variable table name.

Below is the stored procedure for selecting the mirror":

CREATE PROCEDURE selectMirror(IN tableName varchar(255), IN lookupName varchar(255), OUT id int)
BEGIN
DECLARE notFound INT DEFAULT 0;
DECLARE totalWeight,mirrorWeight,mirrorID,selectedID,r int;
DECLARE mirrors CURSOR FOR SELECT * FROM auto_geo_TEMP;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET notFound=1;

DROP table IF EXISTS auto_geo_TEMP;
set @tempQuery = concat(
‘CREATE TEMPORARY TABLE auto_geo_TEMP SELECT id,weight FROM ‘,
tableName,
‘ WHERE Upper(rdtype)=\’A\’
and Upper(name)=Upper(\”,lookupName,’\')
and enabled
‘);
PREPARE stmt FROM @tempQuery;
EXECUTE stmt;
SELECT SUM(weight) into totalWeight from auto_geo_TEMP;
set selectedID=-1;
OPEN mirrors;

selection: REPEAT
FETCH mirrors into mirrorID,mirrorWeight;
set r = FLOOR(1 + (RAND() * (totalWeight-1)));
IF r<= mirrorWeight THEN SET selectedID = mirrorID;
END IF;
SET totalWeight=totalWeight – mirrorWeight;
if selectedID != -1 THEN LEAVE selection;
END IF;
SET totalWeight=totalWeight – mirrorWeight;
UNTIL (totalWeight<1)OR(notFound=1) END REPEAT;
CLOSE mirrors;
set id=selectedID;
END