表数据膨胀导致SQL查询慢，用户前台页面数据加载不出

问题现象

GaussDB数据库性能时快时慢问题，原先执行几秒钟的sql，当前执行20几秒未出结果，导致前台页面数据加载超时，无法对用户提供图表显示。

原因分析

• 大量表频繁增删改，未及时清理，导致脏数据过多，表数据膨胀，查询慢。
• 内存参数设置不合理。

分析过程

1. 和客户确认是部分业务慢，可以提供部分慢sql，打印执行计划，耗时主要在index scan上，怀疑是IO争抢导致，通过监控IO，发现并没有IO资源使用瓶颈。

   

   

   

2. 查询当前活跃sql，发现有大量的create index语句，需要和客户确认该业务是否合理。

   1	SELECT * from pg_stat_activity where state !='idle' and usename !='Ruby';

   

3. 根据执行计划，发现在部分DN上耗时较高，查询表的倾斜情况，并未发现有倾斜的情况。

   1	SELECT table_skewness('table name');

   

4. 联系运维人员登录集群实例，检查内存相关参数，设置不合理，需要优化。
   • 单节点总内存大小为256G
   • max_process_memory为12G，设置过小
   • shared_buffers为32M，设置过小
   • work_mem：CN：64M 、DN：64M
   • max_active_statements: -1（不限制并发数）

5. 按以下值设置：

   gs_guc set -Z coordinator -Z datanode -N all -I all -c "max_process_memory=25GB"

   gs_guc set -Z coordinator -Z datanode -N all -I all -c "shared_buffers=8GB"

   gs_guc set -Z coordinator -Z datanode -N all -I all -c "work_mem=128MB"

6. 进一步分析扫描慢的原因，发现表数据膨胀严重，对其中一张8G大小的表，总数据量5万条，做完vacuum full后大小减小为5.6M。

   

   

处理方法

1. 对业务涉及到的常用的大表，执行vacuum full操作，清理脏数据。
2. 设置GUC内存参数。