Add script to update existing GeoIP fields

May I ask when you intend to work on this?

Thanks for the script.

It works, but unfortunately very slow. One IP takes approx. 2 seconds to update! In one of my sites I have approx. 500000 rows in the {visitors} table - thus updating them all would take about 12 entire days!

And I have lots of similar sites in the same (multisite) Drupal installation.

Any faster approach?

And, instead of outputting each and every updated IP address I would suggest to print outputs like "n IP addresses updated in x seconds" every x seconds (where x is a defined interval of maybe 60).

Yeah, you are right, not all 500000 rows need to be updated.

SELECT count( DISTINCT visitors_ip )
FROM visitors

gives approx. 22500, thus it would still take 22500*2/3600 = 12,5 hours :-(

Before digging into performance optimisation, I have enhanced your script to enable incremental updates, along with other enhancements such as printing a more useful output. So far, if the script did not complete in one run, it would re-calculate *all* IP addresses on the next run, thus making it impossible to complete in more than one run.

This version only updates those (unique) IP addresses that have not yet been resolved into GeoIP data, and prints how many IPs have to be processed at the beginning and how many were completed at the end. Also, it integrates into the module and can be executed on the command-line from the "cli" sub directory. Running via the webbrowser is prohibited.

Here is an example output from two subsequent test runs with the setting

$max_runtime = 60;
// Maximum allowed runtime in seconds

:

[root@www cli]# php ./update_geoips.php
Connection to mysql server 5.1.59 established (Localhost via UNIX socket).
Updating GeoIP data in "visitors" table of database "drupal_iseki_dev" (containing 552516 rows).
14795 unique IP addresses have to be processed.
1: Updated IP 137.204.73.180 (2311866804): continent_code = EU
2: Updated IP 193.6.166.226 (3238438626): continent_code = EU
3: Updated IP 94.68.20.136 (1581520008): continent_code = EU
4: Updated IP 81.82.199.183 (1364379575): continent_code = EU
5: Updated IP 66.249.65.123 (1123631483): continent_code = EU
6: Updated IP 195.23.8.5 (3273066501): continent_code = EU
7: Updated IP 148.204.137.33 (2496432417): continent_code = NA
8: Updated IP 84.103.54.7 (1416050183): continent_code = EU
9: Updated IP 194.210.255.207 (3268607951): continent_code = EU
10: Updated IP 195.240.175.41 (3287330601): continent_code = EU
11: Updated IP 201.174.19.86 (3383628630): continent_code = NA
12: Updated IP 142.244.178.175 (2398401199): continent_code = NA
13: Updated IP 110.226.52.216 (1860318424): continent_code = AS
14: Updated IP 62.28.132.233 (1042056425): continent_code = EU
15: Updated IP 124.121.122.254 (2088336126): continent_code = AS
16: Updated IP 87.97.21.127 (1465980287): continent_code = EU
Script stopped because maximum allowed runtime of 60 seconds reached.
72 IP addresses skipped.
16 IP addresses successfully updated in 63 seconds (3.938 s/IP).
There are still 14707 IP addresses that have not yet been checked (Expected required time: 16.09 h).

[root@www cli]# php ./update_geoips.php
Connection to mysql server 5.1.59 established (Localhost via UNIX socket).
Updating GeoIP data in "visitors" table of database "drupal_iseki_dev" (containing 552516 rows).
14779 unique IP addresses have to be processed.
1: Updated IP 142.244.142.55 (2398391863): continent_code = NA
2: Updated IP 89.152.103.168 (1503160232): continent_code = EU
3: Updated IP 24.126.155.191 (410950591): continent_code = NA
4: Updated IP 82.131.156.188 (1384357052): continent_code = EU
5: Updated IP 195.251.116.163 (3288036515): continent_code = EU
6: Updated IP 184.76.138.189 (3092023997): continent_code = NA
7: Updated IP 188.80.165.146 (3159401874): continent_code = EU
8: Updated IP 190.136.104.15 (3196610575): continent_code = SA
9: Updated IP 65.52.104.43 (1093953579): continent_code = NA
10: Updated IP 121.97.98.210 (2036425426): continent_code = AS
11: Updated IP 184.73.9.35 (3091794211): continent_code = NA
12: Updated IP 65.52.104.38 (1093953574): continent_code = NA
13: Updated IP 113.199.157.95 (1908907359): continent_code = AS
14: Updated IP 98.230.202.168 (1659292328): continent_code = NA
15: Updated IP 160.75.45.203 (2689281483): continent_code = EU
Script stopped because maximum allowed runtime of 60 seconds reached.
72 IP addresses skipped.
15 IP addresses successfully updated in 60 seconds (4 s/IP).
There are still 14692 IP addresses that have not yet been checked (Expected required time: 16.32 h).

Here is the result of a run with $max_runtime = 3600 * 3;(3 hours):

Script stopped because maximum allowed runtime of 10800 seconds reached.
91 IP addresses skipped.
2 IP addresses could not be resolved into GeoIP data.
2876 IP addresses successfully updated in 10803 seconds (3.756 s/IP).
There are still 11795 IP addresses that have not yet been checked (Expected required time: 12.31 h).

Hm, may I ask why you have changed the status of this issue to fixed? I don't see a code commit.

OK, no problem for me. Attached is a slightly improved version of the script.

And, adding an index on the visitors_ip column indeed increased performance drastically!

11678 IP addresses successfully updated in 41 seconds (0.004 s/IP).

Why not adding the index by the module itself?

The attached version of the update script fixes a small bug.