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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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? >> [EMAIL PROTECTED] >> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
