Solução de problemas de desempenho de consulta no Banco de Dados do Azure para MySQL – Servidor Flexível
APLICA-SE A:
Banco de Dados do Azure para MySQL – Servidor Único Banco de Dados do Azure para MySQL – Servidor Flexível
Importante
O Banco de Dados do Azure para servidor único MySQL está no caminho de desativação. É altamente recomendável que você atualize para o Banco de Dados do Azure para o servidor flexível MySQL. Para obter mais informações sobre como migrar para o Banco de Dados do Azure para o servidor flexível MySQL, consulte O que está acontecendo com o Banco de Dados do Azure para Servidor Único MySQL?
O desempenho da consulta pode ser afetado por vários fatores, portanto, primeiro é importante examinar o escopo dos sintomas que você está enfrentando em sua instância de 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 instância de servidor flexível do MySQL?
- Um conjunto específico de consultas?
- Uma consulta específica?
Lembre-se também de que as alterações recentes na estrutura ou nos dados subjacentes das tabelas que você está consultando podem afetar o desempenho.
Habilitar a funcionalidade de log
Antes de analisar consultas individuais, você precisa definir parâmetros de comparação 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 excedem 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 do log de consultas lentas, você pode usar o diagnóstico do MySQL para solucionar problemas dessas consultas.
Antes de começar a rastrear consultas de execução prolongada, você precisa habilitar o parâmetro slow_query_log 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 long_query_time para especificar o número de segundos que as consultas podem ser executadas antes de serem identificadas como consultas de "execução lenta". O valor padrão do parâmetro é de 10 segundos, mas você pode ajustar o valor para atender às necessidades do SLA do aplicativo.
Embora o log de consultas lentas seja uma ótima ferramenta para rastrear consultas de execução prolongada, há certos cenários em que ele pode não ser eficaz. Por exemplo, o log de consulta lenta:
- Afetará negativamente o desempenho se o número de consultas for muito alto ou se a instrução de consulta for muito grande. Ajuste o valor do parâmetro
long_query_timeadequadamente. - Talvez não será útil se você também tiver habilitado o parâmetro
log_queries_not_using_index, que especifica as consultas de log 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.
Recuperar informações dos logs
Logs estão disponíveis por até sete dias desde a criação deles. Você pode listar e baixar logs de consultas lentas usando o portal do Azure ou a 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 os 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
Observação
Para obter mais exemplos para começar a diagnosticar logs de consulta lenta por meio de logs de diagnóstico, consulte Analisar logs nos logs do Azure Monitor.
O instantâneo a seguir ilustra uma consulta lenta de exemplo.
# 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%';;
Observe que 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ê observar consultas com uma Query_time alta, mas apenas algumas Rows_examined, isso geralmente indicará a presença de um gargalo de recursos. Para esses casos, você deve verificar se há alguma restrição de E/S ou uso de CPU.
Criar 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 coletar mais detalhes.
Para verificar o plano de consulta, execute o seguinte comando:
EXPLAIN <QUERY>
Observação
Para obter mais informações sobre como usar instruções EXPLICAR, consulte Como usar EXPLAIN para criar perfil de desempenho de consulta no Banco de Dados do Azure para servidor flexível MySQL.
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 analisar 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>;
Observação
A criação de perfil de consultas individuais só está disponível em uma sessão e as instruções históricas não podem ser analisadas.
Vamos examinar mais de perto o uso desses comandos para analisar uma consulta. Primeiro, habilite a criação de perfil para a sessão atual. Execute o comando SET PROFILING = 1:
mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Em seguida, execute uma consulta subideal que executa 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 comando SHOW PROFILES:
mysql> 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)
Por fim, para exibir o perfil da consulta 1, execute o comando SHOW PROFILE FOR QUERY 1.
mysql> 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)
Listar 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 de 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 de 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;
Observação
Use essa consulta para avaliar o desempenho das principais consultas executadas no servidor de banco de dados e determinar se houve uma alteração nas principais consultas ou se alguma consulta existente no parâmetro de comparação inicial aumentou na duração da execução.
Listando as 10 consultas mais dispendiosas por tempo de execução total
A saída da consulta a seguir fornece informações sobre as dez principais consultas em execução no servidor de banco de dados e o número de execuções no servidor de banco de dados. Também fornece outras informações úteis, como as latências de consulta, tempos de bloqueio, o número de tabelas temporárias criadas como parte do runtime de consulta etc. Use essa saída de consulta para acompanhar as principais consultas no banco de dados e 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;
Monitorar a coleta de lixo do InnoDB
Quando a coleta de lixo do InnoDB é bloqueada ou atrasada, o banco de dados pode desenvolver um retardo de limpeza substancial que pode afetar negativamente a utilização do armazenamento e o desempenho da consulta.
O tamanho 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 de 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 foi bloqueada por uma consulta ou transação de execução prolongada.
Atrasos excessivos na coleta de lixo podem ter consequências severas e negativas:
- O espaço de tabela do sistema do 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 para 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 impeçam que o mecanismo reutilize o armazenamento ocupado por esses registros.
- O desempenho de todas as consultas pode ser degradado 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 HLL.
Localizar valores de HLL
Você pode localizar o valor de HLL executando o comando de status do InnoDB do mecanismo de exibição. O valor será listado na saída, no título TRANSACTIONS:
mysql> 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 de 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)
Interpretar valores de HLL
Ao interpretar valores de HLL, considere as diretrizes listadas na seguinte tabela:
| Valor | Observações |
|---|---|
| Menor que ~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 um pequeno atraso na coleta de lixo. Esses valores poderão ser aceitáveis se permanecerem estáveis e não aumentarem. |
| Maior que ~1.000.000 | Esses valores devem ser investigados e podem exigir ações corretivas |
Abordar valores de HLL excessivos
Se a HLL mostrar picos grandes 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 problemas de carga de trabalho que possam estar impedindo o andamento do processo de coleta de lixo. Embora não seja esperado que o banco de dados esteja livre de defasagem de limpeza, você não deve deixar que o atraso cresça incontrolavelmente.
Para obter informações de transação da tabela information_schema.innodb_trx, por exemplo, execute os seguintes comandos:
select * from information_schema.innodb_trx
order by trx_started asc\G
Os detalhes na coluna trx_started ajudarão você 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 sessões de banco de dados atuais, incluindo o tempo gasto no estado atual da sessão, verifique a tabela information_schema.processlist. A seguinte saída, por exemplo, mostra uma sessão que está executando ativamente uma consulta nos últimos 1.462 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
Verifique se o banco de dados tem recursos suficientes alocados para executar suas consultas. Às vezes, talvez seja necessário escalar verticalmente 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 execução dividindo-as em transações menores.
Configure innodb_purge_threads de acordo com sua carga de trabalho para aprimorar a eficiência para operações de limpeza em segundo plano.
Observação
Teste as alterações nessa variável de servidor para cada ambiente para avaliar a alteração no comportamento do mecanismo.
Use alertas em "Porcentagem da CPU do Host", "Porcentagem de Memória do Host" e "Total de Conexões" para que você receba notificações se o sistema exceder qualquer um dos limites especificados.
Use a Análise de Desempenho de Consultas ou as Pastas de Trabalho do Azure para identificar consultas problemáticas ou lentas e otimizá-las.
Para servidores de banco de dados de produção, colete diagnósticos em intervalos regulares para garantir que tudo esteja em execução sem problemas. Caso contrário, solucione e resolva os problemas que você identificar.
Próximas etapas
Para localizar respostas de pares às suas perguntas mais importantes ou publicar ou responder a uma pergunta, acesse o Stack Overflow.