395950 - map ISO country codes onto bouncer regions

Status: RESOLVED FIXED

(Webtools :: Bouncer, defect)

Description

[Michael Morgan [:morgamic]]

Need to change the database to allow for a mapping of country codes onto regions.

Comment 1

[Fred Wenzel [:wenzel]]

I will create the default data for our database from geonames (http://www.geonames.org/export/), which lists ISO-3166 alpha2 country codes and maps them to continents.

The data is licensend under a CC 3.0 attribution license, I will give appropriate credit in the SQL file.

Comment 2

[Fred Wenzel [:wenzel]]

Attachment: SQL mapping country codes to Bouncer regions

This is a list of countries mapped to Bouncer regions. Two notes: There are five that are not mapped at all (not internetified regions of the world) and Africa, which is mapped but Mozilla doesn't have any mirrors there at the moment.

But this won't harm, regions without a mirror assigned will just get a random mirror from elsewhere (= the current behavior).

Comment 3

[Fred Wenzel [:wenzel]]

Attachment: Countries to regions, with MaxMind extensions

Adding the two Maxmind extensions "EU" (Europe) and "AP" (Asia-Pacific) which denote regions that can't be specified more precisely.

Comment 4

[Fred Wenzel [:wenzel]]

Attachment: IP to Country Mapping SQL

This maps IP blocks to countries, the data is from MaxMind's free database. This plays in the league of about 100.000 rows, but since we are doing simple ip >= ip_start AND ip <= ip_end queries, I am confident MySQL can handle the additional load easily.

Not mapped regions are now only teeny islands in Oceania, Anonymous proxies (obviously) and Antarctica, each of which do not belong to our biggest user bases, I reckon.

Comment 5

[Fred Wenzel [:wenzel]]

Attachment: patch to use GeoIP in bounce script

This patch implements GeoIP in the main bounce script. If the region could not be detected or if that region doesn't have any mirrors, a random mirror is used (i.e., fallback to the current behavior).

Comment 6

[Fred Wenzel [:wenzel]]

This is in SVN, r7785. Thanks!

Comment 7

[Jeremy Orem [:oremj]]

Looks like getRegionFromIP is a bit too slow.  We discovered using this query:

SELECT
            cty.region_id
        FROM
            mirror_country_to_region AS cty
        INNER JOIN 
                mirror_ip_to_country AS ip 
                ON 
                ip.country_code = cty.country_code AND
                ip_end = ( select MIN(ip_end) from mirror_ip_to_country where ip_end >=  INET_ATON('%s') LIMIT 1 )

increases the performance of the app by about 100 times.

Comment 8

[Jeremy Orem [:oremj]]

The old Attachment 285782 [details] was based on a database with int columns instead of unsigned int, so half of the ip ranges were missing.  Instead we should use this one http://people.mozilla.org/~oremj/mirror_ip_to_country.sql.bz2

Comment 9

[Fred Wenzel [:wenzel]]

Jeremy: What do I need to do here to unblock bug 459919? Is comment 7 still relevant? If so, want me to try to find an improved query?

Comment 10

[Fred Wenzel [:wenzel]]

Attachment: Improved query

Here's an improved query. The evil subselect is gone, so it should perform better. Sadly, the range query will always remain on the slow side, because the index won't help both "> start" and "< end" at the same time (spatial index, anyone?).

We could get further improvement from getting rid of the join altogether and de-normalizing (i.e. adding the region directly to the ip-to-country list, therefore saving the join on the country), but my impression is that that join is not what makes this query slow.

Comment 11

[Jeremy Orem [:oremj]]

Comment on attachment 415642 [details] [diff] [review]
Improved query

I've benchmarked the queries and this one is just slightly slower than the original. I was able to run 65025 queries in 3.5 seconds using the first query and 4.2 seconds with the new query.

Comment 12

[Fred Wenzel [:wenzel]]

We discussed this and agreed that the query is fast enough for now, even during high-load times.