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

问题现象

数据库中原先执行几秒钟的SQL语句，现在执行二十几秒未出结果，导致前台页面数据加载超时，无法对用户提供图表显示。

原因分析

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

分析过程

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. 联系运维人员登录集群实例，检查内存相关参数，设置不合理，需要优化。
   • 单节点总内存大小为256GB。
   • max_process_memory为12GB，设置过小。
   • shared_buffers为32MB，设置过小。
   • work_mem：CN：64MB 、DN：64MB。
   • 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. 进一步分析扫描慢的原因，发现表数据膨胀严重，对其中一张8GB大小的表，总数据量5万条，做完VACUUM FULL后大小减小为5.6MB。

   

   

处理方法

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