How Fast Can MySQL Insert and Delete? FunTester Performance Benchmarks

The author continues the FunTester series, which previously covered Redis performance testing, by introducing MySQL performance benchmarks for common CRUD operations using a custom Java framework.

Insert benchmark

The test inserts single rows with random string and integer values. The SQL statement used is:

INSERT INTO funtesters (name,age) VALUES ("fun${StringUtil.getString(10)}",${getRandomInt(100)});

In Java this is built as a concatenated string:

"INSERT INTO funtesters (name,age) VALUES (\"fun"+StringUtil.getString(10)+"\","+getRandomInt(100)+");"

Parameterization relies on random data without uniqueness constraints.

/**
 * MySQL insert statement practice
 */
class MysqlInsert extends SqlBase {
    static final String url = "jdbc:mysql://localhost:3306/funtester?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true&useSSL=false";
    static final int thread = 20;
    static final int times = 10000;
    public static void main(String[] args) {
        RUNUP_TIME = 0;
        def task = [];
        thread.times { task << new FunTester() }
        new Concurrent(task, "FunTester框架测试MySQL").start();
        FunLibrary.testOver();
    }
    private static class FunTester extends FixedThread {
        def connection = getConnection(url, "root", "root123456");
        def statement = getStatement(connection);
        FunTester() { super(null, times, true); }
        @Override
        protected void doing() throws Exception {
            statement.execute("INSERT INTO funtesters (name,age) VALUES (\"fun${StringUtil.getString(10)}\",${getRandomInt(100)});");
        }
        @Override
        protected void after() { super.after(); close(connection, statement); }
        @Override
        FixedThread clone() { return new FunTester(limit); }
    }
}

Insert test result

{
  "rt":1,
  "failRate":0.0,
  "threads":20,
  "deviation":"13.1%",
  "errorRate":0.0,
  "executeTotal":198907,
  "qps2":17380.898287312128,
  "total":198907,
  "qps":20000.0,
  "startTime":"2021-11-17 15:32:52",
  "endTime":"2021-11-17 15:33:03",
  "mark":"FunTester框架测试MySQL171532",
  "table":"eJzj5VIgCNxK80JSi0tSi54tbHs2b9uzrd0v1k/1rQwO9FEwMlAoyShKTUwhbAovFy9+u4JSiwvy84pTFUIyc1OtFCp0i1OLMhNzFPJKc3UUKnVzU1MyE/MI2UHYHQoKuZl5ChDTrAwUcot1chMrrIyADGL0EvQFOeDRtA4gwkuN2jpq66ito7aO2jpq68iwlQilQ4wa9dnQo0Z9NvSoUZ8NPWrUZ0OPGvXZ0KNGfTb0qFGfDT1qOPsMAApg90I="
}

The insert operation achieves roughly 20,000 QPS, indicating very fast write performance under the test conditions.

Delete benchmark

Deletion is more complex because removed data cannot be reused. The test inserts a row with a random name and immediately deletes it, using the same random name as the key.

/**
 * MySQL delete statement practice
 */
class MysqlDelete extends SqlBase {
    static final String url = "jdbc:mysql://localhost:3306/funtester?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true&useSSL=false";
    static final int thread = 10;
    static final int times = 100;
    public static void main(String[] args) {
        RUNUP_TIME = 0;
        def task = [];
        thread.times { task << new FunTester() }
        new Concurrent(task, "FunTester框架测试MySQL").start();
        FunLibrary.testOver();
    }
    private static class FunTester extends FixedThread {
        def connection = getConnection(url, "root", "root123456");
        def statement = getStatement(connection);
        FunTester() { super(null, times, true); }
        @Override
        protected void doing() throws Exception {
            String name = "fun${StringUtil.getString(10)}";
            statement.execute("INSERT INTO funtesters (name,age) VALUES (\"$name\",${getRandomInt(100)});");
            statement.execute("DELETE FROM funtesters WHERE name = \"$name\";");
        }
        @Override
        protected void after() { super.after(); close(connection, statement); }
        @Override
        FixedThread clone() { return new FunTester(limit); }
    }
}

Delete test result

{
  "rt":1354,
  "failRate":0.0,
  "threads":10,
  "deviation":"0.47%",
  "errorRate":0.0,
  "executeTotal":1000,
  "qps2":7.3508872520913275,
  "total":1000,
  "qps":7.385524372230428,
  "startTime":"2021-11-17 15:51:41",
  "endTime":"2021-11-17 15:53:57",
  "mark":"FunTester框架测试MySQL171551",
  "table":"eJwBLwDQ/+aVsOaNrumHj+WkquWwkSzml6Dms5Xnu5jlm74hIOW6lOW9k+Wkp+S6jiAxMDI0/eodgA="
}

The delete operation’s QPS drops to around 7, highlighting the performance impact of combined insert‑delete cycles.

Future articles will cover select and update benchmarks and a Go implementation of the same tests.