Otimizando as configurações do MySQL - o mysqld está ficando sem memória

Question

Estou um pouco confuso sobre quais configurações alterar no meu arquivo my.conf para otimizar para o meu servidor (o servidor mysql fica travando devido ao nosso alto tráfego).

Aqui está o arquivo my.cnf:

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

key_buffer              = 16M
max_allowed_packet      = 16M
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

query_cache_limit       = 1M
query_cache_size        = 16M

E as especificações do servidor são:

CPU Cores   2 cores
RAM (Memory)    1GB
SSD (Disk Space)    20GB

Executando o Ubuntu 12.04 LTS

Aqui estão minhas partições:

NAME   FSTYPE   SIZE MOUNTPOINT LABEL
vda              20G            
├─vda1 ext4     476M /boot      
├─vda2 swap     477M [SWAP]     
└─vda3 ext4    19.1G / 

Qualquer ajuda seria muito apreciada.

Olhando os logs de erro, posso ver que o mysqld estava sem memória:

Feb 17 11:02:06 111488 kernel: [8276839.559141] Out of memory: Kill process 20719 (mysqld) score 75 or sacrifice child
Feb 17 11:02:06 111488 kernel: [8276839.559209] Killed process 20719 (mysqld) total-vm:1347424kB, anon-rss:30524kB, file-rss:0kB

    

Answer 1

Resposta geral do wiki do guia do servidor :

  

MySQL Tuner

     

O MySQL Tuner é uma ferramenta útil que se conectará a uma instância MySQL em execução e oferecerá sugestões de como ela pode ser melhor configurada para sua carga de trabalho. Quanto mais tempo o servidor estiver rodando, melhor o conselho que o mysqltuner pode fornecer. Em um ambiente de produção, considere aguardar pelo menos 24 horas antes de executar a ferramenta. Você pode obter o mysqltuner dos repositórios do Ubuntu:

sudo apt-get install mysqltuner

     

Então, uma vez instalado, execute-o:

 mysqltuner

     

e aguarde seu relatório final. A seção superior fornece informações gerais sobre o servidor de banco de dados e a seção inferior fornece sugestões de ajuste para alterar em seu arquivo my.cnf. A maioria destes pode ser alterada ao vivo no servidor sem reiniciar, olhe através da documentação oficial do MySQL (link na seção Recursos) para as variáveis relevantes mudarem na produção. O seguinte é parte de um relatório de exemplo de um banco de dados de produção que mostra que pode haver algum benefício em aumentar a quantidade de cache de consultas:

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 1.4G)
    query_cache_size (> 32M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 22G)

  

Um comentário final sobre o ajuste de bancos de dados: Embora possamos dizer amplamente que certas configurações são as melhores, o desempenho pode variar de aplicativo para aplicativo. Por exemplo, o que funciona melhor para o Wordpress pode não ser o melhor para o Drupal, Joomla ou aplicativos proprietários. O desempenho depende dos tipos de consultas, do uso de índices, da eficiência do design do banco de dados e assim por diante. Talvez seja útil passar algum tempo pesquisando dicas de ajuste de banco de dados com base nos aplicativos para os quais você está usando. Depois que você passar de um certo ponto, qualquer ajuste feito resultará apenas em pequenas melhorias, e será melhor melhorar o aplicativo ou ampliar o ambiente de banco de dados usando hardware mais potente ou adicionando servidores escravos.

• Assista à seção de métricas de desempenho do relatório mysqltuner. Mantenha o máximo de memória possível abaixo de 50%.
• tmp_table_size , max_heap_table_size variable : mantenha esses valores iguais e altos.
• join_buffer_size , aumenta em pequenas quantidades; será multiplicado por max_connections .
• %código%. Faça isso alto.

Answer 2

outra possível correção é usar o seguinte na seção [mysqld] da sua configuração /etc/mysql/my.cnf.

performance_schema = off

Fonte: link