Consultas MySQL aleatoriamente lentas

Question

Eu sei que esse tipo de pergunta acontece com frequência. Mas eu fiz muita pesquisa, tentei muitas configurações diferentes, mas ainda tenho o mesmo problema: consultas que geralmente são muito rápidas podem levar de 3 a 5 segundos aparentemente de forma aleatória.

O servidor é um i7-3770 (8 núcleos) com 32 GB de RAM. O uso da CPU é de aproximadamente 50% inativo, não pico de CPU. Nenhuma troca usada, a memória livre é de cerca de 10GB em média. Eu executo o mysql 5.5.32 no CentOS 6.

9GB de RAM foram alocados para o MySQL, ele usa cerca de 2GB. Todos os dados devem caber na memória (600MB de dados, 700MB de índice).

Número de consultas por segundo em média (sem pico real):

• 1,5 SELECIONE
• 0.2 ATUALIZAÇÃO
• 0,05 INSERT

Aqui está um exemplo de consulta que leva apenas alguns ms, mas às vezes mais de 3s:

# Query_time: 4.337884  Lock_time: 0.050146 Rows_sent: 1  Rows_examined: 1
SELECT me.id, me.url, me.filename, me.instance_id, me.virtual_id, me.status, me.user_id, me.time_added, me.time_finished, me.priority, me.size, me.delay, me.flash_delay, me.tries, me.details, me.json_file, me.html, me.shots, me.shot_interval, me.screen_width, me.screen_height FROM Screenshots me WHERE ( me.id = '5992705' ); 

id é uma chave primária.

Embora eu tenha mais consultas SELECT do que INSERT, tenho mais INSERT lento que SELECT

O que eu tentei e testei:

• Certifique-se de que todos os índices necessários estejam lá, mas nenhum redundante e nenhum não utilizado
• sem pico da CPU no momento, sem pico de I / o, sem troca
• 2ª instância do MySQL como escravo, a maioria das consultas SELECT é feita no escravo
• remove e TEXT e tipo de dados equivalente
• sintonize my.cnf

Tuning my.cnf ajudou muito. Eu tentei com cache de consulta ativado e desativado, não muita diferença.

Usar um escravo para o SELECT tornou as coisas realmente piores: eu tinha menos consultas lentas no mestre, mas elas podiam ir até 12s!

Aqui está meu my.cf atual (com cache de consulta, neste caso):

tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 1
query_cache_size               = 1M
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 4096

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_log_buffer_size         = 8M
innodb_thread_concurrency      = 8
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 9G

max_connections=1000
transaction-isolation          = READ-UNCOMMITTED 
innodb_locks_unsafe_for_binlog = 1
innodb_io_capacity             = 1000
innodb_change_buffering        = inserts
innodb_fast_shutdown           = 0

key_buffer_size                = 2G

Estou sem ideias. Não consegui encontrar nenhum padrão (frequência, intervalo, etc.) que explicasse essas consultas lentas.

    

Answer 1

Qual é a configuração do seu disco? Você não menciona isso. Soa como o seu IO ligado.

innodb_io_capacity - deixe isso como padrão, não há razão para alterá-lo, a menos que você tenha um bom motivo ou benchmarks para provar que suas configurações estão corretas.

Eu suspeito que você tenha esse conjunto muito alto. Os threads de gravação do InnoDB podem lançar mais dados nos discos a cada segundo do que eles podem manipular, resultando em enfileiramento de E / S.