生产环境的数据库规划

最新推荐文章于 2024-05-06 17:15:42 发布

楼下老李

最新推荐文章于 2024-05-06 17:15:42 发布

阅读量828

点赞数

分类专栏： mysql 文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/weixin_42751024/article/details/125539462

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

生产环境的数据库规划

1.1 生产环境的数据库服务器配置

一般来说，对于我们这种Java开发者，可能并不会去关心MySQL的服务器配置什么的，大点的公司基本都是DBA给出或者运维给出的；

但是我认为对于MySQL这种组件，我们不能局限于使用，还应该去扩展一些原理、了解部署MySQL的服务器配置，和对MySQL做一些压测（知道它能承受的极限）。

那我们的生产环境一般需要用什么配置的机器来部署MySQL？

首先，如果系统是一个没有什么并发量，用户就几十几百人的一个什么配置系统；那随便你采用什么样的机器去部署，甚至你用你自己的主机去部署一个都没什么问题；（因为数据量很小、并发量很小、操作频率低）；这种就随便你怎么弄；

真实环境：

一般而言，真实环境的数据库部署时选用的机器最低在8核16G，正常在16核32G；

对于 8核16G的机器部署的MySQL，每秒抗个一两千的并发请求没有问题；对于16核32G 部署的MySQL，每秒能够抗个两三千、三四千的并发请求没有问题；

但是如果达到每秒上万的请求，那么服务器的 CPU、磁盘、IO、内存的负载这些可能会飙升很高，数据库也扛不住的（这个情况一般就会采用一些读写分离、分库分表、缓存了）

另外，对于数据库服务器而言，条件允许的话尽量采用SSD固态硬盘而不是普通的机械硬盘；因为数据库最大的消耗这些就在于大量的磁盘IO，需要大量的读写文件，所以如果使用SSD固态硬盘，那数据库每秒能扛的并发请求就会大一些；

当然这些都是一些经验值，根据这些配置的机器来部署的MySQL数据库，大致适合多高的并发访问量。

1.2 MySQL数据库压测的关注点

在大一点的公司里面，MySQL都是由DBA或运维去安装、部署和启动的，并且对于里面的一些参数进行调优后，才会给到开发者手里；

在这个时候，作为一个Java开发者，需要先对这个数据库进行一些基本的压测，知道你手里的数据库，能够抗住多大的压力；

简单来说，可以通过一些工具模拟一个系统，例如每秒发出 1000个请求到这个数据库上，观察服务器的 CPU负载、磁盘IO负载、网络IO负载、内存负载等；然后数据库能否每秒处理完这 1000 个请求；

然后再继续模拟每秒发送 2000 个请求、每秒 3000 个请求；逐步的进行测试，这个数据库在目前机器配置下，大致的负载和压力，性能表现、最多可以抗多少请求；

压测的目标就是看这个数据库在现有机器配置下，每秒可以抗下多少个请求，对应的专业名词为：QPS 和 TPS；

QPS：Query Per Second，每秒可以处理多少个请求；可以理解为，一次请求就是一条 sql 语句，也就是说这个数据库每秒可以处理多少条sql语句（或者你的Java系统每秒处理多少个请求）；
TPS：Transaction Per Second，每秒可以处理多少个事务；（这个一般都用于数据库范围），可以理解为数据库每秒可以处理多次完整的事务（因为一个事务中会包含多个SQL语句，包含提交、回滚）；

1.2.1 压测关注点 — IO相关的性能指标

IOPS：Input/Output Operations Per Second ，每秒执行的IO操作次数（读写次数）；例如 IOPS 是 1000，则就是每秒可以执行 1000 个IO读写操作；
（联想一下，怎么影响MySQL的；例如你要查找数据的时候，当数据没有在内存中，那是不是就得去磁盘中进行查找，就这是发生了磁盘IO；或者内存里面的更新后的脏数据，会由IO线程刷回到磁盘去，这也是发生了磁盘IO）
吞吐量：磁盘每秒可以读写多少字节的数据量；（那这里怎么影响MySQL的呢？例如前面说到的刷脏、或者在将 redo log buffer 中的数据写到redo log 日志中，也就是磁盘；吞吐量就会决定每秒能够写入到磁盘的数据量）
Latency：往磁盘写入一条数据的延迟；在写入一条日志到磁盘的时候，延迟是 1ms 还是 100 us，这个时间就对SQL执行的性能是有影响的；（一般而言，吞吐量越高，延迟就得越低）

1.2.2 压测关注点 — 服务器相关的性能指标

CPU负载：（CPU使用率）很重要的性能指标，表示你的 CPU 在运行时处理以及等待CPU处理的进程数之类的统计信息；
- 这里贴一个 linux 服务器中 load 和 cpu 使用过高的细节分析和排查的帖子：https://blog.csdn.net/yangbindxj/article/details/122956376
网络负载：这个主要受机器的带宽影响，也就是在压测到一定的QPS之后，每秒钟机器的网卡会输入/输出多少MB的数据；
有可能你的带宽每秒最多传输 100MB的数据，当你QPS到1000时，网卡就打满了，那也到了你压测的极限了；
内存负载：也就是看压测到一定情况下时，机器的内存消耗了多少，如果内存消耗过多，也没法继续增大压测了；

1.3 MySQL 数据库压测工具 — sysbench

这个工具可以自动帮你在数据库中构造出来大量的数据，然后可以模拟上千个线程访问你的数据库，模拟使用各种SQL 语句来访问你的数据库，还可以模拟各种事务类型进行提交什么的，甚至模拟几十万的 TPS 进行压测；

直接yum源安装 sysbench 的话有点麻烦，所以可以采用 docker 来安装：

Bash

docker search sysbench
docker pull severalnines/sysbench
docker images
docker run -it --name my-sysbench 0e71335a2211

sysbench --version # 出现版本之后即安装成功

sysbench 压测：

1.1 构造数据（prepare模式）
先创建一个测试的数据库 sysbench-db

Bash

sysbench --db-driver=mysql --time=100 --threads=10 --report-interval=1 --mysql-host=192.168.126.135 --mysql-port=3306 --mysql-user=root --mysql-password=pass1234 --mysql-db=sysbench-db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

–db-driver=mysql：基于 mysql 驱动去连接 mysql 数据库
–time=300：连续访问 100 秒
–threads=10：模拟 10 个线程并发访问
–report-interval=1：每隔 1s 进行输出压测情况
–mysql-host=192.168.126.135 –mysql-port=3306 –mysql-user=root –mysql-password=pass1234：要压测的 mysql 的 ip、port、用户名、密码
–mysql-db=sysbench-db –tables=20 –table_size=1000000：在 sysbench-db 这个数据库内，构造 20 个测试表，每隔表里构造 100w 条测试数据；（测试表的名字会类似 sbtest1、sbtest2、、、）
oltp_read_write：测试的是 OLTP 数据库的读写测试；（这里有多个模式，代表不同的压测性能）
- oltp_read_write：测试综合读写 TPS；
- oltp_read_only：测试只读性能；
- oltp_delete：测试删除性能；
- oltp_update_index：测试更新索引字段性能；
- oltp_update_non_index：测试更新非索引字段性能；
- oltp_insert：测试插入性能；
- oltp_write_only：测试写入性能；
db-ps-mode=disable：禁止 ps 模式
prepare：准备数据（构造数据），就是去创建这些测试表和数据，不是真正进行压测；
1.2 压测性能
- 压测综合读写性能：(注意最后是 run）(oltp_read_write )

sysbench --db-driver=mysql --time=100 --threads=10 --report-interval=1 --mysql-host=192.168.126.135 --mysql-port=3306 --mysql-user=root --mysql-password=pass1234 --mysql-db=sysbench-db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run

压测查询（只读）性能：(oltp_read_write)

Bash

sysbench --db-driver=mysql --time=100 --threads=10 --report-interval=1 --mysql-host=192.168.126.135 --mysql-port=3306 --mysql-user=root --mysql-password=pass1234 --mysql-db=sysbench-db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run

压测删除性能：（oltp_delete）
压测更新索引字段性能：（oltp_update_index）
压测更新非索引字段性能：（oltp_update_non_index）
压测插入性能：（oltp_insert）
压测写入性能：（oltp_write_only）
1.3 压测结果分析：
以压测综合读写性能为例：

Bash

sysbench --db-driver=mysql --time=100 --threads=10 --report-interval=1 --mysql-host=192.168.126.135 --mysql-port=3306 --mysql-user=root --mysql-password=pass1234 --mysql-db=sysbench-db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run

压测结果输出：

Bash


[1s] thds: 10 tps: 220.48 qps: 4532.31 (r/w/o: 3191.47/388.08/952.75) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[2s] thds: 10 tps: 272.30 qps: 5460.07 (r/w/o: 3823.25/449.50/1187.32) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[3s] thds: 10 tps: 235.87 qps: 4703.49 (r/w/o: 3286.25/431.77/985.47) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[4s] thds: 10 tps: 254.14 qps: 5067.78 (r/w/o: 3544.94/421.23/1101.60) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[5s] thds: 10 tps: 249.82 qps: 5027.32 (r/w/o: 3534.41/438.68/1054.23) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[6s] thds: 10 tps: 255.98 qps: 5109.56 (r/w/o: 3567.69/457.96/1083.91) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[7s] thds: 10 tps: 256.03 qps: 5138.63 (r/w/o: 3596.44/455.06/1087.13) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[8s] thds: 10 tps: 259.13 qps: 5145.49 (r/w/o: 3597.74/453.22/1094.53) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[9s] thds: 10 tps: 278.89 qps: 5598.85 (r/w/o: 3920.50/485.81/1192.54) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[10s] thds: 10 tps: 273.12 qps: 5476.32 (r/w/o: 3831.62/473.20/1171.50) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
......
[95s] thds: 10 tps: 344.25 qps: 6876.98 (r/w/o: 4808.48/696.50/1371.99) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[96s] thds: 10 tps: 345.84 qps: 6895.86 (r/w/o: 4825.80/711.68/1358.38) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[97s] thds: 10 tps: 301.88 qps: 6105.55 (r/w/o: 4284.28/602.76/1218.51) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[98s] thds: 10 tps: 338.27 qps: 6732.51 (r/w/o: 4711.86/701.49/1319.16) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[99s] thds: 10 tps: 322.74 qps: 6472.75 (r/w/o: 4532.33/680.55/1259.87) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[100s] thds: 10 tps: 327.01 qps: 6502.16 (r/w/o: 4543.11/685.02/1274.03) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.0

以 [ 5s ] 为例：

thds: 10：有 10 个线程在进行压测；
tps: 249.82：每秒执行了 249.82个事务；
qps: 5027.32：每秒可以执行 5027.32个请求；
(r/w/o: 3534.41/438.68/1054.23)：在每秒 5027.32个请求中：
- 有 3534.41 个请求是读请求；
- 有 438.68 个请求是写请求；
- 有 1054.23 个请求是其他请求；（也就是对 QPS 进行了拆解）
lat (ms,95%): 73.13：95% 的请求的延迟都在 73.13毫秒一下；
err/s: 0.00 reconn/s: 0.00：每秒有 0 个请求是失败的，发生了 0次网络重连；

这个压测结果会根据不同配置的服务器性能有较大的差距，如果服务器性能很高，那可以开很多的并发线程去压测（比如100个线程）；此时可能压测出来的 TPS有上千个；如果机器性能很低，可能 TPS才几十个；

另外在压测执行完成之后，最后会显示一个总的压测报告：

Bash

SQL statistics:
    queries performed:
        read:                            420140    // 在100s的压测期间执行了 420140 次的读请求
        write:                           56987     // 执行了 56987 次的写请求
        other:                           123073    // 执行了 123073 次的其他请求
        total:                           600200    // 一共执行了 600200 次请求
    transactions:                        30010  (299.96 per sec.)    // 一共执行了 3w 多次的事务，每秒执行 300 次事务
    queries:                             600200 (5999.14 per sec.)   // 一共执行了 6w 次的请求，每秒执行 6000 次请求
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
total time: 100.0466s
total number of events: 30010

Latency (ms):
min: 2.42 // 所有请求中最小延迟为 2.42ms
avg: 33.33 // 平均延迟为 33.33ms
max: 240.49 // 最大延迟为 240.49
95th percentile: 62.19 // 95线（95%）的延迟都在 62.19 以内
sum: 1000255.95

Threads fairness:
events (avg/stddev): 3001.0000/20.98
execution time (avg/stddev): 100.0256/0.01

这是以 –threads=20 来跑出来的压测报告：

Bash

SQL statistics:
    queries performed:
        read:                            382690
        write:                           66447
        other:                           97563
        total:                           546700
    transactions:                        27335  (273.09 per sec.)
    queries:                             546700 (5461.84 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
total time: 100.0929s
total number of events: 27335

Latency (ms):
min: 19.37
avg: 73.20
max: 203.10
95th percentile: 118.92
sum: 2001031.92

Threads fairness:
events (avg/stddev): 1366.7500/13.82
execution time (avg/stddev): 100.0516/0.02

可以看出，性能还没有 –threads=10 的时候好，说明这台服务器上MySQL的性能极限应该就是那点了，你再增加更大的并发量，它可能就扛不住，TPS 和 QPS 都会降低，延迟也会增大；

如果在增加线程数的时候，MySQL的 TPS、QPS 这些性能数据是一直在增加的，就说明还没有达到极限，你可以继续增加线程数来压测出MySQL的真实的最高负载能力，直到 TPS、QPS 上不去了；

另外，这个压测，也可以用来对比，MySQL开启和未开启 binlog 对性能的影响之类的；

1.4 观察压测过程中服务器的性能

1.4.1 为什么要关注压测过程中的服务器性能

前面说到可以通过不断提高线程数，来让MySQL承载更高的 QPS；但是这个过程需要配合对服务器的性能表现的观察来做，不能一直盲目的增加线程去进行压测；

例如，假设当前数据库抗下了 10000 的QPS，但是这个时候服务器各种指标都已经满荷运行了；如 CPU负载很高、内存使用率很高、网络带宽几乎被打满了、磁盘IO的等待时间很长等等；这种情况说明机器已经到达极致了，面临随时可能挂掉的情况；

所以这种情况下，你压测出来的 10000 QPS 是不能代表你MySQL的真实性能的；因为生产环境中不可能让服务器处于这种情况下运行，面临随时可能挂掉的风险；

所以，在压测的过程中，在不停增加 sysbench 的线程数量时，需要同时密切关注机器的 CPU、内存、磁盘、网络等的负载情况，需要保证他们在比较正常的范围内；

1.4.2 压测时如何观察服务器的性能指标

CPU负载：

一般观察CPU负载，使用的是 top 命令

Bash

top - 04:10:37 up  4:03,  3 users,  load average: 2.17, 0.53, 0.38
Tasks: 104 total,   1 running, 102 sleeping,   1 stopped,   0 zombie
%Cpu(s): 26.7 us, 65.0 sy,  0.0 ni,  0.0 id,  0.3 wa,  0.0 hi,  8.0 si,  0.0 st
KiB Mem :  1863076 total,    72520 free,   465568 used,  1324988 buff/cache
KiB Swap:  2097148 total,  1222396 free,   874752 used.  1242384 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1514 mysql 20 0 1874616 287116 3384 S 83.4 15.4 14:08.53 mysqld
27688 root 20 0 482764 8564 3140 S 14.0 0.5 0:03.11 sysbench

04:10:37 up 4:03, 3 users：现在时间 04:10:37、运行时间 4:03、3个用户在使用当前服务器
load average: 2.17, 0.53, 0.38：CPU 在一分钟内的负载为 2.17、五分钟内为 0.53、15分钟内为 0.38；
- 这个 CPU 负载，不是单纯的指 CPU 的使用率情况；而是在一段时间内 CPU 正在处理以及等待CPU处理的进程数之和（在运行队列（在CPU上运行或者等待运行的进程）中的平均进程数）
  如果是一个 4核 CPU，当 CPU负载是 0.15，说明 4核中连一个核都没有用满，4核都很空闲；
  如果CPU负载是 1 ，说明一个核被使用繁忙，另外 3个核是比较空闲的；
  如果CPU负载是 1.5 ，说明一个核已经被繁忙使用，另外一个核也在被使用但没那么繁忙，剩余2核是空闲的；
  如果CPU负载是 6，说明4核CPU被繁忙的使用都还不够处理当前的任务，还有很多进程在一直等待CPU去执行自己的任务；
- 所以，如果如果在压测过程中，发现 4核CPU的 load average已经达到了 5,6 ，说明几个CPU基本都跑满了，在满负荷运行，此时可能不能再提高线程数来增加MySQL的QPS了，否则这种长期的 CPU负载太高是不合理的；

内存负载：
- Mem : 1863076 total, 72520 free, 465568 used, 1324988 buff/cache：当前机器的内存使用情况
  - 总内存大概有 2G左右，已经使用了 450M左右，还有 70M左右是空闲的，有 1G左右的内存用作OS内核的缓存区；
  - 对于内存而言，在压测过程中也得密切关注；如果内存使用率在 70%以内，基本都能接受；但是如果在压测时，内存已经到了 80%甚至更高，就已经算是危险了；
磁盘IO情况：
使用 dstat 命令来观察磁盘IO的指标（吞吐量、IOPS这些），dstat 直接使用 yum 安装即可；具体使用：

dstat -d：

Bash

[root@localhost ~]# dstat -d
-dsk/total-
 read  writ
6577k 4564k
 126M   29M
 109M   30M
 103M   29M
 144M   29M
 135M   34M
 126M   30M
 111M   28M
 123M   29M

可以看到，磁盘的IO吞吐量是每秒读取 120M左右的数据、每秒写入 30M 左右的数据；在合理范围内，因为一般机械硬盘可以做到每秒上百M的读写数据量；

dstat -r

Bash


[root@localhost ~]# dstat -r
--io/total-
 read  writ
 145   181 
217  139
255  132 
252  135 
220  151 
227  138

这个结果是读IOPS大概为 230左右，写IOPS大概为 140左右；

一般来说，对于机械硬盘的IOPS大概是每秒 100左右；如果压测的时候IOPS已经达到了 2,300，说明磁盘负载已经太高了；

网卡流量情况：
- dstat -n

Bash

[root@localhost ~]# dstat -n
-net/total-
 recv  send
   0     0 
1327k   16M
1332k   16M
1277k   16M
1280k   16M
1346k   17M

每秒钟网卡接收到流量有 1300k 左右，每秒通过网卡发送出去流量有 16M 左右；通常来说，如果机器使用的是千兆网卡，那每秒网卡的总流量大概就在 100M 左右，甚至更低一些；

总结，在压测的时候，不是盲目扩大线程数的；而是要在服务器硬件的合理负载范围内，将MySQL的QPS提高到最大，得到一个合理的极限的QPS值；

楼下老李

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
生产环境的数据库规划

mysql生产环境配置规划
复制链接

扫一扫

专栏目录

生产环境的数据库规划

生产环境的数据库规划

1.1 生产环境的数据库服务器配置

1.2 MySQL数据库压测的关注点

1.2.1 压测关注点 — IO相关的性能指标

1.2.2 压测关注点 — 服务器相关的性能指标

1.3 MySQL 数据库压测工具 — sysbench

sysbench 压测：

1.1 构造数据（prepare模式）

1.2 压测性能

1.3 压测结果分析：

1.4 观察压测过程中服务器的性能

1.4.1 为什么要关注压测过程中的服务器性能

1.4.2 压测时如何观察服务器的性能指标

“相关推荐”对你有帮助么？