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?
You can’t. InnoDB doesn’t allow you to disable it.
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.
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?