Collecting Statistics on and Diagnosing GaussDB(DWS) Data Tables
GaussDB(DWS) provides statistics and diagnostic tools for you to learn table status, including:
- Skew Rate: Improper distribution columns can cause severe skew during operator computing or data spill to disk. The workloads will be unevenly distributed on DNs, resulting in high disk usage on a single DN and affecting performance. You can query the table skew rate. (This page displays information about the 50 largest tables whose skew rate is higher than 5%.) Based on the table size and skew rate, you can reselect distribution columns for tables with severe skew. For clusters of version 8.1.0 or later, you can use the ALTER TABLE syntax to directly make the adjustment. For the adjustments for other versions, see How Do I Change Distribution Columns?
- Dirty Page Rate: DML operations on tables may generate dirty data, which unnecessarily occupies cluster storage. To handle large tables and those with a high dirty page rate, you can query the dirty page rate of a table and take into account its size and dirty page rate. This page displays information about the 50 largest tables whose dirty page rate is higher than 50%. For how to rectify the fault, see Solution to High Disk Usage and Cluster Read-Only.
- DDL Audit: DDL review is a type of SQL review. To prevent improper DDL design from affecting services, this tool checks whether DDL metadata is standard, detecting potential table definition problems in advance. The check result can also be used as a reference for locating performance issues.
Notes and Constraints
- Only 8.1.1.x and later versions support the table skew rate and dirty page rate features. For earlier versions, contact technical support.
- Only 8.1.1.300 and later versions support the DDL review feature. For earlier versions, contact technical support.
- The collection period of the table skew rate and dirty page rate can be configured on the Monitoring Collection page of the cluster. Frequent collection may affect cluster performance. Set a proper period based on your cluster workloads.
Skew Rate
- Log in to the DWS console.
- Choose Dedicated Clusters > Clusters and locate the cluster to be monitored.
- In the Operation column of the target cluster, click Monitoring Panel.
- In the navigation pane on the left, choose Tools > Table Diagnosis and click the Skew Rate tab. The tables that meet the statistics collection conditions in the cluster are displayed (top 50 tables with the skew rate higher than 5%).
This page uses a unique table skew rate calculation method. It computes the skew rate using this formula: (Largest table size per DN – Smallest table size per DN)/Average table size per DN.
To find the needed values, check the dnsize column in the gs_table_distribution view.
Dirty Page Rate
- Log in to the DWS console.
- Choose Dedicated Clusters > Clusters and locate the cluster to be monitored.
- In the Operation column of the target cluster, click Monitoring Panel.
- In the navigation pane on the left, choose Tools > Table Diagnosis and click the Dirty Page Rate tab. The tables that meet the statistics collection conditions in the cluster are displayed.
DDL Audit
- Log in to the DWS console.
- Choose Dedicated Clusters > Clusters and locate the cluster to be monitored.
- In the Operation column of the target cluster, click Monitoring Panel.
- In the navigation pane on the left, choose Tools > Table Diagnosis, and click the DDL Audit tab. The audit results are displayed.
The selected audit items are displayed on the DDL Audit tab by default. You can configure the audit items on the Monitoring Collection page. For more information, see Table 1.
- If the review result of an item is Failed, click View to go to the details page.
- Click Export in the upper left corner to export the audit result.
Manually Auditing DDL Items
- Log in to the DWS console.
- Choose Dedicated Clusters > Clusters and locate the cluster to be monitored.
- In the Operation column of the target cluster, click Monitoring Panel. The database monitoring page is displayed.
- In the navigation pane on the left, choose Tools > Table Diagnosis, and click the DDL Audit tab. On the page that is displayed, select the items to be audited and click One-Click Audit.
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