A few more ideas you can try:
1. SET UNIQUE_CHECKS=0;
You have a unique key that is quite large (model, id name). If you
know the data is already unique (ie. importing from another data
source), then this can speed up the import *a lot*.
2. SET FOREIGN_KEY_CHECKS=0;
You didn't mention if this table has any foreign keys, but again,
since you are importing from a consistent source, no need to check
these on insertion.
3. Make innodb_buffer_pool_size as large as possible without swapping.
Most of the heavy lifting will go on in here. I know you said you did
50-80%, if you are only doing this load on the server, you might want
to increase it temporarily to even larger, assuming no swap. Also feel
free to set innodb_max_dirty_pages_pct to 100.
4. I notice that id isn't an auto_increment, is this value inserted in
an ordered manner? Keep in mind the primary key for InnoDB is a
clustered index. If you are inserting randomly ordered data it will
take a bit more effort to maintain than normal.
5. Make your innodb log files as large as the innodb_buffer_pool_size
that you set above.
Hope that helps some, good luck.
On Tuesday, August 3, 2004, at 10:23 AM, Luc Charland wrote:
> We are evaluating the replacement of a Sybase database with MySQL. The
> databases are 60+GB, containing more than 100 tables.
> Since we need transactions, that implies InnoDB. We were happy with
> the early results, but we hit a major roadblock when trying to import
> the biggest table (20+GB, with 4 indexes).
> We have reproduced the problem with a simpler table on many different
> servers and MySQL versions (4.X).
> At first, we easily insert 1600+ lines per second. As the number of
> lines grows, the performance deteriorate (which I can understand), but
> it eventually gets so slow that the import would take weeks.
> Doing a vmstat on the server shows that after a certain limit is
> reached (index bigger than the total mem ?), mysqld starts reading as
> much as writing, and the CPU usage goes down as the I/O eventually
> reach the maximum for the server.
> If you wait long enough, you get less than 50 lines per second (which
> is 30+ times slower than the first few million inserts).
> We have done the same tests on Sybase and another database on the same
> machines and have not seen this behavior, so it is not hardware
> We have done the same import in a MyISAM table and have not see any
> slowdown (the whole data was imported very fast, even if we had to
> wait a very long time --5+ hours-- for the index to rebuild after).
> We have tried to transform the MyISAM table into a InnoDB (same
> problem occurs). We have tried to import from the MyISAM table into an
> empty InnoDB, same problem occurs.
> We have of course changed the following
> innodb_buffer_pool_size= (50% to 80% of total ram)
> innodb_log_file_size=(20% to 40% of total ram)
> we have tried different innodb_flush_method
> we have tried innodb_flush_log_at_trx_commit (0, 1)
> we have tried ibdata1:1G:autoextend, and also make it big enough so
> that all the data will fit without autoextending.
> we have tried creating the indexes after instead of before the
> inserts, but like the documentation says, it is not better.
> Is there an upper limit to the size of the indexes of a single table
> in InnoDB?
> Anybody else has seen this kind of slowdown for big InnoDB tables?
> Here is a small table that reproduce the problem (if you make 5 to 15
> million inserts). We wrote a few programs (one in C++, one in Python)
> that generates random data and insert into the database.
> "create table smallest ( id int primary key, name varchar(80), model
> , description varchar(255), lastupdate date, price decimal(8,2), cost
> "create unique index smallcomplex on smalltest (model, id, name)"
> "create index smallprice on smalltest (price)"
> "create index smallcost on smalltest (cost)"
> "create index smallname on smalltest (name)"
> Thanks for any help.
> Luc Charland