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 query for the tables whose data is changed recently. The last change time of a table is recorded only on the CN where INSERT, UPDATE, and DELETE operations are performed. Therefore, you need to query for tables that are changed within the last day (the period can be changed in the function).

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

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 the table_distribution(schemaname text, tablename text) function to query for the storage space occupied the tables on each DN.

1

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	SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;

• If the number of tables in the database is no less than 10,000, you are advised to use the table_distribution() function instead of the PGXC_GET_TABLE_SKEWNESS view because the view takes a longer time (hours) due to the query of the entire database for skew columns. When you use the table_distribution() function, you can define the output based on PGXC_GET_TABLE_SKEWNESS, optimizing the calculation and reducing the output columns. For example:

  1 2 3 4 5 6

  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;