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