Hologres新手如何基于ChatGPT优化Hologres慢SQL

本文涉及的产品
实时数仓Hologres,5000CU*H 100GB 3个月
简介: Hologres新手如何基于ChatGPT优化Hologres慢SQL。

Hologress是为全集团TL提供实时OKR分析看板的数据存储及计算库,方便团队TL聚焦重点、统一目标、跟踪进展,做好目标管理及协同。


鉴于最近ChatGPT的火热应用,我们尝试着将Hologres的慢SQL通过ChatGPT调优,有一些有意思的收获,下面给大家分享一下使用教程和使用收获,也希望这个分享能帮助大家在日常工作中利用AI的能力集思广益,释放手工生产力!


工具:Hologres,ChatGPT。作者是用VSCODE安装ChatGPT插件。(开源ChatGPT也可以的)

步骤:

  1. VSCODE 安装ChatGPT中文版
  2. 将Hologres慢SQL的执行计划复制到ChatGPT

VSCODE 安装ChatGPT中文版,将执行计划复制到ChatGPT

机1.png

继续提问:如何优化

机2.png

ChatGPT还不支持自动返回优化脚本,但可以继续问,例如如何创建索引

机3.png

但如果有不清楚的专业名词可以继续提问,例如:PostgreSQL优化全表扫描和数据重新分发操作

机4.png

机5.png

机6.png

通过上面的调优建议和方案,再结合Hologres的官方调优建议,总结出的调优方案如下:

参考Hologres调优文档:

1. 基于ChatGPT的性能调优

1.1 执行计划

执行计划如下:

Limit(cost=0.00..26.95 rows=1 width=16)->  Result  (cost=0.00..26.95 rows=1 width=16)->Limit(cost=0.00..25.95 rows=1 width=24)->  Partial Aggregate  (cost=0.00..25.95 rows=1 width=24)->  Gather  (cost=0.00..25.94 rows=10 width=24)->  Partial Aggregate  (cost=0.00..25.94 rows=10 width=24)->  Result  (cost=0.00..23.19 rows=54569 width=40)->  Result  (cost=0.00..19.39 rows=54569 width=232)->  Hash Right Join(cost=0.00..18.00 rows=54569 width=224)                                                  Hash Cond:(ads_is_rd_okr_emp_pxxxi.work_no= ads_is_rd_okr_emxxxxxe_df_i.work_no)->  Partial HashAggregate  (cost=0.00..11.45 rows=27285 width=167)Group Key: ads_is_rd_okr_xxxf_i.work_no->  Redistribution  (cost=0.00..9.66 rows=27285 width=263)->  Exchange (Gather Exchange)(cost=0.00..8.96 rows=27285 width=263)->  Result  (cost=0.00..8.94 rows=27285 width=263)->  Decode  (cost=0.00..8.94 rows=27285 width=263)->  Partial HashAggregate  (cost=0.00..8.84 rows=27285 width=263)Group Key: ads_is_rd_xxxiod_df_i.work_no->  Seq Scan on ads_is_rd_okxxxx_df_i  (cost=0.00..6.08 rows=27285 width=259)                                                                                            Filter:((COALESCE(is_deleted,'1'::text)<>'Y'::text)AND(period_id = ANY ('{61e79735a,621baa21bd16,621baa7dbd16,6295ffb8,62b122830a33af,630f858094}'::text[])))->  Hash  (cost=5.02..5.02 rows=1000 width=72)->  Redistribution  (cost=0.00..5.02 rows=1000 width=72)->  Seq Scan on ads_is_rd_okr_xxx_df_i  (cost=0.00..5.01 rows=1000 width=72)                                                                    Filter:((dept_full_path ~~'%00001%'::text)AND(s_dept_no = ANY ('{00002,}'::text[])))

1.2 执行计划解读(来自ChatGPT)

  • 首先执行Seq Scan on ads_is_rdxxxt_tree_df_i,对表进行全表扫描。这个操作的代价为5.01,将结果存储在一个Hash表中。
  • 接下来执行Partial HashAggregate,对ads_is_rd_okr_emp_period_df_i表进行聚合操作,将work_no相同的记录合并在一起。这个操作的代价为8.84。
  • 然后执行Decode,对ads_is_xxxx_df_i表进行解码操作。这个操作的代价为8.94。
  • 然后执行Result操作,将ads_is_rdxxxxdf_i表的结果发送给Exchange节点。这个操作的代价为8.94。
  • 然后执行Exchange(Gather Exchange)操作,将Exchange节点的结果聚集到一个节点上。这个操作的代价为8.96。
  • 然后执行Redistribution操作,对数据进行重新分发。这个操作的代价为9.66。
  • 然后执行Partial HashAggregate,对adxxxxxd_df_i表进行聚合操作,将work_no相同的记录合并在一起。这个操作的代价为11.45。
  • 然后执行Hash Right Join操作,对ads_is_xxxe_df_i和ads_is_rdxxxxx_df_i进行哈希连接。这个操作的代价为18.00
  • 然后执行Result操作,将结果发送给Limit节点。这个操作的代价为19.39。
  • 然后执行Result操作,将结果发送给Limit节点。这个操作的代价为23.19。
  • 然后执行Partial Aggregate操作,对结果进行聚合操作。这个操作的代价为25.95。
  • 最后执行Limit操作,返回结果集中的前1条记录。这个操作的代价为26.95。

综上所述,这段执行计划的主要操作是对两个表进行聚合和连接操作,其中大量使用了哈希表和哈希连接来优化查询性能。同时,在执行计划中还出现了全表扫描和数据重新分发等操作,代价较高。需要进一步优化查询语句和数据结构,以提高查询性能和效率。


2. 优化方案(结合ChatGPT)

根据ChatGPT的解读和优化建议,再根据Hologres的调优建议,最终总结出来慢SQL的优化方案如下:

2.1 修改表存储模式和加索引

ChatGPT:在查询语句中使用到的字段上添加索引,可以减少全表扫描的操作,提高查询性能。

优化手段:将Hologres行存储表改成列存储表,并加上索引。

温馨提示:行存储表不能直接改列存储表,可以先复制一张备份表B,改成列存储,在通过rename方式,把A换成C,B换成A,这样达到删表重建效果,不影响线上)

机7.png

改列存储原因:1. 存储模式提示文案修改:Hologres支持列存、行存和行列共存3种存储模式。默认为列存,列存适合各种OLAP场景的复杂查询;行存适用于适用于基于主键(PK)的KV查询场景。行列共存则同时适用列存和行存的场景。

机8.png


先复制一张备份表B:

机9.png

改成列存储:

机10.png

通过rename方式完成删表重建功能,不影响线上

机11.png

第一个慢SQL优化后时间缩短一半:

机12.png

第二个慢SQL优化前:

机13.png

第二个慢SQL优化后:

机14.png

2.2 调整查询条件

ChatGPT:在查询语句中添加合适的查询条件,过滤掉不必要的记录,减少数据处理的代价。

优化手段:暂时还没有做这一步,因为查询性能已经达到要求了。

2.3 调整数据分布

ChatGPT:调整数据分片的方式,让相同的数据均匀地分布在不同的节点上,避免数据重新分发的操作。

优化手段:

  • 将join的key或者group by 频繁的key设置成distribution key,这样实现local join的能力,减少执行计划中的redistribution key(因为涉及的改动较多,所以这次没有改动了)
  • 将查询条件用到的字段bitmap和dictionary都设置为on。因为表的字段长度都很长,设置bitmap和dictionary会加快查询。
BEGIN;CALL UPDATE_TABLE_PROPERTY('public.ads_is_rd_okr_emp_period_df_i','dictionary_encoding_columns','"is_deleted:on","period_id:on"');CALL UPDATE_TABLE_PROPERTY('public.ads_is_rd_okr_emp_period_df_i','bitmap_columns','"is_deleted:on","period_id:on"');COMMIT;
  • 字典编码列:字典编码列,即dictionary_encoding_columns,为该列构建字典编码映射。字典编码可以将字符串的比较转成数字的比较,加速group by、filter等查询。默认所有text列设置为auto模式,系统基于字段基数自动判断是否生成字典编码,建表后可修改。
  • 位图列:位图列,即bitmap_columns,为该列构建bitmap索引。bitmap索引可以对数据进行快速的等值过滤,建议把等值查询列建成位图列。默认所有 text 列都会被隐式地设置到bitmap_columns中,建表后可修改。

调整前:

机15.png

调整后:减少5毫秒

机16.png

3. 优化后效果

根据建议优化后:从早上10点半优化后,访问人数最高到8.32,没有出现一条慢SQL,而前一天半小时内慢SQL能达到上千条。

机17.png

附上测试同学对我调优之后的压测报告:

机18.png

4. 总结

以上是我根据ChatGPT做的部分慢SQL调优,取得一些比较好的反馈。但是上述分享只是我个人的经验所得,实际调优请以Hologres官方文档为准,这次分享的核心目的是希望在释放手工生产力提升工作效率方面有更多的思路。


说明:本文为业务方基于兴趣总结的经验,仅供参考,实际SQL调优请以Hologres官网文档为准。


相关实践学习
基于Hologres轻松玩转一站式实时仓库
本场景介绍如何利用阿里云MaxCompute、实时计算Flink和交互式分析服务Hologres开发离线、实时数据融合分析的数据大屏应用。
相关文章
|
25天前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
39 2
|
11天前
|
SQL 资源调度 分布式计算
如何让SQL跑快一点?(优化指南)
这篇文章主要探讨了如何在阿里云MaxCompute(原ODPS)平台上对SQL任务进行优化,特别是针对大数据处理和分析场景下的性能优化。
|
19天前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
22天前
|
SQL 关系型数据库 PostgreSQL
遇到SQL 子查询性能很差?其实可以这样优化
遇到SQL 子查询性能很差?其实可以这样优化
67 2
|
16天前
|
存储 缓存 数据处理
深度解析:Hologres分布式存储引擎设计原理及其优化策略
【10月更文挑战第9天】在大数据时代,数据的规模和复杂性不断增加,这对数据库系统提出了更高的要求。传统的单机数据库难以应对海量数据处理的需求,而分布式数据库通过水平扩展提供了更好的解决方案。阿里云推出的Hologres是一个实时交互式分析服务,它结合了OLAP(在线分析处理)与OLTP(在线事务处理)的优势,能够在大规模数据集上提供低延迟的数据查询能力。本文将深入探讨Hologres分布式存储引擎的设计原理,并介绍一些关键的优化策略。
61 0
|
19天前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
23天前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化:提升数据库性能的实战技巧
在数据库管理和应用中,SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
|
23天前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化策略
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生不利影响
|
2月前
|
SQL 存储 关系型数据库
Hologres SQL 查询优化技巧
【9月更文第1天】随着大数据处理的需求日益增长,如何高效地进行数据查询和分析变得尤为重要。Hologres 是阿里云推出的一款实时数仓产品,它基于 PostgreSQL 构建,并针对在线分析处理(OLAP)场景进行了优化,支持实时数据写入与查询,能够实现毫秒级的查询响应。本文将探讨在使用 Hologres 时如何编写高效的 SQL 查询,并介绍一些特定于 Hologres 的优化技巧。
153 2
|
3月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
169 0

热门文章

最新文章

相关产品

  • 实时数仓 Hologres