ApsaraDB RDS for MySQL statement queue - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

Em cargas de trabalho MySQL com alta concorrência, pontos de serialização nas camadas de serviço e de mecanismo, como a contenção de bloqueios transacionais, podem degradar o desempenho. O AliSQL oferece o recurso de fila de instruções, que utiliza um mecanismo de enfileiramento baseado em buckets para reduzir a sobrecarga de contenção e melhorar o desempenho da instância. Esse recurso atribui instruções propensas a contenção, como as que operam na mesma linha, ao mesmo bucket, otimizando a execução concorrente.

Informações básicas

Nas camadas de serviço e de mecanismo do MySQL, a execução concorrente de instruções envolve múltiplos pontos de serialização que causam contenção facilmente. A contenção de bloqueios transacionais, por exemplo, é comum durante a execução de instruções DML. No mecanismo InnoDB, a menor granularidade de um bloqueio transacional é o bloqueio no nível de linha. Quando várias instruções operam simultaneamente na mesma linha, ocorre uma contenção severa, reduzindo drasticamente o throughput do sistema à medida que a concorrência aumenta. Para resolver esse problema, o AliSQL disponibiliza o recurso de fila de instruções, melhorando o desempenho da instância ao reduzir a sobrecarga de contenção.

Pré-requisitos

Este recurso está disponível para instâncias do ApsaraDB RDS for MySQL nas seguintes versões:

MySQL 8.4 na Basic Edition ou High-availability Edition
MySQL 8.0 na Basic Edition ou High-availability Edition (versão secundária do mecanismo 20191115 ou posterior)
MySQL 5.7 na Basic Edition ou High-availability Edition (versão secundária do mecanismo 20200630 ou posterior)

Variáveis de configuração

O AliSQL fornece duas variáveis para definir a quantidade e o tamanho dos buckets na fila de instruções. Modifique essas variáveis no console do ApsaraDB RDS.

ccl_queue_bucket_count: Defina o número de buckets.
- Valores válidos: 1 a 64
- Valor padrão: 4
ccl_queue_bucket_size: Especifique o número de instruções concorrentes permitidas por bucket.
- Valores válidos: 1 a 4096
- Valor padrão: 64

Sintaxe

O AliSQL suporta dois tipos de hints:

ccl_queue_value: Aplica hash a um valor especificado para atribuir a instrução a um bucket.

Sintaxe:

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

Exemplo:

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: Aplica hash ao valor de um campo especificado na cláusula WHERE para atribuir um bucket.

Sintaxe:
```
/*+ ccl_queue_field(string) */
```
Exemplo:
```
update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';
```
Nota
- Ambos os hints são sensíveis à posição e devem ser colocados imediatamente após a palavra-chave UPDATE.
- O hint ccl_queue_field pode especificar apenas um campo por vez. O formato /*+ ccl_queue_field(id name) */ constitui um erro de sintaxe e impede que a fila de instruções tenha efeito. Se você usar hints duplicados, como /*+ ccl_queue_field(id) ccl_queue_field(name) */, apenas o primeiro hint será utilizado.
- O campo especificado no hint ccl_queue_field deve aparecer na cláusula WHERE.
- Para o hint ccl_queue_field, a cláusula WHERE suporta apenas operações binárias em campos brutos (sem funções ou cálculos aplicados ao campo). O valor no lado direito da operação binária deve ser um número ou uma string.

API

O AliSQL fornece duas funções para monitorar a fila de instruções:

dbms_ccl.show_ccl_queue(): Consulta o status atual da fila de instruções.

call dbms_ccl.show_ccl_queue();

Saída retornada:

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

A tabela a seguir descreve os parâmetros.

Parâmetro	Descrição
CONCURRENCY_COUNT	Número máximo de instruções concorrentes permitidas.
MATCHED	Total de instruções que corresponderam à regra.
RUNNING	Quantidade de instruções atualmente em execução.
WAITING	Quantidade de instruções atualmente em espera.

dbms_ccl.flush_ccl_queue(): Limpa os dados na memória.

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

Saída retornada:

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

Práticas

Teste do recurso

Para evitar alterações extensas no código da sua aplicação, utilize a fila de instruções em conjunto com um statement outline para modificar sua aplicação online de forma rápida e simples. O exemplo a seguir usa o caso de teste update_non_index do SysBench para demonstrar esse processo.

Ambiente de teste

Schema da tabela

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;

Instrução de teste
```
UPDATE sbtest1 SET c='xyz' WHERE id=0;
```

Script de teste

./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

Procedimento

Adicione um statement outline online.

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

Saída retornada:

Query OK, 0 rows affected (0.01 sec)

Visualize o statement outline.

call dbms_outln.show_outline();

Saída retornada:

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

Verifique se o statement outline entrou em vigor.

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

Saída retornada:

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

Saída retornada:

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

Verifique o status da fila de instruções.

call dbms_ccl.show_ccl_queue();

Saída retornada:

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

Inicie o teste.

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

Verifique os resultados do teste.

call dbms_ccl.show_ccl_queue();

Saída retornada:

+------+-------+-------------------+---------+---------+---------+
| 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();

Saída retornada:

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

Nota

Os resultados mostram que o statement outline correspondeu à regra 115.795 vezes. O status da fila de instruções indica 10.996 correspondências, com 63 instruções em execução e 4 em espera.

Teste de desempenho

Ambiente de teste
- Servidor de aplicação: Uma instância ECS
- Especificações da instância RDS: 8 vCPUs, 16 GB de memória e um ESSD
- Edição da instância: High-availability Edition, que utiliza replicação assíncrona.

Caso de teste

O seguinte caso de teste do SysBench realiza atualizações concorrentes no registro com id=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

Resultados do teste

Habilitar o recurso de fila de instruções aumenta significativamente o QPS em cargas de trabalho de alta concorrência. Quanto maior a concorrência, maior a melhoria obtida.

Nota
Sem o recurso de fila de instruções, um teste de estresse com 4.096 threads causa um failover entre primário e secundário, resultando em 0 QPS.