Informações básicas sobre o recurso de auto plan cache - PolarDB: Central de Documentação do Alibaba Cloud

O recurso de auto plan cache do PolarDB for MySQL armazena em cache planos de execução de instruções SQL. Isso reduz o tempo de otimização de consultas e melhora o desempenho geral. Este tópico descreve o contexto, os pré-requisitos, os parâmetros e as interfaces do recurso de auto plan cache.

Informações de contexto

A escolha de um plano de execução envolve diversos fatores, como estatísticas, ordens de junção e transformações de consulta. O tempo de otimização varia conforme a instrução. Em algumas instruções SQL, a otimização consome grande parte do tempo total de execução. A execução frequente dessas instruções com longo tempo de otimização aumenta a carga do sistema. Armazenar e reutilizar planos de execução reduz o tempo de otimização a cada execução. Essa prática melhora o desempenho das consultas, diminui a carga do banco de dados e aumenta a capacidade de throughput.

Em muitas outras consultas, o tempo de otimização é baixo, mas o plano de execução escolhido influencia fortemente o tempo de execução. Diferentes valores de parâmetros em uma instrução SQL podem corresponder a planos de execução ideais distintos. Em alguns cenários, o MySQL recupera dados reais do mecanismo com base nos valores dos parâmetros para otimizações adicionais.

Usar um plano de execução fixo para essas consultas pode não melhorar o tempo de resposta nem reduzir a sobrecarga do sistema. Em alguns casos, essa prática causa regressão de desempenho.

O PolarDB for MySQL oferece o recurso de auto plan cache para melhorar o desempenho de instruções SQL com tempos longos de otimização, reduzir a carga do sistema e evitar regressões causadas por planos de execução fixos. Esse recurso possui três modos: AUTO, DEMAND e ENFORCE. Defina o parâmetro loose_plan_cache_type como um desses modos para armazenar planos de execução no plan cache. Essa configuração reduz o tempo de otimização e aprimora o desempenho das consultas. Um plano de execução em cache é invalidado automaticamente se as estatísticas de uma tabela referenciada forem alteradas ou se uma operação de Data Definition Language (DDL) for executada nessa tabela.

Pré-requisitos

Seu cluster PolarDB deve atender a um dos seguintes requisitos de versão:

PolarDB for MySQL 8.0.1 com versão de revisão 8.0.1.1.33 ou posterior.
PolarDB for MySQL 8.0.2 com versão de revisão 8.0.2.2.12 ou posterior.

Parâmetros

Configure os parâmetros da tabela a seguir no console do PolarDB. Para mais informações, consulte Definir parâmetros de cluster e nó.

Parâmetro	Descrição
loose_plan_cache_type	Modo do auto plan cache. Valores válidos: OFF (Padrão): Desativa o recurso de auto plan cache. AUTO: Armazena automaticamente em cache os planos de execução de instruções SQL que atendem às condições de cache. Nota Condições de cache: O plano de execução de uma instrução SQL será armazenado em cache se o tempo total de execução for maior ou igual ao valor do parâmetro `loose_auto_plan_cache_time_threshold` e se a porcentagem do tempo de otimização em relação ao tempo total de execução for maior ou igual ao valor do parâmetro `loose_auto_plan_cache_pct_threshold`. DEMAND: Armazena em cache os planos de execução de instruções SQL específicas. ENFORCE: Força o armazenamento em cache dos planos de execução de todas as instruções SQL.
loose_plan_cache_expire_time	Se um plano de execução no plan cache não for utilizado dentro deste período, sua memória será recuperada. A unidade é segundos. Intervalo de valores: 0 a 4294967295. Valor padrão: 1800.
loose_auto_plan_cache_pct_threshold	Limiar para a porcentagem do tempo de otimização em relação ao tempo total de execução de uma instrução. Intervalo de valores: 0 a 100. Valor padrão: 20.
loose_auto_plan_cache_time_threshold	Limiar para o tempo total de execução de uma instrução SQL. A unidade é microssegundos. Intervalo de valores: 0 a 18446744073709551615. Valor padrão: 400.
loose_auto_plan_cache_count_threshold	Quando o parâmetro `loose_plan_cache_type` está definido como AUTO, este é o limiar para o número de vezes que o plano de execução de uma instrução SQL qualificada é armazenado em cache. Intervalo de valores: 0 a 18446744073709551615. Valor padrão: 512. Nota O plano de execução em cache só entra em vigor quando o número de vezes que foi armazenado for maior ou igual ao valor do parâmetro `loose_auto_plan_cache_count_threshold`.

Descrição das interfaces

dbms_sql.add_plan_cache(schema, query): Armazena o plano de execução de uma instrução SQL específica no plan cache.
Quando o parâmetro loose_plan_cache_type estiver definido como DEMAND, utilize este procedimento armazenado integrado para armazenar em cache o plano de execução de uma instrução SQL específica. Exemplo:
```
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
```
Após a execução dessa instrução, o plano de execução fica em cache para qualquer instrução SQL que corresponda ao modelo SELECT * FROM t_for_plan WHERE c1 > ? AND c1 < ?.
dbms_sql.display_plan_cache_table(): Visualize informações sobre as tabelas referenciadas no plan cache atual. Exemplo:
```
CALL dbms_sql.display_plan_cache_table()\G
```
Resultado retornado:
```
*************************** 1. row ***************************
 SCHEMA_NAME: test
  TABLE_NAME: t_for_plan
   REF_COUNT: 1
     VERSION: 0
VERSION_TIME: 2023-03-10 17:21:35.605264
```
A tabela a seguir descreve os parâmetros retornados.
- SCHEMA_NAME: Nome do schema onde reside a tabela referenciada.
- TABLE_NAME: Nome da tabela referenciada.
- REF_COUNT: Número de vezes que a tabela é referenciada no plan cache.
- VERSION: Versão da tabela referenciada no plan cache.
- VERSION_TIME: Momento em que a versão atual da tabela foi referenciada.
dbms_sql.delete_sharing_by_rowid(row_id): Exclua o plano de execução de uma instrução SQL específica.

O row_id corresponde ao ID da linha do plano de execução armazenado na tabela mysql.sql_sharing.

Example
1. Execute o comando a seguir para visualizar as informações do plano de execução no cache.
```
SELECT Id, Schema_name, Type, Digest_text FROM mysql.sql_sharing WHERE Type = 'PLAN_CACHE'\G
```
  Resultado retornado:
```
*************************** 1. row ***************************
         Id: 1
Schema_name: test
       Type: PLAN_CACHE
Digest_text: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
```
  O resultado da consulta indica que o valor de row_id é 1.
2. Exclua o plano de execução obtido na consulta anterior.
```
CALL dbms_sql.delete_sharing_by_rowid(1);
```

Obter informações em cache do plan cache

Os planos de execução de instruções SQL ficam armazenados no módulo SQL Sharing. Execute a instrução SQL a seguir para consultar as informações em cache no plan cache a partir da tabela INFORMATION_SCHEMA.SQL_SHARING.

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

Example

Prepare os dados.

CREATE TABLE t_for_plan AS WITH RECURSIVE t(c1, c2, c3) AS (SELECT 1, 1, 1 UNION ALL SELECT c1+1, c1 % 50, c1 %200 FROM t WHERE c1 < 1000) SELECT c1, c2, c3 FROM t;
CREATE INDEX i_c1_c2 on t_for_plan(c1, c2);

Defina o modo de auto plan cache como DEMAND.
Existem duas maneiras de configurar o modo de auto plan cache:
- Na página Parameters do console do PolarDB, defina o parâmetro loose_plan_cache_type como DEMAND. Após concluir a configuração, desconecte-se do banco de dados e reconecte-se.
- Na conexão atual com o banco de dados, execute o comando a seguir para definir o parâmetro plan_cache_type da sessão atual como demand.
```
SET plan_cache_type=demand;
```
Execute o comando a seguir para armazenar o plano de execução da instrução SQL especificada no plan cache.
```
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
```

Execute a instrução de consulta.

SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10;

Consulte as informações em cache no plan cache.

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

Resultado retornado:

*************************** 1. row ***************************
       TYPE: SQL
     REF_BY: ["PLAN_CACHE(DEMAND)"]
     SQL_ID: 9jrvksr3wjux6
SCHEMA_NAME: test
DIGEST_TEXT: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
    PLAN_ID: NULL
       PLAN: NULL
 PLAN_EXTRA: NULL
      EXTRA: {"TRACE_ROW_ID":1}
*************************** 2. row ***************************
       TYPE: PLAN
     REF_BY: ["PLAN_CACHE"]
     SQL_ID: 9jrvksr3wjux6
SCHEMA_NAME: test
DIGEST_TEXT: NULL
    PLAN_ID: 08xftakma6pm6
       PLAN: /*+ INDEX(`t_for_plan`@`select#1` `i_c1_c2`) */
 PLAN_EXTRA: {"access_type":["`t_for_plan`:range"]}
      EXTRA: {"PLAN_CACHE_INFO":{"tables":[`test`.`t_for_plan`], "versions":[0], "hits": 0}}

No campo EXTRA, o objeto PLAN_CACHE_INFO exibe as tabelas referenciadas, as versões dessas tabelas e o número de acessos ao plano de execução.

Desempenho de consulta

Um teste de estresse foi realizado em um cluster de 8 núcleos e 32 GB. O banco de dados continha 25 tabelas, cada uma com 4 milhões de linhas de dados. O teste utilizou a instrução SQL SELECT id FROM sbtestN WHERE k IN(...), com uma lista IN de comprimento 20. O desempenho foi testado com o parâmetro loose_plan_cache_type definido como OFF, AUTO e ENFORCE, tanto nos protocolos Prepared Statement (PS) quanto nos não-PS. Os resultados são apresentados a seguir:

Resultados do teste de desempenho no protocolo PS:
Resultados do teste de desempenho no protocolo não-PS:

Os resultados demonstram que o recurso de auto plan cache melhora o desempenho em mais de 50%, tanto nos protocolos PS quanto nos não-PS.