Geolocationing system on the server page would be cool. So let’s do it!
There are several ways to get location information on that page, which way to go?
- The easy way: do it by PHP
- DB style: fetch info from database
- Advanced style: PHP module
- Anything, as far as it works
The easy way is to get Maxmind’s GeoIP.dat (used by CountryFilter plugin to block connections from USA, Canada and GB) and use it through “pure” PHP API. On the server page all that is needed is something like $countrycode = geoip_get_country_code($ip) and that’s it. The code must be searched from 700 kB geoip database, which obviously takes some time. Doing the search for nearly 3000 servers on one page slows down generation of the page to about 10 seconds.
DB style thing is to get Maxmind’s IP-to-Country database in CSV format (Comma Separated Value) and load it to database. Then it is possible to query country information while querying all other server information, such as server name and number of players. This should be fast and neat, but it turns out that it’s only neat. Rendering the server page takes over 10 seconds, even though MySQL says it completes the query in 5. Either way, it’s too slow.
Advanced style gets us to real world implementations. Maxmind has C library and PHP module implementation of the IP-to-Country system. Obviously the C implementation of the same system as in “the easy way” is far faster than PHP version. This proves to be true. Compiling C library and managing to install PHP module with PEAR was somewhat tricky to get along, especially the PEAR part, but eventually PHP found GeoIP module and GeoIP module found GeoIP database. For some reason PHP module’s function name was different than on the “pure” PHP API. Luckily I noticed that while messing with the installation. The end result is fast and quite neat, only one line of code to get a country code for certain IP. This method renders the server list page in 2.5 seconds. Well, not something that I’d expect.
Quite poor result with PHP modules was surprise. Searching 3000 servers’ location shouldn’t be taking 2.5 seconds. Well, it wasn’t. Rendering the page without country information takes 1,5-2 seconds, so retrieving the location information takes some 0,5-1 seconds. Moreover for some reason PHP is slowing down MySQL queries. Querying 3000 servers from some 30 000 servers takes only split second for MySQL to complete. It shouldn’t take seconds to render by PHP.
From the very start it’s been clear that ultimately the fastest way would be to retrieve location information only once for every server and store them somewhere, most preferably to the DB with all other server information. This, however, wouldn’t be very neatly integrated to the current server query system. It would take much more effort to change the current server querier - without breaking it - to use Maxmind’s PERL module (they have APIs for every use) than to do things separately.
The conclusion is that the current server list page is already rendering itself very slow. Somekind of caching could be used to speed up things. Then it wouldn’t matter if rendering the page every now and then would take up to 5 seconds.
Update (15 minutes later 
)
Getting pages cached in PHP seems to be very straightforward. The same method is already being used on server stats images, but they are images. Now we have some HTML cached too. Yeah!
