Você pode dividir a carga relativa do MySQL pela “fonte” de consulta?

Question

Estou tentando melhorar o desempenho do servidor, e está claro que o MySQL é um grande contribuidor para o problema. A resolução de problemas, no entanto, é muito difícil. Eu estou usando o log de consultas lentas para direcionar tipos específicos de consultas, mas a questão real é que o MySQL é usado por processos Java, processos PHP e cron jobs (que são tipicamente processos PHP, mas executados através da linha de comando versus via Apache )

Normalmente, quando o servidor fica lento, eu vou executar alguns comandos como "ps" ou "top" para tentar encontrar o culpado, mas mesmo se eu sei que o MySQL é o culpado, eu não sei qual dos três " reinos "eu mencionei pode estar realmente causando lentidão. Em outras palavras, eu adoraria de alguma forma quebrá-lo e ver que, por exemplo, 80% das demandas colocadas no MySQL naquele momento são devidas a consultas do PHP, enquanto apenas 20% vêm do Java. Como tenho tarefas automatizadas e eventos periódicos acionados de ambas as "regiões", é difícil isolar seus efeitos apenas por meio de tentativa e erro.

Tudo está em um servidor, então todas as consultas do MySQL vêm do localhost. Eu poderia também "marcar" consultas, adicionar comentários a elas ou, de outra forma, adicionar algum tipo de meta-dados para que eu pudesse analisá-las posteriormente para uma carga relativa?

Eu duvido que haja uma maneira de obter informações como essa, mas se alguém puder ajudar a fornecer uma maneira de detalhar o carregamento do MySQL de uma forma que ajude a identificar a origem dessas consultas (IDs de processo podem funcionar também), ajudar imensamente. Obrigado!

    

Answer 1

Sugiro usar o Proxy do MySQL . Com o MySQL Proxy você pode interceptar e registrar todas as consultas no seu servidor.

Uma abordagem mais simples seria executar mysqlbinlog para analisar os arquivos de log binários gerados pelo MySQL (consulte man mysqlbinlog ).

De qualquer forma, se você ainda não fez isso, seria uma boa ideia executar mysqltuner para ver se há problemas óbvios ou afunilamentos (como, por exemplo, um número incomum de consultas executando JOINs sem índices) .

    

Answer 2

Você já tentou fazer login no serviço mysql e emitiu um SHOW FULL PROCESSLIST ou usando mytop para ver quais consultas estão sendo executadas?

SHOW FULL PROCESSLIST fornece uma lista de todas as consultas e tarefas que o MySQL está fazendo atualmente. Ele precisa ser executado como um usuário com privilégios "PROCESS" ou "SUPER" no banco de dados. mysqladmin também possui um argumento processlist.

mytop é muito bom para mostrar o que o servidor está fazendo em uma base contínua. Pense nisso como algo como o topo do MySQL.

Ambos irão dizer a você quais comandos estão rodando, de onde eles vêm e por quanto tempo o MySQL está tentando atendê-los até agora.

Uma vez que você está armado com essa informação, você pode decidir o que fazer. Se o sistema estiver ligado, talvez seja necessário particionar seus dados. Se você é lido, existem coisas que podem ajudar. Tabelas de indexação corretamente podem dar um impulso significativo ao desempenho de leitura em muitos casos. Se você tiver muitas consultas idênticas, adicionar um cache pode ajudar. Adicionar escravos lidos também pode melhorar o desempenho. Armazenar em cache e adicionar escravos aumentará o desempenho, mas adicionará mais complexidade à sua rede e aplicativo.

    

Answer 3

Uma possibilidade é garantir que os diferentes processos usem credenciais diferentes para se conectar ao mysql. Tenho certeza que o mysql registra o nome de usuário que executou a consulta no log de consulta.

    

Answer 4

Sim, você pode, mas a criação de perfil sem usar um criador de perfil baseado em cliente requer que você passe as solicitações por algo que possa capturá-las. Nesse caso, isso será MySql-Proxy . Há instruções em seu site sobre como configurá-lo para impedir e consultas de perfil e, em seguida, você pode executar explica e outras operações contra os que parecem estar gastando muito tempo.

Outro método que não é tão complicado, mas pode funcionar, é definir o tempo limite de consulta lenta como um valor muito baixo e, em seguida, certifique-se de ativar o log de consultas lentas. Dessa forma, você verá qualquer coisa que demore mais do que alguns segundos. Isso não ajudará se os desenvolvedores (ou seus ORMs) fizerem um monte de consultas básicas e, em seguida, resumirem os dados no aplicativo, é claro.

    

Answer 5

MOSTRAR PROCESSLIST COMPLETA;

Isso mostra os processos e tempos de execução. Para diferenciar a fonte, você pode usar logins diferentes para cada "região" OU pré-definir a região em todas as consultas. por exemplo, esta é uma consulta válida:

/ * php * / SELECT * de itens;

como está:

/ * java * / SELECT * FROM itens;

etc.

Mas você ainda não consegue ver o carregamento do sistema causado pelas consultas. Pode ser útil de qualquer maneira, e você pode hackear suas ferramentas de monitoramento para representar graficamente os dados para procurar correlações com seu desempenho.

    

Answer 6

O script mk-query-digest do Maatkit não será classificado por fonte, mas fornecerá muitas informações sobre quais consultas estão sobrecarregando mais os recursos.

link

    

Answer 7

Marcamos consultas com comentários, como mibus sugerido acima. Em seguida, gravamos regularmente a saída FULL PROCESSLIST para os arquivos. Quando temos problemas de desempenho, nós importamos esses arquivos para o banco de dados e obtemos os dados para ver as consultas longas. Pelo menos para nós, estes causam mais carga.

Eu seria cuidadoso ao executar o MySQL Proxy em um sistema de produção. Eu encontrei para bloquear ocasionalmente. Além disso, você tem mais uma infra-estrutura que precisa monitorar.

    

Answer 8

Você está pensando em usar o perfil do mysql?

link

mysql>set profiling=1;
<run your queries with profiling enabled>
mysql>show profiles;

A saída desta será uma tabela com QueryID, Duration of Query e Query string. Semelhante a isto:

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        0 | 0.00007300 | set profiling=1                               |
|        1 | 0.00044700 | select count(*) from client where broker_id=2 |
+----------+------------+-----------------------------------------------+

A partir daqui, você pode interromper essa consulta com

mysql>show profile for query <QueryID>;

Isso fornecerá uma análise detalhada de quanto tempo a consulta passou em cada estágio da execução. Você pode aprofundar ainda mais os detalhes de quanto tempo de CPU é gasto em uma consulta:

mysql> show profile cpu for query 4;
+----------------------+------------+------------+------------+
| Status               | Duration   | CPU_user   | CPU_system |
+----------------------+------------+------------+------------+
| (initialization)     | 0.00002900 | 0.00000000 | 0.00000000 |
| checking permissions | 0.00000800 | 0.00000000 | 0.00000000 |
| init                 | 0.00004000 | 0.00000000 | 0.00000000 |
| Opening table        | 0.00009400 | 0.00100000 | 0.00000000 |
| System lock          | 0.00000500 | 0.00000000 | 0.00000000 |
| Table lock           | 0.00000700 | 0.00000000 | 0.00000000 |
| setup                | 0.00004200 | 0.00000000 | 0.00000000 |
| creating table       | 0.00195800 | 0.00000000 | 0.00100000 |
| After create         | 0.00010900 | 0.00000000 | 0.00000000 |
| copy to tmp table    | 0.52264500 | 0.55591600 | 0.04199300 |
| rename result table  | 0.11289400 | 0.00199900 | 0.00000000 |
| end                  | 0.00004600 | 0.00000000 | 0.00000000 |
| query end            | 0.00000700 | 0.00000000 | 0.00000000 |
| freeing items        | 0.00001300 | 0.00000000 | 0.00000000 |
+----------------------+------------+------------+------------+

Sugiro ler a página de informações para descobrir exatamente quais informações você gostaria, pois a ferramenta é bastante detalhada, mas isso deve ajudá-lo a encontrar seus gargalos no daemon mysql.

    

Answer 9

O primeiro passo é não ter todos os seus processos usando o mesmo usuário. Especialmente se todos eles estão vindo através de 'localhost'. Mesmo para consultas vindas da mesma ferramenta (como em php), as que estão no cron usam um usuário separado daquelas que estão sendo executadas através do apache.

Uma vez feito isso, você pode usar mysqlbinlog para analisar os logs lentos (ele tem alguns grandes campos para ordenar as consultas / normalizar as consultas) ou até mesmo usar algo mais avançado como um profiler para encontrar as consultas mais lentas