Use Statement Queue to queue statements in buckets - PolarDB: Central de Documentação do Alibaba Cloud

Quando várias instruções DML (Data Manipulation Language) acessam a mesma linha simultaneamente, o bloqueio em nível de linha do InnoDB as serializa. Conforme a concorrência aumenta, as threads enfileiram-se aguardando locks e o throughput diminui. A fila de instruções resolve esse problema ao direcionar instruções conflitantes para buckets compartilhados por meio de um algoritmo de hash. Assim, elas aguardam na fila em vez de competir pelo lock, o que reduz a sobrecarga dos ciclos repetidos de aquisição e liberação de locks.

Em testes de benchmark com 128 instruções UPDATE simultâneas em uma única linha, o PolarDB com fila de instruções atinge aproximadamente quatro vezes o throughput do MySQL padrão.

Quando usar a fila de instruções

A fila de instruções é mais eficaz quando um alto volume de instruções DML tem como alvo as mesmas linhas — por exemplo, em contadores de ranking, atualizações de estoque ou sistemas de reserva de ingressos nos quais muitas gravações simultâneas atingem a mesma chave primária.

Ela é menos eficaz para cargas de trabalho em que as instruções acessam linhas diferentes, pois há pouca contenção de lock a reduzir.

Se você ativou a otimização de hot row definindo a variável hotspot como ON, a fila de instruções será ignorada automaticamente. Use apenas um dos dois recursos, não ambos.

Pré-requisitos

Antes de começar, verifique se você possui:

Um cluster do PolarDB for MySQL executando uma das seguintes versões secundárias do mecanismo:
- PolarDB for MySQL 8.0, versão secundária do mecanismo 8.0.1.1.10 ou posterior
- PolarDB for MySQL 5.7, versão secundária do mecanismo 5.7.1.0.6 ou posterior
- PolarDB for MySQL 5.6, versão secundária do mecanismo 20200601 ou posterior
Antes de usar o recurso Statement Queue, defina o parâmetro loose_rds_ic_reduce_hint_enable como ON. O valor padrão é OFF. A modificação entra em vigor imediatamente, sem necessidade de reiniciar o cluster.

Sintaxe

PolarDB for MySQL 5.6

Insira a palavra-chave POLARDB_STATEMENT_CONCURRENT_QUEUE imediatamente após a palavra-chave DML (SELECT, UPDATE, INSERT ou DELETE). Essa palavra-chave aplica-se aos quatro tipos de instrução DML.

Enfileirar por valor

Atribua instruções a um bucket com base em um valor explícito de inteiro ou string. Instruções com o mesmo valor são direcionadas ao mesmo bucket.

Sintaxe:

POLARDB_STATEMENT_CONCURRENT_QUEUE [int | string]

Exemplos:

INSERT POLARDB_STATEMENT_CONCURRENT_QUEUE 1 INTO t VALUES(1, 1, 'xpchild');
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE 1 t SET c=c+1 WHERE id = 1;
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE "xpchild" t SET col1 = col1+1 WHERE id = 1;

Enfileirar por campo da cláusula WHERE

Atribua instruções a um bucket com base no valor de tempo de execução de uma coluna na cláusula WHERE. Instruções com o mesmo valor de campo vão para o mesmo bucket.

Sintaxe:

POLARDB_STATEMENT_CONCURRENT_QUEUE [field]

Exemplos:

SELECT POLARDB_STATEMENT_CONCURRENT_QUEUE id * FROM t WHERE 3 = id;
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE id t SET c=c+1 WHERE id = 1 AND name = 'xpchild';

A cláusula WHERE deve usar apenas operações binárias em colunas originais. O lado direito de cada operação binária deve ser um número literal ou uma string. Não há suporte a funções e cálculos em colunas originais.

PolarDB for MySQL 5.7 e 8.0

Use a sintaxe de hint /*+ ... */ posicionada imediatamente após a palavra-chave DML (SELECT, UPDATE, INSERT ou DELETE). O hint aplica-se aos quatro tipos de instrução DML.

Enfileirar por valor

Sintaxe:

/*+ CCL_QUEUE_VALUE([INT | STRING]) */

Exemplos:

UPDATE /*+ ccl_queue_value(1) */ t SET c=c+1 WHERE id = 1;
UPDATE /*+ ccl_queue_value('xpchild') */ t SET c=c+1 WHERE name = 'xpchild';

Enfileirar por campo da cláusula WHERE

Sintaxe:

/*+ CCL_QUEUE_FIELD(STRING) */

Exemplo:

UPDATE /*+ ccl_queue_field("id") */ t SET c=c+1 WHERE id = 1 AND name = 'xpchild';

A cláusula WHERE deve usar apenas operações binárias em colunas originais. O lado direito de cada operação binária deve ser um número literal ou uma string. Não há suporte a funções e cálculos em colunas originais.

Configure o tamanho da fila

Duas variáveis controlam a capacidade da fila. Defina-as conforme seus requisitos de concorrência.

Variável	Descrição	Valores válidos	Padrão
`ccl_queue_bucket_count`	Número de buckets	1–64	4
`ccl_queue_bucket_size`	Máximo de instruções simultâneas por bucket	1–4096	64

Monitorar o status da fila

Use os seguintes stored procedures para inspecionar e redefinir o estado da fila.

Verificar o status atual

CALL dbms_ccl.show_ccl_queue();

Exemplo de saída:

+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       1 |       0 |        0 |
|    2 | QUEUE |                64 |   40744 |      65 |        6 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+

Campos de saída:

Campo	Descrição
`CONCURRENCY_COUNT`	Máximo de instruções simultâneas permitidas neste bucket
`MATCHED`	Total de instruções que corresponderam à regra deste bucket
`RUNNING`	Instruções em execução neste bucket
`WAITTING`	Instruções aguardando na fila

Redefinir dados da fila

O comando dbms_ccl.flush_ccl_queue() limpa as estatísticas da fila na memória e retorna o status atual.

CALL dbms_ccl.flush_ccl_queue();
CALL dbms_ccl.show_ccl_queue();

Exemplo de saída após a limpeza:

+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       0 |       0 |        0 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+

Aplicar hints de fila sem modificar o código da aplicação

No PolarDB for MySQL 5.7 e 8.0, combine a fila de instruções com Statement outline para injetar hints de fila em instruções SQL existentes no nível do servidor, sem alterar o código da aplicação.

Essa integração não está disponível para o PolarDB for MySQL 5.6, que usa uma sintaxe diferente.

O exemplo a seguir usa o Sysbench com o script update_non_index.lua para demonstrar esse fluxo de trabalho.

Esquema de teste:

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 alvo:

UPDATE sbtest1 SET c='xpchild' WHERE id=0;

Procedimento:

Crie um statement outline para injetar o hint de fila online.

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

Confirme a criação do outline.

CALL dbms_outln.show_outline();

Saída esperada:

+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| 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` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+

Verifique se o hint está ativo executando EXPLAIN na instrução alvo.

EXPLAIN UPDATE sbtest1 SET c='xpchild' WHERE id=0;
SHOW WARNINGS;

A saída do SHOW WARNINGS deve exibir o hint injetado:

+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ CCL_QUEUE_FIELD('id') */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xpchild' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+

Verifique se a fila não apresenta atividade antes do início do teste.
```
CALL dbms_ccl.show_ccl_queue();
```

Execute o teste do Sysbench.

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 o status da fila durante o teste para confirmar o roteamento correto das instruções.

CALL dbms_ccl.show_ccl_queue();

Saída esperada durante o teste:

+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |   10996 |      63 |        4 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+

Compare com a contagem de acertos do outline:

CALL dbms_outln.show_outline();

Nesta execução de teste, 115.795 instruções corresponderam à regra do outline; dessas, 10.996 atingiram a fila, 63 estavam em execução simultânea e 4 aguardavam.

Próximos passos

Statement outline — injete hints de otimizador sem alterar o código da aplicação