Embora não haja uma fórmula mágica para o ajuste do MySQL, existem algumas áreas que podem ser focadas no início e que podem melhorar drasticamente o desempenho da instalação do MySQL. Embora muitas informações tenham sido publicadas sobre esse tópico ao longo dos anos, eu queria analisar algumas das configurações mais críticas que qualquer um pode implementar sem a necessidade de suposições.
Dependendo da versão do MySQL que você está executando, alguns dos valores padrão usados neste artigo podem ser diferentes da sua instalação, mas a premissa ainda é basicamente a mesma.
O ajuste inicial de desempenho do MySQL pode ser dividido nas seguintes categorias:
- Ajuste para o seu hardware
- Ajuste para melhor desempenho / melhores práticas
- Ajuste para sua carga de trabalho
Ajustando o MySQL para o seu hardware
Dependendo do hardware em que você instalou o MySQL, algumas variáveis precisam ser definidas com base nas especificações da máquina (ou VM). As seguintes variáveis dependem muito do seu hardware:
innodb_buffer_pool_size
- Geralmente, defina para 50% – 70% de sua RAM total como ponto de partida.
- Ele não precisa ser maior do que o tamanho total do banco de dados.
- O Percona Monitoring and Management (PMM) pode oferecer uma visão adicional, mostrando o uso do pool de buffer e permitindo que você ajuste de acordo.
innodb_log_file_size
- Geralmente é definido entre 128M – 2G.
- Deve ser grande o suficiente para armazenar no máximo uma hora ou mais de toras.
- Isso é mais do que suficiente para que o MySQL possa reordenar as gravações para usar I / O sequencial durante os processos de limpeza e ponto de verificação.
- PMM pode oferecer uma visão adicional, como se você estiver usando mais de 50% do seu espaço de log, você pode se beneficiar de um aumento no tamanho do arquivo de log.
innodb_flush_log_at_trx_commit
- Definir como “1” (padrão em 5.7) oferece maior durabilidade.
- Definir como “0” ou “2” proporcionará mais desempenho, mas menos durabilidade.
innodb_flush_method
- Definir isso como O_DIRECT evitará uma penalidade de desempenho de buffer duplo.
Ajuste de MySQL para melhor desempenho / práticas recomendadas
innodb_file_per_table
- Definir isso como “ON” irá gerar um espaço de tabela InnoDB independente para cada tabela no banco de dados.
innodb_stats_on_metadata
- Definir como “OFF” evita atualizações desnecessárias das estatísticas InnoDB e pode melhorar muito as velocidades de leitura.
innodb_buffer_pool_instances
- A melhor prática é definir isso como “8”, a menos que o tamanho do buffer pool seja <1 G, caso em que defina como “1”.
query_cache_type & query_cache_size
- Definir ambos como “0” desabilitará totalmente o cache de consulta.
Ajuste para sua carga de trabalho
Para sintonizar ainda mais, mais informações serão necessárias. A melhor maneira de coletar essas informações é instalar uma ferramenta de monitoramento / criação de gráficos MySQL como a plataforma de monitoramento e gerenciamento Percona . Depois de instalar uma ferramenta, podemos mergulhar nas métricas individuais e começar a personalizar com base nos dados.
Eu recomendaria começar com uma das variáveis mais impactantes – o innodb_buffer_pool_size . Compare a RAM e o número de páginas livres em sua instância com o tamanho total do buffer pool. Com base nessas métricas, você pode determinar se precisa aumentar ou diminuir a configuração geral do tamanho do buffer pool.
A seguir, dê uma olhada em suas métricas para o uso do arquivo de log InnoDB. A regra é que seus arquivos de log devem conter aproximadamente uma hora de dados. Se você perceber que seus dados gravados nos arquivos de log a cada hora excedem o tamanho total dos arquivos de log, você pode querer aumentar a variável innodb_log_file_size e reiniciar o MySQL. Você também pode verificar com “SHOW ENGINE INNODB STATUS;” por meio da CLI do MySQL para auxiliar no cálculo de um bom tamanho do arquivo de log do InnoDB.
Outros ajustes
Outras configurações do InnoDB que podem ser ajustadas para um melhor desempenho são:
innodb_autoinc_lock_mode
- Definir isso como “2” (modo intercalado) pode remover a necessidade de um bloqueio de integração automática (no nível da tabela) e pode aumentar o desempenho ao usar instruções de inserção de várias linhas para inserir valores em uma tabela com uma chave primária de incremento automático. Observe que isso requer o formato de binlog ROW ou MIXED.
innodb_io_capacity / innodb_io_capacity_max
- Essas configurações afetarão seu banco de dados se você estiver utilizando um fluxo de trabalho com muitas gravações. Isso não se aplica ao tráfego de leitura (SELECT). Para ajustar esses valores, é melhor saber quantos iops seu sistema pode executar. É uma boa ideia executar o sysbench ou outra ferramenta de referência para determinar a taxa de transferência de armazenamento.
- O PMM pode oferecer uma visão adicional, mostrando seu uso de IO e permitindo que você ajuste de acordo.
Resumindo
Embora este não seja, de forma alguma, um artigo abrangente sobre o ajuste do MySQL, as sugestões acima devem eliminar alguns dos problemas mais fáceis e deixar seu sistema mais perto de uma configuração ideal. Como acontece com todo ajuste de banco de dados, seu processo deve ser contínuo com base nas informações atuais.
- Examine as configurações propostas acima e implemente-as se fizerem sentido para o seu ambiente / carga de trabalho.
- Instale uma boa ferramenta de monitoramento para fornecer informações sobre o banco de dados ( Percona Monitoring and Management é nossa sugestão).
- Fique atualizado em seus gráficos de monitoramento para determinar outras áreas onde você pode precisar ajustar.
