Cómo optimizar MySQL y pensando en WordPress
Publicado el Miércoles 01 de Agosto del 2007 @ 19:53 por Armonth.
- 14
- 5
Comentar
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?.
14 Comentarios
-
alex:
Genial entrada, útil 100%
-
The-RockeR:
Ésto solamente funciona si tienes un servidor dedicado ¿no? Porque lo de modificar el archivo /etc/mysql/my.conf en un servidor compartido de Dreamhost como que no lo veo yo muy claro…
-
Andrew Mitnik:
Me parece muy buen artículo. Estoy poniéndolo en práctica en una organización con servidor Linux (Ubuntu 6) para sentir el cambio. No prometo poner los resultados, pero si hay quejas, sabrás que no funcionó ^-^.
A The-RockeR: pues claro, sería imposible hacer cambios en un shared, de la marca que sea. Se supone que los shared vienen ya configurados y los optimizan sus administradores, aunque no siempre sea así. Encontré unos admins que no agregan a los usuarios al grupo de Apache y luego los usuarios no pueden borrar archivos por FTP. Con esto te puedes dar una idea.
-
Troy:
Si, realmente interesante… Me voy aficionando a tu blog… aunque me fusiles al Buhonejo ;-)
-
outime:
@The-RockeR: Lógicamente no se puede. Hay ciertos ajustes, como por ejemplo, de PHP, que sí se pueden realizar (hasta cierto punto) colocando un php.ini en la localización adecuada. Tendrás que disponer de tu propia máquina para esos menesteres.
¿Qué decir? Simplemente genial la entrada ;-)
Un saludo
-
InKiLiNo:
Tremendo el post, eres mi heroe :P
-
Victor:
Sí señor.
Muchas gracias :)
-
nacho:
Hola, me gustaría saber si existe algún tutorial/plugin/explicación para: eliminar las entradas en blanco que dejamos muchas veces los que utilizamos wordpress en las bases de datos mysql.
No sé si me he explicado. Cuando uso wordpress y creo una entrada después de publicarla me sale para escribir otra (o doy a crear nueva entrada sin querer) y no escribo nada (o lo cancelo) y esa entrada se queda grabada en la base de datos y cuenta como entrada, aunque no haya nada escrito. ¿Cómo puedo borrar esas entradas?
Por favor, a ver si me puedes echar una mano.
-
Armonth:
Nacho, las entradas si no las guardas como borrador no se graban lo que sí puede pasar es lo siguiente:
Se graba una entrada/borrador y luego se borra definitivamente, en la BBDD queda el ID del post. Eso no se puede tocar ya que aumenta cada vez sólo (auto_increment en MySQL), o mejor dicho: en teoría se puede pero al hacerlo se reorganizan todos los ID y a lo mejor una entrada con ID 5 pasa a ser ID 2 y la base de datos queda incongruente (por ejemplo las etiquetas del ID 2 salen en el ID 10).
En la práctica el único “problema” es que una entrada puede tener ID 20 y la siguiente 23. Mira si no los comentarios: éste debe ser el 62.000 (comment_ID 62.000 y pico) debido a que, aunque hay 9000 reales y contabilizados se han insertado 62.000 comentarios (contando los spam y borrados).
-
j0an:
postaso!
eres mi idolo, como dijeron mas arriba, me viene de 10 este post! :)
-
Pablo:
Gracias héctor otra vez. Ahora que tengo un servidor dedicado estuve editando valores MySQL ;)
Saludos!
-
Si queréis optimizar vuestro servidor mysql (configurando el archivo my.cnf) os dejo este script que adecua los valores de configuración a los de vuestro servidor:
http://day32.com/MySQL/tuning-primer.sh
Un saludo.
-
Victor ¿te has leído el artículo? El penúltimo apartado. :)
-
Jeje la verdad es que me lo salté. Sorry :P