:Resource Manager

Prerequisites

Your PolarDB cluster must run PolarDB for MySQL 8.0, and its revision version must be 8.0.2.1.0 or later.

Usage notes

• Enabling the thread pool feature results in inaccurate statistics for CPU utilization and memory usage. Therefore, you cannot enable the resource_group_enabled and loose_thread_pool_enabled parameters at the same time.
• Custom resource group configurations are not supported. All user threads are assigned to the user_default resource group after a successful connection. System background threads are assigned to the sys_default resource group.

Parameter configuration

You can configure the following parameters for Resource Manager:

Resource monitoring

• You can run the following commands to query the resource usage of threads:

  call db_resource_manager.show_processlist();
  call db_resource_manager.show_processlist_verbose();

  Note

  • The show_processlist command aggregates the resources of parallel subthreads into the main thread, so the results do not display subthreads separately. As a result, the reported CPU utilization may exceed 100%.
  • The show_processlist_verbose command displays subthreads separately.

  mysql> call db_resource_manager.show_processlist();
  +------+------+-----------------+------+---------+------+-----------+-----------+----------------+-----------+---------+-----+----------------------------------------------+
  | ID   | USER | HOST            | DB   | COMMAND | TIME | STATE     | PARENT_ID | RESOURCE_GROUP | CPU_RATIO | MEM     | DOP | INFO                                         |
  +------+------+-----------------+------+---------+------+-----------+-----------+----------------+-----------+---------+-----+----------------------------------------------+
  |    7 | root | 127.0.0.1:47983 | test | Query   |    0 | starting  |      NULL | USR_default    | 0.00      |  455273 |   1 | call db_resource_manager.show_processlist()   |
  |   12 | root | 127.0.0.1:53189 | tpch | Query   |   51 | executing |      NULL | USR_default    | 380.86    | 1238314 |   4 | select                                       |
  |      |      |                 |      |         |      |           |           |                |           |         |     | l_returnflag,                                |
  |      |      |                 |      |         |      |           |           |                |           |         |     | l_linestatus,                                |
  |      |      |                 |      |         |      |           |           |                |           |         |     | sum(l_quantity) as sum_qty,                  |
  |      |      |                 |      |         |      |           |           |                |           |         |     | sum(l                                        |
  +------+------+-----------------+------+---------+------+-----------+-----------+----------------+-----------+---------+-----+----------------------------------------------+
  2 rows in set (0.00 sec)
  
  mysql> call db_resource_manager.show_processlist_verbose();
  +------+------+-----------------+------+---------+------+-----------+-----------+----------------+-----------+--------+-----+----------------------------------------------+
  | ID   | USER | HOST            | DB   | COMMAND | TIME | STATE     | PARENT_ID | RESOURCE_GROUP | CPU_RATIO | MEM    | DOP | INFO                                         |
  +------+------+-----------------+------+---------+------+-----------+-----------+----------------+-----------+--------+-----+----------------------------------------------+
  |    7 | root | 127.0.0.1:47983 | test | Query   |    0 | starting  |      NULL | USR_default    | 0.02      | 455273 |   1 | call db_resource_manager.show_processlist_verbose() |
  |   12 | root | 127.0.0.1:53189 | tpch | Query   |   53 | executing |      NULL | USR_default    | 0.00      | 521446 |   4 | select                                       |
  |      |      |                 |      |         |      |           |           |                |           |        |     | l_returnflag,                                |
  |      |      |                 |      |         |      |           |           |                |           |        |     | l_linestatus,                                |
  |      |      |                 |      |         |      |           |           |                |           |        |     | sum(l_quantity) as sum_qty,                  |
  |      |      |                 |      |         |      |           |           |                |           |        |     | sum(l                                        |
  |   13 | root | 127.0.0.1:53189 | tpch | Query   |   53 | executing |        12 | USR_default    | 96.71     | 192442 |   1 | NULL                                         |
  |   14 | root | 127.0.0.1:53189 | tpch | Query   |   53 | executing |        12 | USR_default    | 93.13     | 185954 |   1 | NULL                                         |
  |   15 | root | 127.0.0.1:53189 | tpch | Query   |   53 | executing |        12 | USR_default    | 94.73     | 183398 |   1 | NULL                                         |
  |   16 | root | 127.0.0.1:53189 | tpch | Query   |   53 | executing |        12 | USR_default    | 96.73     | 196194 |   1 | NULL                                         |
  +------+------+-----------------+------+---------+------+-----------+-----------+----------------+-----------+--------+-----+----------------------------------------------+
  6 rows in set (0.00 sec)

  You can also run the following commands to filter and sort the query results:

  select * from information_schema.processlist_verbose order by cpu_ratio desc limit 10;
  select * from information_schema.processlist_verbose where time > 60 order by mem desc

• You can run the following command to query all resource groups in the system and their configurations:

  select * from information_schema.resource_group;

  Note Before you run this query, you must set the resource_group_enabled parameter to ON. For more information, see Parameter configuration.

Performance impact

Enabling the Resource Manager feature has a minor impact on the performance of a PolarDB cluster. However, because enabling performance_schema activates numerous statistics collectors by default, it can have a more significant performance impact.

The preceding Sysbench performance tests show the following:

• Enabling Resource Manager (by setting performance_schema and resource_group_enabled to ) with performance_schema_minimal set to decreases performance by approximately 8% compared to when the feature is disabled (the default setting where performance_schema and resource_group_enabled are ).
• Enabling Resource Manager (by setting performance_schema and resource_group_enabled to ) with performance_schema_minimal set to causes a smaller performance decrease of approximately 2% compared to when the feature is disabled (the default setting where performance_schema and resource_group_enabled are ).