Help Center > > Developer Guide> Excellent Practices> Excellent Practices for Data Skew Query> Quickly Locating the Tables That Cause Data Skew

Quickly Locating the Tables That Cause Data Skew

Updated at: Mar 13, 2020 GMT+08:00

Currently, the table_distribution(schemaname text, tablename text) and table_distribution() functions as well as the PGXC_GET_TABLE_SKEWNESS view are provided to query for data skew. You can choose any of them as needed.

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 PGXC_GET_TABLE_SKEWNESS view to query for 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;
    

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel