Are you disabling autocommit before doing the inserts? And committing after all inserts are complete?
-----Original Message----- From: Luc Charland [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 7:54 PM To: [EMAIL PROTECTED] Subject: Insert problems with InnoDB (big table) 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
