Table Bloating Causes Slow SQL Query and Failed Data Loading on the GUI

Symptom

The results of SQL statements that can be executed within seconds are not generated after more than 20 seconds. As a result, data loading on the GUI times out and charts cannot be displayed for users.

Possible Causes

• A large number of tables are frequently added, deleted, and modified and are not cleared in a timely manner. As a result, a large amount of dirty data exists and the table data size expands, resulting in slow query.
• The memory parameters are configured improperly.

Cause Analysis

1. The customer confirms that some services are slow. Some slow SQL statements can be provided and the execution plan can be printed. The time is spent mostly on the index scan, which may be caused by I/O contention. After I/O monitoring, no I/O resource usage bottleneck is found.

   

   

   

2. Query the active SQL statements. A large number of create index statements are found. Confirm with the customer whether the service is proper.

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

   

3. According to the execution plan, it takes a long time to execute statements on some DNs. No table skew occurs.

   1	SELECT table_skewness('table name');

   

4. Contact the O&M personnel to log in to the cluster instance and check the memory parameters. The parameters are configured improperly and need to be adjusted.
   • The total memory size of a single node is 256 GB.
   • The value of max_process_memory is 12 GB, which is too small.
   • The value of shared_buffers is 32 MB, which is too small.
   • work_mem: CN: 64 MB; DN: 64 MB
   • The value of max_active_statements is -1. (The number of concurrent statements is not limited.)

5. Configure this parameter as follows:

   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. It is found that the table data size expands excessively. After the VACUUM FULL operation is performed on an 8 GB table that contains 50,000 data records, the table size decreases to 5.6 MB.

   

   

Handling Procedure

1. Perform the VACUUM FULL operation on large tables to clear dirty data.
2. Configure GUC memory parameters.