规划生产环境下的数据库

生产数据库的配置

普通的Java应用系统部署在机器上能抗多少并发？

高并发场景下，数据库应该用什么样的机器

IO相关的压测

压测性能指标

数据库压测工具

1. 在linux上安装sysbench工具

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
sysbench --version

2. 数据库压测的测试用例
在数据库里创建一个测试库，叫做test_db，同时创建好对应的测试账号，可以叫做test_user，密码也是test_user，让这个用户有权限可以访问test_db。 然后我们将要基于sysbench构建20个测试表，每个表里有100万条数据，接着使用10个并发线程去对这个数据库发起访问，连续访问5分钟，也就是300秒，然后对其进行压力测试。

3. 基于sysbench构造测试表和测试数据

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

参数
• -db-driver=mysql：基于mysql的驱动去连接mysql数据库
• -time=300：连续访问300秒
• -threads=10：用10个线程模拟并发访问
• -report-interval=1：每隔1秒输出一下压测情况
• -mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user：连接到哪台机器的哪个端口上的MySQL库，他的用户名和密码是什么
• -mysql-db=test_db --tables=20 --table_size=1000000：在test_db这个库里，构造20个测试表，每个测试表里构造100万条测试数据，测试表的名字会是类似于sbtest1，sbtest2
• oltp_read_write：执行oltp数据库的读写测试
• -db-ps-mode=disable：禁止ps模式

4. 对数据库进行360度的全方位测试
• 测试数据库的综合读写TPS，使用的是oltp_read_write模式

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_read_write --db-ps-mode=disable run

• 测试数据库的只读性能，使用的是oltp_read_only模式

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_read_only --db-ps-mode=disable run

• 测试数据库的删除性能，使用的是oltp_delete模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_delete --db-ps-mode=disable run

• 测试数据库的更新索引字段的性能，使用的是oltp_update_index模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_update_index --db-ps-mode=disable run

• 测试数据库的更新非索引字段的性能，使用的是oltp_update_non_index模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_update_non_index --db-ps-mode=disable run

• 测试数据库的更新非索引字段的性能，使用的是oltp_update_non_index模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_update_non_index --db-ps-mode=disable run

• 测试数据库的插入性能，使用的是oltp_insert模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_insert --db-ps-mode=disable run

• 测试数据库的写入性能，使用的是oltp_write_only模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_write_only --db-ps-mode=disable run

 
• 执行下面的cleanup命令，清理数据

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=
3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --
table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

5. 压测结果分析
按照上面的命令，每隔1秒都会输出一次压测报告的，此时每隔一秒会输出类似下面的一段东西：

[ 22s ] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s:
0.00

首先说的这是第22s输出的一段压测统计报告，然后是其他的一些统计字段：
• thds: 10，有10个线程在压测
• tps: 380.99，每秒执行了380.99个事务
• qps: 7610.20，每秒可以执行7610.20个请求
• (r/w/o: 5132.99/1155.86/1321.35)，在每秒7610.20个请求中，有5132.99个请求是读请求，1155.86个请求是写请求，1321.35个请求是其他的请求，就是对QPS进行了拆解
• lat (ms, 95%): 21.33，95%的请求的延迟都在21.33毫秒以下
• err/s: 0.00 reconn/s: 0.00，每秒有0个请求是失败的，发生了0次网络重连
在完成压测之后，最后会显示一个总的压测报告:

SQL statistics:
queries performed:
	read: 1480084 // 在300s的压测期间执行了148万多次的读请求
	write: 298457 // 在压测期间执行了29万多次的写请求
	other: 325436 // 在压测期间执行了30万多次的其他请求
	total: 2103977 // 一共执行了210万多次的请求
transactions: 105180( 350.6 per sec. )// 一共执行了10万多个事务，每秒执行350多个事务
queries: 2103977 ( 7013.26 per sec. )//一共执行了210万多次的请求，每秒执行7000+请求
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

// 下面就是说，一共执行了300s的压测，执行了10万+的事务
General staticstics:
	total time: 300.0052s
	total number of events: 105180

Latency (ms):
	min: 4.32 // 请求中延迟最小的是4.32ms
	avg: 13.42 // 所有请求平均延迟是13.42ms
	max: 45.56 // 延迟最大的请求是45.56ms
	95th percentile: 21.33 // 95%的请求延迟都在21.33ms以内

数据库的压测中，观察机器性能

• 为什么在不停增加线程数量的时候，要密切关注机器性能？
如果一直不停的在压测过程中增加sysbench的线程数量，然后数据库此时勉强抗到了每秒5000的QPS了，但是这个时候发现机器的CPU已经满负荷运行了，内存使用率特别高，内存都快要不够了，网络带宽几乎被打满，磁盘IO的等待时间特别长，这个时候说明机器已经到了极致了，再搞下去，机器都快挂了。 这个时候压测出来的5000 QPS是没什么代表性的，因为在生产环境根本不可能让数据库抗下这么高的QPS，因为到这么高的QPS就说明数据库几乎已经快要挂掉了，这是不现实的。
 

• 压测时如何观察机器的CPU负载情况？
top命令监测linux机器性能

top - 15:52:00 up 42:35, 1 user, load average: 0.15, 0.05, 0.01

这行信息是最直观可以看到机器的cpu负载情况的，首先15:52:00指的是当前时间，up 42:35指的是机器已经运行了多长时间，1 user就是说当前机器有1个用户在使用。 最重要的是load average: 0.15, 0.05, 0.01这行信息，他说的是CPU在1分钟、5分钟、15分钟内的负载情况
 
CPU负载
如果CPU负载是1，那说明4核CPU中有一个核已经被使用的比较繁忙了，另外3个核还是比较空闲一些。
 
要是CPU负载是1.5，说明有一个核被使用繁忙，另外一个核也在使用，但是没那么繁忙，还有2个核可能还是空闲的。
如果你的CPU负载是4，那说明4核CPU都被跑满了，如果你的CPU负载是6，那说明4核CPU被繁忙的使用还不够处理当前的任务，很多进程可能一直在等待CPU去执行自己的任务。
 
是如果压测的过程中，发现4核CPU的load average已经基本达到3.5，4了，那么说明几个CPU基本都跑满了，在满负荷运转，那么此时你就不要再继续提高线程的数量和增加数据库的QPS了，否则CPU负载太高是不合理的。

• 观察机器的内存负载情况
在你执行top命令之后，中间我们跳过几行内容，可以看到如下一行内容：

Mem: 33554432k total, 20971520k used, 12268339 free, 307200k buffers

这里说的就是当前机器的内存使用情况，这个其实很简单，明显可以看出来就是总内存大概有32GB，已经使用了20GB左右的 内存，还有10多G的内存是空闲的，然后有大概300MB左右的内存用作OS内核的缓冲区。
对于内存而言，同样是要在压测的过程中紧密的观察，一般来说，如果内存的使用率在80%以内，基本都还能接受，在正常范 围内，但是如果你的机器的内存使用率到了70%~80%了，就说明有点危险了，此时就不要继续增加压测的线程数量和QPS 了，差不多就可以了。
 

• 观察机器的磁盘IO情况
使用dstat -d命令

-dsk/total -
read writ
103k 211k
0 11k

存储的IO吞吐量是每秒钟读取103kb的数据，每秒写入211kb的数据，像这个存储IO吞吐量基本上都不算多的，因为普通的机械硬盘都可以做到每秒钟上百MB的读写数据量。
使用命令：dstat -r

--io/totalread
writ
0.25 31.9
0 253
0 39.0

读IOPS和写IOPS分别是多少，也就是说随机磁盘读取每秒钟多少次，随机磁盘写入每秒钟执行多少次，一般来说，随机磁盘读写每秒在两三百次都是可以承受的。 所以在这里，我们就需要在压测的时候密切观察机器的磁盘IO情况，如果磁盘IO吞吐量已经太高了，都达到极限的每秒上百MB了，或者随机磁盘读写每秒都到极限的两三百次了，此时就不要继续增加线程数量了，否则磁盘IO负载就太高了

• 观察网卡的流量情况
dstat -n 命令

-net/totalrecv
send
16k 17k

每秒钟网卡接收到流量有多少kb，每秒钟通过网卡发送出去的流量有多少kb，通常来说，如果机器使用的是千兆网卡，那么每秒钟网卡的总流量也就在100MB左右，甚至更低一些。 所以在压测的时候也得观察好网卡的流量情况，如果网卡传输流量已经到了极限值了，那么此时再怎么提高sysbench线程数量，数据库的QPS也上不去了，因为这台机器每秒钟无法通过网卡传输更多的数据了。
 

数据库可视化监控平台

基于Prometheus+Grafana来搭建生产环境数据库的可视化监控平台
Prometheus其实就是一个监控数据采集和存储系统，他可以利用监控数据采集组件（比如mysql_exporter）从你指定的MySQL数据库中采集他需要的监控数据，然后他自己有一个时序数据库，他会把采集到的监控数据放入自己的时序数据库中，其实本质就是存储在磁盘文件里
采集到了MySQL的监控数据还不够，现在我们还要去看这些数据组成的一些报表，所以此时就需要使用Grafana了，Grafana就是一个可视化的监控数据展示系统，他可以把Prometheus采集到的大量的MySQL监控数据展示成各种精美的报表，让我们可以直观的看到MySQL的监控情况。 其实不光是对数据库监控可以采用Prometheus+Grafana的组合，对你开发出来的各种Java系统、中间件系统，都可以使用这套组合去进行可视化的监控，无非就是让Prometheus去采集你的监控数据，然后用Grafana展示成报表而已
 

安装和启动Prometheus

http://cactifans.hi-www.com/prometheus/ 下载到下面两个压缩包，这里prometheus就是用来部署监控系统自己的，然后node_exporter是用来采集MySQL数据库所在机器的CPU、内存、网络、磁盘之类的监控数据的：prometheus-2.1.0.linux-amd64.tar.gz , node_exporter-0.15.2.linux-amd64.tar.gz
下载第三个压缩包：mysqld_exporter-0.10.0.linux-amd64.tar.gz，这个mysqld_exporter就是用来采集MySQL数据库自己的一些监控数据的，比如SQL性能、连接数量之类的：

https://github.com/prometheus/mysqld_exporter/releases/download/v0.10.0/mysqld_exporter-0.10.0.linuxamd64.
tar.gz
mkdir /data
mkdir /root
tar xvf prometheus-2.1.0.linux-amd64.tar -C /data
tar xf node_exporter-0.15.2.linux-amd64.tar -C /root
tar xf mysqld_exporter-0.10.0.linux-amd64.tar.gz -C /root
cd /data
mv prometheus-2.1.0.linux-amd64/ prometheus
cd /prometheus

接下来修改prometheus的配置文件:vi prometheus.yml，，其实主要是在scrape_configs下面加入一大段自定义的配置，因为需要去采集MySQL数据库本身和MySQL所在机器的监控数据：

scrape_configs:
- file_sd_configs:
-files:
- host.yml
job_name: Host
metrics_path: /metrics
relabel_configs:
- source_labels: [__address__]
regex: (.*)
target_label: instance
replacement: $1
- source_labels: [__address__]
regex: (.*)
target_label: __address__
replacement: $1:9100
- file_sd_configs:
- files:
- mysql.yml
job_name: MySQL
metrics_path: /metrics
relabel_configs:
- source_labels: [__address__]
regex: (.*)
target_label: instance
replacement: $1
- source_labels: [__address__]
regex: (.*)
target_label: __address__
replacement: $1:9104
- job_name: prometheus
static_configs:
- targets:
- localhost: 9090

在/data/prometheus目录中，去执行启动命令/data/prometheus/prometheus --storage.tsdb.retention=30d &
这里的30d是说你的监控数据保留30天的。启动之后，就可以在浏览器中访问9090端口号去查看prometheus的主页了。

部署Grafana

下载grafana-4.6.3.linux-x64.tar.gz，然后一步一步的执行下面的命令，完成他的启动

https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana-4.6.3.linux-x64.tar.gz
tar xf grafana-4.6.3.linux-x64.tar.gz -C /data/prometheus
cd /data/prometheus
mv grafana-4.6.3 grafana
cd /data/prometheus/grafana
./bin/grafana-server &

接着就完成了grafana的启动，然后可以通过浏览器访问3000端口，默认的用户名和密码是admin/admin。接着在Grafana左侧菜单栏里有一个Data Sources，点击里面的一个按钮是Add data source，就是添加一个数据源。
在界面里输入你的数据源的名字是Prometheus，类型是Prometheus，HTTP URL地址是http://127.0.0.1:9090，其他的都用默认的配置就行了，接下来Grafana就会自动从Prometheus里获取监控数据和展示了。 接着需要安装Grafana的仪表盘组件，首先需要下载grafana-dashboards-1.6.1.tar.gz，用如下的链接即可： https://github.com/percona/grafana-dashboards/archive/v1.6.1.tar.gz。 接着执行一系列的命令去安装grafana-dashboard组件。

tar xvf grafana-dashboards-1.6.1.tar.gz
cd grafana-dashboards-1.6.1
updatedb
locate json |grep dashboards/

这个时候会看到一大堆的json文件，就是各种不同的仪表盘对应的json配置文件，你可以把这些json配置文件通过WinSCP之类的工具从linux机器上拖到你的windows电脑上来，因为需要通过浏览器上传他们。 接着在grafana页面中，可以看到最上面有一个Home按钮，点击一下进入一个界面，你会看到一个Import Dashboard的按钮，就是说可以导入一些仪表盘，这个时候就是要导入刚才看到的一大堆的json文件。
你点击Upload json file按钮，就会出现一个界面让你上传一个一个的json文件，然后你就依次上传，接着grafana中就会出现一大堆的仪表盘了，比如机器的CPU使用率的仪表盘，磁盘性能仪表盘，磁盘空间仪表盘，MySQL监控仪表盘，等等。

添加MySQL所在机器的监控

在MySQL机器上解压缩和启动node_exporter，这启动之后是个linux进程，他会自动采集这台linux机器上的CPU、磁盘、内存、网络之类的各种监控数据，其实本质你可以理解为通过我们之前讲解的那些linux命令，就可以采集到一切你想要的linux机器的监控数据。

tar xf node_exporter-0.15.2.linux-amd64.tar
mv node_exporter-0.15.2.linux-amd64 node_exporter
cd node_exporter
nohup ./node_exporter &

到这一步为止，我们就在MySQL所在的机器上启动了一个node_exporter了，他就会自动采集这台机器的CPU、磁盘、内存、网络的监控数据，但是此时还不够，因为Prometheus上还没加入对这台机器的监控。 此时我们应该还记得，之前在Prometheus的yml配置文件中，我们已经定义了一个host监控项，他就是用来监控机器的，他的配置文件是host.yml，此时我们可以编辑一下这个host.yml配置文件，加入mysql所在机器的地址就可以了

vi host.yml
- labels:
service: test
targets:
- 127.0.0.1

接着Prometheus就会跟MySQL机器上部署的node_exporter进行通信，源源不断的获取到这台机器的监控数据，写入自己的 时序数据库中进行存储。接着我们就可以打开Grafana的页面，此时你就可以看到这台机器的相关性能监控了
 

添加MySQL数据库的监控

接着我们同样需要在MySQL所在机器上再启动一个mysqld_exporter的组件，他负责去采集MySQL数据库自己的一些监控数据，我们看下面的指令就可以了。

tar xf mysqld_exporter-0.10.0.linux-amd64.tar
mv mysqld_exporter-0.10.0.linux-amd64 mysqld_exporter

接着需要配置一些环境变量，去设置mysqld_exporter要监控的数据库的地址信息，看下面配置了账号、密码以及地址和端口号

export DATA_SOURCE_NAME='root:root@(127.0.0.1:3306)/'
echo "export DATA_SOURCE_NAME='root:root@(127.0.0.1:3306)/'" >> /etc/profile

接着启动mysqld_exporter

cd mysqld_exporter
nohup ./mysqld_exporter --collect.info_schema.processlist --collect.info_schema.innodb_tablespaces --
collect.info_schema.innodb_metrics --collect.perf_schema.tableiowaits --collect.perf_schema.indexiowaits --
collect.perf_schema.tablelocks --collect.engine_innodb_status --collect.perf_schema.file_events --
collect.info_schema.processlist --collect.binlog_size --collect.info_schema.clientstats --
collect.perf_schema.eventswaits &

上面的启动命令指定了大量的选项去开启一些监控的采集。 接着这个mysqld_exporter进程就会自动采集MySQL自己的监控数据了，然后我们还需要在Prometheus里配置一下他去跟 mysqld_exporter通信获取数据以及存储，然后Grafana才能看到对应的报表。

vi /data/prometheus/mysql.yml
- labels:
service: mysql_test
targets:
- 127.0.0.1

接着我们在Grafana中就可以看到MySQL的各种监控数据了。
 

RAID存储架构

MySQL数据库底层用磁盘来存储数据，基于内存来提升数据读写性能，然后设计了复杂的数据模型，帮助我们高效的存储和管理数据。
MySQL运行过程中，他需要使用CPU、内存、磁盘和网卡这些硬件，但是不能直接使用，都是通过调用操作系统提供的接口，依托于操作系统来使用和运行的，然后linux操作系统负责操作底层的硬件。



很多数据库部署在机器上的时候，存储都是搭建的RAID存储架构,RAID就是一个磁盘冗余阵列, 用来管理机器里的多块磁盘的一种磁盘阵列技术。



有了RAID这种多磁盘阵列技术之后，可以在一台服务器里加多块磁盘，扩大磁盘存储空间。往磁盘里写数据的时候，通过RAID技术可以帮助选择一块磁盘写入，在读取数据的时候，我们也知道从哪块磁盘去读取。
除此之外，RAID技术很重要的一个作用，就是他还可以实现数据冗余机制。同样一份数据，在两块磁盘上都写入的，这样可以让两块磁盘上的数据一样，作为冗余备份，然后当一块磁盘坏掉的时候，可以从另外一块磁盘读取冗余数据出来，这一切都是RAID技术自动管理的。
RAID还可以分成不同的技术方案，比如RAID 0、RAID 1、RAID 0+1、RAID2，等等，一直到RAID 10，很多种不同的多磁盘管理技术方案。
 

RAID存储架构的电池充放电原理

服务器使用多块磁盘组成的RAID阵列的时候，一般会有一个RAID卡，这个RAID卡是带有一个缓存的，这个缓存不是直接用服务器的主内存的那种模式，它是一种跟内存类似的SDRAM。
然后我们可以把RAID的缓存模式设置为write back，这样的话，所有写入到磁盘阵列的数据，先会缓存在RAID卡的缓存里，后续慢慢再写入到磁盘阵列里去，这种写缓冲机制，可以大幅度提升我们的数据库磁盘写的性能。
RAID卡一般都配置有自己独立的锂电池或者是电容，如果服务器突然断电，RAID卡自己是基于锂电池来供电运行的，然后它会把缓存里的数据写入到阵列中的磁盘上 去。
但是锂电池是存在性能衰减问题的，所以一般来说锂电池都是要配置定时充放电的，也就是说每隔30天~90天（不同的锂电池厂商是不一样的），就会自动对锂电池充放电一次，这可以延长锂电池的寿命和校准电池容量。
在锂电池充放电的过程中，RAID的缓存级别会从write back变成write through，我们通过RAID写数据的时候，IO就直接写磁盘了，如果写内存的话，性能也就是0.1ms这个级别，但是直接写磁盘，就性能退化10倍到毫秒级 导致数据库服务器的RAID存储定期的性能出现几十倍的抖动，间接导致数据库每隔一段时间就会出现性能几十倍的抖动！

RAID 10

所谓的RAID 10，就是RAID 0 + RAID 1组合起来，就是说当时生产环境的服务器部署，我们有6块磁盘组成了一个RAID 10的阵列，那么其实就是每2块磁盘组成一个RAID 1互为镜像的架构，存放的数据是冗余一样的，一共有3组RAID 1，然后对于每一组RAID 1写入数据的时候，是用RAID 0的思路，就是不同组的磁盘的数据是不一样的，但是同一组内的两块磁盘的数据是冗余一致的
于这样的一个使用了RAID 10架构的服务器，他必然内部是有一个锂电池的，然后这个锂电池的厂商设定的默认是30天进行一次充放电，每次锂电池充放电就会导致RAID写入时不经过缓存，性能会急剧下降，所以我们发现线上数据库每隔30天就会有一次剧烈性能抖动，数据库性能下降了10倍。

充放电导致线上数据库的性能定期抖动

使用linux命令查看了RAID硬件设备的日志，这个具体什么命令不说了，因为你用不同的厂商的RAID设备，这个命令实际上是不一样的，发现RAID就是每隔30天有一次充放电的日志，所以就是由于这个定期的充放电导致了线上数据库的性能定期抖动！
对于RAID锂电池充放电问题导致的存储性能抖动，一般有三种解决方案：
1. 给RAID卡把锂电池换成电容，电容是不用频繁充放电的，不会导致充放电的性能抖动，还有就是电容可以支持透明充放电，就是自动检查电量，自动进行充电，不会说在充放电的时候让写IO直接走磁盘，但是更换电容很麻烦，而且电容比较容易老化，这个其实一般不常用
2. 手动充放电，这个比较常用，包括一些大家知道的顶尖互联网大厂的数据库服务器的RAID就是用了这个方案避免性能抖动，就是关闭RAID自动充放电，然后写一个脚本，脚本每隔一段时间自动在晚上凌晨的业务低峰时期，脚本手动触发充放电，这样可以避免业务高峰期的时候RAID自动充放电引起性能抖动
3. 充放电的时候不要关闭write back，就是设置一下，锂电池充放电的时候不要把缓存级别从write back修改为write through，这个也是可以做到的，可以和第二个策略配合起来使用