MySQL Performance Testing Practice: Simulating New User Registration

We use a small supermarket scenario to conduct MySQL performance testing focused on the user table, which stores basic user information with fields such as id, name, age, level, region, address, and create_time.

The table is created with InnoDB engine and utf8mb4 charset to support high‑concurrency reads/writes and diverse characters.

New User Registration

During promotional peaks the registration interface becomes slow; the bottleneck is the insert operation into the user table, which currently holds about 100,000 rows and experiences ~500 TPS. The goal is to raise TPS to 1,000.

Test cases avoid batch inserts and instead simulate single‑row INSERT statements with parameterized SQL, randomizing string fields (name, address, region) and integer fields (age, level) to reflect real user input.

Test Case Code

package org.funtester.performance.books.chapter06.section3;

import org.funtester.performance.books.chapter03.common.ThreadTool;
import org.funtester.performance.books.chapter03.section3.ThreadTask;
import org.funtester.performance.books.chapter03.section4.TaskExecutor;
import org.funtester.performance.books.chapter04.section3.RandomTool;
import org.funtester.performance.books.chapter06.section2.MySqlBase;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 新用户注册插入FunTester数据库
 */
public class UserRegisterCase {
    public static void main(String[] args) throws InterruptedException {
        // 配置FunTester测试数据库连接
        String url = "jdbc:mysql://127.0.0.1:3306/funtester";
        String user = "root";
        String password = "funtester";
        int totalNum = 1000; // 每个线程执行次数
        int threadNum = 8;   // 线程数，模拟并发
        int rumpUpTime = 20; // 预热时间（秒）
        List
tasks = new ArrayList<>(); // 任务集合
        String sqlFormat = "INSERT INTO user (name, age, level, region, address) VALUES ('%s', %d, %d, '%s', '%s')";
        AtomicInteger successUser = new AtomicInteger(); // 统计成功注册用户数

        // 创建多线程任务
        for (int i = 0; i < threadNum; i++) {
            ThreadTask threadTask = new ThreadTask() {
                /** 数据库操作基础类 */
                MySqlBase base;

                @Override
                public void before() {
                    super.before(); // 调用父类初始化
                    base = new MySqlBase(url, user, password); // 初始化FunTester数据库连接
                    try {
                        base.init(); // 初始化资源
                    } catch (SQLException e) {
                        throw new RuntimeException("FunTester数据库连接失败: " + e.getMessage());
                    }
                }

                @Override
                public void test() {
                    // 模拟真实用户数据
                    String name = RandomTool.getRandomString(32); // 随机生成用户名，最大32字符
                    int age = ThreadLocalRandom.current().nextInt(18, 100); // 随机年龄，模拟成年用户
                    int level = ThreadLocalRandom.current().nextInt(1, 10); // 随机用户等级
                    String region = RandomTool.getRandomString(32); // 随机地区，模拟多样化输入
                    String address = RandomTool.getRandomString(512); // 随机地址，覆盖长短字符串
                    String sql = String.format(sqlFormat, name, age, level, region, address); // 格式化SQL
                    try {
                        int rows = base.statement.executeUpdate(sql); // 执行插入
                        if (rows == 1) successUser.getAndIncrement(); // 成功插入计数
                    } catch (SQLException e) {
                        throw new RuntimeException("FunTester插入用户失败: " + e.getMessage());
                    }
                    ThreadTool.sleep(5); // 模拟用户操作间隔
                }

                @Override
                public void after() {
                    super.after(); // 调用父类清理
                    try {
                        base.close(); // 关闭FunTester数据库资源
                    } catch (SQLException e) {
                        throw new RuntimeException("FunTester资源关闭失败: " + e.getMessage());
                    }
                }
            };
            threadTask.totalNum = totalNum; // 设置执行次数
            threadTask.costTime = new ArrayList<>(totalNum); // 初始化耗时集合，优化内存分配
            tasks.add(threadTask); // 添加任务
        }

        // 启动FunTester性能测试任务
        TaskExecutor taskExecutor = new TaskExecutor(tasks, "新用户注册插入FunTester数据库", rumpUpTime);
        taskExecutor.start(); // 执行测试
        System.out.println("成功注册用户数量: " + successUser.get()); // 输出结果
    }
}

Code Analysis

Multithreaded concurrency: 8 threads each perform 1,000 inserts, totaling 8,000 operations, mimicking peak load. The ThreadTask class encapsulates before/after resource handling.

Parameterized input: Random strings for name, region, and address (max 32/512 characters) and random integers for age and level simulate diverse user data.

Success counting: An AtomicInteger records successful inserts in a thread‑safe manner.

Resource management: MySqlBase handles connection initialization and cleanup; ThreadTool.sleep(5) adds a short pause to emulate user think time.