为什么DWS使用一段时间后执行SQL很慢?
数据库在使用一段时间后,随着业务的增加促使表数据增加,或者对表数据经常进行增、删、改过后,引发数据膨胀和统计信息不准造成性能下降。
建议对于频繁增、删、改的表,定期做vacuum full和analyze操作。操作步骤如下:
1.默认每30000条数据收集100条做统计信息,当数据量大的时候,发现sql时快时慢,一般是执行计划发生了变化,统计信息的需要调整采样率。set default_statistics_target 可以提高采样率,对优化器生成最优计划有所帮助。
2.重新analyze。详细信息请参见《开发指南》的“ANALYZE | ANALYSE”章节。
说明若用户想要知道是否是磁盘碎片的问题影响了数据库的性能,可以使用以下函数进行查询:
select * from pgxc_get_stat_dirty_tables(30,100000);
为什么DWS的性能在极端场景下并未比单机数据库好
DWS 中由于MPP架构的限制导致少部分PG系统方法、函数无法下推到DN节点来执行,仅能在CN端出现性能瓶颈。
原理解释
- 一个操作能够并行执行是有条件的,需要逻辑上能够并行,比如做汇总(SUM),我们可以各个节点(DN)并行汇总后,最后的汇总一定是不能并行,要在某一个节点(CN)上执行,由于大部分的汇总工作已经在DN节点完成,CN端的工作是比较轻量的。
- 某些场景必须要集中执行,比如事务号,必须要保证全局唯一,这个任务在我们系统里是通过GTM来实现的,因此,GTM也是全局唯一的组件(主备)。所有需要全局唯一的任务DWS 都是通过GTM来完成,只是在设计上尽量避免阻塞在GTM上,因此GTM并没有太多瓶颈,而且有些场景下还可以GTM-Free和GTM-Lite。
- 从传统单机数据库的应用开发模式到并行数据库,为确保获得更好的性能,可能需要对业务进行少量改动,尤其是传统Oracle的存储过程互相嵌套的开发模式,如果要保证高性能,需要进行业务修改及对应的适配。
解决方案
- 如遇到此类问题,请参考《数据仓库服务数据库开发指南》中的“优化查询性能”章节。
- 或者,请联系技术人员进行业务适配的修改调优。
业务读写阻塞,如何查看某个时间段的sql执行记录?
当您的数据库集群出现读写阻塞时,可通过TopSQL功能查看某个时间段所执行的sql语句,支持查看当前CN或者所有CN的sql语句。
TopSQL功能包括查看实时sql语句和历史sql语句:
- 实时sql语句查询请参见:《数据仓库服务开发指南》的“实时TopSQL”章节。
- 历史sql语句查询请参见:《数据仓库服务开发指南》的“历史TopSQL”章节。
DWS中“算子下盘”是什么含义?
用户业务查询执行过程中,当集群内存不足时,数据库可能会选择将临时结果暂存到磁盘。当临时结果的磁盘存储量超过一定值后,用户会收到告警“查询语句触发下盘量超阈值”,那这个告警中的“下盘量”或者经常数据库中经常出现的“算子下盘”如何理解呢?
算子下盘的概念
任何计算都需要耗费内存空间,差别在于多少而已,对于如果耗费内存过多,会导致其他作业运行内存空间不足,导致作业不稳定,因此我们需要对查询语句的作业内存使用进行限制,保证作业运行的稳定性。
假如作业想要使用500M内存,但实际上因为内存限制最终只分配到300M内存,则需要把临时不用的数据写到磁盘上,内存中只保留正在使用的数据,这就是中间数据落盘的由来。当发生中间数据落盘时,称之为 算子下盘 。算子落盘空间太大除了会对查询性能有较大影响,还有可能导致数据库只读甚至磁盘满,因此DWS提供了用户算子空间限制,可以限制用户算子落盘的大小,在超限时查询报错退出。
哪些算子会发生下盘
当前DWS可下盘算子有六类(向量化及非向量化共10种):Hash(VecHashJoin),Agg(VecAgg),Sort(VecSort),Material(VecMaterial), SetOp(VecSetOp),WindowAgg(VecWindowAgg)。
哪些参数可以控制下盘
- work_mem:可以判断执行作业可下盘算子是否已使用内存量触发下盘点,当内存使用超过该参数后将触发算子下盘。该参数仅在非内存自适应场景(enable_dynamic_workload=off)时生效。work_mem参数设置通常是一个权衡,即要保证并发的吞吐量,又要保证单查询作业的性能,故需要根据实际执行情况(结合Explain Performance输出)进行调优。
- temp_file_limit:可以限制落盘算子的落盘文件大小,一般建议根据实际情况设置,防止下盘文件将磁盘空间占满,超过该值将报错退出。
如何判断语句是否发生了下盘
- 通过下盘文件确认:下盘文件位于实例目录的base/pgsql_tmp目录下,下盘文件以pgsql_tmp**queryid_**pid命名,可以根据queryid确认是哪条sql发生了下盘。
- 根据等待视图(pgxc_thread_wait_status)确认:等待视图中,当出现write file时,表示发生了中间结果下盘。
- 根据执行计划确认(explain performance):performance中出现spill、written disk、temp file num等关键字时,说明对应的算子出现了下盘。
- 根据topsql确认(前提:topsql功能已开启):实时topsql或历史topsql中,spill_info字段中会包含下盘信息,如果该字段不为空,说明有DN实例出现了下盘。
如何避免下盘
发生算子下盘时,算子运算数据将写入磁盘,由于磁盘操作相对内存访问缓慢导致性能下降,查询响应时间出现极大劣化,因此应尽可能避免查询执行过程中的算子下盘,建议使用以下方法:
- 减小中间结果集:发生下盘时往往是由于中间结果集过大,因此可以增加过滤条件减少中间结果集大小。
- 避免数据倾斜:数据倾斜严重时会导致单DN上数据量过大,引起单DN下盘。
- 及时analyze:当统计信息不准时,行数估算可能偏小,导致计划选择非最优,从而出现下盘。
- 单点调优:对业务sql进行单点调优。
- 非内存自适应场景下,当中间结果集无法减少时,应根据实际情况适当调大work_mem参数。
- 内存自适应场景下,内存使用配置要使得数据库可用内存尽量增大,减少下盘概率。