MySQL Bulk Insert Guide: Fast Loading & Performance Tips

1. Introduction

The Importance of Bulk Insert

Purpose of This Article

2. Basics of Bulk Insert

What is Bulk Insert?

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

INSERT INTO users (name, email) VALUES 
('Alice', 'alice@example.com'), 
('Bob', 'bob@example.com');

Benefits of Bulk Insert

1. Improved Performance Processing multiple rows at once reduces the number of query executions, easing network traffic and disk I/O load.
2. Simplified Transaction Management Handling multiple rows in a single transaction makes it easier to maintain data consistency.
3. Code Simplification You can reduce repetitive code, improving maintainability.

Common Use Cases for Bulk Insert

• When regularly storing large volumes of log data in the database
• Importing data from external systems (e.g., reading CSV files)
• Data migration or restoring from backups

3. How to Perform Bulk Inserts in MySQL

Using Multi-Row INSERT Statements

Basic Syntax

INSERT INTO table_name (column1, column2, ...) VALUES 
(value1, value2, ...), 
(value3, value4, ...), 
...;

Example

INSERT INTO users (name, email) VALUES 
('Alice', 'alice@example.com'), 
('Bob', 'bob@example.com'), 
('Charlie', 'charlie@example.com');

Pros and Cons

• Pros
• Implementation is straightforward, and if you’re familiar with SQL, it’s intuitive to use.
• Using transactions can also ensure data consistency.
• Cons
• If the data volume is too large, you may hit the query size limit (default is 1 MB).

Using the LOAD DATA INFILE Command

Basic Syntax

LOAD DATA INFILE 'file_path' 
INTO TABLE table_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '
';

Example

1. CSV File Contents

   Alice,alice@example.com
   Bob,bob@example.com
   Charlie,charlie@example.com

2. Executing the Command

   LOAD DATA INFILE '/path/to/users.csv' 
   INTO TABLE users 
   FIELDS TERMINATED BY ',' 
   ENCLOSED BY '"' 
   LINES TERMINATED BY '
';

Pros and Cons

• Pros
• Extremely fast, allowing efficient insertion of massive data sets.
• Because it leverages native file operations, it is well suited for very large data sets.
• Cons
• It depends on file paths and permission settings, so careful initial configuration is required.
• For security reasons, some servers do not allow LOAD DATA INFILE.

Using the mysqlimport Utility

Basic Syntax

mysqlimport --local database_name file_name

Example

mysqlimport --local --fields-terminated-by=',' --lines-terminated-by='
' my_database /path/to/users.csv

Pros and Cons

• Pros
• Can be run easily from the command line.
• Fast, similar to LOAD DATA INFILE.
• Cons
• If the file format is not correct, errors may occur.
• Compared to writing SQL statements, it may take some time to get used to.

4. Bulk Insert Considerations and Limitations

Query Size Limit

Solution

• Increase max_allowed_packet in the server configuration:

  SET GLOBAL max_allowed_packet = 16M;

• Split the query into batches (e.g., process 1,000 rows per batch).

Impact of Indexes

Solution

• Temporarily disable indexes before inserting: When inserting a large amount of data, it can be effective to drop the indexes temporarily and recreate them after the data load.

  ALTER TABLE table_name DISABLE KEYS;
  -- Bulk insert processing
  ALTER TABLE table_name ENABLE KEYS;

• Add indexes after data insertion: Rebuilding the indexes after the load creates them in bulk, improving processing speed.

Transaction Management

Solution

START TRANSACTION;
INSERT INTO table_name ...;
-- Execute all required insert operations
COMMIT;

ROLLBACK;

Security and Permissions

Solution

• If the server does not allow LOAD DATA INFILE, use the client-side LOAD DATA LOCAL INFILE.
• Check the required permissions and consult the administrator to request appropriate settings.

Other Considerations

• Matching character set: If the data file’s character set doesn’t match the table’s setting, you’ll get garbled text or errors. Verify the character set before loading.
• Deadlock risk: When multiple processes insert data simultaneously, deadlocks can occur. Serializing insert operations can avoid this.

5. Best Practices for Bulk Inserts

Leverage Transactions

START TRANSACTION;
-- Execute bulk insert
COMMIT;

Optimize Index Operations

ALTER TABLE table_name DISABLE KEYS;
-- Execute bulk insert
ALTER TABLE table_name ENABLE KEYS;

Choose an Appropriate Batch Size

Practical Example

INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
... -- about 1000 rows
;

Validate Data

# Example: Data validation using Python
import csv

with open('users.csv', mode='r') as file:
    reader = csv.reader(file)
    for row in reader:
        # Check if format is correct
        if not row[1].contains('@'):
            print(f"Invalid email format: {row[1]}")

Implement Error Handling

LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
LOG ERRORS INTO 'error_log';

6. Bulk Insert Performance Tuning

Optimizing Batch Size

Best Practices

• Recommended size: Typically 1,000–10,000 rows is appropriate.
• If the batch size is too small, the number of query executions increases, putting more load on the network and disk.
• If the batch size is too large, you may hit the max_allowed_packet limit or risk increased memory pressure.

Example

INSERT INTO users (name, email) VALUES 
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
... -- up to 1000 rows
;

Temporarily Disabling Indexes

Solution

• Disable indexes before inserting and rebuild them afterward.

  ALTER TABLE table_name DISABLE KEYS;
  -- execute bulk insert
  ALTER TABLE table_name ENABLE KEYS;

Using Table Locks

Example Usage

LOCK TABLES table_name WRITE;
-- execute bulk insert
UNLOCK TABLES;

LOAD DATA INFILE Optimization

Option Examples

• IGNORE: Insert while ignoring duplicate rows.

  LOAD DATA INFILE '/path/to/file.csv' 
  INTO TABLE users 
  IGNORE;

• CONCURRENT: Minimizes load even when the table is being accessed by other queries concurrently.

  LOAD DATA CONCURRENT INFILE '/path/to/file.csv' 
  INTO TABLE users;

Tuning MySQL Settings

1. innodb_buffer_pool_size If you are using InnoDB tables, increasing this parameter improves data read/write speed.

   SET GLOBAL innodb_buffer_pool_size = 1G;

2. bulk_insert_buffer_size If you are using MyISAM tables, setting this parameter improves bulk insert performance.

   SET GLOBAL bulk_insert_buffer_size = 256M;

3. autocommit temporary disabling Temporarily disabling autocommit: Disable autocommit during insertion and re-enable it afterward.

   SET autocommit = 0;
   -- execute bulk insert
   COMMIT;
   SET autocommit = 1;

Before/After Performance Comparison

-- record timestamp before insertion
SET @start_time = NOW();

-- execute bulk insert
INSERT INTO users (name, email) VALUES 
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
... -- about 1000 rows

-- measure execution time
SELECT TIMESTAMPDIFF(SECOND, @start_time, NOW()) AS execution_time;

7. Bulk Insert Practical Example

Example: Inserting User Data from a CSV File

1. Data Preparation

Alice,alice@example.com
Bob,bob@example.com
Charlie,charlie@example.com

2. Table Creation

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

3. Bulk Insert: Multi-Row INSERT Statement

INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');

4. Bulk Insert: LOAD DATA INFILE

Command Example

LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
'
(name, email);

5. Performance Measurement

Script Example

SET @start_time = NOW();

LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
'
(name, email);

SELECT TIMESTAMPDIFF(SECOND, @start_time, NOW()) AS execution_time;

8. FAQ

Q1: A “duplicate data” error occurs during bulk insert. How should I address it?

1. IGNORE option Insert data while ignoring duplicate errors.

   INSERT IGNORE INTO users (name, email) VALUES 
   ('Alice', 'alice@example.com'), 
   ('Bob', 'bob@example.com');

2. Use ON DUPLICATE KEY UPDATE Update existing rows when duplicate data is found.

   INSERT INTO users (name, email) VALUES 
   ('Alice', 'alice@example.com') 
   ON DUPLICATE KEY UPDATE email = VALUES(email);

Q2: Using LOAD DATA INFILE results in a “permission denied” error. How can I fix this?

1. Use LOAD DATA LOCAL INFILE Use the LOCAL option when reading a file from the client machine.

   LOAD DATA LOCAL INFILE '/path/to/users.csv' 
   INTO TABLE users 
   FIELDS TERMINATED BY ',' 
   LINES TERMINATED BY '
';

2. Check MySQL configuration Ensure that local_infile is enabled in the server settings.

   SHOW VARIABLES LIKE 'local_infile';
   SET GLOBAL local_infile = 1;

Q3: Bulk insert performance does not improve as much as expected. What should I check?

1. Reduce the number of indexes Temporarily disabling indexes during bulk insert can improve insertion speed (see the earlier “Impact of Indexes” for details).
2. Adjust batch size Choose an appropriate batch size (around 1,000–10,000 rows) based on the volume of data.
3. Tune MySQL settings

• Increase innodb_buffer_pool_size (for InnoDB).
• Adjust bulk_insert_buffer_size (for MyISAM).

1. Use table locking Temporarily lock the table to avoid conflicts with other queries.

   LOCK TABLES users WRITE;
   -- Execute bulk insert
   UNLOCK TABLES;

Q4: Errors occur due to the CSV file format. Please provide the correct format.

1. Each field is separated by a comma (,).

   Alice,alice@example.com
   Bob,bob@example.com

2. If the data contains special characters, escape them properly.

   "Alice O'Conner","alice.o@example.com"

3. Include a newline character at the end of the last line.

• If the final line does not end with a newline, it may be ignored.

Q5: How can I maintain data integrity?

1. Use transactions Commit only when all data has been inserted successfully to maintain consistency.

   START TRANSACTION;
   -- Execute bulk insert
   COMMIT;

2. Validate input data Use scripts or tools before insertion to check data format and duplicates.
3. Leverage error logs Record erroneous data, fix it later, and reinsert.

   LOAD DATA INFILE '/path/to/users.csv'
   INTO TABLE users
   LOG ERRORS INTO 'error_log';

9. Summary

The Importance of Bulk Inserts

1. Basics of Bulk Inserts

• Fundamental concepts and typical use cases.

1. Specific Implementation Methods

• Data insertion using multi-row INSERT statements, LOAD DATA INFILE, and mysqlimport.

1. Precautions and Limitations

• Issues and solutions related to query length limits, index impact, and permission settings.

1. Performance Tuning

• Optimizing batch sizes, leveraging table locks, and adjusting MySQL settings.

1. Practical Examples

• Step-by-step procedures and performance measurements using sample data.

1. FAQ

• Challenges encountered in real-world operations and their solutions.

Give It a Try

1. Prepare a small dataset and experiment with multi-row INSERT statements.
2. For large datasets, try LOAD DATA INFILE and measure performance.
3. If needed, incorporate transactions and error handling, and apply them to your production environment.

For Further Learning

• MySQL Official Documentation (Details on Bulk Inserts and Related Settings)

Conclusion