Managing O&M Plans

Notes and Constraints

• This feature is supported only in 8.1.3 or later. The small CU threshold and small CU ratio are displayed only for version 9.1.0.200 or later clusters.
• After completing a VACUUM FULL O&M task, the system automatically performs the ANALYZE operation.
• Only clusters running version 8.1.3 or later support the common configuration module for O&M tasks. For earlier versions, contact technical support to upgrade them.

• Do not choose peak hours when configuring the time window for automatic vacuum O&M tasks. Otherwise, service deadlocks may occur.
• User tables can have up to 24 concurrent Vacuum Full O&M tasks, or as few as 0. System tables allow only 1 concurrent task, or none at all. The concurrency value is not user-defined but can be automatically adjusted based on the value of io_util.
  • Two intervals for 0% to 60%
    • 0% to 30%: The concurrency value increases by 2 each time the value of io_util decreases by 15%.
    • 30% to 60%: The concurrency value is incremented by 1 each time the value of io_util decreases by 15%.
  • 60% to 70%: The concurrency value remains unchanged.
  • Above 70%: The concurrency value decreases by 1 until it reaches 0.
• The scheduler checks the expansion of column-store small compression units (CUs) within a set time. If a large number of small CUs whose average number of records is less than the specified threshold (for example, 1,000) are detected, the CUs may be fragmented or space is wasted. In this case, the scheduler preferentially processes these tables. The scanning of column-store CUs is not limited by table bloat or table reclaimable space.
• A maximum of 100 tables can be added to the priority list.
• The scheduler autovacuum function depends on the statistics. If the statistics are inaccurate, the execution sequence and results may be affected.
• The scheduler does not support names containing spaces or single quotation marks, including database names, schema names, and table names. Otherwise, the tables will be skipped. Priority tables whose names contain spaces or single quotation marks will also be skipped automatically.

Setting the Common Configurations of O&M Tasks

The configuration applies to the Vacuum Full O&M task for each user table. Running tasks are unaffected. The new periodic task settings take effect on the next run.

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List. In the cluster list, click the name of the target cluster.
3. On the displayed Cluster Information page, click the Intelligent O&M tab.
4. In the Common O&M Task Configurations area on the upper part of the page, modify the following common configuration items:
   • Maximum Concurrent User Table VACUUM FULL Tasks: Sets the limit for how many VACUUM FULL maintenance tasks can run at the same time on each user table. The value range is 1 to 24 and recommended value is 5. Running tasks are not affected. For periodic tasks, the new limit for concurrent tasks applies the next time the O&M task runs.
   • Small CU threshold: helps identify small CU tables. A table is classified as small if its CU value is lower than the threshold. When the CU value is at or below the threshold, it triggers Vacuum. A higher threshold value increases the trigger sensitivity. The recommended default value for this parameter is 1000. This parameter is displayed only for clusters running version 9.1.0.200 or later.
   • Small CU ratio: indicates the ratio of small CU tables to all CU tables. When the ratio is at or above the parameter value, Vacuum is triggered. A lower value increases the trigger sensitivity. The recommended default value for this parameter is 50%. This parameter is displayed only for clusters running version 9.1.0.200 or later.

5. Confirm the settings and click Save.

Adding an O&M Plan

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List. In the cluster list, click the name of the target cluster.
3. On the displayed Cluster Information page, click the Intelligent O&M tab.
4. In the O&M Plan area, click Add O&M Task.
5. In the displayed dialog box, configure basic information about the O&M task.

   Table 1 Basic configuration items of an O&M task

   Configuration Item	Description	Example
   O&M Task	Vacuum (Currently, only Vacuum O&M tasks are supported.)	Vacuum
   Description	Brief description of the intelligent O&M task.	This intelligent O&M task helps users periodically invoke Vacuum commands to reclaim space.
   Remarks	Supplementary information.	-
   Scheduling Mode	Its options include Auto: Intelligent O&M scans the database in a specified time window, and automatically delivers table-level vacuum tasks by service load and reclaimable space of user tables. Specify: You need to specify a vacuum target. Intelligent O&M will automatically deliver a table-level vacuum task in a specified time window. Priority: You can specify vacuum targets. During the remaining time window (if any), Intelligent O&M will automatically scan other tables that can be vacuumed and deliver table-level vacuum tasks. NOTE: You are advised to select Specify for VACUUM and VACUUM FULL operations. Do not perform VACUUM FULL on wide column-store tables. Otherwise, memory bloat may occur.	Specify
   Autovacuum	Its options include User tables (VACUUM FULL) and System catalogs (VACUUM). A system catalog VACUUM transaction holds a level-4 lock (share update exclusive lock). It has minimal impact, blocking only concurrent DDL on the system catalog itself. A user table VACUUM FULL transaction holds a level-8 lock (access exclusive lock). All the other transactions on the table are blocked until VACUUM FULL is complete. To avoid affecting services, you are advised to perform VACUUM FULL during off-peak hours. In clusters of version 9.1.0.x, VACUUM FULL lock mechanism is added and enabled by default. The O&M VACUUM FULL task automatically releases its lock to prevent service blocking. You can use the forbid_interrupt_vacuum_full_appnames parameter to control which statements are given locks by VACUUM FULL. If the application name of a statement is included in the list specified by the parameter, VACUUM FULL does not lock the statement. During VACUUM FULL, the space usage will first increase and then decrease, because this operation requires the same space as the table to be vacuumed. The actual table size is calculated as the total table size multiplied by (1 - dirty page rate). Ensure you have sufficient space before doing VACUUM FULL.	User tables (VACUUM FULL)
   Vacuum Targets	You can configure vacuum targets. Each row corresponds to a table. Each table is represented in the format of "Database name Schema name Table name".	database1 schema1 table1
   Advanced Settings	If you select Custom, you can configure Autovacuum Trigger, including Table Bloat and Table Reclaimable Space. If you select Default, the default configuration is used. NOTE: VACUUM bloat rate: After frequent UPDATE and DELETE operations are performed in a database, the deleted or updated rows are logically deleted from the database, but actually still exist in tables. Before VACUUM is complete, such data is still stored in disks, causing table bloat. If the bloat rate reaches the percentage threshold set in an O&M task, VACUUM will be automatically triggered.	Default configuration (table bloat rate 80% or reclaimable space 100 GB.)

6. Click Next: Configure Schedule and configure scheduling for O&M tasks.

   Select an O&M type.

   • One-off: Set the start time and end time of the task.
   • Periodic: Set Interval to Daily, Weekly, or Monthly, and set Time Window. Intelligent O&M will automatically analyze the time window and deliver O&M tasks accordingly.

7. Click Next: Finish. Confirm the information and click Finish to submit the request. On the O&M Plan page, check for the added O&M plan.

Managing O&M Plans

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List. In the cluster list, click the name of the target cluster.
3. On the displayed Cluster Information page, click the Intelligent O&M tab.
4. In the O&M Plan area, perform the following operations on a specified O&M plan.

   Table 2 Managing O&M plans

   Operation	Description
   Modifying a plan	Locate a plan, click Modify in the Operation column, and modify the parameters as needed. The parameters for modifying an O&M task are identical to those for adding one. For details, see Adding an O&M Plan. The modification takes effect upon the next task running.
   Suspending plans	Select plans and click Suspend above the list to suspend the specified O&M plans.
   Resuming plans	Select plans and click Resume above the list to resume the suspended O&M plan.
   Viewing plan details	Locate a plan, click Details in the Operation column, and check the information on the displayed page.
   Deleting plans	Select plans and click Delete above the list to delete the specified O&M plans.

5. Confirm the change and click OK.