Jia Jun Goes GSoC

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.

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.

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.

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

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.