MySQL InnoDB Fast Import data

Import the data that needs to be analyzed into the database today.

Data of nearly 70 million, stored in TXT file, a total of 5.75G. It was imported using load data infile and took 18 hours to import. The following changes were mainly made:

1. mysql optimization

Key_buffer_size = 64M
Max_allowed_packet = 8M

Innodb_buffer_pool_size = 1024M

Innodb_flush_log_at_trx_commit = 0

Innodb_support_xa = 0

2. mysql Settings

Poke me.

(1)

SET autocommit=0;

　　　... SQL import statements

　　　...COMMIT;

(2)

SET unique_checks=0;

　　... SQL import statements ...

SET Unique_checks=1;

(3)

SET foreign_key_checks=0;

　　... SQL import statements ...

SET Foreign_key_checks=1;

　　

No picture, no truth.

MySQL InnoDB Fast Import data