性能调优
本文档介绍了定位性能瓶颈的思路,以及存在于 YMatrix 数据库内部及外部的一些典型性能调优场景。
1 怎样定位性能瓶颈?
当发现某业务 SQL 变慢,或想提升某 SQL 的查询性能时,请先根据现象确认方向性的问题:
是服务器系统整体变慢:
- 通过分析服务器资源使用情况判断
还是只是某个 SQL 变慢:
- 通过 YMatrix 图形化界面的“查询监控”功能
- 或通过 Grafana/Prometheus 监控的 Overview 界面观测是否有实时的慢查询
确定有慢查询 SQL,请参考下述思路进行排查与分析:
- 首先,需要确认性能瓶颈是否存在于数据库外部(服务器资源/业务变更等)
- 如果非外部原因,那么则需要对数据库内部进行分析(统计信息更新/数据倾斜判断/查询计划分析等)
1.1 数据库外部如何定位
1.1.1 服务器资源分析
注意!
信息收集方法多样,我们在此只详细说明其中 1-2 种。若你有更习惯使用的命令,可以另作收集与分析。
类别 | 信息 | 收集 | 分析 |
服务器资源 | CPU 使用率 | YMatrix 图形化界面 - “集群管理” - “指标视图”: ∙ 每个节点的 CPU 使用率 或使用 top 命令收集 CPU 详细信息: ∙ 用户 CPU(us) ∙ 系统 CPU(sy) ∙ 空闲 CPU(id) | ∙ 当 CPU 使用率高时,确定是用户 CPU 高,还是系统 CPU 高 ∙ 如果是用户 CPU 高,则说明某个程序的 CPU 资源占用率高,需要定位代码程序运行的效率 ∙ 如果是系统 CPU 高,则同步观察是否是其他服务器资源(磁盘 I/O,内存,网络等)不足 |
内存与虚拟内存 | YMatrix 图形化界面 - “集群管理” - “指标视图”: ∙ 每个节点的 MEM 使用率 或使用 vmstat 命令查看内存详细信息: ∙ si(每秒从交换区写到内存的大小) ∙ so(每秒写入到交换区的内存大小) | 如果 si,so 长期不为 0,则表示内存不足,使用了大量虚拟内存导致性能降低 | |
磁盘 I/O(磁盘读/写速率) | YMatrix 图形化界面 - “集群管理” - “指标视图”: ∙ 每个节点的磁盘 I/O 或使用 iostat -x 命令查看磁盘 I/O 详细信息: ∙ %util(每一秒用于 I/O 时间的百分比) ∙ %iowait(CPU 等待 I/O 完成的时间的百分比) | ∙ 影响性能的是磁盘的 I/O 速度,而非磁盘大小。如果 %util 接近 100%,则说明 I/O 请求过多,I/O 系统已经满负荷 ∙ 如果 %iowait 值过高,则表示磁盘存在 I/O 瓶颈,需考虑更换或升级磁盘阵列 |
|
网络 | YMatrix 图形化界面 - “集群管理” - “指标视图”: ∙ 每个节点的网络接收/发送速率 或使用 sar -n DEV 1 2 命令查看网络详细信息: ∙ rxkb/s(每秒接收的数据量,千字节数) ∙ txkb/s(每秒发送的数据量,千字节数) | 将 rxkB/s 与该网络总带宽进行对比,如果其接近网络总带宽,则说明存在网络瓶颈 | |
内核参数 | 如果为 Linux 系统,则在 /proc/sys 目录下查看相应内核参数文件的值,例如:cat overcommit_memory | 针对操作系统的参数优化,主要是调整服务器的内存使用策略,增加 swap 空间,分担内存压力。通过更改 Linux 系统中 /proc/sys 中内核参数对应的文件可以达到修改内核参数的目的(修改过后,保存配置文件就马上自动生效),重新启动机器后之前修改的参数值失效 |
1.1.2 业务变更确认
- 确认是否是新上线的业务
- 查看集群数据表的 DDL,确认是否由于在线 DDL 导致的问题。例如添加了索引的大表,会消耗集群较多的资源,从而干扰集群正常的访问请求
1.2 数据库内部如何排查
注意!
表格中选项非全部必选。
软件环境 | 操作系统版本 | 使用 uname -a 命令查看 | 分析此瓶颈是否与操作系统版本相关 |
YMatrix 版本 | 使用 SELECT version(); 命令查看 | 分析此瓶颈是否与 YMatrix 版本相关 | |
集群信息 | 集群部署拓扑 | ∙ YMatrix 图形化界面 - “集群管理” ∙ 或使用命令 SELECT * FROM gp_segment_configuration; 查看 | 如果集群发生故障自动转移(Failover),那么单个物理节点会承接更多 Segments,可能会造成一定的性能下降 |
数据库信息 | 表结构 | ∙ YMatrix 图形化界面 - “数据表” ∙ 或使用命令 \d+ 查看 | 确认是否因分布键设置不合理,导致数据倾斜严重 |
相关日志 | YMatrix 部分日志存放的默认目录为 $HOME/gpAdminLogs 数据库相关日志在相关数据目录下 | 如果需要,分析相关日志 | |
慢查询 | YMatrix 图形化界面 - “查询监控“:查看是否存在阻塞会话 | 如果存在慢查询,则需定位并分析该慢查询 | |
查询计划 | 使用 EXPLAIN SELECT... 命令查看某查询的查询计划 | 如果查询计划代价过高,则需分析其具体路径根究原因 | |
保存现场环境 | 使用 YMatrix 提供的分析工具 minirepro |
2 数据库内部调优典型场景
数据库内部调优即单个查询(SQL)语句的调优。
2.1 统计信息失真
现象:
数据表中有较大的数据变动(如数据写入、删除等),需要重新对该表执行 ANALYZE
命令,以收集当下更为准确的统计信息,避免在执行查询计划的时候因统计信息不准确而选择错误的计划,最终导致查询性能降低。
分析方法:
可以根据 EXPLAIN ANALYZE
命令的输出确定统计信息是否错误,如果查询计划中 row
值偏差太大,则说明统计信息失真,重新执行以下命令:
=# ANALYZE <tablename>;
2.2 数据倾斜
现象:
某些业务场景在初始设计数据模型时选取的分布键不合理,使得写入的数据落在集群小部分的 Segment 实例上,造成数据倾斜。数据倾斜会显化 YMatrix 分布式架构的木桶效应:多个 Segments 实例在协作处理数据的时候,执行查询任务的时间取决于最慢的 Segment 实例的执行时间 + Master 的处理时间。
分析方法:
使用以下命令查看某数据表的数据分布情况,如果不同 Segment 的数据分布相差过大,则视为数据倾斜:
=# SELECT gp_segment_id,count(*) FROM <tablename> GROUP BY gp_segment_id;
解决思路:
如果出现数据倾斜现象,那么需要考虑修改分布键,重新均匀地分布数据。修改分布键的行为通过如下命令实现:
=# ALTER TABLE <tablename> SET DISTRIBUTED BY(<newcolumn>);
=# ALTER TABLE <tablename> SET WITH (REORGANIZE=true);
=# ANALYZE <tablename>;
注意!
在设计数据模型时,分布键的选择会影响到表的数据分布,进而影响到其查询性能。而当业务开始运行,再修改分布键就拥有了更大的性能风险。因此我们建议你在设计阶段更加谨慎地选择分布键,可以参考YMatrix DDL 最佳实践。
2.3 数据膨胀
现象:
数据表因进行频繁的更新或删除操作,而又没有设计合理的数据清理周期,导致数据膨胀。
解决思路:
- 删除已经只存储历史数据(冷数据)的分区子表,使用以下命令实现:
=# DROP TABLE <partition_tablename>;
- 制定更合理的数据清理周期,使用以下命令实现:
=# VACUUM <tablename>;
详见日常清理。
- 对于膨胀严重的表,需要进行数据重组,使用以下命令实现:
=# ALTER TABLE <tablename> SET WITH (REORGANIZE=true);
=# ANALYZE <tablename>;
2.4 查询计划分析
如果以上原因都不是造成该 SQL 性能瓶颈的实际原因,那么请分析其查询计划,并且提出以下问题:
- 该计划中的操作花费了特别长的时间吗?
- 查找消耗了多数查询执行时间的操作。例如,如果一个索引扫描花费了比预期长的时间,该索引可能过期并且需要重建索引。或者,调整
enable_\<operator>
参数来看看是否能够强制优化器来为该查询选择一个不同的计划。
- 查找消耗了多数查询执行时间的操作。例如,如果一个索引扫描花费了比预期长的时间,该索引可能过期并且需要重建索引。或者,调整
- 在该计划中是否很早就应用了选择性谓词?
- 在计划中早些应用最具选择性的过滤条件,这样会有较少的行在计划树中向上移动。如果查询计划没有正确地估计查询谓词的选择度,应在相关列上收集更多统计信息。你还可以尝试重新排序 SQL 语句中的
WHERE
子句。
- 在计划中早些应用最具选择性的过滤条件,这样会有较少的行在计划树中向上移动。如果查询计划没有正确地估计查询谓词的选择度,应在相关列上收集更多统计信息。你还可以尝试重新排序 SQL 语句中的
- 优化器是否选择了最好的连接顺序?
- 当查询连接多个表时,确保优化器选择了最具选择性的连接顺序。计划中应该尽早做消除最多行的连接,这样会有较少的行在计划树中向上移动。 如果计划没有选择最优的连接顺序,可以设置配置参数 join_collapse_limit 为
1
并且在 SQL 语句中使用显式的JOIN
语法来强制优化器用指定的连接顺序。还可以在相关的连接列上收集更多的统计信息。
- 当查询连接多个表时,确保优化器选择了最具选择性的连接顺序。计划中应该尽早做消除最多行的连接,这样会有较少的行在计划树中向上移动。 如果计划没有选择最优的连接顺序,可以设置配置参数 join_collapse_limit 为
- 优化器是否有选择地扫描分区表?
- 如果在使用表分区,优化器是否有选择地只扫描满足查询谓词所需的子表?对父表的扫描应该会返回
0
行,因为父表中不包含任何数据。
- 如果在使用表分区,优化器是否有选择地只扫描满足查询谓词所需的子表?对父表的扫描应该会返回
- 优化器是否在使用时选择了哈希聚集和哈希连接操作?
- 哈希操作通常比其他类型的连接或者聚集快很多:行比较和排序可以在内存中完成而不需要消耗磁盘 I/O。
- 是否开启/关闭向量化会较大程度地影响性能?
- 实际上,对于不同的场景,向量化执行引擎相比传统的面向行的执行引擎,性能的提升是不同的,例如,对于能够完全按顺序操作的算子,比如一般的表达式计算、选择算子和聚集算子(Agg),能够最大程度得到性能提升。而对于引入了随机性的算子,比如排序算子(Sort)、哈希算子(Hash),则性能提升空间有限。
查询计划相关信息详见理解查询计划。