Contrôle de concurrence des instructions - ApsaraDB RDS - Centre de documentation Alibaba Cloud

Alibaba Cloud propose la fonctionnalité de contrôle de concurrence (CCL) et le package DBMS_CCL afin de garantir la stabilité des instances ApsaraDB RDS for MySQL en cas de trafic inattendu, d'instructions gourmandes en ressources ou de modifications du modèle d'accès SQL. La fonctionnalité CCL repose sur des règles syntaxiques. Utilisez le package DBMS_CCL pour limiter le nombre maximal d'instructions SQL de types spécifiques exécutées simultanément. Lorsque ce plafond est atteint, les instructions supplémentaires passent en état d'attente jusqu'à ce que le nombre d'instructions actives redescende sous la limite.

Prérequis

L'instance RDS doit exécuter l'une des versions MySQL suivantes :

MySQL 8.4
MySQL 8.0 avec une version mineure du moteur 20190816 ou ultérieure
MySQL 5.7 avec une version mineure du moteur 20200630 ou ultérieure

Paramètres

Parameter

Description

ccl_max_waiting_count

Le nombre maximal d'instructions SQL correspondant à la même règle CCL pouvant se trouver dans l'état Concurrency control waiting. Si cette limite est dépassée, les instructions échouent et renvoient l'erreur ERROR 7534 (HY000): Concurrency control waiting count exceed max waiting count. La valeur par défaut est 0, ce qui signifie qu'aucune limite n'est imposée.

ccl_wait_timeout

La durée maximale pendant laquelle une instruction SQL peut rester dans l'état Concurrency control waiting. Si cette limite est dépassée, l'instruction SQL est exécutée et n'est plus limitée par le CCL. La valeur par défaut est de 86 400 secondes.

Remarques d'utilisation

Les opérations CCL n'affectent que l'instance RDS actuelle, car aucun journal n'est généré. Par exemple, les opérations CCL effectuées sur l'instance RDS principale ne sont pas synchronisées avec l'instance RDS secondaire, l'instance RDS en lecture seule ou l'instance RDS de reprise après sinistre.
Le CCL fournit un mécanisme de délai d'expiration pour résoudre les interblocages de transactions causés par les instructions DML. Les threads en attente répondent également au délai d'expiration de la transaction et à la terminaison du thread afin d'éviter les interblocages.

Description de la fonctionnalité

Le CCL propose des fonctionnalités basées sur les dimensions suivantes :

Commande SQL : le type de commande SQL, tel que SELECT, UPDATE, INSERT ou DELETE.
Objet : l'objet sur lequel la commande SQL est exécutée, comme une TABLE ou une VIEW.
Mots-clés : les mots-clés présents dans une commande SQL.
Modèle : le modèle d'une commande SQL.

Créer une table CCL

AliSQL utilise une table système nommée concurrency_control pour stocker les règles CCL. Le système crée automatiquement cette table au démarrage. Exécutez les instructions suivantes pour créer la table :

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'

Parameter

Description

L'ID de la règle CCL.

Type

Le type de l'instruction SQL.

Schema_name

Le nom de la base de données.

Table_name

Le nom de la table dans la base de données.

Concurrency_count

Le nombre de connexions simultanées. Si vous définissez ce paramètre sur 0, toutes les instructions SQL correspondant à la règle sont refusées et renvoient l'erreur ERROR 7535 (HY000): Concurrency control refuse to execute query.

Keywords

Le mot-clé. Séparez plusieurs mots-clés par des points-virgules (;).

State

Indique si la règle est activée.

Ordered

Indique si plusieurs keywords doivent être mis en correspondance dans l'ordre.

Digest

La chaîne de hachage de 64 octets obtenue par calcul de hachage basé sur SQL_template.

SQL_template

Le modèle de l'instruction SQL.

Gérer les règles CCL

AliSQL propose quatre interfaces de gestion dans le package DBMS_CCL. La liste suivante décrit ces interfaces :

add_ccl_rule: Ajouter une règle

Syntaxe

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

Exemples
- Définissez le nombre de connexions simultanées pour toutes les instructions SELECT sur 10.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- Définissez le nombre de connexions simultanées sur 20 pour les instructions SELECT contenant le mot-clé key1.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- Définissez le nombre de connexions simultanées sur 20 pour les instructions SELECT exécutées sur la table test.t et contenant le mot-clé key2.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, 'key2');
```

add_ccl_template_rule: Ajouter une règle de modèle

Prérequis

Pour ajouter une règle de modèle, votre instance doit exécuter l'une des versions suivantes :
- MySQL 8.4
- MySQL 8.0 avec une version mineure du moteur 20230630 ou ultérieure
- MySQL 5.7 avec une version mineure du moteur 20241231 ou ultérieure

Syntaxe
```
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'', 'Template_sql');
```
Remarque
- Vous n'avez pas besoin de spécifier le paramètre Table_name. Vous devez spécifier une instruction SQL pour le paramètre Template_sql.
- Utilisez la fonction intégrée STATEMENT_DIGEST_TEXT pour afficher le modèle SQL correspondant à l'instruction SQL.

Exemple

Définissez le nombre de connexions simultanées sur 30 pour les instructions SQL de la base de données test qui correspondent au modèle 'SELECT c FROM t1 WHERE id=?'.
```
call dbms_ccl.add_ccl_rule('TEMPLATE', 'test', '', 30, '', 'SELECT c FROM t1 WHERE id=4');
```
Remarque
- Chaque instruction SQL ne peut correspondre qu'à une seule règle.
- Les règles de différents types sont répertoriées par ordre décroissant : règle de modèle, règle complète, règle de mot-clé, règle d'instruction SQL. Chaque ensemble de règles est vérifié tour à tour pour déterminer si une règle est activée.

del_ccl_rule: Supprimer une règle

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

Exemple

Supprimez la règle CCL dont l'ID est 15.

mysql> call dbms_ccl.del_ccl_rule(15);

Remarque

Si la règle que vous souhaitez supprimer n'existe pas, le système affiche un avertissement. Exécutez la commande show warnings; pour afficher les détails de l'avertissement.

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: Consulter les règles activées

Syntaxe
```
dbms_ccl.show_ccl_rule();
```

Exemple

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 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

Le tableau suivant décrit les paramètres MATCHED, RUNNING et WAITTING.

Parameter

Description

MATCHED

Le nombre de fois où la règle a été mise en correspondance.

RUNNING

Le nombre de threads s'exécutant simultanément selon la règle.

WAITTING

Le nombre de threads en attente selon la règle.

flush_ccl_rule: Appliquer les règles

Syntaxe

Si vous modifiez directement les règles dans la table concurrency_control, les modifications ne prennent pas effet immédiatement. Exécutez la commande suivante pour appliquer les règles :
```
dbms_ccl.flush_ccl_rule();
```

Exemple

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)

Test de la fonctionnalité

Script de test

Exécutez les instructions suivantes pour créer des règles pour trois dimensions.

-- 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, '');

Scénario de test
Utilisez sysbench pour effectuer les tests dans les scénarios suivants :
- 64 threads
- 4 tables
- select.lua

Résultats des tests

Exécutez l'instruction suivante pour interroger le nombre de threads simultanés selon les règles :

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)

Les nombres affichés dans la colonne RUNNING correspondent à ceux spécifiés lors de la création des règles.