MySQL性能测试之insert&delete【FunTester框架】

之前写过一个FunTester框架对Redis进行几种常见API进行性能测试的系列文章,今天如约开始写FunTester框架对MySQL常用功能的性能测试。

我们来回顾一些Redis性能测试的文章:

MySQL系列文章预计分两期,分别是增删、改查,进行简单的SQL语句操作,如在实际工作中用到,需要自行实现SQL语句的参数化,满足业务需求。

MySQL是我本地的服务,用的也是本机,本次测试只演示常见增删改查功能的性能测试,不测试MySQL服务性能,也不设置索引等参数。后续等我学完Go语言的gorm框架,我再写一篇Go语言的MySQL性能测试文章分享。

准备工作省略不写,无非就是起个服务,建个库表,主要name和age两个字段,一个字符串一个整型数字。

insert

这个在工作中用到的不少,估计在造数据的时候用到会比较多。这里使用单条数据执行的方式。

脚本用到SQL:INSERT INTO funtesters (name,age) VALUES (\"fun${StringUtil.getString(10)}\",${getRandomInt(100)});

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

参数化:通过随机字符串和随机数字构成,由于本项目没有去重需求,所以没有采取唯一变量的方式。

脚本用例


/**
 * MySQL insert语句实践
 */
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)
        }
    }
}

测试结果

~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~ JSON ~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~
>  {
>  ① . "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="
>  }
~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~ JSON ~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~

看QPS还挺快的,哈哈。

delete

删除这个比较麻烦,因为这些数据删了就没了。如果采取如何对消息队列做性能测试这个方案,有点大材小用,我这里就用随机名称作为删除数据的依据,执行先insert再delete的测试方案。

测试用例

/**
 * MySQL insert语句实践
 */
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)
        }
    }
}

测试结果

~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~ JSON ~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~
>  {
>  ① . "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=="
>  }
~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~ JSON ~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~

可以看到,受delete方法的影响,性能下降挺多的。

敬请期待下期的select和update以及未来可能的Go语言实践,Java和Go的HTTP客户端和服务端对比压测已经在路上了。

欢迎关注FunTester,Have Fun ~ Tester !