Tengo una base de datos MySQL 5.1.61 que se ejecuta detrás de dos servidores web Apache con equilibrio de carga que alojan sitios de WordPress bastante ocupados (100K únicos por día). Estoy almacenando en caché con Cloudflare, W3TC y Varnish. La mayoría de las veces, el servidor de la base de datos maneja muy bien el tráfico. “Mostrar lista de procesos completa” muestra de 20 a 40 consultas en un momento dado, la mayoría en estado de suspensión.
Sin embargo, periódicamente (particularmente cuando aumenta el tráfico o cuando se borra una gran cantidad de comentarios), MySQL deja de responder. Encontraré 1000-1500 consultas ejecutándose, muchos “enviando datos”, etc. Ninguna consulta en particular parece estar sobrecargando la base de datos (todas son consultas estándar de WordPress), pero parece que el volumen simultáneo de solicitudes provoca todas las consultas colgar. (Por lo general) todavía puedo iniciar sesión, ejecutar “mostrar lista de procesos completa” u otras consultas, pero las más de 1000 consultas que ya están allí simplemente se sientan. La única solución parece ser reiniciar mysql (a veces violentamente mediante kill -9 si no puedo conectarme).
Todas las tablas son innodb, el servidor tiene 8 núcleos, 24 GB de RAM, mucho espacio en disco y el siguiente es mi archivo 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
¿Alguna sugerencia sobre cómo puedo mejorar potencialmente la configuración de MySQL u otros pasos para mantener la estabilidad de la base de datos bajo una carga pesada?
Como se ha dicho, piense fuera de la caja y investigue por qué estas consultas son lentas o se cuelgan de alguna manera. Un viejo pero una buena fuente de problemas incluso para (supuestamente) ingenieros de sistemas inteligentes es el equilibrio de carga que causa problemas en el servidor web o en las sesiones de la base de datos. Con todo el almacenamiento en caché y el equilibrio de carga, ¿está seguro de que todo se conecta siempre de un extremo a otro según lo previsto?
Estoy de acuerdo con alditis y Bjoern
Soy bastante novato con mysql, pero ejecutar mysqltuner puede revelar algunas optimizaciones de configuración basadas en consultas recientes de la base de datos. https://github.com/rackerhacker/MySQLTuner-perl
Y si es posible, almacene los archivos de base de datos en una partición físicamente separada del sistema operativo, el sistema operativo puede consumir IO, lo que ralentiza la base de datos. Como con el problema de logrotate de Bjoern.
Primero eche un vistazo al comportamiento básico del sistema en el momento de los problemas. Use tanto vmstat como iostat si puede encontrar algún problema. Vea si el sistema comienza a intercambiar (columnas pi,po en vmstat) y si se están produciendo muchos IO. Este es el primer paso para depurar su problema.
Otra fuente de información útil es SHOW INNODB STATUS. Ver por http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ sobre cómo interpretar la salida.
Puede ser que, en un momento determinado, sus escrituras estén matando el rendimiento de lectura porque vacían el caché de consultas.
Siempre hay espacio para mejoras en la configuración de MySQL, pero lo primero que me viene a la mente es el almacenamiento en caché. ¿Ya tienes algún método de almacenamiento en caché en uso? Para WordPress, recomendaría ocaoimh.ie/wp-super-cache. Esto reduce drásticamente la carga de su base de datos.
– Bjoern
30 de noviembre de 2012 a las 6:43
Bjoerm: estoy usando barniz para almacenar en caché para Apache y W3TC para almacenar en caché WordPress. El almacenamiento en caché ha ayudado enormemente (particularmente Varnish), pero todavía recibo estos “abrazos mortales” todos los días o dos.
– EvilPlutón
30 de noviembre de 2012 a las 6:45
Puede intentar aumentar el límite de caché de consultas a 32 MB o incluso a 64 MB, si cree que es la base de datos la responsable de este problema de carga. Sin embargo, trata de pensar fuera de la caja. Tal vez otro proceso del servidor, probablemente ni siquiera directamente relacionado con la base de datos (un trabajo de copia de seguridad, un análisis de virus, lo que sea), está consumiendo algunos de sus recursos y ralentizando el rendimiento de su base de datos. Tuve una situación similar no hace mucho tiempo, y resultó ser una prioridad alta
logrotatefue la fuente de la misma.– Bjoern
30 de noviembre de 2012 a las 6:52
MySQL 5.5 incluye una serie de importantes mejoras de rendimiento. ¿Ha intentado probar con esa versión para ver si podría ayudar con esta situación? También debe verificar la errata en versiones posteriores de 5.1 para ver si se trata de un error que se solucionó.
– tadman
30 de noviembre de 2012 a las 7:10
¿Hay alguna razón por la que tienes la
innodb_buffer_pool_sizeconfigurado para usar solo 5GB de ram? Si MySQL es lo único en esta máquina (que es de esperar que sea el caso), debe configurarlo para que sea 70-80% de RAM para utilizar completamente su hardware. Para obtener más ajustes, consulte: mysqlperformanceblog.com/2007/11/01/…– Brian Calle
30 de noviembre de 2012 a las 7:22