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:

wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
perl 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:

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.35-0+wheezy1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 46G (Tables: 1014)
[--] Data in InnoDB tables: 5G (Tables: 1006)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 7)
[!!] Total fragmented tables: 143

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 14d 20h 32m 20s (354M q [276.419 qps], 5M conn, TX: 337B, RX: 48B)
[--] Reads / Writes: 12% / 88%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (15% of installed RAM)
[OK] Slow queries: 0% (2K/354M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/21.4G
[OK] Key buffer hit rate: 99.6% (3B cached / 12M reads)
[OK] Query cache efficiency: 71.7% (79M cached / 111M selects)
[!!] Query cache prunes per day: 1816027
[OK] Sorts requiring temporary tables: 0% (159 temp sorts / 7M sorts)
[!!] Joins performed without indexes: 79453
[OK] Temporary tables created on disk: 25% (672K on disk / 2M total)
[OK] Thread cache hit rate: 99% (31K created / 5M connections)
[!!] Table cache hit rate: 0% (400 open / 101K opened)
[OK] Open file limit used: 4% (402/10K)
[OK] Table locks acquired immediately: 99% (265M immediate / 265M locks)
[!!] InnoDB  buffer pool / data size: 128.0M/5.1G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 5G)

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.

mysql> SHOW TABLE STATUS FROM nombre_database;
...
mysql> OPTIMIZE LOCAL TABLE nombre_tabla;

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

$ mysqlcheck -o <nombre_database>

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

$ mysqlcheck -o --all-databases

[!!] 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:

 

mysql> select count(*) as connections from information_schema.processlist;
+-------------+
| connections |
+-------------+
|           3 |
+-------------+

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:

mysql> select ( pl.connections / gv.max_connections ) * 100 as percentage_used_connections
from
( select count(*) as connections from information_schema.processlist ) as pl,
( select VARIABLE_VALUE as max_connections
  from information_schema.global_variables 
  where variable_name = 'MAX_CONNECTIONS' ) as gv;
+-----------------------------+
| percentage_used_connections |
+-----------------------------+
|          1.3245033112582782 |
+-----------------------------+

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:

Variables to adjust:
    wait_timeout (< 28800)
    interactive_timeout (< 28800)

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:

wait_timeout = 300
interactive_timeout = 300

 

[!!] 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

query_cache_size (> 16M)

[!!] 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:

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

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:

$ mysqladmin status
Uptime: 52643  Threads: 1  Questions: 70083195  Slow queries: 23952  Opens: 18456
Flush tables: 1  Open tables: 400  Queries per second avg: 1331.291

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)