MySQL Write Performance Testing with Concurrency: Benchmarks and Optimization Strategies

The previous article introduced several single‑threaded approaches for MySQL write load testing; based on reader feedback, this follow‑up examines how concurrency impacts performance.

Three concurrency objects are considered: java.sql.Statement , java.sql.Connection , and the database itself (multiple connections). The author’s own maximum single‑threaded insert rate is about 500 k rows per second, with an estimated ceiling near 1 M.

Baseline Test

A simple benchmark using a fixed INSERT statement is run on an unstable workstation to illustrate the method.

package com.funtest.temp

import com.funtester.db.mysql.FunMySql
import com.funtester.frame.SourceCode

class MysqlTest extends SourceCode {
    public static void main(String[] args) {
        StringBuilder s = new StringBuilder();
        String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八组一对')";
        String ipPort = "127.0.0.1:3306"; // server address
        String database = "funtester"; // database name
        String user = "root"; // username
        String password = "funtester"; // password
        def base = new FunMySql(ipPort, database, user, password); // create DB helper
        def statement = base.connection.createStatement(); // create SQL statement object
        while (true) {
            statement.executeUpdate(sql); // execute insert
        }
        statement.close(); // close resources
        base.close(); // close resources
    }
}

The benchmark yields roughly 9 800–10 300 rows per second over 36–45 seconds, noticeably better than earlier tests.

Statement Concurrency

Although Statement is not thread‑safe for queries, the author tests it for inserts using a shared statement across ten threads.

import com.funtester.db.mysql.FunMySql
import com.funtester.frame.SourceCode
import java.util.concurrent.ExecutorService
import java.util.concurrent.Executors

class MysqlTest extends SourceCode {
    public static void main(String[] args) {
        String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八组一对')";
        def base = new FunMySql("127.0.0.1:3306", "funtester", "root", "funtester");
        def statement = base.connection.createStatement();
        ExecutorService executors = Executors.newFixedThreadPool(10);
        10.times {
            executors.execute {
                while (true) {
                    statement.executeUpdate(sql);
                }
            }
        }
        statement.close();
        base.close();
    }
}

The result shows only a modest improvement (≈9 500–10 300 rows in 42–53 s), indicating that shared Statement does not provide a significant throughput gain.

Connection Concurrency

Each thread creates its own Statement via a separate Connection , which isolates work and avoids contention.

import com.funtester.db.mysql.FunMySql
import com.funtester.frame.FunPhaser
import com.funtester.frame.SourceCode
import java.util.concurrent.ExecutorService
import java.util.concurrent.Executors

class MysqlTest extends SourceCode {
    public static void main(String[] args) {
        String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八组一对')";
        def base = new FunMySql("127.0.0.1:3306", "funtester", "root", "funtester");
        ExecutorService executors = Executors.newFixedThreadPool(10);
        def phaser = new FunPhaser();
        10.times {
            phaser.register();
            executors.execute {
                def statement = base.connection.createStatement();
                while (true) {
                    statement.executeUpdate(sql);
                }
                phaser.done();
            }
        }
        executors.shutdown();
        phaser.await();
        base.close();
    }
}

The benchmark again shows little difference (≈10 000 rows in 57–58 s), suggesting that the bottleneck lies elsewhere, likely at the single underlying connection’s capacity.

Database‑Level Concurrency

By opening many separate connections, the test achieves a dramatic speedup, reaching roughly 38 000–47 000 rows in just 32–41 seconds, confirming that scaling the number of connections can substantially improve write throughput.

Maximum Single‑Thread Performance

Repeating the best single‑thread method (batch insert of N rows) yields over 240 k rows in 12–20 seconds, demonstrating that the optimal approach combines large batch sizes with minimal connection overhead.

Conclusion and Tuning Factors

The author lists numerous MySQL configuration and architectural factors that affect write performance, including InnoDB buffer pool size, log file size, flush settings, index strategy, partitioning, data types, batch inserts, transaction size, lock contention, connection pooling, concurrency level, engine choice (InnoDB vs MyISAM), network latency/bandwidth, OS I/O tuning, and file‑system selection.

In real‑world scenarios, selecting and combining the appropriate optimizations based on workload characteristics is essential for achieving the desired write performance.