数据库查询性能优化一直是程序员绕不开的话题, 当我们遇到业务刷新报表缓慢或者查询获取结果延迟太大, 可以采用提问法来思考如何进行优化.
1. 什么样的环境
硬件环境
query 执行的速度和我们的硬件息息相关, 当前用的什么样的 CPU, 有多少核多少线程, 内存有多大都直接影响了运算速度, 磁盘是 SSD 还是 HDD, 网卡什么速率都直接影响了我们数据读取的时延
软件环境
软件环境虽然不像硬件一样, 各种参数看的见摸得着, 但仍然影响着我们的查询性能. 没一套系统实际上都在特定的场景有着各自的优势. 我们的查询系统是什么样的架构, 适合什么样的 query, 在线还是离线, 计算多还是数据读取多, 这些在我们做优化的时候都应该了然于心.
下面我们根据这种思路来看看如何做性能优化
2. 什么样的 query
首先我们优化查询的时候, 需要看看 query 究竟是哪种类型. 写入还是查询(这里鉴于篇幅只谈查询), CPU 密集还是 IO 密集. 如果我们的系统是适合 OLTP 低延时点查的场景, 想要在这种系统上做 OLAP 大规模分析很显然就不太适合, OLTP 一般专注于数据一致性较高的点查, 而 OLAP 由于数据量庞大, 一般都需要采用向量并发查询. OLAP 不专注于毫秒级的低延迟, 而 OLTP 不专注于上亿级的数据统计.
3. 如何寻找性能瓶颈
3.1 vmstat 查看系统情况
整体系统不知道当前的瓶颈在哪里时, 我们可以先用 vmstat 工具来简单的看一下系统的大致情况. 如下图所示, 2 表示每个两秒采集一次服务器状态.
procs : 查看进程状态
r : 运行队列, 即当前可运行 (正在运行或者等待运行) 的进程数量. 目前 CPU 比较空闲, 这个数量很小, 当这个值超过了 CPU 数目, 就会出现 CPU 瓶颈了.
b : 阻塞的进程, 即处在不可中断 sleep 状态下的进程数量.
memory : 查看内存状态
swpd : 已使用的虚拟内存大小, 如果大于 0, 表示机器开始使用虚拟内存了, 虚拟内存运行会很慢. 这里数值为 0 表示我们关闭了虚拟内存功能.
free : 空闲的物理内存的大小.
buff : 内存做为系统 buffers 的大小.
cache : 内存做为系统 cache 的大小.
swap : 磁盘和内存做数据交换的状态
nesi : 每秒从磁盘读入虚拟内存的大小, 如果这个值大于 0, 表示物理内存不够.
so : 每秒虚拟内存写入磁盘的大小.
io: 磁盘的 io 信息
bi : 每秒从块设备接收的块数量.
bo : 每秒发送给块设备的块数量.
如果这两个值较大, 表示 IO 比较频繁, 可以考虑 IO 优化.
system : 系统状态信息
in : 每秒 CPU 的中断次数(包括时钟中断)
cs : 每秒上下文切换次数, 我们调用系统函数, 线程的切换, 就需要上下文切换, 这个值要太大就可以考虑 减少系统的上下文切换, 比如协程替代多线程等方式.
CPU : CPU 信息
us : 包括用户时间和 nice 时间, 跑非内核的代码 (或者用户代码) 的时间.
sy : 系统占用时间, 跑内核代码 (比如系统调用) 占用的时间.
id : 花费在 idle 上的 CPU 时间.
wa : 等待 IO CPU 时间. 如果这个值太大, 表示 IO 系统瓶颈在 IO 上.
如果 CPU 占用高表示系统在 CPU 上, 如果系统的 swap 比较频繁, 很可能是系统内存泄露或者内存不够用, 需要扩展内存, 如果是 IO 等待较多则系统瓶颈出现在 IO 上, 如果上下文切换, 或者系统调用占比太大, 则我们需要思考下我们程序的设计, 减少系统调用或者上下文切换.
3.2 CPU 占用过高
我们可以通过 uptime,top,mpstat 或者 sar 等一些工具来查看当前 CPU 占用过高的情况.
我们可以通过 uptime 看看当前系统的整体情况, 当前的系统时间和运行时间, 登陆的用户数量, 还有最近 5,10 和 15 分钟的系统平均负载.
top 则可以显示较详细的信息. head 部分有 CPU 占用的详细信息, 下面的列表也有记录每个进程占用的 CPU 情况.
如果是多线程, 我们还可以通过 top -H -p pid 来查看进程的每个线程的 CPU 占用情况
我们找到哪个线程占用的比例多之后, 可以根据这个线程的线程名查看该线程是用来做什么处理的. 大致了解下是什么样的处理让 CPU 比较高.
mpstat 则可以查看系统每个核的运行状态.
sar 的功能比较全, 这里不再做科普.
CPU 用户态的占用比较高, 一般就是我们的程序编写的效率太低, 具体哪里低, 我们可以通过 perf 工具或者 Intel 的 vtunes 来查看性能瓶颈. perf top 的执行结果如下图所示, 我们拿到对应的堆栈信息之后, 就可以针对性的消除 CPU 瓶颈了.(vtune 的用法可以自行谷歌).
鉴于上述工具检查出来的情况, 如果 CPU 确实水位很高, 则 CPU 基本就是性能瓶颈. 如果不高则, 需要进行下一步来判断性能瓶颈.
3.3 IO 占用过高的情况
IO 定位的工具多种多样, 一般查看 IO 问题我们可以使用 iostat,pidstat 和 iotop 工具. 当然我们也可以使用其他的工具, 大家可以自己搜索相关的工具使用, 这里主要介绍常用的几种工具.
pidstat
pidstat 是 sysstat 工具的一个命令, 用于监控全部或指定进程的 CPU, 内存, 线程, 设备 IO 等系统资源的占用情况. 用户可以通过指定统计的次数和时间来获得所需的统计信息.
我们通过这个命令可以知道哪个进程占用的 IO 比较多. 然后我们可以通过指定进程号的方式查看更详细的信息.
这样我们就可以知道是哪个进程中的哪个线程占用了较多的 IO 资源, 然后我们可以通过对应的 TID, 找到对应的执行代码进行分析.
iostat
iostat 是 I/O statistics(输入 / 输出统计)的缩写, 它可以对系统的磁盘操作活动进行监控, 汇报磁盘活动统计情况. 但是 iostat 仅对系统的整体情况进行统计, 不能对某个进程进行深入分析, 单独的进程分析我们可以用 iotop 工具, 使用方法和 top 类似.
1 表示每秒打印一次当前磁盘的统计信息. 我们需要注意的是后面几个指标.
avgrq-sz | 平均每次 IO 操作的数据量 (扇区数为单位) |
---|---|
avgqu-sz | 平均等待处理的 IO 请求队列长度 |
await | 平均每次 IO 请求等待时间 (包括等待时间和处理时间,毫秒为单位) |
svctm | 平均每次 IO 请求的处理时间 (毫秒为单位) |
%util | 采用周期内用于 IO 操作的时间比率,即 IO 队列非空的时间比率 |
avgrq-sz 直接反应了当前 io 的种类, 比如大块数据读取还是小数据量的读取.
avgqu-sz 反应了当前 IO 的繁忙情况, 如果队列长度太长, 说明 IO 现在很忙很多任务处理不过来, 换句话说 I,IO 成为了瓶颈.
await 也是一样, 如果等待比较高, 说明 IO 成了累赘.
svctm 则和 avgrq-sz 一样, 反应了 IO 操作的处理规模, 如果是大块数据读写, 这个时间就会拉长.
iotop
iotop 可以用于查看哪些进程执行占用了的 I/O, 使用方式和 top 类似, 这里不再做过多描述.
3.4 其他情况
如果 TOP 占用不高, IO 也不是瓶颈, 则可能处在程序架构上, 比如并发控制的不够好有较多的线程在 sleep 状态. 这种情况可以通过 pstack 看看当前所有线程的堆栈.
4. 优化性能瓶颈
CPU 瓶颈型
面对这种类型, 一般我们需要通过 perf 配合对应的代码去进行优化, 核心思想就是减少计算的量. 具体方法以下仅供参考:
多采用 SIMD 来代替老式的计算指令或者 C++ 的操作运算符. 可以引进类似 Intel 的 MKL 库来辅助计算.
减少不必要的重复计算, 减少 for 循环的次数. 比如有些 std 库的数据结构都有 find 函数都带有起始坐标, 善用起始坐标避免从 0 坐标重复查询.
如果是系统调用过多, 比如分配内存之类的, 可以考虑预分配内存的方式, 或者直接使用 tcmalloc 等类似的内存管理库进行兜底, 有条件的可以基于这类库再开发适合自己的内存管理体系
IO 瓶颈型
IO 瓶颈一般都是和磁盘相关的, 网络上, 因为网卡升级, 速度上去比较快, 相比来说, 限制的 io 基本都是磁盘上的 io. 下面也只说说磁盘的 IO 优化方法.
如果是读类型的请求造成了 IO 瓶颈, 可以考虑上层多开 cache. 比如全局的 query cache, session 级别的 session cache, 块设备的 block cache 等, 从上层去减少磁盘的 io 请求.
如果是是小数据大并发的写入类型的造成了 IO 瓶颈, 我们可以考虑在内存做一次 cache, 对这多次写入先在内存处理, 然后通过时间或者大小阈值等策略控制, 刷到磁盘上.
如果是大数据的写入, 我们可以考虑做下平滑写入, 每次限制写入的数量.
如果是因为流量的关系, 某一时间点出现峰值, 之后回落, 则可以考虑通过第三方来写入. 比如消息队列, 先写到消息队列 i 进行削峰, 再平滑写入系统.
除此之外我们还可以换更好的硬件, 比如磁盘阵列等.
内存瓶颈型
内存瓶颈一般比较难出现, 内存毕竟比较便宜, 基本上都会满足内存的需求. 如果真的因为虚拟内存的问题造成了程序运行效率低下, 我们一方面是考虑增加内存, 关闭虚拟内存来解决, 同时我们也应该思考自己的程序模型, 比如减少中间数据的存在, 多用写时复制技术, 多用用系统的 no copy 接口替换老的接口等.
5. 后续
如果实在没有方法优化了, 我们真的就需要看看当前的 query 是否真的合适我们的系统了. 还是那句话, 每套系统都有适合自己的业务, 一般公司的系统体系里都会有多种数据库引擎, 针对我们的 query, 去寻找合适的.
来源: https://segmentfault.com/a/1190000039989132