本文分享自天翼云开发者社区《弹性云主机mysql数据性能测试》，作者：高淑杰
0 准备工作
创建两台 ECS 虚机，两台虚机在同一个 vpc 下：
server：安装 mysql
client：安装 sysbench
1 mysql 安装
发布文章1 .依赖安装yum -y install libaioyum -y install net-tools2. 安装mysqlwget 'https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpmrpm -Uvh mysql57-community-release-el7-11.noarch.rpmrpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022yum install -y mysql-community-server3.修改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-reload4.启动mysql服务systemctl start mysqldsystemctl status mysqld5 编辑/etc/my.cnf 修改mysql配置信息#datadir=/var/lib/mysql datadir=/data/mysql ----修改数据目录#socket=/var/lib/mysql/mysql.socksocket=/data/mysql/mysql.sock --创建socket# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0skip_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 重启mysqlsystemctl restart mysqld2 sysbench 安装
1 安装sysbenchsudo yum install gcc gcc-c++ autoconf automake make libtool mysql-devel git mysqlgit clone https://github.com/akopytov/sysbench.gitcd sysbenchgit checkout 1.0.18./autogen.sh
./configure --prefix=/usr --mandir=/usr/share/manmakemake install2 修改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 65535root hard nofile 65535root soft nofile 65535* soft nproc 65535* hard nproc 65535root soft nproc 65535root hard nproc 65535* soft core unlimited* hard core unlimitedroot soft core unlimitedroot hard core unlimitedEOF3 测试 client 与 server 连通性
1 配置ecs 安全组，允许外部访问mysql服务端口2 测试client与mysql server 访问连通性[root@test-client test]# telnet 10.99.210.7 3306Trying 10.99.210.7...Connected to 10.99.210.7.Escape character is '^]'.N5.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: 1000General statistics: total time: 120.0060s total number of events: 198045Latency (ms): min: 8.08 avg: 602.22 max: 10974.99 95th percentile: 1836.24 ---95分位延迟 sum: 119265914.92Threads fairness: events (avg/stddev): 199.0450/15.45 execution time (avg/stddev): 119.2659/0.75
*博客内容为网友个人发布，仅代表博主个人观点，如有侵权请联系工作人员删除。
