Use Statement Concurrency Control (CCL) rules - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

O Alibaba Cloud oferece o recurso de controle de concorrência (CCL) e o pacote DBMS_CCL para garantir a estabilidade das instâncias do ApsaraDB RDS for MySQL em casos de tráfego de solicitações inesperado, instruções que consomem muitos recursos e alterações no modelo de acesso SQL. O recurso CCL foi desenvolvido com base em regras de sintaxe. Use o pacote DBMS_CCL para limitar o número máximo de instruções SQL executadas simultaneamente para tipos específicos. Quando a quantidade de instruções SQL em execução simultânea atinge o limite máximo, as instruções excedentes entram em estado de espera até que o número fique abaixo desse limite.

Pré-requisitos

A instância RDS deve executar uma das seguintes versões do MySQL:

MySQL 8.4
MySQL 8.0 com versão secundária do mecanismo 20190816 ou posterior
MySQL 5.7 com versão secundária do mecanismo 20200630 ou posterior

Parâmetros

Parâmetro	Descrição
ccl_max_waiting_count	O número máximo de instruções SQL que correspondem à mesma regra CCL e podem estar no estado `Concurrency control waiting`. Se esse limite for ultrapassado, as instruções falharão e retornarão o erro `ERROR 7534 (HY000): Concurrency control waiting count exceed max waiting count`. O valor padrão é 0, indicando que não há limite imposto.
ccl_wait_timeout	O tempo máximo de espera de uma instrução SQL no estado `Concurrency control waiting`. Caso esse limite seja ultrapassado, a instrução SQL é executada e deixa de ser limitada pelo CCL. O valor padrão é 86400s.

Notas de uso

As operações de CCL afetam apenas a instância RDS atual, pois nenhum log é gerado. Por exemplo, as operações de CCL realizadas na instância RDS primária não são sincronizadas com a instância RDS secundária, a instância RDS somente leitura ou a instância RDS de recuperação de desastres.
O CCL fornece um mecanismo de tempo limite para resolver deadlocks de transação causados por instruções DML. As threads pendentes também respondem ao tempo limite da transação e à terminação da thread, evitando assim os deadlocks.

Descrição do recurso

O CCL oferece recursos com base nas seguintes dimensões:

Comando SQL: o tipo de comando SQL, como SELECT, UPDATE, INSERT ou DELETE.
Objeto: o objeto no qual um comando SQL é executado, como uma TABLE ou VIEW.
Palavras-chave: as palavras-chave dentro de um comando SQL.
Modelo: o modelo de um comando SQL.

Criar uma tabela CCL

O AliSQL utiliza uma tabela do sistema chamada concurrency_control para armazenar regras CCL. O sistema cria essa tabela automaticamente durante a inicialização. Crie a tabela executando as seguintes instruções:

CREATE TABLE `concurrency_control` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Type` enum('SELECT','UPDATE','INSERT','DELETE','TEMPLATE') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'SELECT',
  `Schema_name` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `Table_name` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `Concurrency_count` bigint NOT NULL,
  `Keywords` text COLLATE utf8mb3_bin,
  `State` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'Y',
  `Ordered` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
  `Digest` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `SQL_template` longtext COLLATE utf8mb3_bin,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 COMMENT='Concurrency control'

Parâmetro	Descrição
Id	O ID da regra CCL.
Type	O tipo da instrução SQL.
Schema_name	O nome do banco de dados.
Table_name	O nome da tabela no banco de dados.
Concurrency_count	A contagem de concorrência. Se o valor for 0, todas as instruções SQL correspondentes à regra serão recusadas e retornarão o erro `ERROR 7535 (HY000): Concurrency control refuse to execute query`.
Keywords	A palavra-chave. Separe várias palavras-chave com ponto e vírgula (;).
State	Especifique se a regra deve ser ativada.
Ordered	Especifique se várias keywords devem ser correspondidas em sequência.
Digest	A string de hash de 64 bytes obtida pelo cálculo de hash com base no SQL_template.
SQL_template	O modelo da instrução SQL.

Gerenciar regras CCL

O AliSQL fornece quatro interfaces de gerenciamento no pacote DBMS_CCL. A lista a seguir descreve essas interfaces:

add_ccl_rule: Adicione uma regra

Sintaxe

dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'<Keywords>');

Exemplos
- Defina a contagem de concorrência como 10 para todas as instruções SELECT.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- Defina a contagem de concorrência como 20 para instruções SELECT que contêm a palavra-chave key1.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- Defina a contagem de concorrência como 20 para instruções SELECT executadas na tabela test.t e que contêm a palavra-chave key2.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, 'key2');
```

add_ccl_template_rule: Adicione uma regra de modelo

Pré-requisitos

Para adicionar uma regra de modelo, sua instância deve executar uma das seguintes versões:
- MySQL 8.4
- MySQL 8.0 com versão secundária do mecanismo 20230630 ou posterior
- MySQL 5.7 com versão secundária do mecanismo 20241231 ou posterior

Sintaxe
```
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'', 'Template_sql');
```
Nota
- Não é necessário especificar o parâmetro Table_name. Especifique uma instrução SQL para o parâmetro Template_sql.
- Use a função integrada STATEMENT_DIGEST_TEXT e visualize o modelo SQL correspondente à instrução SQL.

Exemplo

Defina a contagem de concorrência como 30 para instruções SQL no banco de dados test que correspondem ao modelo 'SELECT c FROM t1 WHERE id=?'.
```
call dbms_ccl.add_ccl_rule('TEMPLATE', 'test', '', 30, '', 'SELECT c FROM t1 WHERE id=4');
```
Nota
- Cada instrução SQL pode corresponder a apenas uma regra.
- As regras de diferentes tipos são listadas em ordem decrescente: regra de modelo, regra completa, regra de palavra-chave e regra de instrução SQL. Cada conjunto de regras é verificado em sequência para determinar se uma regra foi atingida.

del_ccl_rule: Exclua uma regra

Sintaxe
```
dbms_ccl.del_ccl_rule(<Id>);
```

Exemplo

Exclua a regra CCL cujo ID é 15.

mysql> call dbms_ccl.del_ccl_rule(15);

Nota

Caso a regra a ser excluída não exista, o sistema relatará um aviso. Execute o comando show warnings; e visualize os detalhes do aviso.

mysql> call dbms_ccl.del_ccl_rule(100);
  Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 7514 | Concurrency control rule 100 is not found in table |
| Warning | 7514 | Concurrency control rule 100 is not found in cache |
+---------+------+----------------------------------------------------+

show_ccl_rule: Visualize regras ativadas

Sintaxe
```
dbms_ccl.show_ccl_rule();
```

Exemplo

mysql> call dbms_ccl.show_ccl_rule();
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| 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 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

A tabela a seguir descreve MATCHED, RUNNING e WAITTING.

Parâmetro	Descrição
MATCHED	O número de correspondências da regra.
RUNNING	O número de threads em execução simultânea sob a regra.
WAITTING	O número de threads pendentes sob a regra.

flush_ccl_rule: Aplique regras

Sintaxe

Caso você modifique regras diretamente na tabela concurrency_control, as alterações não entrarão em vigor imediatamente. Execute o seguinte comando para aplicar as regras:
```
dbms_ccl.flush_ccl_rule();
```

Exemplo

mysql> update mysql.concurrency_control set CONCURRENCY_COUNT = 15 where Id = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> call dbms_ccl.flush_ccl_rule();
Query OK, 0 rows affected (0.00 sec)

Teste do recurso

Script de teste

Crie as regras para três dimensões. Para isso, execute as seguintes instruções:

-- Create a rule for which the number of concurrent SELECT statements with the sbtest1 keyword is 3.
call dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

-- Create a rule for which the number of concurrent SELECT statements with the sbtest2 keyword is 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');  

-- Create a rule for which the number of concurrent SELECT statements is 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');

Cenário de teste
Use o sysbench para os testes nos seguintes cenários:
- 64 threads
- 4 tabelas
- select.lua

Resultados do teste

Execute a seguinte instrução para consultar o número de threads simultâneas sob as regras:

mysql> call dbms_ccl.show_ccl_rule();
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
| 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)

Os números exibidos na coluna RUNNING correspondem aos valores especificados na criação das regras.