I have been trying to speed up an ALTER TABLE command that adds a column to a large InnoDB table of about 80M rows.
I have found and tried many different methods, but they are all slow.I have tried both optimizing the ALTER TABLE command, and dumping and loading the table (in both SQL and CSV formats). The table size is about 10GB, and the combined index size is about 6GB. I am trying to understand why it is slow. I have read that dumping and loading in the CSV format should be the absolute fastest, and it does only take 20 minutes to dump the 70M rows. However, it takes the LOAD FILE command 13 hours to import the CSV file. My understanding of LOAD FILE was that it was already optimized to load the data, then build the indices afterwords. I don't understand why it takes so long. I have read that breaking a SQL dump in to "chunks" is also supposed to be fast, but later chunks insert more slowly than earlier chunks. This is with keys disabled, and other options disabled. Ideally I could stick with the ALTER TABLE command, and I have played around with a lot of the buffer settings. My understanding is, any enabled key indices need to fit in to RAM, and I have played around with a lot of those settings. Mainly I have increased the buffer size and log buffer size. When importing records, I see the "free buffers" slowly run-out, and the import speed drops off when the buffers are used up. The first few million rows import at up to 30k rows per second, but eventually it slows to a crawl. I have read a lot about this on the mysqlperformance blog. There is a lot of information on the web about this topic, but I am not always sure which parts are for ISAM and which apply to InnoDB. I have not experimented with ISAM, since my tables are InnoDB. This process is slow on a larger box, which belongs to someone else, and on my own desktop PC. Should I stick with trying to make ALTER TABLE work, or should I be trying to get LOAD FILE to work? Any suggestions on adding a column to a large table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
