From 5488a9f41d7c594ffb45817fcfb4237dd1926cdc Mon Sep 17 00:00:00 2001 From: Michael Tremer Date: Fri, 18 Apr 2014 15:36:35 +0200 Subject: [PATCH] Rewrite geo load balancing. --- templates/geoip/index.html | 17 ++++++++ webapp/backend/geoip.py | 20 ++------- webapp/backend/mirrors.py | 86 +++++++++++++++++++++---------------- webapp/handlers.py | 4 +- webapp/handlers_download.py | 31 ++++++------- 5 files changed, 86 insertions(+), 72 deletions(-) diff --git a/templates/geoip/index.html b/templates/geoip/index.html index bc1aab1c..e0609a48 100644 --- a/templates/geoip/index.html +++ b/templates/geoip/index.html @@ -35,6 +35,23 @@ {% end %} {% end %} + + {% if mirrors %} +

{{ _("Preferred Mirrors") }}

+ + + {% end %}
diff --git a/webapp/backend/geoip.py b/webapp/backend/geoip.py index 0fe4310f..9a3650f0 100644 --- a/webapp/backend/geoip.py +++ b/webapp/backend/geoip.py @@ -21,26 +21,14 @@ class GeoIP(Object): return ret.country def get_location(self, addr): - family = self.guess_address_family(addr) - - if family == 6: - query = "SELECT *, NULL AS city, NULL AS postal_code FROM geoip_ipv6 WHERE %s \ - BETWEEN start_ip AND end_ip LIMIT 1" - elif family == 4: - query = "SELECT * FROM geoip_ipv4 WHERE inet_to_bigint(%s) \ - BETWEEN start_ip AND end_ip LIMIT 1" + query = "SELECT * FROM geoip \ + WHERE %s BETWEEN start_ip AND end_ip LIMIT 1" return self.db.get(query, addr) def get_asn(self, addr): - family = self.guess_address_family(addr) - - if family == 6: - query = "SELECT asn FROM geoip_asnv6 WHERE %s \ - BETWEEN start_ip AND end_ip LIMIT 1" - elif family == 4: - query = "SELECT asn FROM geoip_asnv4 WHERE inet_to_bigint(%s) \ - BETWEEN start_ip AND end_ip LIMIT 1" + query = "SELECT asn FROM geoip_asn \ + WHERE %s BETWEEN start_ip AND end_ip LIMIT 1" ret = self.db.get(query, addr) diff --git a/webapp/backend/mirrors.py b/webapp/backend/mirrors.py index 71d19609..b8c8188a 100644 --- a/webapp/backend/mirrors.py +++ b/webapp/backend/mirrors.py @@ -109,9 +109,10 @@ class Mirrors(Object): return MirrorSet(self.backend, mirrors) def get_by_hostname(self, hostname): - mirror = self.db.get("SELECT id FROM mirrors WHERE hostname=%s", hostname) + ret = self.db.get("SELECT * FROM mirrors WHERE hostname = %s", hostname) - return Mirror(self.backend, mirror.id) + if ret: + return Mirror(self.backend, ret.id, ret) def get_with_file(self, filename, country=None): # XXX quick and dirty solution - needs a performance boost @@ -132,35 +133,36 @@ class Mirrors(Object): return mirrors - def get_for_country(self, country): - # XXX need option for random order - mirrors = self.db.query("SELECT id FROM mirrors WHERE prefer_for_countries LIKE %s", country) - - for mirror in mirrors: - yield self.get(mirror.id) - - def get_for_location(self, location): + def get_for_location(self, location, max_distance=4000, filename=None): if not location: - return None - - distance = 2500 + return [] + + if filename: + res = self.db.query("\ + WITH client AS (SELECT point(%s, %s) AS location) \ + SELECT * FROM mirrors WHERE mirrors.state = %s \ + AND mirrors.id IN ( \ + SELECT mirror FROM mirror_files WHERE filename = %s \ + ) AND mirrors.id IN ( \ + SELECT id FROM mirrors_locations, client \ + WHERE geodistance(mirrors_locations.location, client.location) <= %s \ + )", + location.latitude, location.longitude, "UP", filename, max_distance) + else: + res = self.db.query("\ + WITH client AS (SELECT point(%s, %s) AS location) \ + SELECT * FROM mirrors WHERE mirrors.state = %s AND mirrors.id IN ( \ + SELECT id FROM mirrors_locations, client \ + WHERE geodistance(mirrors_locations.location, client.location) <= %s \ + )", + location.latitude, location.longitude, "UP", max_distance) mirrors = [] - all_mirrors = self.get_all() - - while all_mirrors and len(mirrors) <= 3 and distance <= 8000: - for mirror in all_mirrors: - mirror_distance = mirror.distance_to(location) - if mirror_distance is None: - continue - - if mirror_distance<= distance: - mirrors.append(mirror) - all_mirrors.remove(mirror) - - distance *= 1.2 + for row in res: + mirror = Mirror(self.backend, row.id, row) + mirrors.append(mirror) - return mirrors + return sorted(mirrors) def get_all_files(self): files = [] @@ -175,6 +177,27 @@ class Mirrors(Object): return files + def get_random(self, filename=None): + if filename: + ret = self.db.get("SELECT * FROM mirrors WHERE state = %s \ + AND mirrors.id IN (SELECT mirror FROM mirror_files \ + WHERE filename = %s) ORDER BY RANDOM() LIMIT 1", "UP", filename) + else: + ret = self.db.get("SELECT * FROM mirrors WHERE state = %s \ + ORDER BY RANDOM() LIMIT 1", "UP") + + if ret: + return Mirror(self.backend, ret.id, ret) + + def file_exists(self, filename): + ret = self.db.get("SELECT 1 FROM mirror_files \ + WHERE filename = %s LIMIT 1", filename) + + if ret: + return True + + return False + class MirrorSet(Object): def __init__(self, backend, mirrors): @@ -229,15 +252,6 @@ class MirrorSet(Object): return random.choice(mirrors) - def get_for_country(self, country): - mirrors = [] - - for mirror in self._mirrors: - if country in mirror.prefer_for_countries: - mirrors.append(mirror) - - return MirrorSet(self.backend, mirrors) - def get_for_location(self, location): distance = 2500 mirrors = [] diff --git a/webapp/handlers.py b/webapp/handlers.py index 9537ace7..ea46985b 100644 --- a/webapp/handlers.py +++ b/webapp/handlers.py @@ -117,4 +117,6 @@ class GeoIPHandler(BaseHandler): if peer: peer["country_name"] = self.geoip.get_country_name(peer.country) - self.render("geoip/index.html", addr=addr, peer=peer) + mirrors = self.mirrors.get_for_location(peer) + + self.render("geoip/index.html", addr=addr, peer=peer, mirrors=mirrors) diff --git a/webapp/handlers_download.py b/webapp/handlers_download.py index cfe46f8b..a7cb9c53 100644 --- a/webapp/handlers_download.py +++ b/webapp/handlers_download.py @@ -83,25 +83,21 @@ class DownloadFileHandler(BaseHandler): self.redirect_to_mirror(filename, log_download=True) def find_mirror(self, filename): - # Get all mirrors... - mirrors = self.mirrors.get_all() - mirrors = mirrors.get_with_file(filename) - mirrors = mirrors.get_with_state("UP") - - if not mirrors: + exists = self.mirrors.file_exists(filename) + if not exists: raise tornado.web.HTTPError(404, "File not found: %s" % filename) # Find mirrors located near to the user. - # If we have not found any, we use all. + # If we have not found any, we use a random one. remote_location = self.get_remote_location() if remote_location: - mirrors_nearby = mirrors.get_for_location(remote_location) + mirrors = self.mirrors.get_for_location(remote_location, filename=filename) - if mirrors_nearby: - mirrors = mirrors_nearby + if mirrors: + return random.choice(mirrors) - return mirrors.get_random() + return self.mirrors.get_random(filename=filename) def redirect_to_mirror(self, filename, log_download=False): # Find a random mirror. @@ -128,17 +124,14 @@ class DownloadFileHandler(BaseHandler): class DownloadCompatHandler(BaseHandler): def get(self, path, url): - _filename = None - for filename in self.mirrors.get_all_files(): - if filename.endswith("/%s" % url): - _filename = filename - break + if not filename.endswith("/%s" % url): + continue - if not _filename: - raise tornado.web.HTTPError(404) + self.redirect("/%s" % filename) + return - self.redirect("/%s" % _filename) + raise tornado.web.HTTPError(404) class DownloadSplashHandler(BaseHandler): -- 2.47.3