Otimize completamente o banco de dados mysql sob alta carga no Linux

Ao mesmo tempo, o número de visitas online continua a aumentar. Quando o servidor com memória 1G está obviamente sobrecarregado, ele pode até travar todos os dias ou travar de vez em quando. Esse problema me incomoda há mais da metade. por mês, o MySQL usa um algoritmo muito escalável, então você geralmente pode executá-lo com menos memória ou fornecer mais memória ao MySQL para obter melhor desempenho.

Após instalar o mysql, os arquivos de configuração devem estar no diretório /usr/local/mysql/share/mysql. Existem vários arquivos de configuração, incluindo my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf. , Sites com diferentes fluxos de tráfego e ambientes de servidor com configurações diferentes exigirão, obviamente, arquivos de configuração diferentes.

Em circunstâncias normais, o arquivo de configuração my-medium.cnf pode atender à maioria de nossas necessidades; geralmente copiaremos o arquivo de configuração para /etc/my.cnf e só precisaremos modificar este arquivo de configuração, usar variáveis ​​​​mysqladmin estendidas-status –u root –p pode ver os parâmetros atuais. Existem três parâmetros de configuração que são os mais importantes, nomeadamente key_buffer_size, query_cache_size, table_cache .

key_buffer_size funciona apenas para tabelas MyISAM,

key_buffer_size especifica o tamanho do buffer de índice, que determina a velocidade de processamento do índice, especialmente a velocidade de leitura do índice. Geralmente, definimos para 16M. Na verdade, esse número está longe de ser suficiente para sites um pouco maiores. Ao verificar os valores de status Key_read_requests e Key_reads, você pode saber se a configuração key_buffer_size é razoável. A proporção key_reads / key_read_requests deve ser a mais baixa possível, pelo menos 1:100, 1:1000 é melhor (o valor de status acima pode ser obtido usando SHOW STATUS LIKE 'key_read%'). Ou se você instalou o phpmyadmin, você pode vê-lo através do status de execução do servidor. O autor recomenda usar o phpmyadmin para gerenciar o mysql.

Este servidor está em execução há 20 dias
key_buffer_size – 128 milhões
key_read_requests – 650759289
key_reads - 79112
A proporção é próxima de 1:8.000 e o estado de saúde é muito bom.

Outra forma de estimar key_buffer_size é somar o espaço ocupado pelos índices de cada tabela no banco de dados do seu site. Tome este servidor como exemplo: os índices de várias tabelas maiores somam cerca de 125M. maior.

A partir da versão 4.0.1, o MySQL fornece um mecanismo de buffer de consulta. Usando buffer de consulta, o MySQL armazena a instrução SELECT e os resultados da consulta no buffer. No futuro, para a mesma instrução SELECT (diferencia maiúsculas de minúsculas), os resultados serão lidos diretamente do buffer. De acordo com o manual do usuário do MySQL, o uso do buffer de consulta pode atingir até 238% de eficiência.

Ajustando os parâmetros a seguir, você pode saber se a configuração query_cache_size é razoável.
Inserções Qcache
Acertos do Qcache
Ameixas lowmem do Qcache
Blocos gratuitos do Qcache
Total de blocos do Qcache

Se o valor de Qcache_lowmem_prunes for muito grande, indica que muitas vezes há buffer insuficiente. Ao mesmo tempo, se o valor de Qcache_hits for muito grande, indica que o buffer de consulta é usado com muita frequência. precisa ser aumentado. Se o valor de Qcache_hits for pequeno, isso indica que sua taxa de repetição de consulta é muito alta. Nesse caso, o uso do buffer de consulta afetará a eficiência, portanto, você pode considerar não usar o buffer de consulta. Além disso, adicionar SQL_NO_CACHE à instrução SELECT pode indicar claramente que o buffer de consulta não está sendo usado.
Qcache_free_blocks, se o valor for muito grande, indica que há muitos fragmentos no buffer query_cache_type especifica se deve usar buffer de consulta

Eu configurei:
query_cache_size = 32 milhões
query_cache_type = 1

Obtenha o seguinte valor de status:
Consultas Qcache no cache 12737 indicam o número de itens atualmente armazenados em cache
Inserções Qcache 20649006
Qcache atinge 79060095 Parece que a taxa de consultas repetidas é bastante alta.
Qcache lowmem prunes 617913 Muitas vezes o cache fica muito baixo.
Qcache não armazenado em cache 189896
Memória livre Qcache 18573912 Espaço de cache restante atual
Blocos livres Qcache 5328 Este número parece um pouco grande e fragmentado
Total de blocos do Qcache 30953
Se a memória permitir 32M, você deverá adicionar mais.

table_cache especifica o tamanho do cache da tabela. Sempre que o MySQL acessa uma tabela, se houver espaço no buffer da tabela, a tabela é aberta e colocada nela, permitindo acesso mais rápido ao conteúdo da tabela. Ao verificar os valores de status Open_tables e Opened_tables no horário de pico, você pode decidir se precisa aumentar o valor de table_cache. Se você achar que open_tables é igual a table_cache e open_tables está crescendo, então você precisa aumentar o valor de table_cache (o valor de status acima pode ser obtido usando SHOW STATUS LIKE 'Open%tables'). Observe que table_cache não pode ser definido cegamente com um valor grande. Se definido como um valor muito alto, poderá causar descritores de arquivo insuficientes, resultando em desempenho instável ou falha de conexão.

Para máquinas com 1G de memória, o valor recomendado é 128-256.

Configurações do autor
cache_tabela = 256

Obtenha o seguinte status:
Mesas abertas 256
Mesas abertas 9046
Embora open_tables já seja igual a table_cache, em relação ao tempo de execução do servidor, ele está em execução há 20 dias e o valor de open_tables também é muito baixo. Portanto, aumentar o valor de table_cache deve ser de pouca utilidade. Se o valor acima aparecer após 6 horas de execução, você deve considerar aumentar table_cache.

Se você não precisar registrar logs binários, desative esta função. Observe que, após desativá-la, você não poderá restaurar os dados antes do problema. O log binário contém todas as instruções que atualizam os dados e seus dados. o objetivo é restaurar o banco de dados. Use-o para restaurar os dados ao seu estado final, tanto quanto possível. Além disso, se você fizer replicação síncrona (replicação), também precisará usar o log binário para transferir modificações.

log_bin especifica o arquivo de log. Se nenhum nome de arquivo for fornecido, o MySQL irá gerar o próprio nome de arquivo padrão. O MySQL adicionará automaticamente um índice numérico após o nome do arquivo e gerará novamente um novo arquivo binário sempre que o serviço for iniciado. Além disso, use log-bin-index para especificar o arquivo de índice; use binlog-do-db para especificar o banco de dados para gravação; use binlog-ignore-db para especificar o banco de dados que não será gravado. Nota: binlog-do-db e binlog-ignore-db especificam apenas um banco de dados por vez. A especificação de vários bancos de dados requer diversas instruções. Além disso, o MySQL mudará todos os nomes de banco de dados para letras minúsculas. Você deve usar todos os nomes em letras minúsculas ao especificar o banco de dados, caso contrário não funcionará.

Para desativar esta função, basta adicionar um sinal # na frente dela
#log-bin

Ativar log de consulta lenta (log de consulta lenta)

O log de consultas lentas é útil para rastrear consultas problemáticas. Ele registra todas as consultas que verificaram long_query_time e, se necessário, registra sem usar um índice. Aqui está um exemplo de log de consulta lenta:

Para habilitar logs de consulta lenta, você precisa definir os parâmetros log_slow_queries, long_query_times e log-queries-not-using-indexes.

log_slow_queries especifica arquivos de log Se nenhum nome de arquivo for fornecido, o MySQL irá gerar um nome de arquivo padrão por si só. long_query_times especifica o limite de consulta lenta, o padrão é 10 segundos. log-queries-not-using-indexes é um parâmetro introduzido após 4.1.0, que indica que as consultas que não usam índices são registradas. O autor define long_query_time=10

Configurações do autor:

sort_buffer_size = 1 milhão
max_connections=120
espera_tempo limite = 120
back_log=100
read_buffer_size = 1 milhão
thread_cache=32
interact_timeout=120
thread_concurrency = 4

Descrição do parâmetro:

back_log
O número de conexões que o MySQL precisa ter. Isso funciona quando o thread principal do MySQL recebe muitas solicitações de conexão em um curto período de tempo, e então o thread principal leva algum tempo (embora brevemente) para verificar as conexões e iniciar um novo thread. O valor back_log indica quantas solicitações podem ser armazenadas na pilha em um curto período de tempo antes que o MySQL pare temporariamente de responder a novas solicitações. Somente se você espera muitas conexões em um curto período de tempo é necessário aumentá-lo, ou seja, esse valor é o tamanho da fila de escuta para conexões TCP/IP de entrada. Seu sistema operacional tem seu próprio limite para esse tamanho de fila. A página man para a chamada do sistema listen(2) do Unix deve ter mais detalhes. Verifique a documentação do seu sistema operacional para descobrir o valor máximo desta variável. Tentar definir o back_log acima do limite do seu sistema operacional não terá efeito.
max_connections
O número máximo de conexões simultâneas é 120. Caso ultrapasse esse valor, ele se recuperará automaticamente e os problemas serão resolvidos automaticamente.

thread_cache
Não consegui encontrar nenhuma instrução específica, mas depois de defini-lo para 32, mais de 400 threads foram criados em 20 dias, enquanto milhares de threads foram criados em um dia antes, por isso ainda é útil.

thread_concurrency
#Defina para o número da sua CPU x2, por exemplo, se houver apenas uma CPU, então thread_concurrency=2
#Existem 2 CPUs, então thread_concurrency=4

pular-innodb
#Remover suporte innodb