Optimizar MySQL para WordPress

Cuando un blog crece se acaba teniendo que alojar en un servidor dedicado. Para usuarios inexpertos o no informaticos esto suele ser un autentico suplicio, ya que te lo tienens que configurar todo. Aqui te enseñamos cómo optimizar MySQL y pensando en WordPress.

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?.

 

Optimiza MySQL facilmente sin ser un experto

Quizás, una de los apartados más crí­ticos en la configuración de un servidor, es la optimización de MySQL.

El quedarse corto o pasarse, significará el desaprovechar recursos, quitándoselos a otras tareas o quedarse corto y que el servidor vaya muy lento e incluso caiga.

Quizás es una de las tareas más tediosas que hay.

He estado buscando durante mucho tiempo algo que me facilitara la vida para optimizar MySQL tanto en mis servidores como en los VPS, Servidores Hí­bridos y Dedicados de mis clientes.

Normalmente, hay que ir haciendo ajustes en estos para que estén al 100%, no es realizarlo una vez y te olvidas de por vida. SI las necesidades de estos disminuyen o aumentan, hay que ajustarlos de nuevo.

La pesadilla, siempre a sido referente a MySQL.

Después de buscar mucho tiempo, he encontrado un script que es una auténtica maravilla.

El script lo encontrareis en esta página:

http://www.day32.com/MySQL/

Si tenis un VPS o Dedicado, os facilitará la vida muchí­simo.

Las instrucciones que voy a dar, son referentes a una instalación en CentOS.

Sirve si tenéis instalado cPanel en él.

Lo primero que deberemos hacer es acceder al servidor por SSH

Una vez que estemos conectados, escribimos:

wget http://www.day32.com/MySQL/tuning-primer.sh


Con esto, nos descargaremos el script

Para que funcione bien este script, deberemos tener instalado bc, por lo tanto, escribiremos:

yum install bc

Lo anterior nos instalará bc.

Una vez que lo tengamos instalado, escribimos:

sh tuning-primer.sh

Debemos estar en el mismo directorio que el archivo.

El script se pondrá en marcha.

Os aconsejo que abráis otra conexión SSH, de esta forma, en una tendréis el script y en la otra podremos configurar MySQL.

Y aquí­ el texto completo que nos muestra:


-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.90-community x86_64

Uptime = 0 days 14 hrs 6 min 16 sec
Avg. qps = 2
Total Questions = 102107
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 4 out of 102128 that take longer than 10 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 1024
Current threads_cached = 5
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 3
Historic max_used_connections = 8
The number of used connections is 8% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 542 M
Configured Max Per-thread Buffers : 400 M
Configured Max Global Buffers : 510 M
Configured Max Memory Limit : 910 M
Physical Memory : 31.37 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 73 M
Current key_buffer_size = 250 M
Key cache miss rate is 1 : 58
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 250 M
Current query_cache_used = 14 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 5.73 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 768 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 11 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 8302 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
Current table_cache value = 4096 tables
You have a total of 13106 tables
You have 4096 open tables.
Current table_cache hit rate is 6%
, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 32 M
Current tmp_table_size = 32 M
Of 1472 temp tables, 43% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 32 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 696
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Cómo podéis ver, lo que hace es hacer un estudio de como ha estado funcionando MySQL y da consejos sobre parámetros que debemos tocar.

Una vez que nos muestra el resultado, deberemos realizar las modificaciones aconsejadas por el script.

Para ello, en la otra ventana que tenemos una conexión activa SSH escribimos:

cp /etc/my.cnf /etc/my.cnfBACKUP

Con lo anterior realizamos una copia de seguridad que no está de más.

vi /etc/my.cnf

Esto nos abrirá el archivo de configuración de MySQL.

Lo abro con vi, ya que es el que más me gusta, pero podéis realizar las modificaciones con el que más os guste.

Una vez abierto, para los que no estén familiarizados con vi, debéis apretar en el teclado la tecla Insert”, de esta forma podréis escribir en el archivo.

Realizar las modificaciones que os aconseje el script.

Una vez que las hayáis realizado, apretar el botón Escape” y escribir:

:w

Esto guardará las modificaciones.

Una vez realizadas las modificaciones, apretar:

:q

Lo anterior os sacará del editor.

Una cosa importante, ya que hay veces que sucede.

Si borras más cosas de la cuenta y no sabes que has borrado, lo mejor es que salgas sin guardar. Para salir sin guardar debéis escribir:

:q!

Acordaros de apretar la tecla escape” antes, o no tendrá efecto, estaréis escribiendo el el archivo.

Una vez que hayáis realizado los cambios y los hayáis guardado, hay que reiniciar el servidor MySQL.

Si usáis WHM/cPanel, ir a WHM y en Restart Services” Apretar sobre SQL Server (MySQL)”

Si no usais WHM, escribir en la consola:

/etc/init.d/mysqld restart

Si MySQL no reinicia, algo habéis hecho mal en el archivo de configuración.

Para resolverlo y que no nos caigan gotas de sudor, escribimos:

rm –rf /etc/my.cnf
cp /etc/my.cnfBACKUP /etc/my.cnf

y volvemos a reiniciar.

Si no habí­amos cometido ningún error, casi con toda seguridad la página os irá mucho más fluida.

Ahora, esperáis de 24 a 48h y volvéis a poner en marcha el script y veis resultados.

Si todo sale Ok, perfecto, si os sale algún otro consejo, pues volvéis a realizar el proceso.