GeoIP Targeting

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

GeoIP Targeting

Post by GeXus »

Hello,

Well, I've been dealing with a dilemma for the past week... I've been trying to do geoip targeting using MaxMind's City database. No matter what I try, it always VERY slow... I've tried doing it in MySQL using one table, from a CSV they provide. I've tried using two tables, from 2 CSV's they provide, I've tried using mod_geoip which is an apache module that uses a binary .dat file as the source and allows apache to handle ip detection using variables such as apache_note('REGION_CODE'), I've tried using a php API they provide to read the binary .dat file... When using apache, it caused apache to jump from about 1% memory utilization to 35% utilization and naturally cause everything to just creep along. I've had the best results so far with it being in two tables in MySQL, but even that was very very sluggish, we're talking about 4-5 seconds to load.

I'm on a dual processor dual core optiplex w/ 12GB memory... but no matter what I do I can't get this to speed up. If it's a matter of purchasing a new server, solely for handling ip detection, I will do that, however I'm still not sure that would fix it.

If anyone has experience dealing with geoip detection, I would LOVE to hear any feedback or suggestions... Ideally this would all run through MySQL (as apache just killed utilization)... and yes, I had everything index'ed appropriately.

Thank you!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: GeoIP Targeting

Post by califdon »

My experience with geoip detection is extremely limited and I don't claim to know much about it, but I'm using an api for geobytes.com on 3 very low traffic sites. It seems to be quite fast and it certainly requires almost no resources at my end. I can't say how it might operate on high activity sites.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: GeoIP Targeting

Post by Kieran Huggins »

Check out http://www.hostip.info/use.html - it's still a web API but you could cache the results for faster access on the second lookup.

Alternatively, there are geoIP databases you can purchase. Don't know where though.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: GeoIP Targeting

Post by GeXus »

Thanks, Yeah I really want to stick with maxmind... I've just heard they are the best (plus I've already paid $350 for the DB ). Theirs uses memory caching also, but doesn't appear to help much.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: GeoIP Targeting

Post by Kieran Huggins »

just how big is this DB of yours?

I can't imagine why it's being so slow... it seems like a simple enough query.

Are the IPs stored in separate columns for each octet?
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: GeoIP Targeting

Post by GeXus »

One table has 3,022,052 rows and is 71MB the other is 172,545 rows and is 4MB... so neither are huge. This is the query I'm using...

Code: Select all

 
SELECT geoip_locations.* FROM geoip_blocks
INNER JOIN (
   SELECT MAX(ip_address_start) AS max_ip
     FROM geoip_blocks
     WHERE ip_address_start <= INET_ATON('216.243.185.70')
   ) s ON s.max_ip = geoip_blocks.ip_address_start
INNER JOIN geoip_locations ON geoip_locations.loc_id = geoip_blocks.loc_id
WHERE geoip_blocks.ip_address_end >= INET_ATON('216.243.185.70')
 
When I put it all in one table, and did a simple query

Code: Select all

 
SELECT * from geoip WHERE ip_address_start <= INET_ATON('216.243.185.70') and WHERE ip_address_end >= INET_ATON('216.243.185.70') 
 
It went even slower...


They IP's are stored as INT's in the geoip_blocks table...
Post Reply