Resource Manager allows you to monitor CPU usage and memory usage in real time. You can monitor resources based on user threads or resource groups. You can monitor CPU usage and memory usage to obtain the real-time running status of your system. In this way, you can identify the sources of system pressure, and schedule your business to distribute the loads. This topic describes how to use the Resource Manager feature.

Considerations

  • Resource groups cannot be configured. All the connected user threads belong to the default resource group user_default. System background threads belong to the sys_default resource group.
  • If the thread pool feature is enabled, the collected CPU and memory statistics are inaccurate. Do not enable the resource group feature and the thread pool feature at the same time.

Parameters

The following table describes the parameters that you must specify for Resource Manager.

Note If you want to enable the Resource Manager feature, you must set the resource_group_enabled, performance_schema, and cpu_usage_capture_enabled parameters to ON.
Parameter Description
resource_group_enabled Specifies whether to enable the resource group feature. Valid values: ON and OFF. Default value: OFF.
performance_schema Specifies whether to enable the performance schema feature. Valid values: ON and OFF. Default value: OFF.
cpu_usage_capture_enabled Specifies whether to enable the CPU usage collection feature. Valid values: ON and OFF. Default value: OFF.
cpu_usage_capture_interval The interval at which you want to collect the CPU usage. Unit: milliseconds. Valid values: 100 to 1000. Default value: 500.
cpu_usage_summarize_interval The interval at which you want to analyze the collected CPU usage statistics. Unit: milliseconds. Valid values: 1000 to the value that is specified by ULONG_MAX. Default value: 2000.
performance_schema_minimal Specifies whether to minimize the memory usage of the performance schema feature. Valid values: ON and OFF. Default value: OFF.
Note You can specify this parameter only after the performance_schema parameter is set to ON. This parameter does not take effect if the performance_schema parameter is set to OFF.

Monitor resources

  • You can execute the following SQL statements to query the resources used by threads:
    call db_resource_manager.show_processlist();
    call db_resource_manager.show_processlist_verbose();

    By default, the preceding statements return only the resources that are used by the threads of the current user. If you have the PROCESS permission, the statements return the resources that are used by the threads of all users.

    • The show_processlist statement returns the sum of the resources that are used by the main thread and the parallel subthreads.
    • The show_processlist_verbose statement returns the resources that are used by each subthread.1
  • You can execute the following SQL statement to query all the resource groups in the current system and the resource group configurations:
    select * from information_schema.resource_group;
    Note Before you query resource group information, make sure that the resource_group_enabled parameter is set to ON.