MySQL 8.0: implementing High Memory Usage During Bulk Inserts with InnoDB
I'm relatively new to this, so bear with me. I'm experiencing important memory consumption when performing bulk inserts into my InnoDB tables in MySQL 8.0... I've set up a script in PHP using PDO to insert large datasets (around 100,000 records at a time) into a table designed to hold user data. While executing the inserts, the server's memory usage spikes to nearly 90%, causing the MySQL service to become unresponsive, and ultimately, it crashes. I have tried breaking the batch size down to 10,000 records and using transactions to improve performance, but the memory scenario continues. Here's a snippet of my insert code: ```php try { $pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'password'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->beginTransaction(); $stmt = $pdo->prepare('INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)'); foreach ($data as $user) { $stmt->execute([$user['name'], $user['email'], date('Y-m-d H:i:s')]); } $pdo->commit(); } catch (Exception $e) { $pdo->rollBack(); echo "behavior: " . $e->getMessage(); } ``` I also reviewed the InnoDB parameters in my `my.cnf`, particularly `innodb_buffer_pool_size`, which is set to 1G. I’ve considered increasing this value, but I'm worried it might exacerbate the scenario. Additionally, I’m using the default `innodb_flush_log_at_trx_commit` setting of `1` for durability, which I suspect might be contributing to the slowdown. I’ve checked for other running processes on the server, but nothing else seems to be consuming excessive memory. The data being inserted is fairly standard and doesn’t contain complex types like JSON or large text fields. Is there a recommended approach to handle bulk inserts with high memory efficiency, or am I missing some configuration that could alleviate this question? My development environment is Windows.