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!
GeoIP Targeting
Moderator: General Moderators
Re: GeoIP Targeting
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.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Re: GeoIP Targeting
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.
Alternatively, there are geoIP databases you can purchase. Don't know where though.
Re: GeoIP Targeting
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.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Re: GeoIP Targeting
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?
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?
Re: GeoIP Targeting
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...
When I put it all in one table, and did a simple query
It went even slower...
They IP's are stored as INT's in the geoip_blocks table...
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')
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')
They IP's are stored as INT's in the geoip_blocks table...