how can i stop redo log while load data infile to a innodb table?

selfthingselfthing EntrantInactive User Role Participant
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?

Comments

  • xaprbxaprb Mentor Inactive User Role Leader
    You can't. InnoDB doesn't allow you to disable it.
  • sterin71sterin71 Advisor Inactive User Role Mentor
    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.
  • selfthingselfthing Entrant Inactive User Role Participant
    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?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.