Aunque mis conocimientos en el campo de los SGDB es limitado a menudo me encuentro con la situación de que muchos pequeños problemas en servidores dedicados vienen por valores incorrectos de la configuración en el servidor de base de datos (concretamente MySQL).
He visto máquinas enteras saturadas por un servidor MySQL mal configurado con apenas una docena de bases de datos y 50.000 impresiones diarias. Pero también sitios con quinientas bases de datos y cien veces más impresiones diarias funcionando sin saturar la máquina.
Algunas soluciones obvias para aligerar la carga de la máquina ya han sido tratadas por aquí pero hasta ahora MySQL no. Vamos a hacerlo pensando en WordPress pero intentando ser un poco generales. Como siempre se aceptará cualquier corrección.
La primera parte del texto está basada en un artículo de IBM que es lectura obligatoria, hay una parte que no menciono sobre buffers y caches que puede ayudar a diagnosticar problemas o mejorar todavía más el asunto :-).
Lo primero: activar la cache de peticiones
Si por defecto activamos WP-Cache para evitar procesar de forma innecesaria el mismo código PHP y las mismas peticiones SQL al enviar una página ¿por qué no hacer lo mismo con las peticiones SQL que no son cacheadas pero sí se repiten?.
Para activar el cache hay que descomentar la directiva
query_cache_size
de /etc/mysql/my.conf
. Podemos
empezar con un valor de 32MB.
Una vez hecho esto, podemos conectarnos al servidor MySQL y mediante la
petición SHOW STATUS LIKE 'qcache%';
ver los datos de la cache.
Los valores son mostrados en bytes por lo que hay que dividirlos por 1024 dos veces para tener la cantidad en megabytes. Los puntos más interesantes que nos muestra la clausula son:
Qcache_free_memory
: la memoria de la cache que nos queda libre.Qcache_inserts
: el valor crece cada vez que una petición es insertada. Al dividir el número por el valor de Qcache_hits obtenemos el ratio de perdidas. Si por ejemplo el resultado nos da 0.175, restamos a 1 ése número y nos da 0.825 o lo que es lo mismo: aproximadamente el 82% de las peticiones están siendo servidas de la cache.Qcache_lowmem_prunes
: el número de veces que la cache se queda sin memoria y tiene que hacer limpieza. Si el número va aumentando toca subir el espacio para la cache.
Comprobar peticiones lentas o sin índices
MySQL dispone de "Slow Query Log" una opción que permite registrar las
peticiones que se consideran lentas. Toca abrir el fichero
/etc/mysql/my.cnf
y mirar las tres siguientes opciones:
[mysqld] ; enable the slow query log, default 10 seconds log-slow-queries ; log queries taking longer than 5 seconds long_query_time = 5 ; log queries that don't use indexes even if they take less than long_query_time ; MySQL 4.1 and newer only log-queries-not-using-indexes
La primera parte es para activarlo, long_query_time = 5
define
que guardará un log con toda petición que dure más de 5 segundos. Para
WordPress yo bajaría ese valor a 2 para ver si algún plugin o algo
genera una petición demasiado lenta.
Por último log-queries-not-using-indexes
sirve para registrar en el
log las peticiones SQL que no usen índices. Cabe mencionar que si bien
se suelen usar muy a menudo a veces el uso de un índice no es necesario
y/o ralentiza en lugar de acelerar por lo que es relativamente normal
encontrarse peticiones que no usan índices.
En ambos casos, la idea es buscar cadenas que no responden a nuestras expectativas (por lentas o porque "pensábamos" que tendría un índice y no es así).
Endurecer los límites
La máximo de un servidor es que no tenga problemas de saturación, por ello hay que endurecer los límites puestos a MySQL para que no sea el culpable de saturar la conexión.
Vamos a ver los tres puntos relacionados más importantes en el fichero my.cnf:
set-variable=max_connections=500 set-variable=wait_timeout=10 max_connect_errors = 100
El número de conexiones máximo es equivalente al MaxClients de Apache,
la idea es permitir como máximo número de conexiones la cantidad que
puedes permitirte. Para ver el número de conexiones máximas hechas
recientemente puedes conectar a MySQL y escribir SHOW STATUS LIKE
'max_used_connections'
.
La segunda línea le dice al servidor MySQL que termine toda conexión que ha estado inactiva durante más de 10 segundos. En webs y aplicaciones LAMP la conexión a la base de datos dura tanto como el servidor web tarda en procesar la petición. A veces, las conexiones por culpa de la carga se quedan a la espera ocupando espacio en la tabla.
Si tienes muchos usuarios interactivos o que usan conexiones persistentes a la base de datos poner este valor bajo es una mala idea.
Para WordPress (y en general) yo intentaría reproducir las peticiones más complejas existentes con carga para saber que es lo máximo a esperar pero creo que el valor de 10 o incluso probando a menos ya es bastante interesante.
La tercera línea es por razones de seguridad. Si un host tiene problemas para conectarse a un servidor y termina abortando la petición muchas veces el host terminará bloqueando su acceso mediante FLUSH HOSTS. Por defecto con 10 fallos es suficiente para causar el cierre. Cambiando el valor a 100 le damos suficiente tiempo al servidor para recuperarse de cualquier problema que pueda tener. Subir aún más el valor no ayudará debido a que si el servidor no puede conectar después de 100 intentos, seguramente no podrá conectar en absoluto.
key_buffer_size
y otras variables
Algunas de las variables de MySQL son muy importantes, podeis darle un buen repaso a un artículo en Database Journal al respecto.
La variable key_buffer_size
es de las más importantes a adaptar a
nuestras necesidades. Cuanto más espacio le demos más índices de las
tablas MyISAM serán guardados en memoria en lugar de ser leídos desde
disco.
Teniendo en cuenta que muchas peticiones usan índices y que la memoria RAM es varias veces más rápida que la del disco duro, la importancia de un óptimo valor para esta variable no puede ser inflavalorada.
En servidores dedicados exclusivamente para tareas de MySQL la regla
aprobada por muchos es apuntar a adjudicar al menos una 1/4 parte de la
memoria RAM pero nunca más de la mitad a la variable
key_buffer_size
. Lo ideal sería que el valor fuera suficiente para
contener todos los índices (el tamaño total de todos los ficheros
.MYI). WordPress usa MyISAM en algunas partes, por lo que es
recomendable mirar de cachear lo máximo posible.
Si esto no es posible, la mejor manera de afinarlo es comparar
key_reads
con key_read_requests
escribiendo en
el servidor MySQL la petición SHOW STATUS LIKE '%key_read%';
.
El segundo es el número de lecturas de peticiones que han hecho uso del
índice mientras que el primero es el total de esas peticiones que han
sido hechas desde disco.
Al menos 100 peticiones deberian ser hechas desde el buffer por cada petición hecha desde disco, preferiblemente muchas más.
El artículo continua (al igual que el anterior de IBM) con la cache de tablas que no vamos a tocar aquí.
¿Alguien dijo WordPress MU?
Existe un hilo de discusión en los foros de WordPress que puede interesar a los usuarios de WordPress MU y en menor medida a los usuarios de WordPress.
Por ejemplo comentan que Wordpress.com tiene la base de datos partida en varias partes, algunos usuarios de WPMU crean un directorio por cada 25.000 blogs y lo distribuyen, dan ejemplos de configuración de MySQL para ordenadores con mucha RAM (4GB), etcétera.
MySQL Perfomance Tuning Primer Script
En day32.com hay un script llamado MySQL Perfomance Tuning Primer Script que toma información de "SHOW STATUS LIKE" y "SHOW VARIABLES LIKE" para producir recomendaciones para adaptar las variables del servidor MySQL. Está pensando para ser usado desde MySQL 3.23 para arriba y produce recomendaciones para los siguientes puntos:
- Slow Query Log.
- Max Connections.
- Worker Threads.
- Key Buffer.
- Query Cache
- Sort Buffer.
- Joins.
- Temp Tables.
- Table (Open & Definition) Cache.
- Table Locking.
- Table Scans (read_buffer).
- Innodb Status.
¿Optimizado para lectura o para escritura?
Nunca lo he probado pero cada base de datos es un mundo y algunas tienen un porcentaje muy alto de lecturas frente a uno muy reducido de escrituras (por ejemplo un portal donde se escriben artículos sin posibilidad de comentarlos).
En Optimizing for Read Perfomance ofrecen un escenario para optimizar las lecturas de la base de datos frente a las escrituras.
Quedan apuntes por mencionar, pero el grueso del asunto aquí está. ¿Qué otras cosas tomarías en consideración?.
Comentarios