mysqlslap压力测试和线程池

1. mysqlslap介绍

mysqlslap是一个诊断程序，旨在模拟客户端并发访问MySQL服务器，测试MySQL服务的负载，主要工作场景就是对数据库服务器做基准测试。

mysqlslap官方文档

MySQL :: MySQL 5.7 Reference Manual :: 4.5.8 mysqlslap — A Load Emulation Client

2. mysqlslap常用参数

--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。
--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。
--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。
--only-print 只打印测试语句而不实际执行。
--detach=N 执行N条语句后断开重连。
--debug-info, -T 打印内存和CPU的相关信息。

测试的过程需要生成测试表，插入测试数据，这个mysqlslap可以自动生成，默认生成一个mysqlslap的schema，如果已经存在则先删除。可以用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。

3. 开始测试

sbtest.sql是自己编写的压力测试脚本

3.1 单线程

[root@localhost soft]# mysqlslap -uroot -p123456 --query=stock.sql --number-of-queries=100000

3.2 多线程

[root@localhost soft]# mysqlslap -uroot -p123456 -c4 --query=sbtest.sql --number-of-queries=100000

3.3 50和100个并发

[root@localhost soft]# mysqlslap -uroot -p123456 -c50,100 --query=stock.sql --number-of-queries=100000
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Average number of seconds to run all queries: 21.113 seconds
    Minimum number of seconds to run all queries: 21.113 seconds
    Maximum number of seconds to run all queries: 21.113 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2000

Benchmark
    Average number of seconds to run all queries: 21.445 seconds
    Minimum number of seconds to run all queries: 21.445 seconds
    Maximum number of seconds to run all queries: 21.445 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1000

3.4 迭代测试

50和100个并发，3次迭代测试，3次执行测试得到平均值

[root@localhost soft]# mysqlslap -uroot -p123456 -c50,100 --query=stock.sql --number-of-queries=100000 -i3
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Average number of seconds to run all queries: 20.784 seconds
    Minimum number of seconds to run all queries: 20.439 seconds
    Maximum number of seconds to run all queries: 21.095 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2000

Benchmark
    Average number of seconds to run all queries: 22.958 seconds
    Minimum number of seconds to run all queries: 22.471 seconds
    Maximum number of seconds to run all queries: 23.381 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1000

4.结果解释

Benchmark
    #运行所有语句的平均秒数
    Average number of seconds to run all queries: 22.958 seconds
    #运行所有语句的最小秒数
    Minimum number of seconds to run all queries: 22.471 seconds
    #运行所有语句的最大秒数
    Maximum number of seconds to run all queries: 23.381 seconds
    #100并发
    Number of clients running queries: 100
    #每个客户端执行的语句数
    Average number of queries per client: 1000

当测试线程为4,8,16,32,64,128,256,512,1024时，线程为32 QPS达到最大，往后线程越多QPS越低，需要设置线程池，控制并发量

5.线程池

    为了解决one-thread-per-connection(每个连接一个线程)存在的频繁创建和销毁大量线程以及高并发情况下msql吞吐量严重下降的问题，实现mysql在高并发环境依然能保持较高的性能。

    Oracle和MariaDB都推出了ThreadPool方案，目前Oracle的Thread pool实现为Plugin方式，并且只添加到在Enterprise版本中，Percona移植了MariaDB的Thread pool功能，并做了进一步的优化。我的环境是基于Percona MySQL 5.7版本。

    为了处理并发请求，MySQL提供了thread_handling 参数，用于控制线程的管理方式。它可以影响数据库的性能、吞吐量以及对并发请求的处理能力。在默认情况下，thread_handling 参数的值为one-thread-per-connection，即每个客户端连接都会分配一个独立的线程来处理。

常见的 thread_handling 参数取值（perconna mysql）

1. one-thread-per-connection:每个客户端连接分配一个独立的线程来处理。这是默认的线程管理方式，适用于低并发的情况。在高并发的环境下，由于线程的创建和销毁开销较大，可能导致性能下降。
2. one-thread-for-all-connections:所有客户端连接共享一个线程。这种方式适用于高并发的应用场景，可以降低线程创建和销毁的开销，提高数据库的吞吐量。不过，由于只有一个线程处理所有的连接，可能会导致请求处理的延迟增加。
3. pool-of-threads:为客户端连接维护一个线程池，连接请求会被分配到池中的空闲线程进行处理。这种方式适用于中等并发的场景，可以在一定程度上平衡线程的创建和销毁开销与请求处理的延迟。

5.1 开启线程池

perconna mysql配置文件添加如下参数，重启mysql使配置生效

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
thread_handling = pool-of-threads

5.2 关于线程池的参数

(root@localhost) [(none)]> show variables like 'thread%';
+-------------------------------+-----------------+
| Variable_name                 | Value           |
+-------------------------------+-----------------+
| thread_cache_size             | 13              |
| thread_handling               | pool-of-threads |
| thread_pool_high_prio_mode    | transactions    |
| thread_pool_high_prio_tickets | 4294967295      |
| thread_pool_idle_timeout      | 60              |
| thread_pool_max_threads       | 100000          |
| thread_pool_oversubscribe     | 3               | #group中的最大线程数，每个group的最大线程数为thread_pool_oversubscribe+1
| thread_pool_size              | 4               | #线程池的Group的数量，默认为系统CPU的个数
| thread_pool_stall_limit       | 500             |
| thread_stack                  | 262144          | #每个线程堆栈大小
| thread_statistics             | OFF             | #
+-------------------------------+-----------------+

• thread_pool_size

该参数是设置线程池的Group的数量，默认为系统CPU的个数，充分利用CPU资源。

• thread_pool_oversubscribe

该参数设置group中的最大线程数，每个group的最大线程数为thread_pool_oversubscribe+1，注意listener线程不包含在内。

• thread_pool_high_prio_mode

高优先级队列的控制参数，有三个值(transactions/statements/none)，默认是transactions，三个值的含义如下：

transactions：对于已经启动事务的语句放到高优先级队列中，不过还取决于后面的thread_pool_high_prio_tickets参数。

statements：这个模式所有的语句都会放到高优先级队列中，不会使用到低优先级队列。

none：这个模式不使用高优先级队列。

• thread_pool_high_prio_tickets

该参数控制每个连接最多语序多少次被放入高优先级队列中，默认为4294967295，注意这个参数只有在thread_pool_high_prio_mode为transactions的时候才有效果。

• thread_pool_idle_timeout

worker线程最大空闲时间，默认为60秒，超过限制后会退出。

• thread_pool_max_threads

该参数用来限制线程池最大的线程数，超过该限制后将无法再创建更多的线程，默认为100000。

• thread_pool_stall_limit

该参数设置timer线程的检测group是否异常的时间间隔，默认为500ms。