Luc,
do you use the mysql client for the insert operations?
And is autocommit set to "yes"?
Then the answer is:
turn off autocommit mode and commit every <high number but not too high to grow InnoDB's transaction handling resources too big> rows.
Commit every 100,000 rows for example.
The speeds up the whole thing a lot because there is no need for a disk flush after every record insert.
"commit" means "the data are on disk for sure now
(or after a few seconds (if you set innodb_flush_log_at_trx_commit to 0 or 2 instead of 1))".
Regards, Frank.
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
-- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
