Quickly Locating Tables That Cause Data Skew

Scenario 1: Data Skew Caused by a Full Disk

First, use the pg_stat_get_last_data_changed_time(oid) function to identify tables with recent data changes. Since the last modification time of tables is recorded only on the CN where INSERT, UPDATE, and DELETE operations are executed, you can use the following encapsulated function to pinpoint tables that were modified within the past day (adjustable in the function):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

gaussdb=# CREATE OR REPLACE FUNCTION get_last_changed_table(OUT schemaname text, OUT relname text) 
RETURNS setof record 
AS $$ 
DECLARE 
 row_data record; 
 row_name record; 
 query_str text; 
 query_str_nodes text; 
 BEGIN 
  query_str_nodes := 'SELECT node_name FROM pgxc_node where node_type = ''C'''; 
  FOR row_name IN EXECUTE(query_str_nodes) LOOP 
   query_str := 'EXECUTE DIRECT ON (' || row_name.node_name || ') ''SELECT b.nspname,a.relname FROM pg_class a INNER JOIN pg_namespace b on a.relnamespace = b.oid where pg_stat_get_last_data_changed_time(a.oid) BETWEEN current_timestamp - 1 AND current_timestamp;'''; 
   FOR row_data IN EXECUTE(query_str) LOOP 
    schemaname = row_data.nspname; 
    relname = row_data.relname; 
    return next; 
   END LOOP; 
  END LOOP; 
  return; 
 END; $$ 
LANGUAGE 'plpgsql'; 

Then execute table_distribution(schemaname text, tablename text) to query the storage space occupied by the tables on each DN.

1

gaussdb=# SELECT table_distribution(schemaname,relname) FROM get_last_changed_table();

Scenario 2: Routine Data Skew Inspection

• If the number of tables in the database is less than 10,000, use the skew view to query data skew of all tables in the database.

  1	gaussdb=#SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;

• When there are a substantial number of tables (at least over 10,000) in the database, using the PGXC_GET_TABLE_SKEWNESS view involves thorough skewness calculations across the entire database, potentially requiring a significant amount of time (hours). To optimize the calculations and reduce the output columns, you can use the table_distribution() function to customize the output based on the definition of the PGXC_GET_TABLE_SKEWNESS view.

  1 2 3 4 5 6

  gaussdb=#SELECT schemaname,tablename,max(dnsize) AS maxsize, min(dnsize) AS minsize FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype = 'H' GROUP BY schemaname,tablename;