mysql 压测工具之 mysqlslap

mysqlslap 是mysql自带的基准测试工具，它可以模拟多个客户端同时并发的向服务器发出查询更新，给出了性能测试数据而且提供了多种引擎的性能比较。mysqlslap为mysql性能优化前后提供了直观的验证依据，可以提前预判线上数据库支撑的用户流量上限及其抗压性等问题。

• 压测之前调整基准：更改mysql默认的最大连接数

#.修改最大连接数
sed -i 's/^max_connections.*$/max_connections=1024/g' /data/mysql_3306/my_3306.cnf
sed -i 's/^max_user_connections.*$/max_user_connections=1024/g' /data/mysql_3306/my_3306.cnf
cat /data/mysql_3306/my_3306.cnf | egrep "(max_connections|max_user_connections)"

#.重启mysql服务
/opt/mysql/bin/mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock shutdown
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf &

#.修改测试账号的max_user_connections
/opt/mysql/bin/mysql -h127.0.0.1 -udba_admin -pcf_rB1NKCzbaQuPH -e"alter user 'dba_admin'@'%'with max_user_connections 1024;"
/opt/mysql/bin/mysql -h127.0.0.1 -udba_admin -pcf_rB1NKCzbaQuPH -e"select user,host,max_user_connections from mysql.user where user='dba_admin';"

#.确认最大连接数已生效
/opt/mysql/bin/mysql -h127.0.0.1 -udba_admin -pcf_rB1NKCzbaQuPH -e"show variables like 'max_connections';"

• 注意：mysqlslap 与 default-character-set=utf8 冲突，测试之前先将其注释掉

sed -i 's/^default-character-set.*$/#default-character-set.*/g' /data/mysql_3306/my_3306.cnf
cat /data/mysql_3306/my_3306.cnf | grep "default-character-set"

• mysqlslap 参数解释：

• 压力测试场景1：模拟两次读写并发、第一次100、第二次200、自动生成SQL脚本、测试表包含20个init字段、30个char字段、每次执行2000查询请求、测试引擎innodb

[root@localhost ~]# /opt/mysql/bin/mysqlslap --defaults-file=/data/mysql_3306/my_3306.cnf \
--concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 \
--auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb \
--number-of-queries=2000 -h127.0.0.1 -udba_admin -pcf_rB1NKCzbaQuPH --verbose

mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 1.188 seconds     #运行所有查询的平均秒数
        Minimum number of seconds to run all queries: 1.188 seconds     #运行所有查询所需的最小秒数
        Maximum number of seconds to run all queries: 1.188 seconds     #运行所有查询的最大秒数
        Number of clients running queries: 100                          #运行查询的客户端数量
        Average number of queries per client: 20                        #每个客户端的平均查询数

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 1.149 seconds     #运行所有查询的平均秒数
        Minimum number of seconds to run all queries: 1.149 seconds     #运行所有查询所需的最小秒数
        Maximum number of seconds to run all queries: 1.149 seconds     #运行所有查询的最大秒数
        Number of clients running queries: 200                          #运行查询的客户端数量
        Average number of queries per client: 10                        #每个客户端的平均查询数

• 压力测试场景2：由于场景1的压力不存在问题（平均耗时1.188秒），此次压力调大一点，发现此次查询明显变慢了（平均耗时54.405秒）

[root@localhost ~]# /opt/mysql/bin/mysqlslap --defaults-file=/data/mysql_3306/my_3306.cnf \
--concurrency=200,400 --iterations=1 --number-int-cols=50 --number-char-cols=60 \
--auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb \
--number-of-queries=80000 -h127.0.0.1 -udba_admin -pcf_rB1NKCzbaQuPH --verbose

mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 54.405 seconds
        Minimum number of seconds to run all queries: 54.405 seconds
        Maximum number of seconds to run all queries: 54.405 seconds
        Number of clients running queries: 200
        Average number of queries per client: 400

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 59.442 seconds
        Minimum number of seconds to run all queries: 59.442 seconds
        Maximum number of seconds to run all queries: 59.442 seconds
        Number of clients running queries: 400
        Average number of queries per client: 200