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.
- 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.
The following table describes the parameters that you must specify for Resource Manager.
|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.
- 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.
show_processliststatement returns the sum of the resources that are used by the main thread and the parallel subthreads.
show_processlist_verbosestatement returns the resources that are used by each subthread.
- 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_enabledparameter is set to ON.