load data to a innodb table is very slow,but to a myisam is fast,so i figured out that writing redo log slows down the import speed,but i don't know how to stop that while load data.anyone konw how?
-
New Member- Feb 2012
- 1
-
MySQL Sage
- Apr 2008
- 326
-
Counsellor
- Apr 2009
- 108
Selfthing:
What kind of insert speed do you get compared to MyISAM?
Do you have an INT auto_increment as primary key or are your primary key inserted non sequentially?
How large is your innodb_log_file_size?
Have you set innodb_flush_log_at_trx_commit=2 in your configuration or are you using a RAID controller with a BBU cache?
As you can see in another post on this forum you should be able to reach quite fast insert rates on InnoDB also.Comment
-
New Member
- Feb 2012
- 1
thank you,xaprb and sterin71.i will check the post you mentioned.
the csv file is 8GB,no auto_increment ,and the server is quite old,does't have BBU cache,so innodb_flush_log_at_trx_commit=1.
i set innodb_log_file_size to 1000MB,and innodb_log_files_in_group to 4,that helps speed up insert rate,but the speed rate is becoming slower and slower while table size increasing,if i split the csv file to several files,may that help speed up insert rate?Comment
Comment