How to Speed Up Data Import into MySQL InnoDB and Terminate Slow Queries?

Optimization for Lightning-Fast Data Import with MySQL

Query:

When importing voluminous data (approximately 3.6 million rows) into a MySQL InnoDB table using "Load data infile", how can the process be accelerated? Additionally, how can a slow query be terminated without interrupting the MySQL service?

Solution:

Data Import Acceleration:

To optimize the data import process while utilizing Innodb:

• Sort the Input File: Arrange the CSV file in ascending order based on the primary key of the target table. Innodb's clustered primary key structure significantly enhances loading speed when data is sorted.
• Optimized Load Script: Employ the following script for optimal data import:

truncate <table>;

set autocommit = 0;

load data infile <path> into table <table>...

commit;

• Table Optimizations: Enhance load times further by adjusting these table settings:

set unique_checks = 0;
set foreign_key_checks = 0;
set sql_log_bin=0;

• Data Splitting: Divide the CSV file into smaller chunks to improve import speed.

Slow Query Termination:

To terminate a slow query without restarting MySQL:

• Identify the query's thread ID using:

SHOW PROCESSLIST;

• Terminate the query using:

KILL <thread_ID>;

Performance Statistics:

Based on observed bulk load performance:

• Import rate: 3.5 - 6.5 million rows per minute
• Hourly import volume: 210 - 400 million rows

The above is the detailed content of How to Speed Up Data Import into MySQL InnoDB and Terminate Slow Queries?. For more information, please follow other related articles on the PHP Chinese website!