mysql的线上环境硬件配置推荐、性能压测方法以及结果分析

一、硬件配置与所能支持并发量

一般我们说的负载,主要居于单台机器只部署应用或者mysql的情况,常常发现有些人在一台机器上部署了很多的应用,包括数据库也放在一起,发现某个应用配置完全没问题,但偶尔会有卡顿的情况,实际可能被其他应用占用了资源导致。

1、通常情况下,线上环境应用部署在4核8G的机器上,而数据库应部署在8核16G或者16核32G,正常情况下单节点应用服务可支撑500左右的并发,当然还要根据请求的处理时长来计算,比如接口请求响应过慢就会导致并发降低

2、在一个系统中,往往压力会集中在数据库上,因为请求一般最终会落到数据库上,而数据库需要在内存以及磁盘上进行大量的IO操作。一般来说8核16G的机器部署mysql数据库可以扛住一两千的并发,如果并发量再高些比如几千,就会造成数据库CPU、磁盘、IO、内存等负载过高,严重会引起宕机

3、16核32G的机器,一般可以扛住两三千的并发、甚至到三四千也都可以,具体还是有要看sql的复杂度等,对于数据库而已,一般采用的SSD固态硬盘,因为数据操作需要大量的磁盘IO,SSD的IO会相对大很多。

二、如何对即将投入生产环境的数据库进行性能测试

在一个数据库即将投入生产环境前,一般都需要进行一个基准压测,观察系统在不同压力下CPU/磁盘IO/网络/内存等的变化情况,心里大概了解到数据库可以扛住多大的压力,瓶颈在哪里。

1、mysql的性能测试一般关注的指标有:

A、IOPS:机器的随机IO并发处理能力,比如机器可达到的200IOPS,说明每秒可以执行200个随机读写请求

B、吞吐量:单位时间内的可以处理的事务数,也即TPS,也可以看存储每秒可以读写的数据量

C、响应时间/延迟:测试一个任务执行所需的时间,比如写一条日志到磁盘文件,所需要花费的时间

2、mysql的压测工具有:

A、mysqlslap:可以模拟服务器的负载,并输出计时信息,测试时可以指定并发数、sql语句,如果没指定,可以自动生成schema的select语句

B、sysbench:一款多线程压测工具,可以根据影响数据库性能的因素来评估系统性能、可以执行多种类型的基准测试,比如可以提供cpu测试、磁盘io测试、内存访问测试、线程调度测试、数据库oltp测试

三、sysbench的安装、说明、压测和指标分析

1、下载并安装

下载:curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

安装:yum -y install sysbench

查看是否安装成功:sysbench --version

2、sysbench主要配置参数说明(prepare run cleanup help)

输#入sysbench --help可获取参数说明,这里只列举常用的:

#General options(通用配置):

--threads=10 #使用的线程数,默认为1

--events=N #限制事件总数,默认为0

--time=N #总执行时间限制,默认10s,也就是说连续访问10s

--forced-shutdown=STRING #在--time参数限制之后等待多少秒关闭执行,默认是off关闭状态

--thread-stack-size=SIZE #每个线程的栈大小,默认64K

--rate=N #事务平均速率,默认0,不限制速率

--report-interval=N #多久输出一次压测结果,默认0,中间不输出,常常设置为1,一秒钟输出一次

--report-checkpoints=[LIST,...] #转储完整的统计信息,并在指定时间点重置计数器,在报告检查点是从测试开始到检查点所花的时间,默认是关闭的

--debug[=on|off] #打印的日志级别设置为debug,默认是off关闭

--validate[=on|off] #在可能的情况下执行校验,默认是off不校验

#Log options(日志选项):

--verbosity=N #打印的日志级别,5-0(调试信息-只打印基本信息),默认是3

--percentile=N #计算延迟统计中的百分比,可使用0来禁用百分位,默认是95

--histogram[=on|off] #在报告中打印延迟直方图,默认off关闭

#General database options(通用数据库选项):

--db-driver=STRING #指定数据库驱动,默认是mysql,可设置为pgsql(PostgreSQL)

--db-ps-mode=STRING #ps模式,有 {auto, disable}两种,默认 auto

--db-debug[=on|off] #打印特定数据库的调试信息,默认off关闭

#mysql options:

--mysql-host=[LIST,...] #MySQL 服务器,默认localhost

--mysql-port=[LIST,...] #MySQL 端口,默认3306

--mysql-socket=[LIST,...] #MySQL socket

--mysql-user=STRING #MySQL 用户名

--mysql-password=STRING #MySQL 密码

--mysql-db=STRING #MySQL 数据库名

--tables=N #表数量

--mysql-ssl[=on|off] #使用安全SSL连接,如果客户端库中可用,默认是off关闭

--mysql-ssl-cipher=STRING #为SSL连接使用特定的密码

--mysql-compression[=on|off] #如果在客户端库中可用,则使用压缩,默认off关闭

--mysql-debug[=on|off] #跟踪所有客户端库的调用,默认off关闭

--mysql-ignore-errors=[LIST,...] #忽略的错误列表,或“全部”1213,1020,1205

--mysql-dry-run[=on|off] #干式运行,假设所有MySQL客户端API调用都是成功的,而不需要执行它们,默认off关闭

#输入sysbench /usr/share/sysbench/oltp_common.lua help获取oltp相关命令,oltp_common.lua options:

--auto_inc[=on|off] Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]

--create_secondary[=on|off] #除主键外,可用设置一个二级索引 ,默认on开启

--delete_inserts=N #每个事务的删除/插入组合数,默认是1

--distinct_ranges=N #每个事务中不同的查询数量,默认是1

--index_updates=N #每个事务更新索引查询的数量,默认是1

--mysql_storage_engine=STRING #mysql的存储引擎,默认是innodb

--non_index_updates=N #每个事务更新非索引查询的数量,默认是1

--order_ranges=N #每个事务的查询排序数,默认是1

--point_selects=N #每个事务的select查询点数量,默认是10

--range_selects[=on|off] #启用/禁用所有范围选择查询,默认是on开启

--range_size=N #选择范围查询,默认100

--secondary[=on|off] #使用一个二级索引代替主键,默认是off关闭

--simple_ranges=N #每个事务的简单范围查询的数量,默认是1

--skip_trx[=on|off] #不开启显示事务,在自动提交模式下执行所有查询,默认是off关闭

--sum_ranges=N #每个事务中select sum()的查询数

--table_size=N #每个表多少行数据,默认10000

--tables=N #表数量,默认1

3、对mysql数据库进行性能压测测试

A、准备测试数据:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=192.168.198.130 
--mysql-port=3306 --mysql-user=root --mysql-password=root123 --mysql-db=test --tables=20 
--table-size=1000000 oltp_read_write --db-ps-mode=disable prepare

其中 oltp_read_write 标识执行oltp数据库的读写模式,最后的prepare表示按配置去构造测试数据,创建20个表,每个表数据100w条数据

执行以上命令后,可以在数据库中看到生成了20个名为sbtest1、sbtest2、sbtest3。。。,如图所示(执行过程较慢,之间可能会看到只有10个表,是因为只有10个线程在执行)

执行过程中连接的mysql8以上的版本,可能会出现“2059-Authentication plugin 'caching_sha2_password' cannot be loaded”的错误,修改用户验证方式即可

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root123'; # 修改密码

flush privileges;

B、测试数据库的综合读写TPS,使用的仍然是oltp_read_write模式,其中prepare修改为run即可,压测时间300秒

sysbench  --db-driver=mysql --time=300  --threads=10 --report-interval=1 --mysql-host=192.168.198.130  
--mysql-port=3306  --mysql-user=root --mysql-password=root123 --mysql-db=test --tables=20  
--table-size=1000000 oltp_read_write --db-ps-mode=disable run

 压测过程中每秒输出结果说明:

[198s] thds: 10 tps: 27.03 qps: 594.57 (r/w/o: 416.40/124.12/54.05) 
lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
[198s] #表示在198s时输出的压测统计报告
thds #10 表示有10个线程在压测
tps #27.03 表示tps是27.03,也就是每秒钟可以执行了大概27个事务
qps #594.57 表示每秒可以执行大概594个请求
(r/w/o: 416.40/124.12/54.05) #qps为594.57中有416.40个读请求、124.12个写请求,54.05个其他请求
 lat (ms,95%) #450.77 表示95%的请求延迟在450.77ms以下
 err/s: 0.00 #表示没有请求失败的情况
 reconn/s: 0.00 #表示没有发生过数据库重连的情况

压测结束后会有一个总报告: 

SQL statistics:
    queries performed:
        read:                        143416      #执行了143416个读请求
        write:                       40976       #执行了40976个写请求
        other:                       20488       #执行了20488个其他请求
        total:                       204880      #总共执行了204880个请求
    transactions:                    10244  (34.06 per sec.)    #执行了10244个事务,平均每秒34.06
    queries:                         204880 (681.12 per sec.)   #总共执行查询数为204880,平均每秒681.12
    ignored errors:                  0      (0.00 per sec.)     #忽略的错误是0个,也就是没有发生错误
    reconnects:                      0      (0.00 per sec.)     #重连次数为0,没有发生过数据库连接断开重连的情况

General statistics:
    total time:                      300.7942s            #总耗时300.7942s
    total number of events:          10244          #总事件数10244

Latency (ms):
         min:                               42.45        #最小延迟42.45
         avg:                              293.21       #平均延迟293.21
         max:                              1749.36     #最大延迟1749.36
         95th percentile:                  467.30  #95%的请求延迟在467.30以下
         sum:                              3003607.90   #总共压测时间3003607.90s

Threads fairness:  #线程稳定性
    events (avg/stddev):           1024.4000/27.12  #事件(平均值/偏差)  
    execution time (avg/stddev):   300.3608/0.21  #执行时间(平均值/偏差)

C、测试数据库其他性能,都只修改上述语句的oltp模式即可,如下:

oltp_read_only #测试只读性能

oltp_delete #测试数据库的删除性能

oltp_update_index #测试更新索引字段性能

oltp_update_non_index #测试更新非索引字段性能

oltp_insert #测试插入性能

oltp_write_only #测试写入性能

4、每轮测试完成后需要重启mysql实例、删除系统cache使用swap

sync #将脏数据刷盘
echo 3 >/proc/sys/vm/drop_caches  #清除系统cache
swapoff -a && swapon -a #清除swap缓存

四、压测过程中CPU/磁盘IO/网络/内存等的变化观测和分析

比如当每秒有2000qps时,CPU、内存、网络、磁盘IO等都在正常范围,只是负载稍微高一些,可以认为当前数据可以扛住2000qps。当发现此时CPU已经满载、内存使用率特别高、网络带宽基本打满或者磁盘IO等待较长时,说明机器已经达到极限了,需要降低qps重测

1、CPU/内存负载分析

通过top命令,在开头位置的统计说明(内存负载也可使用free -m单独统计)

top - 20:34:47 up 225 days, 11:12,  1 user,  load average: 1.15, 0.70, 0.59
Tasks: 117 total,   1 running, 116 sleeping,   0 stopped,   0 zombie
%Cpu(s):  5.2 us,  2.5 sy,  0.0 ni, 91.9 id,  0.3 wa,  0.0 hi,  0.1 si,  0.0 st
KiB Mem : 16267072 total,   989904 free,  8880320 used,  6396848 buff/cache
KiB Swap:        0 total,        0 free,        0 used.  7015684 avail Mem

20:34:47 #指的当前时间
up 225 days, 11:12 #机器已经运行了225天11个小时12分钟
1 user #当前1个用户在使用
load average: 1.15, 0.70, 0.59 
#机器在1分钟、5分钟、15分钟内的平均负载,假如CPU是4核的,1.15表示一分钟内只用满了1个核,另外一个核也使用了一些,
#假如是4,表示4个核都跑满了,如果大于4,则表示还有很多进程的任务在等待CPU中
Tasks: 117 total,   1 running, 116 sleeping,   0 stopped,   0 zombie 
#总共117个进程,其中有1个在执行,116个在睡眠,0个停止,0个僵死
%Cpu(s):  5.2 us,  2.5 sy,  0.0 ni, 91.9 id,  0.3 wa,  0.0 hi,  0.1 si,  0.0 st 
#用户空间占用CPU为5.2%,内核空间CPU占比2.5%,用户空间内改变过优先级的进程占用CPU比例为0.0%,空闲CPU占比91.9%,
#IO等待占用CPU为0.3%,硬中断占用CPU为0.0%,软中断占用CPU为0.1%,0.0%待研究
KiB Mem : 16267072 total,   989904 free,  8880320 used,  6396848 buff/cache  
#总物理内存为16267072KB,989904KB是空闲的,8880320KB已被使用,6396848KB用作内核缓存
KiB Swap:        0 total,        0 free,        0 used.  7015684 avail Mem  
#交换区总览0KB,空闲交换区总量0KB,已使用交换区0KB,可用内存7015684KB

2、磁盘IO负载分析

#使用dstat -d命令(需要安装dstat   yum install dstat)
-dsk/total-
 read  writ
 567B  317k
#存储的每秒读567B,写317KB。普通的机械磁盘都可以做到每秒上百MB

#使用dstat -r
--io/total-
 read  writ
0.02  24.8 
#IOPS(随机磁盘读写),每秒读0.02次,每秒写24.8次,一般来说随机磁盘读写每秒两三百次都是可以承受

不同磁盘的读写能力有很大的区别,比如机械硬盘以及固态硬盘,对于磁盘IO,需要关注磁盘的读写极限,在压测的时候关注磁盘读写数据量是否达到极限,或者随机读写次数是否达到极限。

3、观察网卡的流量情况

#使用dstat -n命令
-net/total-
 recv  send
  44k   72k
#每秒接受的数据量为44KB,发送数据量为72KB

有的时候发现qps上不去,往往有可能是网卡流量达到极限值或者带宽限制了,比如千兆网卡,每秒网卡的总流量也就100MB左右甚至更低,因为数据传输总量可能会受到主板PCI总线等相关因素的影响,或者有时使用的云服务商的服务器,选用的带宽是固定带宽,如果并发较大也会受到带宽的限制。

本模块内容主要是居于 儒猿技术窝的《从零开始带你成为MySQL实战优化高手》专栏的学习笔记,更多详细内容可扫下面二维码查看~

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页