Hi,
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.
Regards,
Harrison
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 related.
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.
SETUP:
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 char(20)
, description varchar(255), lastupdate date, price decimal(8,2), cost decimal(8,2))
type=innodb"
"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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
