Desempenho do MySQL sobre uma rede (local) muito mais lenta do que eu esperaria

Question

As consultas do MySQL no meu ambiente de produção estão demorando muito mais do que eu esperaria delas também. O site em questão é um site Drupal razoavelmente grande, com muitos módulos instalados. O servidor web (Nginx) e o servidor de banco de dados (mysql) são hospedados em máquinas separadas, conectadas por uma conexão LAN de 100mbps (hospedada pela Rackspace).

Eu tenho exatamente o mesmo site em execução no meu laptop para desenvolvimento. Obviamente, no meu laptop, o servidor web e o servidor de banco de dados estão na mesma caixa.

Aqui estão os resultados dos tempos de consulta do meu banco de dados:

Produção:

Executou 291 consultas em 320,33 milissegundos. (homepage)

Executou 517 consultas em 999,81 milissegundos. (página de conteúdo)

Desenvolvimento:

Executadas 316 consultas em 46,28 milissegundos. (homepage)

Executadas 586 consultas em 79,09 milissegundos. (página de conteúdo)

Como pode ser visto claramente a partir desses resultados, o tempo envolvido na consulta do banco de dados MySQL é muito menor no meu laptop, onde o servidor MySQL está sendo executado no mesmo banco de dados que o servidor da Web.

Por que isso acontece?!

Um fator deve ser a latência da rede. Em média, uma viagem de ida e volta do servidor para o servidor de banco de dados leva 0,16ms (mostrada por ping). Isso deve ser adicionado a todas as consultas singulares do MySQL. Então, pegando o exemplo da página de conteúdo acima, onde existem 517 consultas executadas. Apenas a latência da rede adicionará 82ms ao tempo total da consulta. No entanto, isso não explica a diferença que estou vendo (79ms no meu laptop vs 999ms nas caixas de produção).

Que outros fatores eu deveria estar olhando? Eu pensei em atualizar o NIC para uma conexão gigabit, mas claramente há algo mais envolvido.

Eu executei o script de ajuste de desempenho do MySQL do link e ele me diz que meu servidor de banco de dados está bem configurado (melhor que meu laptop aparentemente). O único problema relatado é "de 4394 tabelas temporárias, 48% foram criadas no disco". Isso é verdade em ambos os ambientes e no ambiente de produção eu até tentei aumentar max_heap_table_size e Current tmp_table_size para 1GB, sem alteração (acho que isso é porque eu tenho algumas colunas BLOB e TEXT).

    

Answer 1

É porque se o DB não estiver no mesmo servidor que o frontend, você terá que fazer uma viagem de ida e volta para cada consulta (possivelmente mais, dependendo do tamanho da resposta). Pode facilmente levar até 1 ms para executar ping em outro servidor no mesmo datacenter. Muito menos se os dois servidores estiverem no mesmo rack, mas 1 ms é uma boa regra prática.

Isso significa que, para ~ 300 consultas pequenas, você deve esperar ~ 300 ms, muito próximo do que está vendo.

Se os dois servidores estiverem na mesma máquina, você só precisará fazer algumas alternâncias de contexto entre os processos para mover os dados do processo de banco de dados para o frontend. Normalmente, um switch de contexto (com todos os flushes típicos) leva ~ 40us (muito amplamente falando), e você precisa de pelo menos um par deles (frontend pede dados, DB lê requisições e prepara e serve os dados, e frontend lê de volta o dados). Então eu esperaria ~ 80us para cada consulta (vamos arredondar para 0.1ms para tornar a matemática mais fácil). Assim, você pode esperar ~ 30 ms por 300 consultas pequenas, o que também é muito parecido com o que você está vendo.

    

Answer 2

É realmente difícil dar uma resposta definitiva. Eu realmente não acho que a rede é o gargalo, bem, a menos que você tenha um tráfego muito pesado no servidor.

Você tem a mesma configuração exata em sua máquina de desenvolvimento e no servidor de produção?

Tem certeza de que todos os índices foram criados com sucesso no servidor de produção?

Poderia a quantidade de dados no seu servidor de produção. Eu quero comparar os resultados entre as máquinas de produção e desenvolvimento você deve ter o mesmo número de linhas nas tabelas, é este o caso?

Você usou os mesmos arquivos de instalação para configurar o MySQL em sua máquina de desenvolvimento e servidor de produção? Quer dizer, você tem a mesma versão do MySQL em suas máquinas de desenvolvimento e produção? É para o mesmo sistema operacional? Também 32 bits ou 64 bits? Provavelmente um bug na versão do MySQL instalado no servidor.

As possibilidades são infinitas, sem mais detalhes, será muito difícil dar uma resposta informativa.

    

Answer 3

The only problem reported is "Of 4394 temp tables, 48% were created on disk". This is true in both environments and in the production environment I have even tried increasing max_heap_table_size and Current tmp_table_size to 1GB, with no change (I think this is because I have some BLOB and TEXT columns).

Você pode colocar o tmpdir do MySQL em um filesys baseado em RAM (por exemplo, tmpfs) para ajudar a melhorar o desempenho.

Felicidades

P.S.

Talvez o impacto no desempenho seja de gravações RAID na Produção.

    

Answer 4

Você tem o cache de consulta ativado? Veja a saída de:

SHOW VARIABLES LIKE 'query_cache_size';

Se for maior que zero, está ativado. Isso fará uma grande diferença no desempenho de um site apoiado pelo MySQL, embora eu não tenha certeza se isso tem algo a ver com o seu problema específico aqui.

Eu não suponho que haja alguma maneira de capturar o conjunto de consultas e executá-las a partir de um login no servidor de banco de dados MySQL, para ver se as coisas são diferentes lá?

Outro possível problema é que, se você não estiver fazendo o cache de sessão, estará abrindo uma nova sessão toda vez que fizer uma chamada ao banco de dados. Você sabe se você está fazendo cache de sessão? Abrir uma nova sessão para cada chamada pode ficar caro em toda a rede.

    

Answer 5

O que eu faria para identificar onde o gargalo é: Eu faria um perfil da consulta: http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

Se os números forem muito mais baixos do que você pode ver na mesma consulta executada em seu servidor da Web, isso é um problema de "transporte". (Latência de rede Rackspace, configuração de comutação, grandes conjuntos de dados (SELECT *) com TEXT / BLOB campos retornados, etc.)

Se os números forem quase parecidos, você terá que otimizar suas consultas e melhorar seu arquivo de configuração. Aqui está um link que pode ajudar: link

BTW, quando meu servidor passou de 100Mbs para 1Gb, o aumento de desempenho foi incrível!

    

Answer 6

A configuração e a desativação da conexão TCP são muito mais caras do que a conexão do soquete ao trabalhar no host local. Verifique quantas conexões ao banco de dados são iniciadas em um carregamento de página.