May 062014
 
Artículo MySQL

Un sitio web típico hace uso de una serie de elementos software de base: un sistema operativo Linux, un servidor web Apache, una base de datos MySQL y una serie de scripts PHP para la generación del contenido dinámico. De entre estos elementos, la base de datos puede ser uno de los elementos más decisivos en el rendimiento del sitio.

En este artículo utilizamos un ejemplo real para presentar las posibilidades que existen a la hora de analizar el rendimiento del sitio, y ajustar la configuración de MySQL para optimizarlo.

Análisis del rendimiento de la base de datos

Una buena manera de comenzar el análisis es utilizando el script mysqltuner.pl:

Este script obtiene del servidor MySQL información estadística sobre el uso que se ha hecho del mismo desde la última vez que se inicializó, y ofrece sugerencias sobre posibles mejoras a la configuración.

En nuestro caso, el resultado del script es:

Como vemos, el script precede con [!!] aquellos parámetros cuyos valores no son adecuados, y sobre los que hay que actuar.

A continuación vemos cómo corregir cada uno de ellos.

[!!] Total fragmented tables: 143

A lo largo del tiempo, en cada una de las tablas de la base de datos se insertan nuevos registros, se modifican los existentes y se eliminan algunos de ellos. Esto hace que se produzcan “huecos” y que el espacio ocupado por la tabla sea superior al necesario, afectando también al rendimiento en el acceso a la misma.

Este problema se soluciona ejecutando el comando “OPTIMIZE TABLE” sobre las tablas que están fragmentadas.

Para obtener un listado de las tablas fragmentadas, podemos ejecutar el comando “SHOW TABLE STATUS”,  y anotar aquellas en las cuales la columna “Data_free” es mayor que cero.

Alternativamente, podemos realizar la optimización de todas las tablas de una base de datos con el comando:

o bien, optimizar todas las bases de datos con un único comando:

[!!] Highest connection usage: 100% (152/151)

Esta advertencia indica que en algún momento se ha alcanzado el límite de conexiones simultáneas establecido en el fichero de configuración (por defecto, 151).

Para determinar el número de conexiones establecidas en un determinado instante, utilizamos el comando:

 

También podemos utilizar un comando un poco más elaborado, para que nos dé el número de conexiones existentes en forma de porcentaje del total de conexiones permitidas:

En nuestro caso, ejecutamos este comando varias veces durante la tarde (en un momento en que la carga del servidor es la habitual), y comprobamos que el número de conexiones establecidas se suele mantener por debajo de diez.

Por lo tanto, en el ejemplo que estamos comentando no tiene sentido modificar la configuración para incrementar el número máximo de conexiones permitidas. En su lugar, debemos investigar qué aplicación pudo provocar un incremento de conexiones (pudo ser alguna aplicación de mantenimiento o de otro tipo, no necesariamente el propio servidor web).

Mientras tanto, en la salida del script mysqltuner.pl hay una sugerencia para minimizar este problema:

Por defecto, el servidor MySQL mantiene abierta una conexión, aunque esté inactiva, durante 28800 segundos (8 horas)

En la mayoría de los casos, este valor se puede reducir significativamente, tanto para conexiones interactivas como no interactivas. Por ejemplo, para establecer un límite de 5 minutos (300 segundos), añadimos al fichero de configuración de mysql (/etc/mysql/my.cnf) los parámetros:

 

[!!] Query cache prunes per day: 1816027

El “Query cache” es un mecanismo que puede resultar adecuado en muchos casos para mejorar el rendimiento del servidor mysql, pero no siempre es el más idóneo. En esta página se ofrece una explicación bastante clara (en inglés) sobre los pros y los contras del query cache.

En todo caso, mysqltuner.pl sugiere que incrementemos el parámetro query_cache_size

[!!] Joins performed without indexes: 79453

Este mensaje indica que algunas de las consultas que se realizan a la base de datos, recogiendo información de dos o más tablas, son del tipo JOIN (INNER JOIN, LEFT OUTER JOIN,…), pero no existe un índice que permita enlazar ambas tablas, por lo que, para cada uno de los registros de la primera tabla, se tiene que realizar una búsqueda secuencial del registro o registros correspondientes en la segunda tabla.

La solución consiste en identificar cuáles son las consultas que tienen este problema, y definir los índices necesarios para optimizar su ejecución.

Para identificar las consultas, podemos añadir al fichero de configuración de mysql las directrices:

Con ellas, todas las consultas que no usan índices, así como todas las consultas que tarden más de un segundo en ejecutarse, se registrarán en el fichero de consultas de larga duración. Posteriormente, podremos analizar este fichero, y realizar las acciones correctoras que resulten oportunas.

[!!] Table cache hit rate: 0% (400 open / 101K opened)

Los datos de cada una de las tablas MyISAM existentes en la base de datos se encuentran almacenados en un fichero del sistema operativo. Cuando la ejecución de una sentencia requiere acceder a los datos de una tabla, mysql debe obtener el correspondiente descriptor de fichero.

La cache de tablas guarda los descriptores abiertos, de modo que pueden ser reutilizados en la ejecucion de otras sentencias que requieren el acceso a las mismas tablas.

El valor por defecto del parámetro table_cache es 64, pero este valor es a menudo insuficiente. Podemos obtener una instantánea de la situación utilizando el comando mysqladmin:

En la salida del comando, podemos ver “Open tables: 400”. Por lo tanto, la recomendación es incrementar el valor del parámetro table_cache a 400 o más.

De todos modos, no está claro el beneficio que se puede obtener modificando el valor de este parámetro, y algunos artículos de autores de reconocido prestigio cuestionan la implementación de esta funcionalidad en MySQL, al menos hasta la versión 5.4:

http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/

En la documentación oficial de MySQL se sugiere calcular el valor de table_cache con la fórmula table_cache = max_connections * N, en donde N el el número máximo de tablas accedidas en las sentencias JOIN ejecutadas.

En cualquier caso, hay que asegurarse de que el valor que se establece para table_cache no supera el número máximo de descriptores de fichero abiertos por proceso que permite el sistema operativo. Más información sobre cómo determinar el valor actual de este límite, y cómo modificarlo, se puede encontrar en esta página.

[!!] InnoDB buffer pool / data size: 128.0M/5.1G

Este mensaje nos advierte de que el tamaña del espacio reservado en RAM para mantener datos leídos de tablas InnoDB es mucho menor que el volumen de datos existente, y por lo tanto la mayoría de las consultas realizadas podrían requerir accesos al sistema de ficheros, en función del patrón de uso de la base de datos.

Siempre interesa aumentar el tamaño de los espacios de almacenamiento en RAM, pero para ello hay que tener en cuenta la cantidad de RAM disponible en nuestro sistema.

Referencias:

 Publicado por en 1:49 pm

  Una respuesta a “Cómo ajustar la configuración de MySQL de un sitio web”

  1. Artículo a los marcadores. Gracias por escribirlo 🙂

 Deja un comentario

(requerido)

(requerido)