Ajuste de desempenho do MySQL

Question

Estou procurando uma lista sucinta de armadilhas comuns e otimizações para ajustar um servidor MySQL como usado para websites de tamanho médio.

Em geral, o tipo de conselho que estou procurando aqui é uma informação que um desenvolvedor ou administrador comum pode implementar facilmente e que proporcionará um benefício mensurável ao desempenho de seu site.

Como exemplo

Aqui está uma dica que eu peguei lendo o MySQL de Alto Desempenho que eu freqüentemente vejo uso para:

Ao usar o mecanismo de armazenamento MyISAM (o padrão), o servidor bloqueará a tabela inteira ao executar a operação DELETE ou UPDATE ou ao executar um INSERT que não seja anexado ao final da tabela (porque há um "furo" "saiu de um DELETE anterior). Nenhuma outra consulta pode usar essa tabela até que a operação seja concluída.

Portanto, você deve usar o InnoDB ou outros mecanismos de bloqueio de nível de linha em qualquer tabela muito usada se houver muita modificação usando qualquer operação diferente de "INSERT".

    

Answer 1

Aqui estão algumas das coisas que encontrei com a otimização do MySQL:

Se você ver que seu servidor de banco de dados (os processos do MySQL em particular) estão usando a maior parte do tempo da CPU, isso provavelmente significa que você está perdendo índices ou precisa otimizar as consultas.

Ative o registro lento de consultas e siga essas consultas lentas para descobrir como elas podem ser otimizadas. Use "explain" com as consultas lentas para descobrir por que elas estão lentas. A documentação do MySQL tem várias seções sobre como interpretar esses resultados.

Use o memcached para armazenar em cache o resultado de qualquer consulta possível por quanto tempo você puder. Isso é extremamente rápido e capaz de armazenar em cache muitas coisas que o cache de consulta interno do MySQL não pode, mas só pode ser usado em casos onde você sabe que os dados podem ser armazenados em cache a longo prazo ou você expira manualmente o cache.

Configure o "munin" no sistema de banco de dados para começar a coletar informações sobre a utilização do sistema e o carregamento da consulta do banco de dados.

Certifique-se de que seu sistema tenha RAM suficiente que você não esteja trocando, e esperançosamente também o bastante para fazer poucas leituras de disco (use vmstat ou munin para monitorar isso). O que significa que os dados são servidos fora do MySQL ou caches de buffer de disco.

Sean

    

Answer 2

Não é exatamente o tópico, mas a otimização do MySQL só não necessariamente resultará na otimização do desempenho do site. O que você descreveu acima é apenas um dos milhões possíveis. É um caso frequente no seu aplicativo da web? Como esse recurso específico do MyIssam desacelera o seu site? (Considerando que o MyIssam é dezenas ou centenas de vezes mais rápido que o InnoDB). Como você descobriu que é o seu caso?

Basicamente, sua pergunta não faz sentido até que você realmente faça o perfil do seu aplicativo e encontre o gargalo. Caso contrário, alguns conselhos comuns iriam contradizer um com o outro.

Além disso, otimização de desempenho é geralmente tudo sobre as trocas. Você terá sacrificado alguma otimização por outro.

Este é um bom artigo para começar: link

Não é sobre o MySQL nem sobre aplicativos da web, é sobre otimização.

    

Answer 3

Uma coisa que pode afetar adversamente o desempenho do MySQL é a criação de tabelas temporárias em disco.

Você pode executar isso por vários minutos:

mysqladmin -u root -p ext -ri 30 | grep Created_tmp_disk

e se o número for alto e estiver crescendo, você pode considerar colocar o tmpdir do MySQL em um sistema de arquivos baseado em RAM (por exemplo, tmpfs).

De alguma forma, isso pode estar tratando o sintoma ao invés da causa, mas pode fornecer algum alívio rápido enquanto você considera se / como tratar a causa. O link a seguir fornece informações sobre como o MySQL usa tabelas temporárias internas:

link

Felicidades

    

Answer 4

Todas ótimas dicas. Outro detalhe que acrescentarei é que às vezes os índices que você tem não são suficientes. Você pode pensar que adicionar um índice com as linhas mais consultadas é suficiente, mas a ordem em que elas estão no índice afeta muito o desempenho. Especialmente para o InnoDB que tenta obter os dados que precisa do índice sem ter que obter a linha em si (ex .: busca de disco). Se você tem uma tabela particularmente grande que é consultada quase igualmente de duas maneiras, é útil ter um índice exatamente para a ordem de pesquisa para os dois tipos de consulta. Pode parecer redundante para você, mas isso realmente leva o otimizador na direção certa:)

Além disso, variáveis de servidor, o arquivo MySQL my.cnf 'out of the box' nunca é o caminho certo. Ele serve muito para levar algum tempo para examinar seu conteúdo e ajustar valores com base em suas configurações de hardware e nos tipos de consultas que você obtém o máximo

    

Answer 5

@TechieGurl, sobre sua consulta de pedidos para as condições, você está errado. Isso foi corrigido em uma versão anterior do 4.x. O otimizador de consulta leva um número de métricas em jogo e usará uma chave que corresponda aos condicionais mais constantes, condicionais de longo alcance, etc. e embaralhará os parâmetros com base na ordem, presumindo que o SQL não contenha uma ordem de precedência que possa excluir tais métricas. uma otimização.

MyISAM irá responder os resultados da chave, InnoDB geralmente não irá e irá bloquear uma linha ao responder como parte da conformidade com o ACID e é por design ao invés de falha.

    

Answer 6

link

MySQLTuner é um script escrito em Perl que irá ajudá-lo com sua configuração do MySQL e fazer recomendações para maior desempenho e estabilidade: http: // github.com/rackerhacker/MySQLTuner-perl