Hi, I love this module and use it everyday. Thanks so much for it.
I had some trouble with it when I was trying to restore my site's databases:
- module import would time out at 10 minutes.
- mysql command line took 12 minutes.
I rewrote the function that creates the INSERT INTO statements to use INSERT's multiple values syntax (http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html)
See attached patch.
The comments describe why the choices were made, but I had to balance to speed up DB performance, and avoid "mysql has gone away" errors. Conservatively, it puts up to 30 items in an INSERT statement that tries not to be much larger than 2000 bytes long.
This shrunk the number of SQL lines from 77k to 5k. When importing the new export, the database sped up:
- module import: fail -> 2 minutes
- mysql command import: 12 mins -> 2 min
Performance for parsing the output SQL would be the same.
I hope this helps!
Mike
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | backup_migrate.module.diff | 42.4 KB | MichaelCole |
| multiple_values_per_insert.patch | 2.99 KB | MichaelCole |
Comments
Comment #1
ronan commentedThis is a great idea. 30/2k seem like fairly safe defaults for the limits but I'd probably want to make the number adjustable so that end users can tweak it if needed. (Maybe just wrap the numbers in a variable_get, and allow overrides in the settings file) That's a pretty impressive speedup though and certainly worth it.
A couple of technical notes the patch:
* It needs to be ported to the 2.x branch as 1.x is feature stable. That should be trivial since the db code is relatively untouched.
* It's not quite Drupal coding standards with regards to comment placement and curly braces (or lack thereof.)
* Why are you limiting the line reads to 1meg? I can see where this would help prevent memory issues and stay short of the packet limit for MySQL, but if the line is being cut of then we need to assume that it is not a valid SQL statement and not send it to the MySQL server.
Other than that it looks like a great way to handle this. Thanks for the patch.
Ronan
Comment #2
MichaelCole commentedHi Ronan, thanks for the review. I put in the 1 meg parameter because php.net's gzgets documentation didn't specify a default. I think your right to leave it alone.
Also, I reorganized the comments to something less controversial. It didn't seem to violate the coding standards here:
- If each line of a list needs a separate comment, the comments may be given on the same line and may be formatted to a uniform indent for readability.
See the new patch for 5.x. I don't have a copy of 6.x to test with, but I think you're right that it's just copying that one function in the patch.
Best wishes,
Mike
Comment #3
MichaelCole commentedComment #4
ronan commentedI ported this patch to the 2.x branch and committed. Thanks for the great idea and for the code.