Testar o desempenho das consultas no mysql
- tenho acesso de raiz Sou o único utilizador a aceder ao servidor.
- principalmente interessado no desempenho InnoDB As consultas que estou a otimizar são na maioria consultas de pesquisa.(
SELECT ... LIKE '%xy%')
até agora eu tenho usado SQL_ NO_CACHE, mas às vezes os resultados de tais testes também mostram comportamento de cache - levando muito mais tempo para executar na primeira execução e tendo menos tempo em corridas subsequentes.
Se alguém puder explicar este comportamento com todo o detalhe, posso continuar a usar SQL_NO_CACHE; acredito que possa ser devido à 'cache' do sistema de ficheiros e/ou 'Cache' dos índices usados para executar a consulta, como Este explica o post. Não é claro para mim quando Buffer Pool e Key Buffer ficam invalidados ou como eles podem interferir com os testes.
então, a não ser reiniciar o servidor mysql, como você recomendaria configurar um ambiente que fosse confiável para determinar se uma consulta funciona melhor do que a outra?
6 answers
Assumindo que você não pode otimizar a operação similar em si, você deve tentar otimizar a consulta base sem que eles minimizem o número de linhas que devem ser verificadas.
Algumas coisas que podem ser úteis para isso:rows coluna em explicar seleccionar ... resultado. Então,
mysql> set profiling=1;
mysql> select sql_no_cache * from mytable;
...
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000063 |
| Opening tables | 0.000009 |
| System lock | 0.000002 |
| Table lock | 0.000005 |
| init | 0.000012 |
| optimizing | 0.000002 |
| statistics | 0.000007 |
| preparing | 0.000005 |
| executing | 0.000001 |
| Sending data | 0.001309 |
| end | 0.000003 |
| query end | 0.000001 |
| freeing items | 0.000016 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+--------------------+----------+
15 rows in set (0.00 sec)
Então,
mysql> FLUSH STATUS;
mysql> select sql_no_cache * from mytable;
...
mysql> SHOW SESSION STATUS LIKE 'Select%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1 |
+------------------------+-------+
5 rows in set (0.00 sec)
E outro valor interessante é last_query_cost, que mostra quão caro o Optimizador estimou a consulta (o valor é o número da Página aleatória leia):
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 2635.399000 |
+-----------------+-------------+
1 row in set (0.00 sec)
Citado de esta página: as opções SQL_ NO_ Cache afectam o caching dos resultados da pesquisa na 'cache' da pesquisa . Se a sua mesa é bastante pequena, é possível, que a própria mesa já está em cache. Uma vez que você apenas evitar caching dos resultados e não as tabelas você começa o comportamento descrito às vezes. Então, como dito nos outros postings, você deve lavar suas tabelas entre as consultas.
Como o artigo associado sugere, use FLUSH TABLES entre os testes para reiniciar o máximo que puder (nomeadamente a 'cache' da consulta).
Você também pode tentar medir o desempenho com tabelas de MyISAM equivalentes, onde {[[0] } realmente irá reiniciá-lo para um ponto de partida quase idêntico.
Já tentou desligar o Cache da pesquisa ao todo? Mesmo com o SQL_ NO_CACHE, há cerca de 3% de penalidade apenas tendo o cache da consulta ligado.Já pensou em usarMaatkit ? Uma de suas capacidades que eu estou um pouco familiarizado é capturar dados da rede MySQL com tcpdump e processar o dump com {[[0]}. Esta ferramenta permite-lhe mostrar alguns detalhes finos sobre cada consulta. Mas há um monte de outras ferramentas que devem tornar a análise de consulta mais fácil.
Você poderia tentar o MySQL workbench, eu pensei que ele tinha um monitor de declarações sql para que você possa ver quão rápido é e por que é rápido
As consultas de texto completo no InnoDB são lentas (como as declarações" %query%"), não há nada que possa fazer para as optimizar. As soluções variam de passagem, que determinada tabela que você está consultando para MyISAM, então você pode criar índices fulltext (que o innoDB não apoio), para denormalizing a linha em índices pesquisáveis (não recomendado), o Doctrine ORM fornece um exemplo fácil de como alcançar este : http://www.doctrine-project.org/documentation/manual/1_1/nl/behaviors:core-behaviors:searchable A solução "adequada" para o seu problema seria indexar a informação que você está usando pesquisas de texto completo, com uma solução como pesquisa Esfinge ou Apache Solr.
Como foi dito anteriormente, você deve considerar o estado de cache ao comparar os resultados, um cache carregado dá consultas extremamente performantes. Você deve considerar a porcentagem de acerto de cache de uma consulta particular, mesmo se for um consulta cara, se ele tem uma taxa de sucesso de cache de 99%, o desempenho médio será muito alto.
Afinar Finegrained das consultas não é uma bala de prata, você pode estar adicionando complexidade à sua aplicação para o bem de optimizações que em geral em um ambiente de produção, são desprezíveis.
Considere a sua carga de trabalho, resolução de problemas frequentes, consultas sem desempenho (use o slow_query_log no mysql, não comece cegamente a otimizar as consultas).