Quickly Locating Tables That Cause Data Skew
Currently, you can choose from the following ways based on your service needs to query data skew: the table_distribution(schemaname text, tablename text) function, the table_distribution() function, and the PGXC_GET_TABLE_SKEWNESS view. For further details, refer to the corresponding function and view sections in Developer Guide.
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;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot