Hello,
I have used this small tips for a year on my server, it works perfectly.
The thing is just to stack the insert commands and execute them every 200 lines. This number can be twicked in the admin panel.
So here is my very small patch that cut the update time by at least 80%.
This uses the drupal DB API, but I have not tested it on something else than mysql.

CommentFileSizeAuthor
#22 2187895-22-D8.patch4.95 KBtr
#21 2187895-20-D8.patch4.43 KBtr
#19 2187895-19-D8.patch4.43 KBtr
#16 2187895-16.patch3 KBtr
#6 lightspeed-updates-2187895-6.patch2.38 KBguile2912
#1 lightspeed-updates-2187895-1.patch2.76 KBguile2912

Comments

guile2912’s picture

guile2912 commented
StatusFileSize
new lightspeed-updates-2187895-1.patch2.76 KB

Status: Needs review » Needs work

The last submitted patch, 1: lightspeed-updates-2187895-1.patch, failed testing.

The last submitted patch, 1: lightspeed-updates-2187895-1.patch, failed testing.

guile2912’s picture

guile2912 commented
Version: 7.x-1.4 » 7.x-1.x-dev
guile2912’s picture

guile2912 commented
Status: Needs work » Needs review

1: lightspeed-updates-2187895-1.patch queued for re-testing.

guile2912’s picture

guile2912 commented
StatusFileSize
new lightspeed-updates-2187895-6.patch2.38 KB

Small mistake I made patching 1.4 and making a diff out of 1.x !

tr’s picture

tr commented

@guile2912: Can you please take a look at #1592198: Optimization of updating geoip information? I tried the same thing years ago and provided a patch which I don't think anyone ever tested. But the bottom line is I saw no speed improvement at all. The patch is essentially the same as yours, except I do an unnecessary array_chunk() in the loop which might be slowing it down.

guile2912’s picture

guile2912 commented

Hello TR,
My patch stacks 200 (configurable) $query->values before calling a single $query->execute();, making it a reall bulk insertion.
For what I understood reading your patch (I might be wrong, I have not tested it for real), you were still doing a $query->execute(); for every line, so not changing much from the current one-insert-at-a-time technic.

The patch from Georgique was doing it aaaallllll at once, wich would make the server go out of memory on big data sets.
I am sure that as this patch let the user select the amount of queries he wants to stack, you can twick giving your sever memory.
And anyway, with as low as 200 queries stacked, the speed improvement is already incredible.

rcodina’s picture

rcodina
Primary language Catalan
Location Barcelona
commented
Priority: Normal » Critical
Status: Needs review » Reviewed & tested by the community

Your patch works like a charm. I move this to critical because without your patch I can't get more than 29000 rows on "ip2country_temp" database table. With your patch I got 107170 which is amazing. Many thanks!

But I still have the same error after that amount rows get imported (not related to this issue):

[Thu Jun 23 10:02:46 2016] [warn] [client 80.24.11.160] mod_fcgid: read data timeout in 300 seconds, referer: http://norelnet.drautadev.com/es/admin/config/people/ip2country
[Thu Jun 23 10:02:46 2016] [error] [client 80.24.11.160] Premature end of script headers: index.php, referer: http://norelnet.drautadev.com/es/admin/config/people/ip2country
rcodina’s picture

rcodina
Primary language Catalan
Location Barcelona
commented
Related issues: +#1950718: Add drush hook for updating IP address data

Combining this patch (#6) with the patch on related issue (drush integration) I managed to get all 165244 rows without any error. So I think this both patches should be commit ASAP. Importing IP data via drush is smarter than doing it via GUI.

rcodina’s picture

rcodina
Primary language Catalan
Location Barcelona
commented

The import also goes well on first module install (using this patch of course!).

guile2912’s picture

guile2912 commented

Thanks for head up rcodina.
I do not really understand with this is still not commited. Neither do I understand why I did not see your comments in 3 months ^^
So if I understand right, the import goes well on module installation, but on update you need to use the drush commmands otherwise it still times out ?

rcodina’s picture

rcodina
Primary language Catalan
Location Barcelona
commented

@rcodina It has been 3 months since I used this and I don't remember. But I guess I used drush for sure. But don't remember anything else. I'm sorry. But I think UI should still be an option. It's just a matter of committing two patches.

Morten Najbjerg’s picture

Morten Najbjerg commented

I had problems updating the database because of timeouts when getting the ARIA database.
This patch was magic. It pulled the database in in under 10 seconds.
Thank You.

guile2912’s picture

guile2912 commented

Good to know this still helps and works :)
Thanks for your feedbacks Morten, I still have some hope this will be commited someday, or integrated in D8.

tr’s picture

tr commented
StatusFileSize
new 2187895-16.patch3 KB

Re-rolled patch against current HEAD, added some comments, and made minor coding standards changes.

  • TR committed 63002e0 on 7.x-1.x authored by guile2912 
    Issue #2187895 by guile2912: Lightspeed DB update
tr’s picture

tr commented
Version: 7.x-1.x-dev » 8.x-1.x-dev
Priority: Critical » Normal

@guile2912: Thank you so much for providing this patch, and thanks for your patience over the last few years when I didn't have a lot of time to work on Drupal.

My tests show the database now loads in about 1/6 the time. On my test server, this means it now takes ~15 seconds instead of ~90 seconds.

Committed. I will now port this same patch to D8.

tr’s picture

tr commented
Status: Reviewed & tested by the community » Needs review
StatusFileSize
new 2187895-19-D8.patch4.43 KB

Here's the D8 patch for the testbot to look at.

Status: Needs review » Needs work

The last submitted patch, 19: 2187895-19-D8.patch, failed testing. View results

tr’s picture

tr commented
Status: Needs work » Needs review
StatusFileSize
new 2187895-20-D8.patch4.43 KB

Doh.

This patch also needs to include a hook_update_N() to update the config from previous versions (I've written that, it's just not in the patch), but I'm going to hold off on that for a few days until I can get the migration template fixed and get rid of the obsolete hook_update_8000() that's in there now.

tr’s picture

tr commented
StatusFileSize
new 2187895-22-D8.patch4.95 KB

Patch with the update hook.

  • TR committed 3c40f52 on 8.x-1.x authored by guile2912 
    Issue #2187895 by guile2912, TR: Lightspeed DB update
tr’s picture

tr commented
Status: Needs review » Fixed

That should fix it for Drupal 8 as well.
Thanks to all who helped out in this issue.

  • TR committed 4c95a0f on 8.x-1.x 
    Issue #2917888 by TR: Add batch_size variable to migration templates -...
guile2912’s picture

guile2912 commented

Wohooo this is great :D, better late than never ^^, thanks for your time maintaining this.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.