MySQL Performance Testing for SELECT and UPDATE Statements Using the FunTester Framework

The article builds on a previous post about MySQL insert/delete performance testing with the FunTester framework and now focuses on measuring SELECT and UPDATE queries.

SELECT

The SELECT statement is the most frequently used query and offers many optimization opportunities, such as indexing and sharding. A simple example selects rows where the age column matches a random value between 0 and 99.

SQL statement used:

SELECT * FROM funtesters WHERE age = ${getRandomInt(100)};

Test Script

package com.funtest.mysqlt

import com.funtester.base.constaint.FixedThread
import com.funtester.db.mysql.SqlBase
import com.funtester.frame.execute.Concurrent
import com.funtester.httpclient.FunLibrary

/**
 * MySQL insert语句实践
 */
class MysqlSelect 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 {
            statement.execute("SELECT * FROM funtesters WHERE age = ${getRandomInt(100)};")
        }

        @Override
        protected void after() {
            super.after()
            close(connection, statement)
        }

        @Override
        FixedThread clone() {
            return new FunTester(limit)
        }
    }
}

Test Result

{
  "rt":115,
  "failRate":0.0,
  "threads":10,
  "deviation":"0.44%",
  "errorRate":0.0,
  "executeTotal":4987,
  "qps2":86.57234615050777,
  "total":4987,
  "qps":86.95652173913044,
  "startTime":"2021-11-26 11:44:40",
  "endTime":"2021-11-26 11:45:38",
  "mark":"FunTester框架测试MySQL261144",
  "table":"eJzt0T0KwjAUB/..."
}

The QPS is relatively low, possibly due to the lack of indexes.

UPDATE

UPDATE is also a common operation. The article references two earlier posts that compare different parameterization strategies for single‑row multi‑update and multi‑row single‑update scenarios.

The presented example locks a row by its id and repeatedly updates the name column, reducing the chance of identical before‑and‑after values.

Test Script

import com.funtester.base.constaint.FixedThread
import com.funtester.db.mysql.SqlBase
import com.funtester.frame.execute.Concurrent
import com.funtester.httpclient.FunLibrary
import com.funtester.utils.StringUtil

/**
 * MySQL insert语句实践
 */
class MysqlUpdate 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 = 500

    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)
        def id

        FunTester() {
            super(null, times, true)
            id = getRandomRange(30, 10000)
        }

        @Override
        protected void doing() throws Exception {
            statement.execute("UPDATE funtesters SET name=\"${StringUtil.getString(20)}\" WHERE id = $id;")
        }

        @Override
        protected void after() {
            super.after()
            close(connection, statement)
        }

        @Override
        FixedThread clone() {
            return new FunTester(limit)
        }
    }
}

Test Result

{
  "rt":118,
  "failRate":0.0,
  "threads":10,
  "deviation":"0.21%",
  "errorRate":0.0,
  "executeTotal":4989,
  "qps2":84.56505525798359,
  "total":4989,
  "qps":84.7457627118644,
  "startTime":"2021-11-26 12:03:10",
  "endTime":"2021-11-26 12:04:09",
  "mark":"FunTester框架测试MySQL261203",
  "table":"eJztk02qwjAUR..."
}

The FunTester framework successfully completes the MySQL performance tests; future work will rewrite the demo in Go using the GORM library.