Jia Jun Goes GSoC

geodns-new.pl

2009-08-20T14:35:00.002-07:00

modified the geodns.pl from the original setup. Most of the file is still largely the same as the original. Added in some functions to support the new views management functions and modified the old ones to support weight configuration.

geodns-new.pl

30/10/2009: Updated the file link, no change in content though.

web UI

2009-08-18T17:38:00.002-07:00

Finally publishing the web UI.
This is a PHP script, hence, PHP need to be installed on the server. PHP 5 to be exact
The database configuration can be done by modifying the variables in sql.php

Web UI

Configuration Download Script

2009-08-11T23:15:00.002-07:00

to automate the comparison and download process of the generated configuration file (from WebUI), the following shell script could be used.
simply replace the following to use the script.
filePath="/var/named/downloaded.views"; #change the filepath to the destination file desired.
replace all http://12.7.0.0.1 with the location where the webUI is hosted.

getConfig.sh

testing tool

2009-08-11T22:57:00.002-07:00

custom created a testing tool that performs simple load testing on the DNS server.
The main feature of the tool allows the results to be saved into the database table (schema provided below) and then displaying the statistics of each result at the end of the test. This allows easy verification of the weights configured on the records.
sample output:

Retriving results for test: 4
Number of results:100000
Formatting output...
Query: releases.geo.mozilla.com
Test started on: 2009-08-05 20:41:41
155.98.64.83    17699(17.70%)
149.20.20.5     17613(17.61%)
129.101.198.59 13460(13.46%)
128.61.111.9    13320(13.32%)
204.152.184.113 6751(6.75%)
202.177.202.154 6693(6.69%)
131.188.12.212 6564(6.56%)
156.56.247.196 4437(4.44%)
64.50.236.214   2365(2.37%)
64.50.238.52    2268(2.27%)
64.50.236.52    2264(2.26%)
204.152.184.196 2231(2.23%)
216.165.129.141 2191(2.19%)
204.246.0.136   2144(2.14%)
Average Query Time: 0.00868 seconds

Files:
loadDNS.pl
database-testLoad.sql

database countries

2009-08-07T15:58:00.002-07:00

To populate the countries in the geoip_geo table, the following file can be used to insert all countries available in the GeoLite Country database into the table.

geoip_geo is used to provide the countries available for matching to views.

databsae-geoip-countries.sql

database schema (upgrade)

2009-08-05T03:51:00.002-07:00

If the database were already containing the old table. Perform the following to upgrade the database

update releases-geo using the upgrade file
    [jiajun@sm-geodnssoc ~]$ mysql -u user -p geo-mozilla-com< database-upgrade.sql

create the new tables and stored procedures
    [jiajun@sm-geodnssoc ~]$ mysql -u user -p geo-mozilla-com< database.sql

rebuild the views either from geodns.pl/web UI/mysql before restarting the DNS server
    [jiajun@sm-geodnssoc ~]$ mysql –u user –p
    mysql> call buildViews();

database-upgrade.sql

database.sql

database schema

2009-08-02T19:31:00.002-07:00

Uploading the database schema. To create the MySQL DB for Bind’s usage, simply create and database and use the file to create the tables and stored procedures.
Example.
[jiajun@sm-geodnssoc ~]$ mysql –u user –p
mysql> CREATE DATABASE `geo-mozilla-com`;
mysql> exit
[jiajun@sm-geodnssoc ~]$ mysql -u user -p geo-mozilla-com< database.sql
rebuild the views either from geodns.pl/web UI/mysql before restarting the DNS server
[jiajun@sm-geodnssoc ~]$ mysql –u user –p
mysql> call buildViews();
NOTE: I had reuploaded the schema file on 5 Aug 2337 hrs (GMT +8).
Changes:
- the new version does not drop the table if it exists. It will create the table only if it does not exists. However, stored procedures will always be recreated.

Files:
Database Schema

Generated Diff

2009-08-01T22:15:00.002-07:00

finished testing the diff file yesterday.
Downloaded bind-9.4.3-P3.tar.gz and made sure the codes work against it and the generated the patch by comparing the original files with the modified ones.
The diff included modifications to enable the dlz driver so that the vanilla codes can be changed without much intervention.
To replicate the results, the instructions are as follows:

download bind-9.4.3-P3.tar.gz
download mysql-bind.tar.gz
copy bind-9.4.3-P3.tar.gz to <Base Directory>
copy mysql-bind.tar.gz to <Base Directory>
cd <Base Directory>
tar -zxvf bind-9.4.3-P3.tar.gz
tar -zxvf mysql-bind.tar.gz
patch -p0 < ../allpatch.diff
cd bind-9.4.3-P3/bin/named/
ln -s ../../../mysql-bind/mysqldb.c mysqldb.c
cd include/named/
ln -s ../../../../../mysql-bind/mysqldb.h mysqldb.h
cd <Base Directory>/bind-9.4.3-P3
./configure --disable-openssl-version-check CFLAGS="-I/usr/local/include/" LDFLAGS="-L/usr/local/lib -lGeoIP"
make
sudo make install

The software requirements before the software to compile is that the geoIP API must be installed.
The files:
- Bind 9.4.3-P3
- MySQL-BIND DLZ
- THE patch:

Config Gen

2009-07-28T04:18:00.002-07:00

completed the prototype of the configuration generator so that it can be used to update Bind.

When a new zone A.K.A domain is added, or a new view is added, the configuration file for Bind must be changed in addition to storing the records in the database. Otherwise, Bind would simply not know of their existence.

What the configuration generator does is to generate the configuration file based on the views that should be available and add the zones in the database to the view. Very view configuration will have the same set of zones, except that the zones in different views would look up different database table to use records configured for different region or country.

The last but important configuration would be the match statement. It provides the interface to the geoip elements. In summary, matching the ip of the client to the country. E.g. all ip from Australia would use the view that matches country_AU.

The PHP script would then be able to output the configuration in the HTML format for display, with all the menus and links etc. Or, it could output in text file format so that the output can be easily downloaded to the server (wget or curl). The MD5 will also be available.

To allow the “match” to be stored in database, i loaded all the possible country from geoip’s database into a table in the database. By using a join table to join the viewname to the countryname, the web ui now knows which view would match which country. The join table is used instead of simply adding a column so that a single view can match multiple countries.

22 July 2009

2009-07-22T03:10:00.002-07:00

can’t wait to put these stuff into real use. :-)

Trying to validate the code as much as i can. The Bind portion seems fine.

managing views in WebUI

2009-07-22T02:38:00.002-07:00

Over the last few days, i added the functionality to manage the views using the Web UI.
And of course, more bug shooting. The record data was not validated before forming the sql query, allowing things like empty rdata. Fixed this one.

WebUI Use Case Diagram

2009-07-19T17:30:00.002-07:00

18 July

2009-07-17T17:31:00.002-07:00

working on the final documentation, not really final, but in process.

Looking at the stuff over again to see if i missed out anything.

For example, discovered i did not provide a way to create new region and CC in the Web UI. So spent a bit of time yesterday to tweak that.

perl and stored procedure

2009-07-16T21:34:00.002-07:00

had upgrade DBD::mysql on the vm using the rpm perl-DBD-mysql-4.005-1.el5.rf.i386.rpm so that geoDNS.pl can call the buildViews stored procedure.

managing named.conf with php

2009-07-14T15:45:00.002-07:00

Was thinking if i should create something that manage named.conf. The interest in this started when i remembered that to add a zone into the DNS server, i will need to declare the zone in named.conf and create the zone file.

The zone file portion is settled by the Web UI. But right now, the named.conf will still need to be changed manually.

Sql & Perl Scripts

2009-07-09T01:31:00.002-07:00

1. procedure-buildviews.sql
This is a stored procedure that i implemented into the database. what it does is to read a table that is populated with the regions/countries for which views will be created for. What is does is similar to the rebuild_views function in geodns.pl.
This can be used as a common method that all Perl and PHP script can call to create the views.
procedure-buildviews.sql

2. releases-geo.sql
This is the latest schema that i worked on. It includes the propose addition of unique constrains on column so that duplicate mirror(same name,rdtype and rdata) cannot be added for the same region. Another change which i did not mentioned earlier (only found this flaw 2 days ago) was the addition of the worldWeight field. What i found out after working on the web UI was that simply having one weight field was insufficient. The weight field would be weighting the mirrors in the same region. However, in views like GLOBAL or where there are no mirror available in that region, mirrors with the world field set to 1 would be used. In this case, the world mirror might come from more then one region. This is why a worldWeight is added so that when the mirror is used in a GLOBAL situation, the worldWeight would take effect. Hope the explanation is clear. :-)

The way i implemented it was for the view to use worldWeight (then renamed to weight) when there is less than 1 non-soa and non-ns record in the region or when auto_geo_global is constructed.
releases-geo.sql

3. zones.sql
This is the table that holds the interesting regions and countries, with the corresponding code (e.g. JP,US,CN)
zones.sql

4. createsp.pl [not in used]
The earlier implementation of using a stored procedure to perform the random weighted selection of mirror used temporary table to overcome the inability for the mysql cursor to use a variable table (or view in our case). However, that consumes memory and impact performances. Furthermore, i had no yet managed to ascertain the effect of creating 1 temporary table for each query on the master-slave mysql configuration. Hence, instead of having one stored procedure to do the job, this perl script would read the zones table (ref: zones.sql) to create a stored procedure for each view. I had corresponding modified the the bind mysql sdb driver to call the correct stored procedure (e.g. selectMirror_auto_geo_US). Hope this reduces the chance for problems to occur.
createsp.pl

5. loaddns.pl
This is the query tool that i created. what it does is to perform the required iteration of queries and log the delay and result of each query to a table in the database. This allows me to check if the distribution of results is according to the configured weight. And also, it gives a gauge of the respond time.
e.g.
./loadDNS.pl --id 2 --query releases.geo.mozilla.com --count 100000 --server 127.0.0.1 -type AAAA
for looking up AAAA records
./loadDNS.pl --id 2 --query releases.geo.mozilla.com --count 100000 --server 127.0.0.1 -type A
for looking up A records
loadDNS.pl

Why the Delay

2009-07-09T01:02:00.002-07:00

there are some stuff i wanted to post up on this blog for a couple of days now. But unfortunately, i had landed myself in hospital. A bit better now, hence starting to get back to speed on the GSoC project.

Test Results

2009-07-01T03:39:00.002-07:00

just completed a new test on a new implementation.
Spent the past few days trying to optimize the solution. I think i had reached the balance solutions.

Test #1:
Algorithm was flawed. Discarded the results
Results distribution: Not Close. Not all available records are used.

Test #2:
Random weight algorithm was implemented in MySQL as a stored procedure. A temporary table containing all the interesting (A records belonging to the same name) would be create. The temporary table works as a workaround for the MySQL’s cursor inability to be decalre using dynamic sql. Bind takes the id for the random record for selection along with the other records (NS and SOA). The results are then fed to Bind and returned as a query result.
Average time taken per query: 0.008112291 seconds
Results distribution: Very close
Analysis File

Test #3:
Improvement from Test #2, the stored procedure will now pass through A and AAAA records. 2 id (A and AAAA) will be returned for selection. Temporary table contains AAAA and A records now.
Average time taken per query: 0.018336635 seconds
Results distribution: Very close
Analysis File

Test #4:
Created a storage procedure for randomly selecting record for each zone in an attempt to remove the need for temporary table (which might be bad for the load balancing of MySQL table). The load on cpu becomes high (to the extend that the whole VM slows down) and the average time taken was high (around 0.8 seconds). Performance was bad even after using the same random number for both A record and AAAA record selection. The test was never completed
Average time taken per query: -
Results distribution: -

Test #5:
Moved the random weight selection to the C code in mysql.c. A new stored procedure to return the total weight for A and AAAA record is created. mysql.c would first call the stored procedure and retrieve the total weights. It would then select all the A record with the same name from the respective view. No temporary tables are used.
Looping through the available records to select the record is done on Bind instead of MySQL. The same process is repeated for AAAA record. The random records for selection along with the other records (NS and SOA) are then selected and used. The select statement at this stage had also been optimized to remove the need for UNION.
Average time taken per query: 0.01798024 seconds
Results distribution: Very Close
Analysis File

The way the test is conducted when testing both A and AAAA records is that both loadDNS.pl is ran at the same time. So that would be 2 threads actively querying the DNS server. The time taken per query would be roughly 1/2 if there is only one thread querying the server.

cpu load issue

2009-06-29T22:20:00.002-07:00

did some finalization of sql schema. and in my attempt to improve the query performance by creating a stored procedure for each view, i had seem to created another big problem.

The cpu load during my test goes as high as 99%. This is bad. Average query time now stands at 0.52 seconds.

I am currently conducting more test and optimize the solution, which otherwise seems to be great.

1st glimpse of web UI

2009-06-26T05:42:00.002-07:00

the web UI for managing the DNS records!!!
1st time the world is seeing it.

Listing the records by region & country

Listing a zone

Adding a new record

Adding a new zone with the default values

Preparing to start testing it this weekend. What you are seeing here is not final, there are still many possibilities for changes to take place.

lighttpd + fastcgi + php

2009-06-21T21:35:00.002-07:00

just got lighttpd and php installed on the vm in preparation of the web console that i will be implementing soon.

trouble was that the vm can’t access the internet. had to download the packages onto my computer before scp it into the vm. so using yum was not possible.

i am now thinking if i should go with one of the php framework (e.g. cakephp) or start of scratch.

weighting AAAA

2009-06-21T18:30:00.002-07:00

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

modifying geodns.pl

2009-06-20T17:54:00.002-07:00

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.

bug shooting #1

2009-06-09T23:31:00.002-07:00

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.

9 June

2009-06-09T03:04:00.002-07:00

testing out if performance would be better if the temporary table created by the procedure is declared to use memory engine. the current implementation seems how have some load issues.