Resolver problemas de desempenho de consultas na Base de Dados do Azure para MySQL – Servidor Flexível
O desempenho da consulta pode ser afetado por vários fatores, por isso é importante primeiro examinar o escopo dos sintomas que você está enfrentando em sua instância do Servidor Flexível do Banco de Dados do Azure para MySQL. Por exemplo, o desempenho da consulta é lento para:
- Todas as consultas em execução no Banco de Dados do Azure para a instância do Servidor Flexível MySQL?
- Um conjunto específico de consultas?
- Uma consulta específica?
Lembre-se também de que quaisquer alterações recentes na estrutura ou nos dados subjacentes das tabelas que você está consultando podem afetar o desempenho.
Ativando a funcionalidade de registro em log
Antes de analisar consultas individuais, você precisa definir benchmarks de consulta. Com essas informações, você pode implementar a funcionalidade de registro em log no servidor de banco de dados para rastrear consultas que excedam um limite especificado com base nas necessidades do aplicativo.
Banco de Dados do Azure para Servidor Flexível MySQL, é recomendável usar o recurso de log de consulta lenta para identificar consultas que levam mais de N segundos para serem executadas. Depois de identificar as consultas a partir do log de consultas lentas, você pode usar o diagnóstico do MySQL para solucionar essas consultas.
Antes de começar a rastrear consultas de longa execução, você precisa habilitar o slow_query_log parâmetro usando o portal do Azure ou a CLI do Azure. Com esse parâmetro habilitado, você também deve configurar o valor do parâmetro para especificar o número de segundos que as consultas podem ser executadas antes de serem identificadas como consultas de long_query_time "execução lenta". O valor padrão do parâmetro é 10 segundos, mas você pode ajustar o valor para atender às necessidades do SLA do seu aplicativo.
Embora o log de consultas lentas seja uma ótima ferramenta para rastrear consultas de longa execução, há certos cenários em que ele pode não ser eficaz. Por exemplo, o log de consulta lenta:
- Afeta negativamente o desempenho se o número de consultas for muito alto ou se a instrução de consulta for muito grande. Ajuste o
long_query_timevalor do parâmetro de acordo. - Pode não ser útil se você também tiver habilitado o
log_queries_not_using_indexparâmetro, que especifica para registrar consultas esperadas para recuperar todas as linhas. As consultas que executam uma verificação de índice completa aproveitam um índice, mas elas seriam registradas porque o índice não limita o número de linhas retornadas.
Recuperando informações dos logs
Os logs ficam disponíveis por até sete dias a partir de sua criação. Você pode listar e baixar logs de consulta lenta por meio do portal do Azure ou da CLI do Azure. No portal do Azure, navegue até o servidor, em Monitoramento, selecione Logs do servidor e selecione a seta para baixo ao lado de uma entrada para baixar os logs associados à data e hora que você está investigando.
Além disso, se seus logs de consulta lenta forem integrados aos logs do Azure Monitor por meio de logs de diagnóstico, você poderá executar consultas em um editor para analisá-las ainda mais:
AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10
Nota
Para obter mais exemplos para começar a diagnosticar logs de consulta lentos por meio de logs de diagnóstico, consulte Analisar logs em logs do Azure Monitor.
O instantâneo a seguir mostra um exemplo de consulta lenta.
# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @ [172.30.209.6] Id: 735026
# Query_time: 25.314811 Lock_time: 0.000000 Rows_sent: 126 Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;
A consulta foi executada em 26 segundos, examinou mais de 443 mil linhas e retornou 126 linhas de resultados.
Normalmente, você deve se concentrar em consultas com valores altos para Query_time e Rows_examined. No entanto, se você notar consultas com um Query_time alto, mas apenas algumas Rows_examined, isso geralmente indica a presença de um gargalo de recursos. Para esses casos, você deve verificar se há algum acelerador de E/S ou uso da CPU.
Definindo o perfil de uma consulta
Depois de identificar uma consulta de execução lenta específica, você pode usar o comando EXPLAIN e a criação de perfil para obter mais detalhes.
Para verificar o plano de consulta, execute o seguinte comando:
EXPLAIN <QUERY>
Nota
Para obter mais informações sobre como usar instruções EXPLICAR, consulte Desempenho da consulta de perfil no Banco de Dados do Azure para MySQL - Servidor flexível usando EXPLAIN.
Além de criar um plano EXPLAIN para uma consulta, você pode usar o comando SHOW PROFILE, que permite diagnosticar a execução de instruções que foram executadas na sessão atual.
Para habilitar a criação de perfil e criar o perfil de uma consulta específica em uma sessão, execute o seguinte conjunto de comandos:
SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;
Nota
A criação de perfil de consultas individuais só está disponível em uma sessão e as declarações históricas não podem ser perfiladas.
Vamos dar uma olhada mais de perto no uso desses comandos para criar o perfil de uma consulta. Primeiro, habilite a criação de perfil para a sessão atual, execute o SET PROFILING = 1 comando:
SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Em seguida, execute uma consulta subótima que execute uma verificação de tabela completa:
mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
| +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ |
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
| +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ |
| 1 row in set (27.60 sec) |
Em seguida, exiba uma lista de todos os perfis de consulta disponíveis executando o SHOW PROFILES comando:
SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration | Query |
| +----------+-------------+----------------------------------------------------+ |
| 1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
| +----------+-------------+----------------------------------------------------+ |
| 1 row in set, 1 warning (0.00 sec) |
Finalmente, para exibir o perfil para a consulta 1, execute o SHOW PROFILE FOR QUERY 1 comando.
SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status | Duration |
| +----------------------+-----------+ |
| starting | 0.000102 |
| checking permissions | 0.000028 |
| Opening tables | 0.000033 |
| init | 0.000035 |
| System lock | 0.000018 |
| optimizing | 0.000017 |
| statistics | 0.000025 |
| preparing | 0.000019 |
| executing | 0.000011 |
| Sending data | 27.594038 |
| end | 0.000041 |
| query end | 0.000014 |
| closing tables | 0.000013 |
| freeing items | 0.000088 |
| cleaning up | 0.000020 |
| +----------------------+-----------+ |
| 15 rows in set, 1 warning (0.00 sec) |
Listando as consultas mais usadas no servidor de banco de dados
Sempre que você estiver solucionando problemas de desempenho de consulta, é útil entender quais consultas são executadas com mais frequência em sua instância do Servidor Flexível do Banco de Dados do Azure para MySQL. Você pode usar essas informações para avaliar se alguma das principais consultas está demorando mais do que o normal para ser executada. Além disso, um desenvolvedor ou DBA pode usar essas informações para identificar se alguma consulta tem um aumento repentino na contagem e duração da execução da consulta.
Para listar as 10 principais consultas mais executadas em sua instância do Servidor Flexível do Banco de Dados do Azure para MySQL, execute a seguinte consulta:
SELECT digest_text AS normalized_query,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected,
sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables,
sum_select_scan,
sum_no_index_used,
sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
Nota
Use essa consulta para comparar as consultas mais executadas no servidor de banco de dados e determinar se houve uma alteração nas consultas principais ou se alguma consulta existente no benchmark inicial aumentou na duração da execução.
Listando as 10 consultas mais caras por tempo total de execução
A saída da consulta a seguir fornece informações sobre as 10 principais consultas em execução no servidor de banco de dados e seu número de execuções no servidor de banco de dados. Ele também fornece outras informações úteis, como as latências de consulta, seus tempos de bloqueio, o número de tabelas temporárias criadas como parte do tempo de execução da consulta, etc. Use essa saída de consulta para acompanhar as principais consultas no banco de dados e as alterações em fatores como latências, o que pode indicar uma chance de ajustar ainda mais a consulta para ajudar a evitar riscos futuros.
SELECT REPLACE(event_name, 'statement/sql/', '') AS statement,
count_star AS all_occurrences ,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency,
Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency,
Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time ,
sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected,
sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables, sum_created_tmp_disk_tables,
IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables /
sum_created_tmp_tables * 100, 0))) AS
tmp_disk_tables_percent,
sum_select_scan,
sum_no_index_used,
sum_no_good_index_used
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE event_name LIKE 'statement/sql/%'
AND count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;
Monitorando a coleta de lixo InnoDB
Quando a coleta de lixo do InnoDB é bloqueada ou atrasada, o banco de dados pode desenvolver um atraso de limpeza substancial que pode afetar negativamente a utilização do armazenamento e o desempenho da consulta.
O comprimento da lista de histórico de segmentos de reversão (HLL) do InnoDB mede o número de registros de alteração armazenados no log de desfazer. Um valor HLL crescente indica que os threads de coleta de lixo do InnoDB (threads de limpeza) não estão acompanhando a carga de trabalho de gravação ou que a limpeza está bloqueada por uma consulta ou transação de longa execução.
Atrasos excessivos na recolha de lixo podem ter consequências graves e negativas:
- O espaço de mesa do sistema InnoDB será expandido, acelerando assim o crescimento do volume de armazenamento subjacente. Às vezes, o espaço de tabela do sistema pode aumentar em vários terabytes como resultado de uma limpeza bloqueada.
- Os registros marcados com exclusão não serão removidos em tempo hábil. Isso pode fazer com que os espaços de tabela do InnoDB cresçam e impede que o mecanismo reutilize o armazenamento ocupado por esses registros.
- O desempenho de todas as consultas pode diminuir e a utilização da CPU pode aumentar devido ao crescimento das estruturas de armazenamento do InnoDB.
Como resultado, é importante monitorar valores, padrões e tendências de LLL.
Encontrar valores de HLL
Você pode encontrar o valor HLL executando o comando show engine innodb status. O valor será listado na saída, sob o título TRANSAÇÕES:
show engine innodb status\G
****************** 1. row ******************
(...)
------------
TRANSACTIONS
------------
Trx id counter 52685768
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle
History list length 2964300
(...)
Você também pode determinar o valor HLL consultando a tabela information_schema.innodb_metrics:
mysql> select count from information_schema.innodb_metrics
-> where name = 'trx_rseg_history_len';
+---------+
| count |
| +---------+ |
| 2964300 |
| +---------+ |
| 1 row in set (0.00 sec) |
Interpretação de valores de HLL
Ao interpretar valores de HLL, considere as diretrizes listadas na tabela a seguir:
| Valor | Notas |
|---|---|
| Menos de ~10.000 | Valores normais, indicando que a coleta de lixo não está ficando para trás. |
| Entre ~10.000 e ~1.000.000 | Esses valores indicam uma pequena defasagem na coleta de lixo. Tais valores podem ser aceitáveis se permanecerem estáveis e não aumentarem. |
| Maior que ~1.000.000 | Estes valores devem ser investigados e podem requerer ações corretivas |
Lidar com valores excessivos de HLL
Se o HLL mostrar grandes picos ou exibir um padrão de crescimento periódico, investigue as consultas e transações em execução na instância do Servidor Flexível do Banco de Dados do Azure para MySQL imediatamente. Em seguida, você pode resolver quaisquer problemas de carga de trabalho que possam estar impedindo o progresso do processo de coleta de lixo. Embora não seja esperado que o banco de dados esteja livre de atraso de purga, você não deve deixar o atraso crescer descontroladamente.
Para obter informações de transação da information_schema.innodb_trx tabela, por exemplo, execute os seguintes comandos:
select * from information_schema.innodb_trx
order by trx_started asc\G
O detalhe na trx_started coluna irá ajudá-lo a calcular a idade da transação.
mysql> select * from information_schema.innodb_trx
-> order by trx_started asc\G
****************** 1. row ******************
trx_id: 8150550
trx_state: RUNNING
trx_started: 2021-11-13 20:50:11
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 19
trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(...)
Para obter informações sobre as sessões atuais do banco de dados, incluindo o tempo gasto no estado atual da sessão, verifique a information_schema.processlist tabela. A saída a seguir, por exemplo, mostra uma sessão que está executando ativamente uma consulta nos últimos 1462 segundos:
mysql> select user, host, db, command, time, info
-> from information_schema.processlist
-> order by time desc\G
****************** 1. row ******************
user: test
host: 172.31.19.159:38004
db: employees
command: Query
time: 1462
info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(...)
Recomendações
Certifique-se de que seu banco de dados tenha recursos suficientes alocados para executar suas consultas. Às vezes, talvez seja necessário aumentar o tamanho da instância para obter mais núcleos de CPU e memória adicional para acomodar sua carga de trabalho.
Evite transações grandes ou de longa duração, dividindo-as em transações menores.
Configure innodb_purge_threads de acordo com sua carga de trabalho para melhorar a eficiência das operações de limpeza em segundo plano.
Nota
Teste todas as alterações nessa variável de servidor para cada ambiente para avaliar a alteração no comportamento do mecanismo.
Use alertas em "Porcentagem de CPU do host", "Porcentagem de memória do host" e "Total de conexões" para receber notificações se o sistema exceder qualquer um dos limites especificados.
Use o Query Performance Insights ou as Pastas de Trabalho do Azure para identificar consultas problemáticas ou de execução lenta e, em seguida, otimize-as.
Para servidores de banco de dados de produção, colete diagnósticos em intervalos regulares para garantir que tudo esteja funcionando sem problemas. Caso contrário, solucione e resolva quaisquer problemas identificados.