8.数据库优化之硬件相关知识

1、本文背景

   做了好多的DB安装和优化,但是一直没有去总结数据库基础：CPU和磁盘,IO方面的总结
本文将针对CPU和磁盘、IO等信息做下介绍，并定位在数据库优化中这些信息的关键作用
同时介绍,磁盘、网络的一些压测内容。

2、跟数据库优化相关的硬件指标

在数据库优化中，主要有以下优化指标。

· 响应时间：衡量数据库系统与用户交互时多久能够发出响应。

· 吞吐量：    衡量在单位时间内可以完成的数据库任务。

数据库优化工作中，第一项就是确定优化目标。

· 性能目标：如 CPU 利用率或 IOPS 需要降到多少。

· 响应时间：需要从多少毫秒降到多少毫秒。

· 吞吐量：    每秒处理的 SQL 数或 QPS 需要提高到多少。

一个已运行的数据库系统，如果前期设计不合理、性能不高，后期在优化时会非常困 难，有可能永远无法达到高性能，因此，在新建一套数据库系统前，首要的事应该是设计 优化。良好的设计能最大限度地发挥系统的性能。

记得在<<SQL优化核心思想>>一文中提到的思想:  SQL优化的核心思想就是降低SQL的IO次数。数据库优化中常用到的手段：建立索引,数据分区、分库，最终的目的都是为了降低SQL的IO次数。 而上面 响应时间和吞吐量则是从另一个角度:更快的做出数据定位 来提升系统性能。

3、怎么测试硬件（网络IO)的性能

测试硬件性能之前，你需要知道 测试目标。即你达到什么样的IOPS、事务量、延迟和磁盘IO带宽才能好的硬件性能指标

3.1 常见的磁盘和网络目标性能值

有了这些常用网络和磁盘参数指标,你才有压测目标,有个参考值。才能有硬件性能等级定位。

3.2 生产网络和磁盘压测值

1、网络如下：

采用netperf工具

测试工具	测试科目	网络设定	Throughput(带宽) 10^6bits/sec	Trans.Rate per sec(事务量)
netperf	UDP协议	万兆网	8093.41	37746.91
	TCP协议		9492.44	36279.79
	UDP协议	千兆网	965.9	15563.6
	TCP协议		948.95	15974.94

2、磁盘如下：

采用ORACLE自带的orion工具进行压测。压测架构为RAC，每个节点上开启两个压测点，采用8k块。

每个节点开启2个压测，共四个并发压测	IO延迟情况	测试用块	Maximum Large MBPS	Maximum Small IOPS
第一个压测	Small Read Latency: avg=741 us, min=97 us, max=49169 us, std dev=527 us @ Small=50 and Large=0 Small Write Latency: avg=1127 us, min=179 us, max=26606 us, std dev=751 us @ Small=50 and Large=0 Minimum Small Latency=152.70 usecs @ Small=1 and Large=0 Small Read Latency: avg=132 us, min=62 us, max=11052 us, std dev=73 us @ Small=1 and Large=0 Small Write Latency: avg=200 us, min=112 us, max=2025 us, std dev=62 us @ Small=1 and Large=0	8K	329.73	58345
第二个压测	Small Read Latency: avg=493 us, min=91 us, max=47045 us, std dev=296 us @ Small=48 and Large=0 Small Write Latency: avg=626 us, min=165 us, max=20297 us, std dev=328 us @ Small=48 and Large=0 Minimum Small Latency=250.36 usecs @ Small=1 and Large=0 Small Read Latency: avg=227 us, min=70 us, max=34715 us, std dev=155 us @ Small=1 and Large=0 Small Write Latency: avg=304 us, min=141 us, max=16508 us, std dev=178 us @ Small=1 and Large=0	8K	572.33	90018
第三个压测	Small Read Latency: avg=635 us, min=93 us, max=47868 us, std dev=374 us @ Small=50 and Large=0 Small Write Latency: avg=868 us, min=165 us, max=26468 us, std dev=525 us @ Small=50 and Large=0 Minimum Small Latency=163.77 usecs @ Small=1 and Large=0 Small Read Latency: avg=143 us, min=65 us, max=21040 us, std dev=90 us @ Small=1 and Large=0 Small Write Latency: avg=212 us, min=119 us, max=4264 us, std dev=70 us @ Small=1 and Large=0	8K	331.50	70910
第四个压测	Small Read Latency: avg=427 us, min=92 us, max=48437 us, std dev=319 us @ Small=44 and Large=0 Small Write Latency: avg=537 us, min=170 us, max=18891 us, std dev=280 us @ Small=44 and Large=0 Minimum Small Latency=238.53 usecs @ Small=1 and Large=0 Small Read Latency: avg=214 us, min=70 us, max=45641 us, std dev=171 us @ Small=1 and Large=0 Small Write Latency: avg=297 us, min=134 us, max=13073 us, std dev=173 us @ Small=1 and Large=0	8K	557.97	95567
汇总	4个压测结果	#吞吐量测试结果，每个压测平均速率为447.88M/s，4个并发压测共1791.53 M/s #IOPS测试结果，每个压测平均IOPS 为78710 I/Os， 4个并发压测IOPS共314840  I/Os

这是比较好的SSD盘，所以IOPS可以达到几十万。

3.3 各种数据库常用的压测工具

优化的第一件事是确定目标，那么要如何确定一个合理的目标呢？这就需要使用测试

工具。熟练使用常用的测试工具是做数据库优化的基础。下面是一些常用的测试工具。

·memtest86+ ：内存测试工具。

·STREAM ：     内存测试工具。

·sysbench ：     综合测试工具，可以测试 CPU 、 I/O 、数据库等。

·pgbench ：       PostgreSQL 自带的测试工具，可以仿真 TPC-B 的测试模型。

·fio ：                最强大的免费 I/O 测试工具。

·orion ：             Oracle 的 I/O 测试工具，测试裸设备的 I/O 能力，功能比 fio 要少，但使用简单。
 

熟练掌握以上几种测试工具的使用方法，对数据库的优化很有帮助。

在数据库优化中，首先需要了解一些常用硬件的相关知识，熟悉这些硬件的特性和性

能，才能知道目前数据库系统使用的硬件是否到达了瓶颈、更换硬件是否能提高数据库的

性能

4、你需要了解的硬件知识

4.1 CPU及服务器体系结构

服务器系统可以分为以下几种体系结构。

（ 1 ） SMP/UMA -Symmetric Multi Processing/Uniform Memory Architecture

· 优点：服务器中多 CPU 对称工作，无主次关系。各 CPU 共享相同的物理内存，访问 内存任何地址  所需的时间相同，因此程序设计较为简单。

· 缺点：因多 CPU 无主次关系，需要解决内存访问冲突，所以硬件实现成本高。

（ 2 ） NUMA-Non-Uniform Memory Access

· 优点：多 CPU 模块，每个 CPU 模块具有独立的本地内存（快），但访问其他 CPU 内 存（慢），硬件实现成本低。

· 缺点：全局内存访问性能不一致；设计程序时需要特殊考虑。

（ 3 ） MPP-Massive Parallel Processing

· 优点：由多个 SMP 服务器通过节点互联网络连接而成，每个节点都可访问本地资源

（内存、存储等），完全无共享（ Share-Nothing ）。最易扩展，软件层面即可实现。

· 缺点：数据重分布；程序设计复杂。

目前， Intel 的 X86 架构属于 NUMA架构，Intel 的 Nehalem 的架构图如下：

从上图 中可以看到，两颗 4 核的 CPU 通过总线连接，内存分别连接到各自的 CPU 上 。所以从一个CPU 的内存访问挂在另一个 CPU 上的内存，速度会慢一些。

下面介绍一些 CPU 的相关知识。先看看 CPU 的部分术语。

· 主频： CPU 的时钟频率，内核工作的时钟频率。

· 外频：系统总线的工作频率。

· 倍频： CPU 外频与主频相差的倍数。

· 前端总线：将 CPU 连接到北桥芯片的总线。

· 总线频率：与外频相同，或者是外频的倍数。

· 总线数据带宽：（总线频率 × 数据位宽） /8 。

Intel CPU 有以下 3 级 cache 。

·L1 、 L2 级 cache ：核心 core 独占；带宽为 20 ～ 80GB/S ；延时为 1 ～ 5ns 。

·L3 级 cache ：核心 core 之间共享；带宽为 10 ～ 20GB/S ；延时为 10ns 。

Intel CPU 通过 QPI （ QuickPath Interconnect 技术）与其他 CPU 通信， QPI 大约在 20GB/s

4.2 内存

内存是 CPU 与外部沟通的桥梁。 CPU 运算时所需的数据都临时保存在内存中，计算机 的所有程序也都运行在内存中，内存通常也用于硬盘等外部存储器的数据缓存。

内存从硬件上分为以下几种。

·SRAM ：静态随机存储器。随机是指数据不是线性依次存储的，而是自由指定地址 进行数据读写的。CPU 的 cache 一般使用这种存储方式，特点是速度快但造价高，不能大 规模使用。

·DRAM ：动态随机存储器。动态是指存储阵列需要不断刷新来保证数据不丢失。造价比SRAM 低得多，但速度也慢一些。

·SDRAM ：同步动态随机存储器，同步是指工作时需要同步时钟，内部命令的发送与数据的传输都以它为基准。

·DDR SDRAM ：双倍数据传输率的 SDRAM ， DDR 是 “Double Data Rate” 的缩写。普通的SDRAM 在一个时钟周期内只传输一次数据，即它在时钟的上升期进行数据传输；而 DD R内存则在一个时钟周期的上升期和下降期各传输一次数据，因此称为双倍速率同步动态 随机存储器。

DDR 内存又分 DDR1 、 DDR2 、 DDR3 、 DDR4 几种，分别对应第一代、第二 代、第三代、第四代DDR 。目前主流的内存为 DDR4 内存。

4.3 硬盘

硬盘按接口可以分为以下 3 种。

·ATA 系列：包括较早的硬盘接口，比如 IDE （ Integrated Drive Electronics ）、 PATA （ Parallel ATA）及 SATA （ Serial ATA ）。

·SCSI 系列：包括早期的并行 SCSI 和现在使用较广泛的 SAS （串行 SCSI ）。

·FC 接口：支持 FC 协议接口的硬盘。

FC 接口的硬盘一般只在专用存储上使用，通常见到的硬盘都是 SATA 或 SAS 接口的。

硬盘按存储介质来区分，可以分为以下两种。

·HDD ：普通机械硬盘。

·SSD ：固态硬盘。

机械硬盘和 SSD 硬盘都有 SATA 和 SAS 接口的这两种。

硬盘通常通过 SAS 或 SATA 接口的卡连接到主机上。 SAS 卡既能接 SAS 硬盘，也能接 SATA硬盘，但 SATA 卡只能接 SATA 硬盘。

目前 SAS 的接口速度一般是 3Gb/s 或 6Gb/s 。

目前在服务器上使用的硬盘其大小有以下两种：

·2.5 英寸。

·3.5 英寸。

当前 SSD 硬盘的大小一般是 2.5 英寸。

机械硬盘的转速通常有以下几种：

·7200 转，目前大多数的 SATA 硬盘都是 7200 转。

·10000 转。

·15000 转。

转速的单位是转 / 分钟，也就是 10000 转的硬盘的转速实际是 1 分钟 10000 转。

机械硬盘有以下性能指标。

· 平均寻道时间（ E ）： 15000 转的 SAS 硬盘为 4ms 左右。 · 旋转延时（ L ）： 15000 转的 SAS 硬盘为 2ms 左右。

· 内部传输时间（ X ）：通常为 0.8ms 。

· 吞吐率（ Throughput ）： 15000 转的 SAS 硬盘为 170Mb/s 左右，机械硬盘为 50 ～ 200Mb/s。

· 磁盘服务时间： RS=E+L+X=6.8ms 。

· 硬盘的 IOPS ：可以算出硬盘的 IOPS=1/RS=1000ms/6.8=147 。

SSD 硬盘一般分为如下两种。

·SLC ：是 “Single Layer Cell” 的缩写，特点是成本高、容量小、速度快，约 10 万次擦写寿命。

·MLC ：是 “Multi-Level Cell” 的缩写，特点是容量大、成本低，但速度慢，约 1 万次擦写寿命。

·TLC ：是 “Trinary-Level Cell” 的缩写，特点是容量最大、成本最低，但速度最慢，约1000次擦写寿命。

MLC 并不像 SLC 一个单元只对应一个比特位。 SLC 一个单元，根据电压的高低只对应 一个比特位，不是0 就是 1 ，而 MLC 一个单元根据电压的高低存储多个值，如 0 、 1 、 2 、 3 等4 个值。所以在 MLC 中，同样的一个单元中可以存储更多的数据，但也因此要通过不同 的电压值来识别出多个值，不像SLC 只需要识别出两个值就可以了，所以 MLC 识别一个单 元值的出错概率会增加，必须进行错误修正，这就导致其性能大幅落后于结构简单的SLC 闪存。也正是因为这个原因，MLC 闪存的复写次数通常只有 SLC 的十分之一。

TLC 利用不同电位的电荷来存储，一个浮动栅存储 3 个 bit 的信息， TLC 的复写次数通常只有MLC 的十分之一。目前因为价格的原因， SLC 的 SSD 基本看不到了，大都是 MLC 和 TLC的 SSD 。

服务器上使用的 SSD 硬盘的性能指标如下。

·IOPS ：读通常可以达到几万以上，写通常在几千以上。

· 吞吐率：读通常可以达到 250Mb/s 以上，写通常可以达到 150Mb/s 以上。

· 响应时间（ Latency ）：读在几十微秒到 100 微秒之间，写在 200 微秒到 1 毫秒之间。擦除时间在2 毫秒左右。

SSD 硬盘与机械硬盘最大的差别有以下几点：

·SSD 的随机性能好。 SSD 的读 IOPS 通常在机械硬盘的两个数据级以上，写 IOPS 也至少是1 个数量级以上。但读写吞吐率一般只有机械硬盘的数倍，最多 10 倍左右。

·SSD 内部存在擦除。也就是在重写旧数据时，不能像机械硬盘一样直接改写，而是需要经过一个擦除的过程后，才能再写。每次擦除的数据块比读写的块要大很多，通常在128KB 到 512KB 之间，而读写的块大小通常为 4KB 。

·SSD 中闪存芯片的写次数是有限的，写到一定次数时就会损坏。这个次数通常为 10000～ 100000 次。所以 SSD 内部需要一定的算法，让写比较平均地分散到其他各处。也就是说，如果一直写SSD 硬盘的相同逻辑地址的同一个位置，实际写的物理芯片并不是同一个位置。

由于物理所需要的 “ 擦除 ” 特性及写寿命等原因， SSD 硬盘存在着写放大的情况，也就是说，外部写4K 的数据，内部实际写的数据量有可能大大超过这个数值。具体会产生多少的写放大，与应用的I/O 特点以及不同 SSD 厂家内置在 SSD 内部的平衡写的算法有很大关系。不同厂家的产品会有很大的不同。所以对于考察一款SSD 硬盘，除了看 IOPS 和吞吐率外，还需要测试其I/O 性能的抖动况。

SSD 硬盘内部通常有以下几种优化。

·FTL （ Flash Translation Layer ）：物理逻辑地址映射。防止某个逻辑地址写太多次数而损坏芯片。

·Reclamation ：异步擦除策略，降低延时。

·Wear Leveling ：均衡写磨损，延长寿命。

·Spare Area ：预留空间，减少写放大。一般情况下， SSD 出厂后，内部会有一部分预留空间，而Intel 的 SSD 用户还可以再多预留一部分空间，这样可以提高写性能。