ApsaraDB RDS for MySQL statement queue - ApsaraDB RDS - Centre de documentation Alibaba Cloud

Dans les charges de travail MySQL à forte concurrence, les points de sérialisation au niveau des couches service et moteur, tels que les conflits de verrous transactionnels, peuvent dégrader les performances. AliSQL propose la fonctionnalité de file d'attente des instructions (statement queue), qui utilise un mécanisme de mise en file basé sur des compartiments (buckets) pour réduire la surcharge liée aux conflits et améliorer les performances de l'instance. Cette fonctionnalité attribue les instructions susceptibles d'entrer en conflit, comme celles opérant sur la même ligne, au même compartiment afin d'optimiser l'exécution concurrente.

Contexte

Au sein des couches service et moteur de MySQL, l'exécution concurrente des instructions implique plusieurs points de sérialisation susceptibles de provoquer des conflits. Par exemple, les conflits de verrous transactionnels sont fréquents lors de l'exécution d'instructions DML. Dans le moteur InnoDB, la granularité la plus fine d'un verrou transactionnel est le verrou de niveau ligne. Lorsque plusieurs instructions opèrent simultanément sur la même ligne, des conflits importants surviennent, ce qui réduit considérablement le débit du système à mesure que la concurrence augmente. Pour résoudre ce problème, AliSQL met à disposition la fonctionnalité de file d'attente des instructions afin d'améliorer les performances de l'instance en réduisant la surcharge induite par les conflits.

Prérequis

Cette fonctionnalité est disponible pour les instances ApsaraDB RDS for MySQL disposant des versions suivantes :

MySQL 8.4 en édition Basic ou High-availability
MySQL 8.0 en édition Basic ou High-availability (version mineure du moteur 20191115 ou ultérieure)
MySQL 5.7 en édition Basic ou High-availability (version mineure du moteur 20200630 ou ultérieure)

Variables de configuration

AliSQL fournit deux variables pour définir le nombre et la taille des compartiments dans la file d'attente des instructions. Modifiez ces variables dans la console ApsaraDB RDS.

ccl_queue_bucket_count : spécifie le nombre de compartiments.
- Valeurs valides : 1 à 64
- Valeur par défaut : 4
ccl_queue_bucket_size : spécifie le nombre d'instructions concurrentes autorisées par compartiment.
- Valeurs valides : 1 à 4096
- Valeur par défaut : 64

Syntaxe

AliSQL prend en charge deux types d'indices (hints) :

ccl_queue_value : hache une valeur spécifiée pour attribuer l'instruction à un compartiment.

Syntaxe :

/*+ ccl_queue_value([int | string]) */

Exemple :

update /*+ ccl_queue_value(1) */ t set c=c+1 where id = 1;

update /*+ ccl_queue_value('xyz') */ t set c=c+1 where name = 'xyz';

ccl_queue_field : hache la valeur d'un champ spécifié issu de la clause WHERE pour attribuer un compartiment.

Syntaxe :
```
/*+ ccl_queue_field(string) */
```
Exemple :
```
update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';
```
Remarque
- Les deux indices sont sensibles à la position et doivent être placés immédiatement après le mot-clé UPDATE.
- L'indice ccl_queue_field ne peut spécifier qu'un seul champ à la fois. Le format /*+ ccl_queue_field(id name) */ constitue une erreur de syntaxe et empêche la file d'attente des instructions de fonctionner. Si vous utilisez des indices dupliqués tels que /*+ ccl_queue_field(id) ccl_queue_field(name) */, seul le premier indice est pris en compte.
- Le champ spécifié dans l'indice ccl_queue_field doit apparaître dans la clause WHERE.
- Pour l'indice ccl_queue_field, la clause WHERE prend uniquement en charge les opérations binaires sur des champs bruts (sans application de fonctions ou de calculs sur le champ). La valeur située à droite de l'opération binaire doit être un nombre ou une chaîne de caractères.

API

AliSQL fournit deux fonctions pour surveiller la file d'attente des instructions :

dbms_ccl.show_ccl_queue() : interroge l'état actuel de la file d'attente des instructions.

call dbms_ccl.show_ccl_queue();

La sortie suivante est renvoyée :

+------+-------+-------------------+---------+---------+---------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING |
+------+-------+-------------------+---------+---------+---------+
|    1 | QUEUE |                64 |       1 |       0 |       0 |
|    2 | QUEUE |                64 |   40744 |      65 |       6 |
|    3 | QUEUE |                64 |       0 |       0 |       0 |
|    4 | QUEUE |                64 |       0 |       0 |       0 |
+------+-------+-------------------+---------+---------+---------+
4 rows in set (0.01 sec)

Le tableau suivant décrit les paramètres.

Paramètre

Description

CONCURRENCY_COUNT

Le nombre maximal d'instructions concurrentes autorisées.

MATCHED

Le nombre total d'instructions ayant correspondu à la règle.

RUNNING

Le nombre d'instructions actuellement en cours d'exécution.

WAITING

Le nombre d'instructions actuellement en attente.

dbms_ccl.flush_ccl_queue() : efface les données en mémoire.

call dbms_ccl.flush_ccl_queue();
call dbms_ccl.show_ccl_queue();

La sortie suivante est renvoyée :

+------+-------+-------------------+---------+---------+---------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING |
+------+-------+-------------------+---------+---------+---------+
|    1 | QUEUE |                64 |       0 |       0 |       0 |
|    2 | QUEUE |                64 |       0 |       0 |       0 |
|    3 | QUEUE |                64 |       0 |       0 |       0 |
|    4 | QUEUE |                64 |       0 |       0 |       0 |
+------+-------+-------------------+---------+---------+---------+
4 rows in set (0.00 sec)

Bonnes pratiques

Test de la fonctionnalité

Pour éviter de modifier longuement le code de votre application, vous pouvez utiliser la file d'attente des instructions conjointement avec un plan d'instruction (statement outline) afin de modifier rapidement et facilement votre application en ligne. L'exemple suivant utilise le cas de test update_non_index de SysBench pour illustrer cette procédure.

Environnement de test

Schéma de la table

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 MAX_ROWS=1000000;

Instruction de test
```
UPDATE sbtest1 SET c='xyz' WHERE id=0;
```

Script de test

./sysbench \
--mysql-host={$ip} \
--mysql-port={$port} \
--mysql-db=test \
--test=./sysbench/share/sysbench/update_non_index.lua \
--oltp-tables-count=1 \
--oltp_table_size=1 \
--num-threads=128 \
--mysql-user=u0

Procédure

Ajoutez un plan d'instruction en ligne.

CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, 
                                             ' /*+ ccl_queue_field(id) */ ',
                         "UPDATE sbtest1 SET c='xyz' WHERE id=0");

La sortie suivante est renvoyée :

Query OK, 0 rows affected (0.01 sec)

Consultez le plan d'instruction.

call dbms_outln.show_outline();

La sortie suivante est renvoyée :

+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| ID   | SCHEMA | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                           | HIT  | OVERFLOW | DIGEST_TEXT                                 |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
|    1 | test   | 7b945614749e541e0600753367884acff5df7e7ee2f5fb0af5ea58897910f023 | OPTIMIZER |       |    1 |  /*+ ccl_queue_field(id) */  |    0 |        0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
1 row in set (0.00 sec)

Vérifiez que le plan d'instruction a pris effet.

explain UPDATE sbtest1 SET c='xyz' WHERE id=0;

La sortie suivante est renvoyée :

+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | sbtest1 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

show warnings;

La sortie suivante est renvoyée :

+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ ccl_queue_field(id) */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xyz' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Vérifiez l'état de la file d'attente des instructions.

call dbms_ccl.show_ccl_queue();

La sortie suivante est renvoyée :

+------+-------+-------------------+---------+---------+---------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING |
+------+-------+-------------------+---------+---------+---------+
|    1 | QUEUE |                64 |       0 |       0 |       0 |
|    2 | QUEUE |                64 |       0 |       0 |       0 |
|    3 | QUEUE |                64 |       0 |       0 |       0 |
|    4 | QUEUE |                64 |       0 |       0 |       0 |
+------+-------+-------------------+---------+---------+---------+
4 rows in set (0.00 sec)

Démarrez le test.

sysbench \
--mysql-host={$ip} \
--mysql-port={$port} \
--mysql-db=test \
--test=./sysbench/share/sysbench/update_non_index.lua \
--oltp-tables-count=1 \
--oltp_table_size=1 \
--num-threads=128 \
--mysql-user=u0

Vérifiez les résultats du test.

call dbms_ccl.show_ccl_queue();

La sortie suivante est renvoyée :

+------+-------+-------------------+---------+---------+---------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING |
+------+-------+-------------------+---------+---------+---------+
|    1 | QUEUE |                64 |   10996 |      63 |       4 |
|    2 | QUEUE |                64 |       0 |       0 |       0 |
|    3 | QUEUE |                64 |       0 |       0 |       0 |
|    4 | QUEUE |                64 |       0 |       0 |       0 |
+------+-------+-------------------+---------+---------+---------+
4 rows in set (0.03 sec)

call dbms_outln.show_outline();

La sortie suivante est renvoyée :

+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
| ID   | SCHEMA | DIGEST    | TYPE      | SCOPE | POS  | HINT                           | HIT    | OVERFLOW | DIGEST_TEXT                                 |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
|    1 | test   | xxxxxxxxx | OPTIMIZER |       |    1 |  /*+ ccl_queue_field(id) */  | 115795 |        0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
1 row in set (0.00 sec)

Remarque

Les résultats indiquent que le plan d'instruction a correspondu à la règle 115 795 fois. L'état de la file d'attente des instructions affiche 10 996 correspondances, avec 63 instructions en cours d'exécution et 4 en attente.

Test de performance

Environnement de test
- Serveur d'application : une instance ECS
- Spécifications de l'instance RDS : 8 vCPU, 16 Go de mémoire et un disque ESSD
- Édition de l'instance : édition High-availability, qui utilise la réplication asynchrone.

Cas de test

Le cas de test SysBench suivant effectue des mises à jour concurrentes sur l'enregistrement dont l'id est égal à 1 :

pathtest = string.match(test, "(.*/)")

if pathtest then
 dofile(pathtest .. "oltp_common.lua")
else
 require("oltp_common")
end

function thread_init()
 drv = sysbench.sql.driver()
 con = drv:connect()
end

function event()
 local val_name
 val_name = "'sdnjkmoklvnseajinvijsfdnvkjsnfjvn".. sb_rand_uniform(1, 4096) .. "'"
 query = "UPDATE sbtest1 SET c=" .. val_name .. " WHERE id=1"
 rs = db_query(query)
end

Résultats du test

L'activation de la fonctionnalité de file d'attente des instructions augmente considérablement le QPS dans les charges de travail à forte concurrence. Plus la concurrence est élevée, plus l'amélioration est importante.

Remarque
Sans la fonctionnalité de file d'attente des instructions, un test de charge avec 4 096 threads provoque un basculement primaire/secondaire, entraînant un QPS nul.