Re: Maximum line length or statement length for mysqldump
On 2011/10/21 10:26 AM, Johan De Meersman wrote: - Original Message - From: Alex Schaftal...@quicksoftware.co.za Got my app reading in a dump created with extended-inserts off, and lumping all of the insert statements together. Works like a charm Just for laughs, would you mind posting the on-disk size of your database, and the restore time with both extended and single inserts? ibdata1 currently sitting at 6 gigs. Without ext inserts about a minute and a half and with a couple of seconds. I'm well aware of the speed differences. That's why I'm now reading in the non extended and joining the values together into big sql statements. This now takes about 10 seconds, but I'm still optimizing that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Maximum line length or statement length for mysqldump
On 2011/10/20 03:43 PM, Johan De Meersman wrote: - Original Message - From: Alex Schaftal...@quicksoftware.co.za I realize that, I'm just trying to stop the phone calls saying I started a restore, and my pc just froze I might just read all the single insert lines, and get a whole lot of values clauses together before passing it on to get around the performance issue while having some idea of progress. Wouldn't it be better to educate your users, then? :-) Much less trouble for you. Either that, or just do the windows thing: print a progress bar that goes to 95% in ten seconds, then run the entire restore and then progress the remaining 5% :-p You could probably write a sed script that intersperses the INSERT INTO lines with some form of progress printing. I remain convinced that users simply need to learn patience, though. Got my app reading in a dump created with extended-inserts off, and lumping all of the insert statements together. Works like a charm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Maximum line length or statement length for mysqldump
- Original Message - From: Alex Schaft al...@quicksoftware.co.za Got my app reading in a dump created with extended-inserts off, and lumping all of the insert statements together. Works like a charm Just for laughs, would you mind posting the on-disk size of your database, and the restore time with both extended and single inserts? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Maximum line length or statement length for mysqldump
Hi, I'm monitoring a mysqldump via stdout, catching the create table commands prior to flushing them to my own text file. Then on the restore side, I'm trying to feed these to mysql via the c api so I can monitor progress (no of lines in the dump file vs no of lines sent to mysql), but the lines are as much as 16k long in the text file times about 110 of those for one huge insert statement. What can I pass to mysqldump to get more sane statement lengths? Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Maximum line length or statement length for mysqldump
On 2011/10/20 10:53 AM, Alex Schaft wrote: What can I pass to mysqldump to get more sane statement lengths? +1 for extended-inserts... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Maximum line length or statement length for mysqldump
- Original Message - From: Alex Schaft al...@quicksoftware.co.za I'm monitoring a mysqldump via stdout, catching the create table commands prior to flushing them to my own text file. Then on the restore side, I'm trying to feed these to mysql via the c api so I can monitor progress (no of lines in the dump file vs no of lines sent to mysql), but the lines are as much as 16k long in the text file times about 110 of those for one huge insert statement. What can I pass to mysqldump to get more sane statement lengths? That's a pretty sane statement length, actually. It's a lot more efficient to lock the table once, insert a block of records, update the indices once and unlock the table; as opposed to doing that for every separate record. If you really want to go to single-record inserts, you can pass --skip-extended-insert. I'm not sure you can control the maximum length of a statement beyond one or lots. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Maximum line length or statement length for mysqldump
On 2011/10/20 11:54 AM, Johan De Meersman wrote: - Original Message - From: Alex Schaftal...@quicksoftware.co.za I'm monitoring a mysqldump via stdout, catching the create table commands prior to flushing them to my own text file. Then on the restore side, I'm trying to feed these to mysql via the c api so I can monitor progress (no of lines in the dump file vs no of lines sent to mysql), but the lines are as much as 16k long in the text file times about 110 of those for one huge insert statement. What can I pass to mysqldump to get more sane statement lengths? That's a pretty sane statement length, actually. It's a lot more efficient to lock the table once, insert a block of records, update the indices once and unlock the table; as opposed to doing that for every separate record. I realize that, I'm just trying to stop the phone calls saying I started a restore, and my pc just froze I might just read all the single insert lines, and get a whole lot of values clauses together before passing it on to get around the performance issue while having some idea of progress. Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Maximum line length or statement length for mysqldump
- Original Message - From: Alex Schaft al...@quicksoftware.co.za I realize that, I'm just trying to stop the phone calls saying I started a restore, and my pc just froze I might just read all the single insert lines, and get a whole lot of values clauses together before passing it on to get around the performance issue while having some idea of progress. Wouldn't it be better to educate your users, then? :-) Much less trouble for you. Either that, or just do the windows thing: print a progress bar that goes to 95% in ten seconds, then run the entire restore and then progress the remaining 5% :-p You could probably write a sed script that intersperses the INSERT INTO lines with some form of progress printing. I remain convinced that users simply need to learn patience, though. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Maximum line length or statement length for mysqldump
I remain convinced that users simply need to learn patience, though. HAHAHAHAHAHAHAHAHAHAHAHAHAHAHA!!! Good one! Sent from my iPad On Oct 20, 2011, at 8:44 AM, Johan De Meersman vegiv...@tuxera.be wrote: I remain convinced that users simply need to learn patience, though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org