MySQL: Como faço para baixar o meu “Máximo uso de memória possível”?

Question

Recentemente, tenho tido problemas com a surra como resultado da falta de memória. (Meu VPS tem um total de 256M)

Estou tentando ajustar o MySQL usando mysqltuner.pl e obter os seguintes resultados:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-3ubuntu5.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 114M (Tables: 454)
[!!] Total fragmented tables: 34

-------- Performance Metrics -------------------------------------------------
[--] Up for: 40s (570 q [14.250 qps], 23 conn, TX: 154K, RX: 23K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 338.0M global + 2.7M per thread (20 max threads)
[!!] Maximum possible memory usage: 392.9M (153% of installed RAM)
[OK] Slow queries: 0% (5/570)
[OK] Highest usage of available connections: 15% (3/20)
[!!] Key buffer size / total MyISAM indexes: 8.0M/9.4M
[!!] Key buffer hit rate: 57.1% (7 cached / 3 reads)
[OK] Query cache efficiency: 21.9% (7 cached / 32 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 32 total)
[OK] Thread cache hit rate: 86% (3 created / 23 connections)
[OK] Table cache hit rate: 26% (128 open / 484 opened)
[OK] Open file limit used: 25% (259/1K)
[OK] Table locks acquired immediately: 100% (492 immediate / 492 locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    key_buffer_size (> 9.4M)

Mas estou um pouco confuso sobre como diminuir o uso de memória? Parece ser baseado em key_buffer e max_connections, mas deve haver algo mais envolvido também?

my.cnf:

key_buffer              = 8M
max_allowed_packet      = 12M
thread_stack            = 128K
thread_cache_size       = 8
max_connections         = 20
table_cache             = 128
tmp_table_size          = 256M
max_heap_table_size     = 256M
join_buffer_size        = 256K
query_cache_limit       = 8M
query_cache_size        = 64M

Eu tenho tentado ler artigos de tuning do MySQL, mas eles parecem voltados para pessoas que já sabem o que estão fazendo! Qualquer ajuda seria apreciada. Obrigado!

    

Answer 1

Você tem um servidor com 256M, mas não pode usar tudo isso - lembre-se de que há alguma sobrecarga no SO. Acrescente a isso com o fato de que você está cometendo demais, como outras pessoas mencionaram, e você definitivamente vai se debater aqui. 256M é suficiente apenas para um pequeno DB, 20 conexões é muito com o que você configurou.

1) reduza suas conexões máximas para 4 (você está usando 3 de 20)

2) otimize seu cache de consulta melhor; O 8M é realmente grande, e o total de 64M é muito baseado em seus hits / ameixas; tente um combo de 4/32 e veja como é. Realmente acho que um combo de 2/24 funcionaria para você.

3) você não tem nenhum tipo de tabela temporária, por que o verbo max_heap_table_size está lá? Comente isso, use os padrões

4) você tem 128 mesas? Tente cortar essa table_cache na metade para 64 ou 48

5) reduza thread_cache_size para 4

6) otimize essas tabelas para reduzir a fragmentação

Essas são algumas coisas para começar. Parece que você jogou um monte de números em uma configuração sem nenhum perfil para saber o que precisava e criou uma bagunça; se tudo isso falhar, volte para os padrões e livre-se de suas configurações personalizadas e comece novamente usando alguns guias de ajuste de desempenho que você pode encontrar no Google. Obtenha a saída de SHOW VARIABLES e SHOW STATUS, encontre qualquer um dos guias de ajuste bajillion e insira seus números reais e reais em suas equações e isso lhe dirá os números exatos que você precisa colocar em seu arquivo de configuração. / p>     

Answer 2

Eu não sou um guru do MySQL e não consigo diagnosticar o problema com essas informações, mas tentei pesquisar a fórmula no código-fonte. Aqui está:

server_buffers + total_per_thread_buffers * max_connections

Onde:

server_buffers = key_buffer_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size

e:

total_per_thread_buffers = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size

Agora, você precisa verificar cada um desses valores e descobrir qual deles é responsável por esse grande número. E não confie nesse script sem reservas - tentei executá-lo em um dos meus servidores de banco de dados e calculei que a memória máxima é 140% da quantidade total de memória física, mas o sistema está em execução há anos sem problemas de estabilidade.

Boa sorte!

    

Answer 3

Se bem me lembro, o MySQL Tuner usa a seguinte fórmula para estimar o uso máximo:

read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size

Tenha em mente que isso não é 100% correto, e é de fato apenas uma estimativa, já que certas configurações no MySQL não têm limite definido.

Você pode começar a reduzir algumas das configurações do seu arquivo de configuração e executar o sintonizador novamente, mas aconselharia obter ajuda de um especialista se você não tiver tempo para perder a alteração my.cnf, reiniciá-lo e executar o afinador.

    

Answer 4

O uso do software mysqlcalculator.com pode economizar muitas horas.