Quais opções de configuração para o MySQL fornecem as maiores melhorias de velocidade?

Question

Quais opções de configuração para o MySQL fornecem as maiores melhorias de velocidade?

Eu estou querendo saber sobre melhorias reais no arquivo de configuração, tipos de tabelas, configurações de hardware, replicação, etc. Qualquer coisa além da estrutura de consulta e estrutura de tabela (elas são fáceis de encontrar no site e Stack Overflow). São coisas como configurações de cache de consulta que lhe deram mais velocidade? Como sobre drives; é melhor tê-lo em um RAID externo ou interno? A replicação oferece melhor desempenho, especialmente com consultas grandes de leitura?

Quais outras configurações / mudanças você fez para melhorar o desempenho do MySQL?

Nota: Eu percebo que estes são muito dependentes de uso (isto é, pequeno site vs data warehouse), mas como eu acho que a maioria de nós provavelmente trabalha em uma variedade de sites / sistemas, é bom conhecer uma variedade de técnicas que podem aplicam-se a diferentes situações. Além disso, acho que algumas técnicas podem ser transferidas entre situações.

    

Answer 1

Aqui estão minhas recomendações (sua produção pode variar)

• Use o RAID de hardware. Isso vai contra as minhas recomendações para usar o software RAID em outros posts, no entanto, esta é uma situação específica onde você quer a placa RAID de hardware. Especificamente, você deseja que a NVRAM suportada por bateria na placa RAID reduza o tempo necessário para executar o fsync do arquivo de registro em disco.
• Use apenas os volumes RAID 1 ou RAID 10. O custo de gravações RAID 5 ou 6 é muito alto para tolerar em uma carga de trabalho mista de leitura / gravação.
• Use LUNs separados para os volumes de dados, log e tmp. Todos devem ser separados do sistema operacional e dos volumes de troca.
• Use InnoDB .
• Use innodb_file_per_table
• Use um sistema operacional de 64 bits
• Defina seu buffer pool InnoDB como ~ 80% da sua RAM disponível
• Defina seus arquivos de log para 1/4 do tamanho do buffer pool, entre 2 e 4 arquivos de log. Arquivos de log maiores significam tempos mais lentos de desligamento e recuperação, mas permitem restaurar grandes despejos de banco de dados mais rapidamente.
• log_slow_queries, consultas de log-não-usando-índices, set-variable = long_query_time = 1, investigar todas as consultas nesse log, refatorar seu esquema para evitar varreduras de tabelas e tabelas tmp sempre que possível.

Answer 2

Mais uma vez Dave Cheney realmente o derrubou do parque aqui. Eu realmente não posso adicionar nada à sua resposta à sua pergunta. No entanto, gostaria de salientar o que você não perguntou. Como Jeremy Zawodny e Peter Zaitsev me ensinaram anos atrás, seu ROI para o tempo gasto rastreando e otimizando consultas ruins vai acabar com o ROI do tempo gasto fazendo alterações de configuração 10 vezes. Claro, você não quer ter uma configuração ruim, a configuração errada do RAID ou RAM insuficiente. Mas, entre excelentes, e até marginais, DBAs do MySQL, consultas ruins (geralmente de desenvolvedores / frameworks, não o DBA) é uma condição crônica , onde a configuração ruim é uma suportável .

(eu procurei esses adjetivos por um tempo e ainda não estou satisfeito com os que escolhi.)

Eu gostaria de enfatizar novamente que se seus desenvolvedores estão usando um ORM como aqueles comuns em frameworks como Ruby on Rails e Django, você REALMENTE DEVE monitorar as consultas que atingem seu banco de dados. Quando os desenvolvedores param de pensar em SQL e deixam o DB ser abstraído, é realmente desagradável essa fluência. Adoro os dois frameworks que acabei de mencionar. (Não me vote mal por falar mal deles.) Isso só torna o Query Sleuthing muito importante. (Leia: Segurança do Trabalho)

    

Answer 3

Algumas outras coisas (que não foram mencionadas na resposta de Dave Cheney)

• Tente definir innodb_flush_method como O_DIRECT para evitar o buffer duplo de dados. Evite isso se sua placa RAID não tiver um cache de gravação com bateria ou se seus dados estiverem em uma SAN.

• Jogue também com innodb_thread_concurrency. Eu acredito que o padrão é 8, mas vale a pena ajustar isso para ver se melhora o desempenho

• Verifique se o cache de consulta está ativado e verifique as estatísticas para ver qual é a taxa de hits. Se for bom, tente aumentá-lo para ver se melhora a taxa de acertos.

• Dependendo da execução dos aplicativos, você poderá alterar o nível de isolamento padrão. O padrão é REPEATABLE_READ, mas READ_COMMITTED pode oferecer melhor desempenho

• Se suas instruções são na maior parte UPDATEs e DELETEs, você pode tentar inicializar o cache no escravo, fazendo uma consulta SELECT que retorna o conjunto de resultados que deve ser modificado. Confira a ferramenta mk-slave-prefetch que será faça isso por você

• Veja outros mecanismos de armazenamento além do MyISAM e do InnoDB

Answer 4

Não é possível dizer nada sobre hardware, mas você pode tentar fazer um link . É um script em Perl que analisa suas configurações do MySQL.

Você pode ver um exemplo de saída no link

    

Answer 5

A primeira coisa geral que você deve fazer é olhar para os parâmetros de memória. As configurações padrão para o MySQL são muito muito conservadoras. Seja qual for o motor que você usa, você provavelmente precisará aumentar alguns parâmetros de memória em dez ou até cem vezes.

A próxima coisa que você deve fazer é olhar o cache da tabela. O valor padrão é 64, que é útil apenas se você não tiver mais de 60 tabelas. Você vai querer levantar um longo caminho.

A terceira coisa que você deve fazer é examinar os parâmetros de thread e conexão. O wait_timeout padrão é extremamente longo para a maioria dos aplicativos baseados na web e pode ser reduzido para algo como 30 segundos. Isso também melhorará o uso de memória, já que o MySQL obterá conexões mais cedo, deixando muito menos em um estado de 'suspensão'.