Geek asks and answers

How to optimize MySQL to insert millions of rows?

I need to insert millions of rows into a MySQL database (InnoDB engine). I have a problem with time when the tables are large. Almost all the time is spent on insert requests. Can anyone know how to optimize it?

+5
source share
2 answers

To import most of the data into InnoDB:

  • installed in MySQL configuration

    • innodb_doublewrite = 0
    • innodb_buffer_pool_size = 50% + system memory
    • innodb_log_file_size = 512M
    • log-bin = 0
    • innodb_support_xa = 0
    • innodb_flush_log_at_trx_commit = 0

  • Add immediately after the start of the transaction:

    SET FOREIGN_KEY_CHECKS = 0;

    SET UNIQUE_CHECKS = 0;

    SET AUTOCOMMIT = 0;

  • Install right before the transaction ends:

    SET UNIQUE_CHECKS = 1;

    SET FOREIGN_KEY_CHECKS = 1;

+14

INSERT, , . , ( ) , SQL. , . , - , .

0

More articles:


All Articles