List:General Discussion« Previous MessageNext Message »
From:Harrison Date:August 5 2004 8:47pm
Subject:Re: Insert problems with InnoDB (big table)
View as plain text  
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

Thread
Insert problems with InnoDB (big table)Luc Charland3 Aug
  • Re: Insert problems with InnoDB (big table)Dr. Frank Ullrich4 Aug
    • Re: Insert problems with InnoDB (big table)Luc Charland5 Aug
  • Re: Insert problems with InnoDB (big table)Harrison5 Aug
    • Re: Insert problems with InnoDB (big table)Luc Charland6 Aug
RE: Insert problems with InnoDB (big table)Amit_Wadhwa4 Aug
  • Re: Insert problems with InnoDB (big table)David Griffiths4 Aug
    • Re: Insert problems with InnoDB (big table)Luc Charland5 Aug
  • Re: Insert problems with InnoDB (big table)Luc Charland5 Aug
    • Re[2]: Insert problems with InnoDB (big table)adburne5 Aug
      • Re: Insert problems with InnoDB (big table)Luc Charland5 Aug