List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:August 18 2010 5:56pm
Subject:RE: Slow ALTER TABLE on 70M row InnoDB table
View as plain text  
What are you using as your primary key on this table?  Is an auto_increment
field or something non-sequential?  Do you have your secondary indexes in
place while you load the table or are you explicitly disabling them and
re-enabling them afterward? 

-Travis

-----Original Message-----
From: Xn Nooby [mailto:xnooby@stripped] 
Sent: Wednesday, August 18, 2010 9:34 AM
To: mysql@stripped
Subject: Slow ALTER TABLE on 70M row InnoDB table

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=1


Thread
Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
  • Re: Slow ALTER TABLE on 70M row InnoDB tablemos18 Aug
    • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
      • Re: Slow ALTER TABLE on 70M row InnoDB tablemos18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
          • Re: Slow ALTER TABLE on 70M row InnoDB tableEric Bergen21 Aug
  • RE: Slow ALTER TABLE on 70M row InnoDB tableTravis Ard18 Aug