本文分享自天翼云开发者社区《弹性云主机mysql数据性能测试》，作者：高淑杰
0 准备工作
创建两台 ECS 虚机，两台虚机在同一个 vpc 下：
server：安装 mysql
client：安装 sysbench
1 mysql 安装
1 .依赖安装
yum -y install libaio
yum -y install net-tools
2. 安装mysql
wget 'https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum install -y mysql-community-server
3.修改mysql系统资源限制
echo "mysql hard nofile 65535" >> /etc/security/limits.conf
echo "mysql soft nofile 65535" >> /etc/security/limits.conf
echo "LimitNOFILE=65535" >> /usr/lib/systemd/system/mysqld.service
systemctl daemon-reload
4.启动mysql服务
systemctl start mysqld
systemctl status mysqld
5 编辑/etc/my.cnf 修改mysql配置信息
#datadir=/var/lib/mysql
datadir=/data/mysql ----修改数据目录
#socket=/var/lib/mysql/mysql.sock
socket=/data/mysql/mysql.sock --创建socket
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip_grant_tables---跳过密码认证
log-error=/var/log/mysqld.log---mysql日志
pid-file=/var/run/mysqld/mysqld.pid
!includedir /etc/my.cnf.d ---mysql配置文件
6 在/etc/my.cnf.d/下创建cnf 自定义配置文件
重要参数建议值：
innodb_buffer_pool_size ---系统内存的50%-75%
read_buffer_size -----{LEAST(系统内存/1048576*128, 262144)}
7 重启mysql
systemctl restart mysqld
2 sysbench 安装
1 安装sysbench
sudo yum install gcc gcc-c++ autoconf automake make libtool mysql-devel git mysql
git clone https://github.com/akopytov/sysbench.git
cd sysbench
git checkout 1.0.18
./autogen.sh
./configure --prefix=/usr --mandir=/usr/share/man
make
make install
2 修改sysbench 系统资源配置
cpu=$(cat /proc/cpuinfo |grep processor |wc -l)
count=""
for i in `seq 1 $((cpu/4))`
do
count="f""${count}"
done
echo $count
sudo sh -c "for x in /sys/class/net/eth0/queues/rx-*; do echo $count >$x/rps_cpus; done"
sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt"
sudo tee -a /etc/security/limits.conf << EOF
* hard nofile 65535
* soft nofile 65535
root hard nofile 65535
root soft nofile 65535
* soft nproc 65535
* hard nproc 65535
root soft nproc 65535
root hard nproc 65535
* soft core unlimited
* hard core unlimited
root soft core unlimited
root hard core unlimited
EOF
3 测试 client 与 server 连通性
1 配置ecs 安全组，允许外部访问mysql服务端口
2 测试client与mysql server 访问连通性
[root@test-client test]# telnet 10.99.210.7 3306
Trying 10.99.210.7...
Connected to 10.99.210.7.
Escape character is '^]'.
N
5.7.42-lo.?Hd Gÿ.z!;H JQpmysql_native_password
4 数据库性能测试
混合读写-内存命中型：
测试数据准备
sysbench --mysql-host=10.99.210.7 --mysql-port=3306 --mysql-user=root --mysql-password=passwd --mysql-db=teletest --mysql-storage-engine=innodb --tables=32 --table-size=80000 --time=120 --events=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --percentile=95 oltp_read_write --forced-shutdown=0 --db-ps-mode=disable --threads=32 prepare
测试
sysbench --mysql-host=10.99.210.7 --mysql-port=3306 --mysql-user=root --mysql-password=passwd --mysql-db=teletest --mysql-storage-engine=innodb --tables=32 --table-size=80000 --time=120 --events=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --percentile=95 oltp_read_write --forced-shutdown=0 --db-ps-mode=disable --threads=32 run
清除测试数据
sysbench --mysql-host=10.99.210.7 --mysql-port=3306 --mysql-user=root --mysql-password=passwd --mysql-db=teletest --mysql-storage-engine=innodb --tables=32 --table-size=80000 --time=120 --events=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --percentile=95 oltp_read_write --forced-shutdown=0 --db-ps-mode=disable --threads=32 cleanup
混合读写-磁盘IO型：
测试数据准备
sysbench --mysql-host=10.99.210.7 --mysql-port=3306 --mysql-user=root --mysql-password=passwd --mysql-db=teletest --mysql-storage-engine=innodb --tables=128 --table-size=800000 --time=120 --events=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --percentile=95 oltp_read_write --forced-shutdown=0 --db-ps-mode=disable --threads=32 prepare
测试
sysbench --mysql-host=10.99.210.7 --mysql-port=3306 --mysql-user=root --mysql-password=passwd --mysql-db=teletest --mysql-storage-engine=innodb --tables=128 --table-size=800000 --time=120 --events=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --percentile=95 oltp_read_write --forced-shutdown=0 --db-ps-mode=disable --threads=32 run
测试数据清除
sysbench --mysql-host=10.99.210.7 --mysql-port=3306 --mysql-user=root --mysql-password=passwd --mysql-db=teletest --mysql-storage-engine=innodb --tables=128 --table-size=800000 --time=120 --events=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --percentile=95 oltp_read_write --forced-shutdown=0 --db-ps-mode=disable --threads=32 cleanup
5 测试结果
SQL statistics:
queries performed:
read: 2778031
write: 792508
other: 397073
total: 3967612
transactions: 198045 (1650.27 per sec.) -----------tps
queries: 3967612 (33061.39 per sec.) ----------qps
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Number of unfinished transactions on forced shutdown: 1000
General statistics:
total time: 120.0060s
total number of events: 198045
Latency (ms):
min: 8.08
avg: 602.22
max: 10974.99
95th percentile: 1836.24 ---95分位延迟
sum: 119265914.92
Threads fairness:
events (avg/stddev): 199.0450/15.45
execution time (avg/stddev): 119.2659/0.75
