Cómo optimizar MySQL y pensando en WordPress

Autor: Armonth | El miércoles 01 de agosto del 2007 @ 19:53.

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