Como ajustar o servidor MySQL quando o pico de uso é esperado

Question

Estou tendo grandes problemas tentando ajustar meu servidor MySQL. Eu estava esperando muitas pessoas no meu site depois de comprar anúncios de TV entre 17h e 18h, tenho mais ou menos 300 pessoas no site durante esse período. Eu tentei um monte de tuning no meu.cnf, mas toda noite é pior e pior .. Eu realmente apreciaria alguma ajuda para descobrir qual é o meu problema lá .. Minha infra-estrutura atual é a seguinte:

Um servidor para o meu site

• apache2 instalado nele
• 6c / 12t - CPU Intel (R) Xeon (R) E5-1650 v2 a 3,50GHz
• 64 GB de RAM - DDR3 ECC 1600 MHz
• Disco - HardRaid + 3x480 Go SSD

Um servidor para meu banco de dados

• Mysql 5.5.38 nele
• 4c / 8t - CPU Intel (R) Xeon (R) E5-1620 v2 a 3.70GHz
• 32 GB de RAM - DDR3 ECC 1600 MHz
• Disco - SoftRaid 3x160 Go SSD

Aqui está minha configuração atual my.cnf:

innodb_file_per_table           = 1
innodb_buffer_pool_instances    = 13
innodb_buffer_pool_size         = 13375M
innodb_open_files               = 300
innodb_thread_concurrency       = 0
#innodb_read_io_threads          = 8
#innodb_write_io_threads         = 8
#innodb_flush_method             = O_DIRECT
#join_buffer_size                               = 30M
#sort_buffer_size                               = 10M
#read_buffer_size                               = 10M

wait_timeout                    = 180
interactive_timeout             = 180

max_connections                 = 250
max_heap_table_size             = 256M
tmp_table_size                  = 256M
table_cache                     = 20000
table_definition_cache          = 20000
#table_open_cache               = 20000
query_cache_type                = 0

E resultados do mysqltuner:

 >>  MySQLTuner 1.6.9 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Performing tests on 127.0.0.1:3306
[OK] Logged in using credentials passed on the command line

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.38-0+wheezy1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 32M (Tables: 126)
[--] Data in InnoDB tables: 10G (Tables: 1503)
[!!] Total fragmented tables: 359

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1m 59s (82K q [695.832 qps], 335 conn, TX: 538M, RX: 34M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 13.8G global + 2.7M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 13.9G (44.07% of installed RAM)
[OK] Maximum possible memory usage: 14.6G (46.49% of installed RAM)
[OK] Slow queries: 0% (1/82K)
[OK] Highest usage of available connections: 3% (11/300)
[OK] Aborted connections: 0.60%  (2/335)
[OK] Query cache is disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts)
[!!] Joins performed without indexes: 131
[OK] Temporary tables created on disk: 5% (927 on disk / 17K total)
[OK] Thread cache hit rate: 96% (11 created / 335 connections)
[OK] Table cache hit rate: 25% (1K open / 6K opened)
[OK] Open file limit used: 0% (301/40K)
[OK] Table locks acquired immediately: 100% (221K immediate / 221K locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (763K used / 4M cache)
[OK] Key buffer size / total MyISAM indexes: 4.0M/10.6M
[OK] Read Key buffer hit rate: 100.0% (562K cached / 0 reads)
[OK] Write Key buffer hit rate: 100.0% (14K cached / 0 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 13.1G/10.7G
[OK] InnoDB buffer pool instances: 13
[!!] InnoDB Used buffer: 5.30% (45409 used/ 855985 total)
[OK] InnoDB Read buffer efficiency: 99.95% (85810161 hits/ 85850600 total)
[!!] InnoDB Write Log efficiency: 61.42% (519 hits/ 845 total)
[OK] InnoDB log waits: 0.00% (0 waits / 326 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)

• A carga da CPU é muito baixa (entre 1 e 4) quando as pessoas estão no site desde alguns segundos. O pior é quando muitas pessoas estão chegando ao mesmo tempo, a carga da CPU vai para 40 ou 50, isso é um problema e eu não entendo qual parâmetro eu poderia ajustar para evitar isso.

• RAM: Eu acho que a RAM é suficiente aqui. O servidor tem 32 GB que nem são usados mais quando há mais pessoas no site. Ele fica em 15-20% e não se move durante o dia .. Eu não entendo porque a RAM não se move, meu objetivo correto seria colocar o máximo de minhas consultas na memória para evitar bater no disco.

Durante os horários de pico, aqui está o free -m :

             total       used       free     shared    buffers     cached
Mem:         32202      25261       6940          0        917      18794
-/+ buffers/cache:       5549      26652
Swap:         1532         21       1511

Estou usando o Prestashop para o meu site, e tenho tabelas que são muito grandes ... vou arquivar e truncar uma grande parte dele neste final de semana. Por exemplo:

SELECT COUNT(*) FROM ps_connections; => 1 330 373
SELECT COUNT(*) FROM ps_guest; => 6 970 248

Se você tiver algum conselho, seria ótimo! obrigado Julien

NB: Desculpe pelo meu mau inglês

    

Answer 1

Embora uma análise completa seja uma tarefa complexa e difícil, posso sugerir algumas áreas a partir das informações que você forneceu e dos avisos no sintonizador.

1) Você tem 32 GB de RAM, mas está usando apenas 14 GB para o MySQL em uma máquina dedicada. Por que não dar mais? É sugerido que 75% da RAM deve ser usada pelo MySQL em um host dedicado (innodb_buffer_pool_size), mas você deve verificar top, vmstat ou qualquer monitoramento de longo prazo que você tenha primeiro para garantir que há headroom para outros serviços ou eventos ocasionais como backups. Nota O Linux sempre se parece com o "esgotamento da RAM" porque ele usa dinamicamente qualquer memória RAM extra para buffers e cache - como mostra o exemplo.

2) innodb_flush_method é um fator de grande desempenho, sua configuração é comentada, por isso não posso saber o valor real (padrão). Verifique as variáveis do servidor para ver o que é. Mas, mudar isso tem um grande impacto na sua integridade no caso de uma interrupção.

3) Você tem muitas tabelas fragmentadas. Isso pode levar à perda de desempenho - se eles forem altamente fragmentados. Existem muitos outros posts [1] que discutem como encontrar tabelas fragmentadas, e se a desfragmentação é necessária, mas novamente é um pouco complexo com diferentes mecanismos de armazenamento e tabela-por-arquivo, etc. Para desfragmentá-los, você pode usar otimizar ou alterar para reconstruir a tabela (é claro que eles bloquearão as tabelas, portanto, é necessário garantir que seja rápido, praticando ou organizando uma interrupção).

1 [ Como encontrar e corrigir tabelas MySQL fragmentadas

4) "Junções realizadas sem índices" - este poderia ser o seu maior problema, embora o 131 não pareça muito alto.

Qualquer consulta que não / não pode usar um índice deve verificar cada registro (varredura de tabela) que é lento para tabelas maiores. Se você não tiver escrito as consultas por si mesmo (por exemplo, o uso desse produto Pretashop), talvez não consiga trabalhar nesse aspecto.

No entanto, para resolver isso, você precisa encontrar as consultas, analisá-las e, em seguida, adicionar índices de acordo. Para encontrar as consultas eu começaria no log de consultas lentas: você não tem muitas consultas lentas, então eu diria que o limite lento está alto demais? Então, se você puder diminuir isso, comece a escolher as consultas lentas e analisá-las. Usando o comando EXPLAIN, você deve conseguir ver como os JOINS estão faltando índices (embora o entendimento de EXPLAIN seja outro tópico inteiro). A adição de índices é simples, mas também tem seus prós e contras (por exemplo, inserções podem ficar mais lentas, muitos índices podem afetar adversamente o desempenho), além de o processo de indexação bloquear a tabela por um tempo.

Também há problemas nas métricas de buffer, mas isso está fora do meu conhecimento (isso é o que me levou a essa pergunta - eu tenho o mesmo problema de eficiência de log de gravação que ainda não entendi).