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?