SEO

Hey all. I have a bit of a problem. We are upgrading from ver 4.1.x to 5.0.45. That's not the problem . The problem is that MySQL recommends dumping and importing your data when doing this upgrade to make sure that things go as smoothly as possible. We have quite a bit of data spread over multiple servers. We have slaves off each master server and the plan is upgrade the slaves and then make them the masters.

The problem is that I am realizing that this dump/import is going to take hours and in some cases days. I am looking for any way to speed this up. Any suggestions?

Thanks,

Keith

--
B. Keith Murphy
Database Administrator
iContact
2635 Meridian Parkway, 2nd Floor
Durham, North Carolina 27713
(o) 919-433-0786
(c) 850-637-3877

B. Keith Murphy wrote:
> Hey all. I have a bit of a problem. We are upgrading from ver 4.1.x to
> 5.0.45. That's not the problem . The problem is that MySQL recommends
> dumping and importing your data when doing this upgrade to make sure that
> things go as smoothly as possible. We have quite a bit of data spread over
> multiple servers. We have slaves off each master server and the plan is
> upgrade the slaves and then make them the masters.
>
> The problem is that I am realizing that this dump/import is going to take
> hours and in some cases days. I am looking for any way to speed this up. Any
> suggestions?

The fastest way I've found is to do SELECT INTO OUTFILE on the master, which
selects into a sort of tab-delimited format by default -- don't specify any
options like field terminators or whatnot. This file can then be imported
directly into LOAD DATA INFILE, again without options.

I think this is faster than loading files full of SQL statements, which have to
be parsed and query-planned etc.

I thought mysqldump had an option to dump this way, but I can't see it now.

I'd use mysqldump to just dump the structures, routines etc without data, and
then load the data separately.

Beware: replication from 4.1.x to 5.0.40 will fail. The MySQL changelog didn't
list it as an incompatible change, but there is some bug that got fixed around
5.0.38 (sorry, I forget what it was and can't find it now). When the slave
checks the master version, it throws an error and stops saying "I refuse because
of this bug."


Baron

On Tue, July 17, 2007 04:29, Baron Schwartz wrote:
> B. Keith Murphy wrote:
>> Hey all. I have a bit of a problem. We are upgrading from ver 4.1.x to
>> 5.0.45. That's not the problem . The problem is that MySQL recommends
>> dumping and importing your data when doing this upgrade to make sure
>> that
>> things go as smoothly as possible. We have quite a bit of data spread
>> over
>> multiple servers. We have slaves off each master server and the plan is
>> upgrade the slaves and then make them the masters.
>>
>> The problem is that I am realizing that this dump/import is going to
>> take
>> hours and in some cases days. I am looking for any way to speed this up.
>> Any
>> suggestions?
>
> The fastest way I've found is to do SELECT INTO OUTFILE on the master,
> which
> selects into a sort of tab-delimited format by default -- don't specify
> any
> options like field terminators or whatnot. This file can then be imported
> directly into LOAD DATA INFILE, again without options.
>
> I think this is faster than loading files full of SQL statements, which
> have to
> be parsed and query-planned etc.
>
> I thought mysqldump had an option to dump this way, but I can't see it
> now.

I think you are looking for the --single-transaction option

>
> I'd use mysqldump to just dump the structures, routines etc without data,
> and
> then load the data separately.
>
> Beware: replication from 4.1.x to 5.0.40 will fail. The MySQL changelog
> didn't
> list it as an incompatible change, but there is some bug that got fixed
> around
> 5.0.38 (sorry, I forget what it was and can't find it now). When the
> slave
> checks the master version, it throws an error and stops saying "I refuse
> because
> of this bug."
>
>
> Baron

--
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

In article <469C2989.2050902@xaprb.com> baron@xaprb.com
(BaronSchwartz) wrote:

> B. Keith Murphy wrote:

>> The problem is that I am realizing that this dump/import is going
>> to take hours and in some cases days. I am looking for any way to
>> speed this up. Any suggestions?

> The fastest way I've found is to do SELECT INTO OUTFILE on the
> master, which selects into a sort of tab-delimited format by default
> -- don't specify any options like field terminators or whatnot. This
¾*file can then be imported directly into LOAD DATA INFILE, again
> without options.
*

You may also see a speed increase by adding the indexes after the data
is inserted.

***
Andrew Hutchings - LinuxJedi - http://www.linuxjedi.co.uk/
Windows is the path to the darkside...Windows leads to Blue Screen. Blue Screen leads to downtime. Downtime leads to suffering...I sense much Windows in you...

Mogens Melander wrote:
> On Tue, July 17, 2007 04:29, Baron Schwartz wrote:
>> B. Keith Murphy wrote:
>>> Hey all. I have a bit of a problem. We are upgrading from ver 4.1.x to
>>> 5.0.45. That's not the problem . The problem is that MySQL recommends
>>> dumping and importing your data when doing this upgrade to make sure
>>> that
>>> things go as smoothly as possible. We have quite a bit of data spread
>>> over
>>> multiple servers. We have slaves off each master server and the plan is
>>> upgrade the slaves and then make them the masters.
>>>
>>> The problem is that I am realizing that this dump/import is going to
>>> take
>>> hours and in some cases days. I am looking for any way to speed this up.
>>> Any
>>> suggestions?
>> The fastest way I've found is to do SELECT INTO OUTFILE on the master,
>> which
>> selects into a sort of tab-delimited format by default -- don't specify
>> any
>> options like field terminators or whatnot. This file can then be imported
>> directly into LOAD DATA INFILE, again without options.
>>
>> I think this is faster than loading files full of SQL statements, which
>> have to
>> be parsed and query-planned etc.
>>
>> I thought mysqldump had an option to dump this way, but I can't see it
>> now.
>
> I think you are looking for the --single-transaction option

I found the option I meant:

-T, --tab=name Creates tab separated textfile for each table to given
path. (creates .sql and .txt files). NOTE: This only
works if mysqldump is run on the same machine as the
mysqld daemon.

Baron

On Tue, July 17, 2007 13:31, Baron Schwartz wrote:
> Mogens Melander wrote:
>> On Tue, July 17, 2007 04:29, Baron Schwartz wrote:
>>> B. Keith Murphy wrote:
>>>>
>>>> The problem is that I am realizing that this dump/import is going to
>>>> take
>>>> hours and in some cases days. I am looking for any way to speed this up.
>>>> Any suggestions?
>>>
>>> The fastest way I've found is to do SELECT INTO OUTFILE on the master,
>>> which
>>> selects into a sort of tab-delimited format by default -- don't specify
>>> any
>>> options like field terminators or whatnot. This file can then be imported
>>> directly into LOAD DATA INFILE, again without options.
>>>
>>> I think this is faster than loading files full of SQL statements, which
>>> have to be parsed and query-planned etc.

That method has proven "very" quick in the past.

>>>
>>> I thought mysqldump had an option to dump this way, but I can't see it
>>> now.
>>
>> I think you are looking for the --single-transaction option
>
> I found the option I meant:
>
> -T, --tab=name Creates tab separated textfile for each table to given
> path. (creates .sql and .txt files). NOTE: This only
> works if mysqldump is run on the same machine as the
> mysqld daemon.
>

Yup, that was what i was trying to write 8^) using this one with the other.


--
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.