Concurrency control - PolarDB - Centro de documentación de Alibaba Cloud

Para garantizar el funcionamiento estable de su clúster de PolarDB, utilice la función de control de concurrencia (CCL). Esta función le permite gestionar los picos repentinos de tráfico en la base de datos, las sentencias SQL que consumen muchos recursos y los cambios en los patrones de acceso SQL. PolarDB proporciona el kit de herramientas DBMS_CCL para simplificar la gestión de esta función.

Requisitos previos

Su clúster de PolarDB debe ejecutar una de a continuación versiones:

PolarDB for MySQL 8.0.
PolarDB for MySQL 5.7 con una versión de revisión 5.7.1.0.6 o posterior.

Nota
Si su clúster ejecuta PolarDB for MySQL 5.7 con una versión de revisión 5.7.1.0.27 o posterior, la función CCL es compatible con la función thread pool.
PolarDB for MySQL 5.6.

Notas de uso

Las reglas CCL solo se pueden modificar en el nodo principal. Las modificaciones se sincronizan automáticamente con todos los demás nodos.

Funcionamiento

Dimensiones de coincidencia

CCL utiliza cinco dimensiones para comparar una sentencia SQL entrante con una regla:

Dimensión	Descripción
TYPE	El tipo de la sentencia SQL, como SELECT, UPDATE, INSERT, DELETE o DDL.
SCHEMA	El nombre de la base de datos sobre la que opera la sentencia SQL.
TABLE	El nombre de la tabla o vista sobre la que opera la sentencia SQL.
KEYWORD	Una palabra clave dentro de la sentencia SQL. Especifique varias palabras clave en una regla CCL y sepárelas con un punto y coma (;).
DIGEST	La cadena hash generada a partir de la sentencia SQL. Para obtener más información, consulte STATEMENT_DIGEST().

Lógica de coincidencia

Si no se especifica el valor DIGEST en una regla CCL:
- Si se especifican los valores TYPE, SCHEMA y TABLE, la sentencia SQL debe coincidir con las tres dimensiones para que se aplique la regla.
- Si solo se especifica el valor TYPE, la sentencia SQL debe coincidir con la dimensión TYPE para que se aplique la regla.
Nota
Si también se especifica la dimensión KEYWORD, se realizan comprobaciones adicionales:
- Si se especifica una sola palabra clave, la sentencia SQL debe contenerla para que la coincidencia sea exitosa.
- Si se especifican varias palabras clave, la sentencia SQL debe contenerlas todas para que la coincidencia sea exitosa. El orden de las palabras clave en la sentencia SQL no importa.
Si se especifica el valor DIGEST en una regla CCL, el valor DIGEST de la sentencia SQL debe coincidir con el valor DIGEST de la regla. Si la regla también especifica el valor SCHEMA, el valor SCHEMA de la sentencia también debe coincidir.
Si no se especifica el valor SCHEMA en una regla CCL, la sentencia SQL solo debe coincidir con el valor DIGEST para que se aplique la regla.

Prioridad de coincidencia

Una sentencia SQL solo puede coincidir con una regla CCL. Si coincide con varias reglas, PolarDB aplica la de mayor prioridad. Cuando varias reglas tienen la misma prioridad, PolarDB utiliza la que tiene el ID más pequeño. La prioridad de una regla depende de sus dimensiones de coincidencia, en el siguiente orden descendente:

DIGEST
TYPE, SCHEMA y TABLE
Solo TYPE

Parámetros

Modifique a continuación parámetros en la consola de PolarDB. Para obtener más información, consulte Configure cluster and node parameters.

Parámetro

Descripción

loose_ccl_mode

La acción que se ejecuta cuando se supera el límite de concurrencia. Valores válidos:

WAIT (predeterminado): la sentencia SQL se coloca en una cola y espera a que finalicen otras sentencias.
REFUSE: se devuelve un error.

Nota

Este parámetro solo es compatible con PolarDB for MySQL 8.0. En las versiones 5.6 y 5.7, el sistema coloca las sentencias en cola automáticamente.

loose_ccl_max_waiting_count

Cuando loose_ccl_mode se establece en WAIT, este parámetro indica el número máximo de sentencias SQL en cola para una sola regla CCL. El sistema devuelve un error si se supera este límite.

Valores válidos: de 0 a 65536. Valor predeterminado: 0.

Nota

Este parámetro solo es compatible con PolarDB for MySQL 5.7 y 8.0.

Tabla de reglas CCL

PolarDB almacena las reglas CCL en la tabla del sistema concurrency_control. El sistema crea esta tabla automáticamente durante el inicio, por lo que no necesita crearla manualmente. A continuación se muestra la sentencia CREATE TABLE que define la estructura de la tabla:

CREATE TABLE concurrency_control (
  Id bigint AUTO_INCREMENT NOT NULL,
  Type varchar(64),
  Schema_name varchar(64),
  Table_name varchar(64),
  Concurrency_count bigint NOT NULL,
  Keywords text,
  State enum('N','Y') COLLATE utf8_general_ci DEFAULT 'Y' NOT NULL,
  Ordered enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
  Digest varchar(64),
  Digest_text longtext,
  Extra mediumtext,
  PRIMARY KEY Rule_id(id)
) Engine=InnoDB STATS_PERSISTENT=0 CHARACTER SET utf8 COLLATE utf8_bin
  comment='Concurrency control' TABLESPACE=mysql;

A continuación se describen los parámetros de la tabla concurrency_control.

Parámetro	Descripción
Id	El ID de la regla CCL.
Type	El tipo de la sentencia SQL, como SELECT, UPDATE, INSERT, DELETE o DDL.
Schema_name	El nombre de la base de datos.
Table_name	El nombre de la tabla en la base de datos.
Concurrency_count	El número máximo de sentencias concurrentes que admite la regla. Nota Establezca el valor de `Concurrency_count` en 0 para implementar una lista negra de SQL que bloquee la ejecución de las consultas coincidentes.
Keywords	La palabra clave o las palabras clave que se deben buscar. Separe varias palabras clave con un punto y coma (;).
State	Indica si la regla está habilitada. Valores válidos: Y (predeterminado): la regla está habilitada. N: la regla está deshabilitada.
Ordered	Si se especifican varias palabras clave en el campo Keywords, este parámetro indica si deben coincidir en el orden especificado. Valores válidos: N (predeterminado): las palabras clave del campo Keywords no necesitan coincidir en orden. Y: las palabras clave del campo Keywords deben coincidir en orden.
Digest	Una cadena hash de 64 bytes generada a partir de `Digest_text`. Para obtener más información, consulte STATEMENT_DIGEST().
Digest_text	El resumen normalizado de la sentencia SQL.
Extra	Información adicional.

Gestión de reglas CCL

PolarDB proporciona seis procedimientos almacenados en el paquete DBMS_CCL para gestionar las reglas CCL:

add_ccl_rule: agrega una regla CCL basada en TYPE, SCHEMA, TABLE y KEYWORD.
Sintaxis
```
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'<Keywords>');
```
Example
- Agregue una regla CCL para las sentencias SELECT con un recuento de concurrencia de 10. Al alcanzar este límite, el sistema coloca en cola o rechaza las sentencias posteriores.
```
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- Agregue una regla CCL para las sentencias SELECT que contienen la palabra clave key1, con un recuento de concurrencia de 20.
```
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- Agregue una regla CCL para las sentencias SELECT que contienen las palabras clave key1, key2 y key3, con un recuento de concurrencia de 20. Las palabras clave pueden aparecer en cualquier orden.
```
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1;key2;key3');
```
- Agregue una regla CCL con TYPE igual a SELECT, SCHEMA igual a test y TABLE igual a t. Cuando el recuento de concurrencia de las sentencias SELECT alcanza 10, estas se colocan en cola o se reporta un error.
```
CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 10, '');
```
add_ccl_digest_rule: agrega una regla CCL basada en el resumen de una sentencia.
Nota
A continuación se muestran las versiones del motor de base de datos compatibles con el procedimiento almacenado add_ccl_digest_rule:
- PolarDB for MySQL 8.0.1 con una versión de revisión 8.0.1.1.31 o posterior.
- PolarDB for MySQL 8.0.2 con una versión de revisión 8.0.2.2.12 o posterior.
Sintaxis
```
dbms_ccl.add_ccl_digest_rule('<Schema_name>', '<Query>', <Concurrency_count>);
```
Example
- Agregue una regla CCL que coincida con la sentencia SQL SELECT * FROM t1 para colocar las solicitudes en cola o reportar un error cuando el recuento de concurrencia alcance 10.
```
CALL dbms_ccl.add_ccl_digest_rule("", "SELECT * FROM t1", 10);
```
- Agregue una regla CCL para el esquema test y para las sentencias SQL que coincidan con SELECT * FROM t1, de modo que coloque las solicitudes en cola o reporte un error cuando el recuento de concurrencia sea 10.
```
CALL dbms_ccl.add_ccl_digest_rule("test", "SELECT * FROM t1", 10);
```
- Agregue una regla CCL que coincida con la sentencia SQL SELECT * FROM t1 WHERE col1=1 para colocar las solicitudes en cola o reportar un error cuando el recuento de concurrencia sea 10.
```
CALL dbms_ccl.add_ccl_digest_rule("", "SELECT * FROM t1 WHERE col1 = 1", 10);
```
  Nota
  Si una sentencia SQL contiene una constante, se produce una coincidencia incluso si los valores de la constante son diferentes. Por ejemplo, la regla CCL anterior también coincide con la sentencia SQL SELECT * FROM t1 WHERE col1 = 2.
add_ccl_digest_rule_by_hash: agrega una regla CCL basada en un hash de resumen precalculado.

Nota
El procedimiento almacenado add_ccl_digest_rule_by_hash solo es compatible si la versión del motor de PolarDB for MySQL es 8.0.1 y la versión de revisión es 8.0.1.1.31 o posterior.
Sintaxis
```
dbms_ccl.add_ccl_digest_rule_by_hash('<Schema_name>', '<Digest>', <Concurrency_count>);
```
Example
- Agregue una regla CCL para las sentencias SQL cuyo valor DIGEST coincida con 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a, de modo que coloque las solicitudes en cola o reporte un error cuando el recuento de concurrencia sea 10.
```
CALL dbms_ccl.add_ccl_digest_rule_by_hash('', '533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a', 10);
```
  En este caso, 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a es el valor DIGEST calculado para SELECT * FROM t1. Utilice el comando SELECT statement_digest("SELECT * FROM t1") para calcular este valor u obténgalo de otros módulos.
- Agregue una regla CCL para las sentencias SQL donde SCHEMA sea test y el valor DIGEST de la sentencia SQL coincida con 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. Cuando el recuento de concurrencia alcance 10, las solicitudes se colocarán en cola o se devolverá un error.
```
CALL dbms_ccl.add_ccl_digest_rule_by_hash('test', '533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a', 10);
```
del_ccl_rule: elimina una regla CCL.
Sintaxis
```
dbms_ccl.del_ccl_rule(<Id>);
```
Example
Elimine la regla CCL con el ID 15.
```
CALL dbms_ccl.del_ccl_rule(15);
```
Si la regla que desea eliminar no existe, el sistema reporta una advertencia. Ejecute el comando SHOW WARNINGS; para consultar el contenido de la advertencia. A continuación se muestra un ejemplo:
1. Elimine la regla CCL con el ID 100.
```
CALL dbms_ccl.del_ccl_rule(100);
```
  Se devuelve a continuación salida:
```
Query OK, 0 rows affected, 2 warnings (0.00 sec)
```
2. Ejecute el siguiente comando para consultar el mensaje de advertencia:
```
SHOW WARNINGS;
```
  Se devuelve a continuación salida:
```
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 7517 | Concurrency control rule 100 is not found in table |
| Warning | 7517 | Concurrency control rule 100 is not found in cache |
+---------+------+----------------------------------------------------+
```
Nota
En el ejemplo anterior, PolarDB for MySQL versión 8.0 tiene un Code de 7517, PolarDB for MySQL versión 5.7 tiene un Code de 3267 y PolarDB for MySQL versión 5.6 tiene un Code de 3045.

show_ccl_rule: muestra las reglas CCL habilitadas que se encuentran actualmente en memoria.

Sintaxis

dbms_ccl.show_ccl_rule();

Example

CALL dbms_ccl.show_ccl_rule();

Se devuelve a continuación salida:

+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
|   17 | SELECT | test   | t     | Y     | N     |                30 |       0 |       0 |        0 |          |
|   16 | SELECT |        |       | Y     | N     |                20 |       0 |       0 |        0 | key1     |
|   18 | SELECT |        |       | Y     | N     |                10 |       0 |       0 |        0 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

Nota

A continuación se describen las columnas MATCHED, RUNNING y WAITTING.

MATCHED: el número total de veces que la regla ha coincidido con una sentencia.
RUNNING: el número de hilos que se están ejecutando actualmente bajo esta regla.
WAITTING: el número de hilos que esperan actualmente para ejecutarse bajo esta regla.

Utilice una sentencia UPDATE para modificar el ID de una regla y cambiar así su prioridad.

Sintaxis

UPDATE mysql.concurrency_control SET ID = xx WHERE ID = xx;

Example

Ejecute el siguiente comando para consultar las reglas CCL habilitadas en memoria:

CALL dbms_ccl.show_ccl_rule();

Se devuelve a continuación salida:

+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
|   17 | SELECT | test   | t     | Y     | N     |                30 |       0 |       0 |        0 |          |
|   16 | SELECT |        |       | Y     | N     |                20 |       0 |       0 |        0 | key1     |
|   18 | SELECT |        |       | Y     | N     |                10 |       0 |       0 |        0 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

Ejecute el siguiente comando para cambiar la prioridad de la regla CCL con el ID 17 al modificar su ID a 20:
```
UPDATE mysql.concurrency_control SET ID = 20 WHERE ID = 17;
```

Ejecute el siguiente comando para consultar las reglas modificadas:

CALL dbms_ccl.show_ccl_rule();

Se devuelve a continuación salida:

+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
|   16 | SELECT |        |       | Y     | N     |                20 |       0 |       0 |        0 | key1     |
|   18 | SELECT |        |       | Y     | N     |                10 |       0 |       0 |        0 |          |
|   20 | SELECT | test   | t     | Y     | N     |                30 |       0 |       0 |        0 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

flush_ccl_rule: si modifica una regla CCL al cambiar el contenido de la tabla concurrency_control, ejecute también el siguiente comando para que la regla surta efecto.
Sintaxis
```
dbms_ccl.flush_ccl_rule();
```
Example
Utilice una sentencia UPDATE para cambiar el recuento de concurrencia de una regla.
```
UPDATE mysql.concurrency_control SET CONCURRENCY_COUNT = 15 WHERE Id = 18;
```
Se devuelve a continuación salida:
```
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```
Ejecute el siguiente comando para aplicar el cambio:
```
CALL dbms_ccl.flush_ccl_rule();
```
Se devuelve a continuación salida:
```
Query OK, 0 rows affected (0.00 sec)
```

Prueba de la función

Cree tres reglas CCL con distintas dimensiones de coincidencia:

CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');  // Limit SELECT statements on the test.sbtest1 table to a concurrency of 3.
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');       // Limit SELECT statements containing the keyword 'sbtest2' to a concurrency of 2.
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');            // Limit all other SELECT statements to a concurrency of 2.

Utilice Sysbench para ejecutar una prueba con a continuación configuración:
- 64 hilos
- 4 tablas
- select.lua

Consulte el estado de concurrencia en tiempo real:

CALL dbms_ccl.show_ccl_rule();

Se devuelve a continuación salida:

+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE   | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
|   20 | SELECT | test   | sbtest1 | Y     | N     |                 3 |     389 |       3 |        9 |          |
|   21 | SELECT |        |         | Y     | N     |                 2 |     375 |       2 |       14 | sbtest2  |
|   22 | SELECT |        |         | Y     | N     |                 2 |     519 |       2 |       34 |          |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
3 rows in set (0.00 sec)

Revise la columna RUNNING. Los valores coinciden con los límites de concurrencia especificados, lo que indica que las reglas funcionan correctamente.