Bloqueio periódico do MySQL quando o Wordpress está sob carga pesada

Question

(não tinha certeza se era uma pergunta do ServerFault ou do StackOverflow, então, por favor, perdoe postagens cruzadas)

Eu tenho um banco de dados MySQL 5.1.61 rodando atrás de dois servidores web Apache com balanceamento de carga que hospedam sites Wordpress bastante ocupados (100K únicos por dia). Estou fazendo cache com o Cloudflare, W3TC e Varnish. Na maioria das vezes, o servidor de banco de dados lida com o tráfego muito bem. "show complete processlist" mostra 20-40 consultas a qualquer momento, com a maioria delas no estado de suspensão.

Periodicamente, porém (particularmente quando há picos de tráfego ou quando um grande número de comentários são apagados), o MySQL pára de responder. Eu vou encontrar 1000-1500 consultas em execução, muitos "envio de dados", etc. Nenhuma consulta particular parece estar sobrecarregando o banco de dados (são todas as consultas padrão do Wordpress), mas parece que o volume simultâneo de solicitações causa todas as consultas para desligar. Eu sou (geralmente) ainda capaz de logar, executar "lista de processos completa" ou outras consultas, mas as mais de 1.000 consultas já estão lá. A única solução parece ser a de reiniciar o mysql (às vezes violentamente via kill -9 se eu não conseguir me conectar).

Todas as tabelas são innodb, o servidor tem 8 núcleos, 24GB de RAM, muito espaço em disco e o seguinte é my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
skip-external-locking
skip-name-resolve
user=mysql
query_cache_type=1
query_cache_limit=16M
wait_timeout = 300
query_cache_size=128M
key_buffer_size=400M
thread_cache_size=50
table_cache=8192
skip-name-resolve
max_heap_table_size = 256M
tmp_table_size = 256M
innodb_file_per_table
innodb_buffer_pool_size = 5G
innodb_log_file_size=1G
#innodb_commit_concurrency = 32
#innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
thread_concurrency = 8
join_buffer_size = 256k
innodb_log_file_size = 256M
#innodb_concurrency_tickets = 220
thread_stack     = 256K
max_allowed_packet=512M
max_connections=2500
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

#2012-11-03
#attempting a ram disk for tmp tables
tmpdir = /db/tmpfs01

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Alguma sugestão de como eu posso potencialmente melhorar a configuração do MySQL, ou outras etapas para manter a estabilidade do banco de dados sob carga pesada?

    

Answer 1

Eu vejo três (3) brindes mortos por falta de desempenho

Versão do MySQL

Você está usando o MySQL 5.1.61. A maioria das pessoas não instala o Plugin InnoDB que vem com o MySQL 5.1 a partir do MySQL 5.1.38. O InnoDB Plugin tem novos aprimoramentos para permitir que o InnoDB realize o engajamento multicore.

Recomendação: Instale o Plugin InnoDB ou o Upgrade para o MySQL 5.5

Configurações do InnoDB

Isto é o que você tem (a partir da pergunta)

innodb_file_per_table
innodb_buffer_pool_size = 5G
innodb_log_file_size=1G
#innodb_commit_concurrency = 32
#innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
thread_concurrency = 8
innodb_log_file_size = 256M

Se você tiver o InnoDB Plugin instalado ou atualizado para o MySQL 5.5, você deve sintonizar o InnoDB para ativar os aprimoramentos multicore.

Aqui estão minhas recomendações:

innodb_thread_concurrency = 0 (default in MySQL 5.5)
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000

Por favor, leia minha postagem DBA StackExchange de Mar 16, 2012 que discute isso em comprimento.

Cache

O seu innodb_buffer_pool_size pode ser muito pequeno. Tente aumentar. Na verdade, escrevi uma postagem sobre Apr 22, 2011 no WordPress StackExchange no dimensionamento do buffer pool .