弹性云主机mysql数据性能测试

天翼云开发者社区 6天前

本文分享自天翼云开发者社区《弹性云主机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复制