Spikes de Tempo de Execução do MySQL

Question

Estou tendo problemas com o MySQL hoje de repente.

Detalhes:

• SO: versão do CentOS 5.7
• Tipo de servidor: contêiner virtuozzo Parallels em execução no pacote DV 4.0 de mediatempo
• Uso total de memória médio: < 500mb
• Uso total de memória permitido: 1gb (parte do pool compartilhado somente para emergência, os usuários só têm garantia de 500mb)
• Processador: > 1ghz
• Tamanhos principais do banco de dados com mais uso: 275mb & 107mb
• pilha do servidor: nginx 1.0.10, mysql 5.1.54, php 5.3.8 com php-fpm
• innodb_buffer_pool_size = 100 milhões
• php-fpm max crianças: 5
• Webapps: sites personalizados baseados em php, magento & drupal
• o tempo limite da consulta lenta está definido como 1 segundo

Etapas concluídas para o diagnóstico:

1. Ainda não é possível reiniciar o contêiner - tentarei mais tarde hoje à noite quando nosso tráfego doméstico tiver caído
2. Habilitado mysql e php-fpm slowlog.
3. Funções encontradas que fizeram consultas de banco de dados no php-fpm slowlog estavam ocupando 1s para serem concluídas em alguns momentos
4. Encontrou algumas consultas simples no mysql slowlog, levando mais de 1s para concluir, o que deve levar menos de 1s.
5. Mais interessante - o tempo de execução parece aumentar às vezes. Uma consulta levará 0,2s algumas vezes, e uma vez levará 8 segundos para executar a mesma consulta. Estes resultados foram verificados executando consultas SQL brutas através da linha de comando do mysql.
6. O topo não revela nada muito interessante
7. Apenas uma coisa relacionada a recursos que eu posso ver é uma média de carga muito maior que o normal
8. Até hoje, o mysql está bem, não houve grandes alterações no banco de dados desde ontem.
9. Às vezes, as coisas estão ruins, estou vendo erros de gateway ruins após os 60 anos de execução.
10. Innodb está fazendo em média 300-1400 leituras / seg.
11. O Mysql está a fazer 3-10 consultas / seg
12. contagem de consultas lenta em 2 horas de tempo de atividade é de 171 (com tempo limite lento em 1 segundo)
13. Tentei reiniciar o mysql, nginx, php-fpm várias vezes

Por exemplo:

UPDATE  'catalogsearch_query' SET 'query_text' = 'EW 90', 'num_results' = '7532', 'popularity' = '99180', 'redirect' = NULL, 'synonym_for' = NULL, 'store_id' = '1', 'display_in_terms' = '1', 'is_active' = '1', 'is_processed' = '1', 'updated_at' =  '2012-05-08 21:38:31' WHERE (query_id='31');

Essa consulta levou 17 segundos para concluir uma vez, o restante do tempo em torno de 0,079 seg. Mas varia, às vezes 1seg, às vezes 0,004 seg. Isso está executando a mesma consulta, repetidamente, com alguns segundos de intervalo entre cada um.

A maioria das tabelas é innodb e, às vezes, notei que o tempo de bloqueio levou 90% do tempo de execução da consulta, mas na maioria das vezes o tempo de bloqueio é insignificante.

Alguma ideia do que está acontecendo aqui?

    

Answer 1

Soa quase definitivamente como um problema periférico que influencia o que está acontecendo e bastante típico de executar o Magento em um ambiente restrito e contido como um VPS.

Se sua configuração já estava funcionando bem e você não alterou nenhum código ou instalou nenhuma extensão - então isso será uma influência externa, provavelmente um aumento no tráfego - mas com um VPS, é mais do que provável que eu tenha aumentado / O atividade.

Soa exatamente como espera de E / S - simplesmente instalar uma ferramenta gráfica como Munin provaria conclusivamente isso (mostrando uma correlação em consultas lentas do MySQL com atividade de E / S / espera).

Vou me referir a alguns outros posts no mesmo vão - como rodar o Magento em um VPS sempre produz um comportamento similar.

1. link
2. link
3. link

Answer 2

Os dois problemas mais prováveis são:

1) sua consulta está sendo bloqueada por outra - verifique no seu log de consultas lentas o que estava em execução quando você iniciou a atualização

2) sua VM não está sendo agendada pela máquina host. Configurar um script de watchdog para gravar um heartbeat em um arquivo de log

Pode haver outras causas, mas a menos que você possa eliminar as 2 acima, não há muito sentido em discuti-las.

Em termos de resolver os problemas, o primeiro é apenas uma questão de ajuste de consulta, para o último, você precisará falar com quem gerencia a máquina host.

    

Answer 3

O que faz:

SHOW CREATE TABLE catalogsearch_query\G
SHOW INNODB STATUS;
SELECT * FROM information_schema.PROCESSLIST WHERE State='Locked' ORDER BY Time;

parece?

Eu suspeito que você esteja sendo atropelado pelo problema de 'abrir mesas / mesas de fechamento'. Eu não iria reiniciar o seu banco de dados muito, ele vai precisar de tempo para se aquecer, especialmente quando se faz muitas atualizações.

Para completar, adoraria ver isso também:

SHOW STATUS LIKE '%cache%';
SHOW STATUS LIKE '%table%';

E tudo isso em um momento de dificuldade, eu aposto que quando você está em apuros e faz um "flush tables"; , vai demorar séculos.

Alguns parâmetros de configuração importantes que aceleram o processamento:

innodb_log_buffer_size  = 16M
innodb_file_per_table   = 1
innodb_open_files = 16000
innodb_io_capacity   = 400
innodb_flush_log_at_trx_commit = 2
innodb_flush_method  = O_DIRECT
innodb_thread_concurrency=8
innodb_lock_wait_timeout = 90

Lembre-se de que isso vem de um MariaDB, assim como de esteróides (XtraDB). Especialmente, a configuração per_table é drástica, mas só afetará novas tabelas, você precisará recriar tabelas de problemas se desejar usá-las em seu próprio innodb. Isso junto com o método flush O_DIRECT e trx_commit = 2 (leia sobre isso primeiro!) Acelerará as coisas severamente, especialmente com grandes atualizações / inserções.

Espero que isso ajude.