Intelligent O&M Overview
DWS provides the intelligent O&M feature to help you quickly and efficiently run O&M tasks. Intelligent O&M selects a proper time window and concurrency to complete specified tasks based on the cluster load. During O&M tasks, intelligent O&M monitors user service changes and promptly adapts task execution policies to minimize the impact on user services. Periodic tasks and one-off tasks are supported, and you can configure the time window as required.
Intelligent O&M ensures high availability. When the cluster is abnormal, failed O&M tasks will be retried. If some steps of an O&M task cannot be completed due to an abnormal cluster, the failed steps will be skipped for cost saving.
Application Scenarios
Intelligent O&M applies to the following scenarios:
- Frequent writes: A large number of DELETE or UPDATE operations can result in severe table bloats. To control the size of a table, run VACUUM periodically.
- Batch deletion: After a large number of DELETE operations are performed, the space is not automatically released. You need to run VACUUM FULL to physically reclaim the space.
- Query performance deterioration: A high dirty page rate slows down full table scans. Running VACUUM can improve the query efficiency.
Example: An enterprise updates and deletes a large number of orders every day, leading to an increasing dirty page rate. You can create an intelligent O&M plan to configure periodic VACUUM tasks, which are automatically executed during off-peak hours to keep the dirty page rate below 10% and ensure stable query performance.
Overview
The Intelligent O&M page consists of the following areas:
- Common O&M Task Configurations
- Maximum concurrent VACUUM FULL tasks on a user table: Set this parameter based on the available disk space and I/O load within a specific time window. The value ranges from 1 to 24. The recommended value is 5.
- Small CU threshold: This parameter helps identify small CU tables. A table with a CU value lower than the threshold is considered small. If the CU value is the same as or lower than the threshold, VACUUM is triggered. A higher threshold means a higher trigger sensitivity. The recommended default value is 1,000.
- Small CU proportion: proportion of small CU tables to all CU tables. If the proportion is the same as or exceeds the parameter value, VACUUM is triggered. A lower value means a higher trigger sensitivity. The recommended default value for this parameter is 50%.
- Ongoing O&M Tasks: information about ongoing O&M tasks (only VACUUM O&M tasks)
- If user data bloats and the disk memory is insufficient, you can perform the VACUUM operation.
- Frequent table creation and deletion can lead to table bloating. To free up space, you can run the VACUUM command on system catalogs.
- Frequently update and delete operations can lead to table bloating. To free up space, you can run the VACUUM or VACUUM FULL command on system catalogs.
- O&M details: O&M Plan and O&M Status O&M Plan displays the basic information about all O&M tasks, and O&M Status displays the statuses of O&M tasks.
Documentation
- VACUUM SQL statements: VACUUM
- VACUUM FAQs: Do I Need to Run VACUUM FULL and ANALYZE on Common Tables Periodically in DWS? and Which System Catalogs in DWS Cannot Undergo the VACUUM FULL Operation?
- Handling slow VACUUM execution: Slow VACUUM FULL Execution.
