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