I have a very busy 14,000,000 record table. I made a new version of
the table, that's more efficient, and now I just need to copy over
the data. Just about anything I try swamps the machine and locks up
MySQL with "too many connections" because it's so damn busy. Can
anyone suggest the most efficient way to copy over all the data to
the new table with low priority so I don't kill the machine? It's OK
if it takes up to around 10 minutes. Thanks...

Brian, I'm not sure there's a quick way to copy 14 million records, no
matter how you slice it. Disabling the indexes on the destination
table might help - but then you've got to devote some time to when you
re-enable them.

You might try this workaround, where you're copying into a duplicate
of your new table structure.

- CREATE TABLE newtable2 LIKE newtable
- INSERT INTO newtable2 SELECT * from oldtable /* or however you're copying */
- RENAME TABLE newtable TO newtable_bkup, newtable2 TO newtable

Dan


On 9/27/06, Brian Dunning <brian@briandunning.com> wrote:
> I have a very busy 14,000,000 record table. I made a new version of
> the table, that's more efficient, and now I just need to copy over
> the data. Just about anything I try swamps the machine and locks up
> MySQL with "too many connections" because it's so damn busy. Can
> anyone suggest the most efficient way to copy over all the data to
> the new table with low priority so I don't kill the machine? It's OK
> if it takes up to around 10 minutes. Thanks...
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

I'm guessing what's happening is that your "import" is locking the table, putting everything else on hold. People keep connecting,
getting put on hold until you run out of connections. It's not that you machine is "so busy", it just can't do two things at once.
One of the limitations of MyISAM tables, Innodb works differently and wouldn't have this problem.

The simplest solution would be to import a little bit at a time by selecting ranges from the old table (i.e. based on date) and
inserting them into the new table. This can be done in just about anything, shell script, php, perl, etc.

I do a nightly import of currently 5 million records, "importing" records contained in 20 minute intervals. Takes about 20 minutes
for the php script to finish, depending on how busy the machine is and how big the table I'm importing into is.

----- Original Message -----
From: "Brian Dunning" <brian@briandunning.com>
To: <mysql@lists.mysql.com>
Sent: Wednesday, September 27, 2006 2:54 PM
Subject: Low priority copy?


>I have a very busy 14,000,000 record table. I made a new version of the table, that's more efficient, and now I just need to copy
>over the data. Just about anything I try swamps the machine and locks up MySQL with "too many connections" because it's so damn
>busy. Can anyone suggest the most efficient way to copy over all the data to the new table with low priority so I don't kill the
>machine? It's OK if it takes up to around 10 minutes. Thanks...
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>

The table switch-a-roo scheme would accomplish this - it lets you copy
the data into the duplicate table, and can run as long as needed since
it won't be tying up a table that your users are trying to access.
Then once the move is completed, the table rename operation should
complete very quickly, transparently to your users.

I agree with Brent, your problem with mysql locking up is not because
the copy operation is so intense, but because of the fact that the
table is locked during the copy. Since none of your apps or users
would know about or try to access the duplicate table, you wouldn't
have a locking problem.

Dan



On 9/27/06, Brian Dunning <brian@briandunning.com> wrote:
> This is the kind of thing I've been trying, but anything like this
> locks up the machine, all the users get errors, and I have to restart
> mysql. This is why I'm looking for something like a "LOW PRIORITY"
> solution, hoping that it won't try to use resources until they're
> available.
>
>
> On Sep 27, 2006, at 12:37 PM, Dan Buettner wrote:
>
> > Brian, I'm not sure there's a quick way to copy 14 million records, no
> > matter how you slice it. Disabling the indexes on the destination
> > table might help - but then you've got to devote some time to when you
> > re-enable them.
> >
> > You might try this workaround, where you're copying into a duplicate
> > of your new table structure.
> >
> > - CREATE TABLE newtable2 LIKE newtable
> > - INSERT INTO newtable2 SELECT * from oldtable /* or however you're
> > copying */
> > - RENAME TABLE newtable TO newtable_bkup, newtable2 TO newtable
> >
> > Dan
> >
> >
> > On 9/27/06, Brian Dunning <brian@briandunning.com> wrote:
> >> I have a very busy 14,000,000 record table. I made a new version of
> >> the table, that's more efficient, and now I just need to copy over
> >> the data. Just about anything I try swamps the machine and locks up
> >> MySQL with "too many connections" because it's so damn busy. Can
> >> anyone suggest the most efficient way to copy over all the data to
> >> the new table with low priority so I don't kill the machine? It's OK
> >> if it takes up to around 10 minutes. Thanks...
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql?
> >> unsub=drbuettner@gmail.com
> >>
> >>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

This is the kind of thing I've been trying, but anything like this
locks up the machine, all the users get errors, and I have to restart
mysql. This is why I'm looking for something like a "LOW PRIORITY"
solution, hoping that it won't try to use resources until they're
available.


On Sep 27, 2006, at 12:37 PM, Dan Buettner wrote:

> Brian, I'm not sure there's a quick way to copy 14 million records, no
> matter how you slice it. Disabling the indexes on the destination
> table might help - but then you've got to devote some time to when you
> re-enable them.
>
> You might try this workaround, where you're copying into a duplicate
> of your new table structure.
>
> - CREATE TABLE newtable2 LIKE newtable
> - INSERT INTO newtable2 SELECT * from oldtable /* or however you're
> copying */
> - RENAME TABLE newtable TO newtable_bkup, newtable2 TO newtable
>
> Dan
>
>
> On 9/27/06, Brian Dunning <brian@briandunning.com> wrote:
>> I have a very busy 14,000,000 record table. I made a new version of
>> the table, that's more efficient, and now I just need to copy over
>> the data. Just about anything I try swamps the machine and locks up
>> MySQL with "too many connections" because it's so damn busy. Can
>> anyone suggest the most efficient way to copy over all the data to
>> the new table with low priority so I don't kill the machine? It's OK
>> if it takes up to around 10 minutes. Thanks...
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?
>> unsub=drbuettner@gmail.com
>>
>>

This is a situation where u should use mysqlhotcopy. That gives u a
snapshot of the current table and lets u work on it "offline" while the
real table is available.

hotcopy table A to B
blank table A to allow inserts
work on table B
merge A into B
delete A
rename B to A



--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

Thanks Chris, this sounds great but when I read about mysqlhotcopy I
didn't see a way to make it create a live table that's open within
the same database, it seems to want only to create a separate backup
file in some directory.


On Sep 27, 2006, at 6:10 PM, Wagner, Chris (GEAE, CBTS) wrote:

> This is a situation where u should use mysqlhotcopy. That gives u a
> snapshot of the current table and lets u work on it "offline" while
> the
> real table is available.
>
> hotcopy table A to B
> blank table A to allow inserts
> work on table B
> merge A into B
> delete A
> rename B to A
>
>
>
> --
> Chris Wagner
> CBTS
> GE Aircraft Engines
> Chris.Wagner@ae.ge.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=brian@briandunning.com
>

The problem when I try this is that the database gets locked up:
INSERT INTO newtable2 SELECT * from oldtable


On Sep 27, 2006, at 12:37 PM, Dan Buettner wrote:

> Brian, I'm not sure there's a quick way to copy 14 million records, no
> matter how you slice it. Disabling the indexes on the destination
> table might help - but then you've got to devote some time to when you
> re-enable them.
>
> You might try this workaround, where you're copying into a duplicate
> of your new table structure.
>
> - CREATE TABLE newtable2 LIKE newtable
> - INSERT INTO newtable2 SELECT * from oldtable /* or however you're
> copying */
> - RENAME TABLE newtable TO newtable_bkup, newtable2 TO newtable
>
> Dan
>
>
> On 9/27/06, Brian Dunning <brian@briandunning.com> wrote:
>> I have a very busy 14,000,000 record table. I made a new version of
>> the table, that's more efficient, and now I just need to copy over
>> the data. Just about anything I try swamps the machine and locks up
>> MySQL with "too many connections" because it's so damn busy. Can
>> anyone suggest the most efficient way to copy over all the data to
>> the new table with low priority so I don't kill the machine? It's OK
>> if it takes up to around 10 minutes. Thanks...
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?
>> unsub=drbuettner@gmail.com
>>
>>

At 01:54 PM 9/27/2006, you wrote:
>I have a very busy 14,000,000 record table. I made a new version of
>the table, that's more efficient, and now I just need to copy over
>the data. Just about anything I try swamps the machine and locks up
>MySQL with "too many connections" because it's so damn busy. Can
>anyone suggest the most efficient way to copy over all the data to
>the new table with low priority so I don't kill the machine? It's OK
>if it takes up to around 10 minutes. Thanks...

If your table is constantly being queried, even a delayed insert may not
have a chance to complete. See
http://dev.mysql.com/doc/refman/5.0/...t-delayed.html (I believe all
inserts to the table must be a delayed insert-you can't mix them with
non-delayed inserts. Also you run the risk of losing the updates if the
server crashes because the delayed updates are in memory.)

Rather than looking for MyISAM workarounds, why can't you bite the bullet
and switch to InnoDb? Use transactions to move the rows into the table and
tweak your configuration settings to improve InnoDb caching and you'll
likely have a more robust table than MyISAM. Granted it takes more time to
tweak, and updates may be a tad slower, but at least it will work without
worrying about a locking problem. I'm going to have to do this myself and
although I'm not going to relish the idea, it's the only legitimate
solution for a locking problem.

Mike

Mysqlhotcopy can copy the files anywhere. Just have it copy under ur
MySQL data directory and the new copy will be visible instantly. e.g.
/var/lib/mysql/data/offline_copy U will instantly see a new database
named "offline_copy" with the copy of ur table in it.

Brian Dunning wrote:
>
> Thanks Chris, this sounds great but when I read about mysqlhotcopy I
> didn't see a way to make it create a live table that's open within
> the same database, it seems to want only to create a separate backup
> file in some directory.
>

--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com