Optimización de bases de datos MySQL

Introducción

Con varios millones de bases de datos MySQL en producción en el mundo y miles de descargas diarias, no cabe ninguna duda de la importancia de este motor de bases de datos en el mercado. Si bien es utilizado en grandes sistemas como AdSense (de Google) y en la Wikipedia (de la Wikimedia Foundation), su importancia radica principalmente en un enorme conjunto de proyectos OpenSource que lo requieren para dar soporte a sus contenidos. WordPress, MediaWiki, Drupal y OsTicket son sólo algunos ejemplos de los cientos miles existentes.

La mayoría de los proyectos pequeños que usan MySQL al principio funcionan muy bien. La performance del motor casi siempre cubre las expectativas de rendimiento y de esta forma se mantiene siempre vigente su bien ganada reputación, en particular cuando se usa en conjunto con Apache y PHP, sea bajo plataformas Windows o Linux. El problema surge cuando estos proyectos, inicialmente pequeños, crecen hasta un punto tal en el que la performance se ve afectada considerablemente.

Al surgir este tipo de problemas lo normal es concentrarse exclusivamente en mejorar el hardware, obteniendo inicialmente buenos resultados pero no pudiendo mantener un crecimiento lineal entre las mejoras del hardware y la performance. La experiencia demuestra que mejorar únicamente el hardware influye en la performance de la base de datos de acuerdo a la ley de los rendimientos marginales decrecientes, claro que un producto marginal del trabajo siempre mayor o igual a cero. Surge entonces la necesidad encarar el problema de otra forma.

Se aclara que las pruebas realizadas para este artículo surgen de una instalación de OsTicket en producción desde hace 4 años, con una base de datos de 110MB en la cual existen dos tablas principales de 50MB cada una (una con 120000 registros y la otra con 260000).

Optimización de bases de datos MySQL

Analizando el entorno operativo de MySQL vemos que los componentes con los cuales se relaciona directa o indirectamente son:

  1. Aplicación
  2. API
  3. Sistema Operativo
  4. Hardware

No hay que olvidar al motor de base de datos en si mismo. Si bien MySQL es un motor de código abierto, no considero que sea normalmente viable una modificación al código del mismo por lo que esto no se analizará en el presente artículo aunque si se considerarán cuestiones como cambios de versión.

Nota: Las posibilidades de optimización de los componentes antes mencionados variarán en función del escenario en el que nos encontremos. En otras palabras, no necesariamente podremos optimizarlos todos, ya que por ejemplo, es posible que no podamos realizar un cambio de hardware por motivos de costos o que no podamos reemplazar el Sistema Operativo por dar el mismo soporte a otras aplicaciones.

 

1. Aplicación

Tanto las consultas SQL como el resto del código de la aplicación influirán directamente en la performance del motor de base de datos. Si bien es bastante engorroso analizar una aplicación que puede tener decenas de miles de líneas de código es importante trabajar en este punto de forma tal de optimizar tanto como sea posible el acceso a la base. Algunos detalles importantes que tienen que ser tenidos en cuenta son:

  • Usar conexiones persistentes: para poder hacer una consulta sobre la base de datos previamente es necesario realizar una conexión a la misma. Una vez conectado y con la consulta finalizada el sistema puede determinar cerrar la conexión o mantenerla abierta. En el primer caso al ser necesario realizar una nueva consulta se deberá realizar una nueva conexión a la base de datos mientras que en el segundo caso esto no será necesario. Si bien parece evidente la ventaja de mantener conexiones persistentes esto no necesariamente es así. Mantener una conexión abierta representa un consumo de recursos, que si bien ínfimo, debe ser tenido en cuenta. Hay que tener en cuenta que el número de conexiones en MySQL está limitado por la variable max_connections (por default en 151 en la versión 5.5 y en 100 en versiones anteriores) por lo que en caso de exceder el número de conexiones las que superen dicho valor serán rechazadas. El uso de conexiones persistentes dependerá de un conjunto de factores:

-Tamaño de la cabecera de conexión (esto puede analizarse desde la estadísticas de MySQL), a lo que habrá que sumar el costo de procesamiento de la conexión. Mientras mayor sea esta cabecera más deseable será mantener las conexiones persistentes.

-Cantidad de clientes. A mayor cantidad de clientes es menos aconsejable el uso de conexiones persistentes. Si bien MySQL no tiene ningún inconveniente en atender algunos miles de usuarios en simultáneo (en Linux o Solaris, mientras que en Windows está limitado a 2048 por compatibilidad POSIX) hay que tener en cuenta la cantidad máxima de conexiones permitidas. Es importante también notar que cada conexión debe ser almacenada en memoria, por la cantidad de RAM instalada limitará directamente la cantidad de conexiones. En el caso de que las mismas superen a la RAM las conexiones se deberán almacenar en disco (paginación) lo que impactará pésimamente en la performance.

-Uso de la aplicación. Si generalmente los usuarios acceden al aplicativo para una consulta y luego cierran la misma, es difícil justificar el uso de conexiones persistentes; en cambio, si mantienen las sesiones de la aplicación abiertas durante la mayor parte de la jornada laboral estas se justifican claramente.

  • Utilizar un caché entre la aplicación y el motor de base de datos: MySQL provee el Query Cache, un componente que cuando se encuentra activado, y de forma totalmente transparente para la aplicación, almacena el resultado completo de una consulta en memoria de forma tal de no necesitar acceder nuevamente a la base de datos en caso de que se vuelva a hacer la consulta. En caso de que la base de datos sea utilizada principalmente para lecturas, activar esta funcionalidad impactará muy positivamente en la performance del sistema. Lo negativo del Query Cache es principalmente el gasto extra de procesamiento que conlleva tenerlo habilitado, máxime considerando que cada vez que se escriba en una tabla se borrarán automáticamente todas las consultas almacenadas previamente en caché que involucran esa tabla pudiendo así haber incurrido en un gasto innecesario.Para activar el Query Cache se debe configurar la variable query_cache_size indicando como valor el tamaño que tendrá el caché (en bytes). También sería sumamente útil considerar el uso de cachés propios de la aplicación, aunque la factibilidad de esto dependerá en gran medida de las reglas del negocio o forma en que se desarrolló la aplicación.
  • Optimizar las consultas SQL: este punto en particular es uno de los más importantes para analizar en la búsqueda de optimizar la performance de una base de datos. Con simples cambios se pueden obtener mejoras increíbles, tranquilamente superiores a las que se obtendrían mejorando otros aspectos. Algunas cuestiones que se deben tener en cuenta son las siguientes:

-Limitar el uso de SQL a la consulta de datos evitando utilizarlo para validar datos u operaciones aritméticas

-Evitar el uso de SELECT * ya que el obligará al motor a leer toda la estructura de la tabla antes de ejecutar la consulta. Asimismo en vez de escribir SELECT Alumno FROM Alumnos, Clases podemos usar SELECT Alumnos.Alumno. Esto evitará que el motor tenga que buscar a que tabla pertenece el campo.

-Seleccionar sólo aquellos campos y tablas que se utilicen

-Utilizar dentro de la cláusula WHERE aquellos campos que sean índices, minimizar el uso de los que no lo sean

-Si se usan varias tablas tener cuidado de agregarlas en la cláusula FROM de menor a mayor, esto hará que el motor tenga que recorrer menos registros a la hora de resolver las consultas

-Utilizar consultas almacenadas, se ejecutarán más rápidamente que las externas

-INNER JOIN, LEFT JOIN y RIGHT JOIN son útiles para unir las tablas y liberan de carga al motor en comparación a la cláusula WHERE

-WHERE valor >=5 es mejor que WHERE valor > 4, ya que en el primer caso el motor saltará directamente al valor 5 mientras que en el segundo compará todos hasta encontrar al 5

-Los wildcards “%” demandan de muchísimo procesamientos para el DBMS y esto se notará exponencialmente a medida que crece la base de datos. Si es necesario su uso tener en cuenta que es mejor usar ‘valor%’ que ‘%valor’ o ‘%valor%’. La diferencia será notable

-De ser posible evitar el uso del operador NOT ya que demanda de un procesamiento extra

-Limitar los resultados con LIMIT. Esto será fundamental a medida que la base de datos crezca

-A la hora de hacer INSERT de ser posible usar los valores por defecto. Esto conllevará menos tareas de parseo para el motor

-LIKE, RLIKE y MATCH AGAINST son distintos y debe analizarse correctamente cual utilizar

En la tabla a continuación pueden observar dos consultas estándar del sistema OsTicket y sus optimizaciones junto a la diferencia de rendimiento que existe entre las mismas. En el primer caso el único cambio que se realizó fue utilizar el operador de comparación adecuado teniendo en cuenta que el campo response de la tabla response dispone de un índice Full Text, consiguiendo así una reducción en el tiempo de ejecución del 20% promedio. En el segundo caso simplemente se depuró la consulta quitando elemenos innecesarios y obteniendo una mejora del 13%.

 

Otro punto relacionado con el diseño de la base de datos y que generalmente no es correctamente considerado a la hora de analizar la performance es el hacer un correcto uso de los tipos de datos en las tablas MySQL. Con soporte para aproximadamente 25 tipos de datos es muy importante que se haga un cuidadoso estudio de que tipo de dato es el óptimo para una determinada columna, cuestiones menores como utilizar INT en lugar de MEDIUMINT representan una diferencia de casi 4MB por cada 100000 registros en una tabla, valor que si bien parece despreciable definitivamente no lo es. El procedure ANALISE puede usarse para obtener sugerencias del tipo de dato óptimo para una columna.También tiene una importancia clave en la performance de la base de datos el diseño que se haya dado a la misma. Si bien la base de datos debe encontrarse normalizada (se sugiere que se encuentre en al menos tercera forma normal), es viable mantener información redundante en pos de aumentar la performance del sistema a pesar de que esto implique un conjunto de triggers para preservar la consistencia de datos (cuestión que sin lugar a dudas tendrá un efecto negativo en la performance). El mantenimiento de información redundante (léase desnormalización) debe ser correctamente estudiado en cada caso particular.

Por último me gustaría dedicar un párrafo a los índices. En algunos proyectos open source observé que es común hacer un abuso intensivo de los mismos. Es absolutamente innegable que los índices son sumamente beneficiosos en la mayoría de los escenarios pero también es importante tener en cuenta que su mantenimiento impacta en la performance por lo que deben ser usados con inteligencia. Mientras que son sumamente buenos para las lecturas no lo son para los inserts y updates por lo que cada caso debe analizarse por separado. Agrego además que en promedio un campo índice ocupa el doble de espacio que el mismo campo sin indexar.

 

2. API

Antes de analizar este punto no estaría de más aclarar que el cambio de API (Application Programming Interface)  en una aplicación productiva es prácticamente imposible o carece de mayor sentido a los fines del presente artículo ya que implica reescribir por completo la aplicación. En esta sección nos concentraremos exclusivamente en el análisis de PHP corriendo sobre Apache, ya que, trabajando sobre MySQL, representa la combinación más utilizada actualmente.

El primer punto a tener en cuenta al trabajar con PHP es utilizar la versión del lenguaje que mejor se adapte a nuestras necesidades. Si bien por cuestiones de funcionalidad y seguridad lo recomendable es ejecutar la última versión estable, desde el punto de vista de performance esto podría no ser lo adecuado ya que la migración a una versión superior podría requerir de un mayor esfuerzo de procesamiento para ejecutar las mismas tareas (por caso, el uso de PHP 5 en reemplazo de PHP 4 implica una baja de la performance del orden del 7%). A pesar de lo antedicho, generalmente en muchos upgrades sucede lo contrario asociado a un incremento de funcionalidad. Como ejemplo podríamos citar que el lanzamiento de PHP 4 (con el motor Zend en el año 2000) implicó una impresionante mejora de la performance principalmente por el hecho de que PHP 3 trabaja interpretando línea a línea el código a medida que el mismo se ejecuta mientras que PHP 4 realiza una compilación del script antes de ejecutarlo. Acercándonos a versiones más actuales es importante destacar el cambio de la versión 5.2 a la versión 5.3, la cual incorporó mysqlnd en reemplazo de libmysql como librería para las extensiones MySQL. Con este cambio hubo una marcada mejora en la performance de varias funciones, como por ejemplo en la búsqueda de datos en buffer.

Otro punto a considerar es la forma en que se ejecutan nuestros scripts PHP. Apache permite varias configuraciones para ejecutar código PHP aunque las más utilizadas son FastCGI y mod_php. En la primera cada vez que es necesario ejecutar un script PHP Apache invoca al intérprete que se ejecuta como un proceso separado. En la segunda el intérprete PHP se ejecuta como un módulo interno de Apache obteniendo así una mejor performance. Si bien es un hecho que en cuestiones de performance mod_php supera ampliamente a FastCGI, este última se utiliza generalmente en hosts compartidos ya que permite atribuir diferentes permisos de ejecución para el intérprete PHP y para Apache.

 

3. Sistema Operativo

El sistema operativo cumple la función de gestionar todos los recursos de hardware existentes en el equipo y por ende tiene un impacto total en la performance del motor de bases de datos. Una mala elección del sistema operativo sin lugar a dudas se traducirá en una pobre performance de la base de datos.

El primer aspecto a considerar, íntimamente relacionado con la plataforma de hardware, es la elección de un sistema operativo de 64 bits. Pruebas realizadas en Windows 2003 Server demostraron que la diferencia de rendimiento contra uno de 32 bits es de un promedio del 50%. En otras palabras, un sistema de 64 bits realizará el doble de operaciones que uno de 32 bits en igual cantidad de tiempo. Y esto aún ejecutando el motor de 32 bits (MySQL 5.0.51).

Con respecto al sistema operativo en sí, se realizaron pruebas en una maquina virtual (VirtualBox 4.1.8) asignándole 1 procesador, 1GB de memoria RAM y un disco rígido dinámico de 10gb. Se utilizó en todos los casos MySQL 5.1 y se trabajo siempre con la misma base de datos. Los resultados demostraron una amplia diferencia entre los sistemas evaluados, la cual puede observarse en el siguiente gráfico:

Como se puede observar en el gráfico, las 4 consultas (A, B, C y D) se ejecutan en prácticamente la mitad de tiempo en Ubuntu Server 11.10 que en Windows 2008 Server R2. Este último a su vez tiene un rendimiento un 40% superior a Solaris. La amplia diferencia de tiempo existente demandó que las pruebas se realicen en dos oportunidades intentando equiparar en el mayor grado posible las configuraciones de los sistemas operativos (se mantuvo siempre que fue posible la configuración por default). La única gran diferencia que se presentó en los escenarios fue a favor de Ubuntu Server y es que no se le instaló interfaz gráfica, en el resto de los parámetros se dejó como venían por default, con la excepción de parámetros de seguridad o funcionamiento propios de la base de datos.

 

4. Hardware

En la introducción se mencionó que la mejora del hardware responde a la ley de rendimientos marginales decrecientes. De esta afirmación surgen dos situaciones a considerar: si nos encontramos en el máximo de producción no obtendremos ninguna mejora por aumentar las capacidades de hardware y en el caso de no encontrarnos en dicho punto interesa identificar aquellos factores que nos den los mejores beneficios. Por caso, el aumento de performance de un upgrade de RAM de 1GB a 2GB podría ser muy superior al cambio de un procesador de 2 núcleos por uno de 4 y además a un precio mucho menor.

Los componentes que influyen en la performance de una base de datos, ordenadas por su relevancia, son la memoria RAM, el procesador y el disco rígido. A continuación analizaremos cada uno de ellos.

Memoria RAM

Para tener un buen desempeño de la base de datos es fundamental minimizar los accesos al disco rígido ya que su velocidad es cientos de veces menor a la de la memoria RAM. Si bien los datos deben almacenarse en disco lo ideal sería que cada vez que el sistema deba acceder a un dato este se encuentre en memoria.

Si bien sería deseable tener una fórmula para calcular cuanta memoria RAM requiere una base de datos la práctica demuestra que no es tan sencillo determinar dicha fórmula. Máxime considerando que, y contrario a lo que podría pensarse, la cantidad de memoria RAM ideal no guarda relación con el tamaño de la base de datos. En MySQL lo ideal es calcularla considerando los distintos buffers (key_buffer_size, innodb_log_buffer_size,. etc), pools (innodb_buffer_pool_size, innodb_additional_memory_pool_size, etc) y cachés (como query_cache_size). La memoria RAM debería ser capaz de contener a todos ellos, sumando unos 32MB para el código de ejecución del servidor MySQL y unos 600 /700 MB para el sistema operativo. Con lo anterior será suficiente para mantener la base de datos pero a ello habría que sumarle lo requerido por cada una de las conexiones.

Cada proceso que se conecte a nuestro MySQL demandará de aproximadamente 256 KB para mantener la conexión y, en función del procesamiento que realice, aumentará sus demandas. En otras palabras esto quiere decir que la complejidad de la consulta conllevará mayores o menores requerimientos de memoria. Las tablas que cada hilo probablemente cargará en memoria son: read_buffer_size, sort_buffer_size y tmp_table_size pero hay que tener en cuenta que de necesitarse subconsultas las volverían a cargar en memoria. Por esto justamente es que es muy difícil establecer una fórmula general para calcular la cantidad de memoria RAM requerida.

Si bien lo ideal sería realizar las pruebas correspondientes con la base de datos real, a modo de regla podría considerarse que una aplicación transaccional, con consultas no complejas, no requerirá más de 1 MB por conexión.

Teniendo en cuenta lo anteriormente dicho, podría realizarse una muy básica aproximación a la cantidad de memoria RAM requerida para sistemas transaccionales con la siguiente fórmula: (SISTEMA OPERATIVO + 32 MB + BUFFERS + POOLS + CACHES + (CONEXIONES * 1 MB)) * 1,2. Tomando un margen de seguridad del 20%.

Con respecto a la velocidad de la memoria RAM, lo ideal sería instalar la más rápida que soporte la plataforma ya que el aumento de velocidad impactará muy positivamente en la performance del motor de base de datos. En general no hay una gran diferencia de precio en memorias de igual capacidad y tecnología pero de diferentes velocidades lo que justifica el razonamiento sin ningún aspecto negativo.

Procesador

El procesador tendrá una injerencia notable en la performance de la base de datos y se podría decir que las prestaciones de la base de datos dependen en gran medida de este. Es por esto que la correcta elección del procesador es clave en la búsqueda de una performance óptima. El problema en la elección del procesador surge en la variada oferta que existe actualmente, considerando que ya no es posible guiarse por la velocidad de los mismos (que dicho sea de paso, en los últimos modelos varía en función de la carga de procesamiento) sino también por la cantidad de núcleos, el tamaño de la caché y algunas tecnologías adicionales como Hyper Threading.

El primer punto a considerar será la cantidad de núcleos del procesador. Para las pruebas en laboratorio se utilizó un Celeron E3400 (2 núcleos, 2,6ghz, 1MB caché, FSB 800mhz) y los resultados determinaron que para una sola consulta en ejecución la mejora del uso de un núcleo a dos núcleos es mínima (del orden del 2% con una utilización de cada núcleo del 54%). Este resultado, sorprendente a priori, demuestra que todavía hay bastante camino por recorrer en la programación multihilo, máxime considerando la subocupación que se realiza de ambos núcleos. La mejora utilizando los dos núcleos surge al correr más de una consulta en simultáneo: con dos consultas idénticas la mejora promedio es del 58%, siempre con una utilización de ambos núcleos al 100%. Resumiendo podríamos decir que el número de núcleos influirá en el rendimiento al trabajar con varias consultas simultáneamente pero no al trabajar con una sola consulta.

Con respecto a la velocidad, las pruebas fueron realizadas sobre un Pentium 4 de 3,4ghz con el multiplicador bloqueado y por ende realizando variaciones del FSB (200mhz por default). Las diversas pruebas arrojaron como resultado que 100mhz de velocidad tienen en promedio una influencia del orden del 3% en el resultado. En otras palabras una consulta se ejecutará un 3% más rápidamente en un procesador de 3,4ghz que en uno de 3,3ghz. Si bien las pruebas fueron concluyentes es importante aclarar que estos resultados no deberían ser trasladados a otras plataformas ya que no es riguroso comparar 100mhz de un Pentium 4 con 100mhz de, por ejemplo, un Core i7.

El último punto a tener en cuenta a la hora de evaluar la influencia de un procesador en la performance de la base de datos es la presencia o ausencia de tecnologías adicionales. Un ejemplo muy común es HT (Hyper Threading) cuyo rendimiento fue probado en laboratorio y cuyos resultados dejaron una no muy grata sorpresa. A la hora de trabajar con una sola consulta el rendimiento con HT habilitado es un 3% inferior en comparación con HT deshabilitado. A pesar de esto el beneficio de tener HT habilitado es claro: al trabajar con consultas en paralelo la performance sufre una mejora promedio del 15%. La habilitación de este tipo de tecnologías debe ser estudiada cuidadosamente no sólo considerando sus beneficios teóricos sino preferentemente realizando pruebas sobre un escenario real.

Disco rígido

Los discos rígidos son el principal cuello de botella en cualquier equipo informático. Mientras los procesadores, las memorias, las redes y el resto de los dispositivos aumentaron exponencialmente sus velocidades en los últimos años, los discos rígidos se mantuvieron sin grandes cambios. A partir del surgimiento de los discos de estado sólido y las nuevas interfaces de alta velocidad esto comenzó a cambiar y se espera que en un futuro no muy lejano siga habiendo avances en la materia.

El problema con los discos rígidos es que toda la información debe almacenarse en los mismos para que sea accesible a largo plazo. Además de esto las limitaciones de direccionamiento de las plataformas actuales también obliga a que gran parte de los datos se almacenen en un disco rígido. Es común en el ámbito de las bases de datos pensar que el rendimiento dependerá exclusivamente de los discos rígidos. Y si bien esto podría ser cierto, antes de afirmarlo es importante considerar el escenario sobre el que trabajamos.

Para bases de datos pequeñas, cuyo contenido pueda caber fácilmente en la memoria RAM del equipo, las prestaciones del disco rígido no tendrán una mayor influencia en la performance de la base de datos. Es cierto que en un primer momento se deberá cargar la base en memoria tomándola desde el disco rígido, pero una vez que se realice esto los accesos disminuirán considerablemente.

En escenarios con bases de datos grandes, que no quepan en la memoria RAM o bien en casos en que se realice un gran número de escrituras constantemente la importancia de un disco rígido performante aumentará.

Sea una base de datos grande o pequeña, interesa conocer algunos parámetros que influirán en la lectura de la base de datos cuando esta se encuentre en el disco rígido (para bases de datos pequeñas se dará principalmente cuando se carguen las tablas en memoria o, en otras palabras, cuando se ejecute la primera consulta). Los parámetros a tener en cuenta para discos con memoria magnética son los siguientes (para discos con memoria de estado sólido básicamente deberíamos considerar los tiempos de lectura y escritura) :

  • Tiempo medio de acceso: es el tiempo que tardará el cabezal en posicionarse sobre el sector que queremos leer. Está fuertemente influido por la velocidad de giro del disco (rpm).
  •  Tasa de transferencia: es la velocidad a la cual el disco transmite la información a la computadora. Lo primordial en este punto es considerar la velocidad de la interfaz (sea IDE, SCSI, SATA o SAS), generalmente medida en mbps.
  • Caché: es una memoria de rápido acceso que se encuentra dentro del disco. Sin lugar a dudas, mientras mayor sea mejor será.

Si bien no es un parámetro relacionado con el disco rígido me gustaría en este apartado mencionar la influencia del sistema de archivos en el funcionamiento general de la base de datos. Si bien el objetivo es minimizar el uso del disco, es evidente que en algún momento se tendrá que acceder al mismo. En el gráfico a continuación se puede observar el tiempo de  respuesta en la ejecución de una consulta de media complejidad sin tener la base de datos todavía en memoria. Del gráfico se puede deducir que no hay mayor diferencia entre sistemas de archivos (3% entre ext2 y ext4) pero si entre diferentes tecnologías. Por caso utilizar RAID 0 (con 2 discos) da un mejora en la perfomance del orden del 7%, performance que prácticamente se duplica (13%) en el caso de usar RAID 5 (con 6 discos). Luego de la ejecución de esta primera consulta, las tablas quedan cargadas en memoria por lo que en las consultas posteriores no se diferencia casi ninguna diferencia de performance entre los diferentes sistemas de archivos.

 

5. Motor MySQL

Existen dos factores que influirán apreciablemente en la performance de las base de datos: la versión del motor de la base de datos y las variables configuradas en la misma.

Al ser una de las bases de datos líderes en el mercado, MySQL está en un constante proceso de corrección y actualización. Las nuevas versiones no sólo agregan funcionalidad sino que también corrigen errores y proveen mejoras de performance. Pero una nueva versión no necesariamente implica mejoras en el rendimiento, no sólo por el hecho de que los desarrolladores no siempre trabajan sobre este aspecto sino también porque la nueva funcionalidad podría requerir de mayores recursos lo que impactaría negativamente en la performance general, al no necesariamente utilizarse esa nueva funcionalidad. Las pruebas demostraron que dentro de una misma versión lo normal es que las actualizaciones provean un aumento de performance aunque mínimo (por ejemplo de la versión 5.5.8 a la 5.5.20 la mejora es del 1,67%) y que la diferencia entre versiones sea variable ya que por ejemplo la versión 5.5 tiene una mejor performance que la versión 5.1 la cual a su vez tiene una peor performance que la versión 5.0.

Otro punto a considerar es la elección de un motor de 32 bits con respecto a uno de 64 bits. La mejora que se obtendrá en el segundo caso es de un 23% lo que representa un enorme beneficio.

A continuación adjunto un gráfico que detalla los tiempos de respuesta promedio de 4 consultas diferentes para cada versión del motor MySQL testeado. Se aclara que todos los motores se testearon bajo las mismas condiciones operativas (HP Proliant ML530 con 2 Xeon dual core de 3ghz, 4GB RAM, RAID 5 y Windows 2003 Server 32bits recién instalado) sin realizar ningún tipo de tuneo de la base de datos.

La configuración del motor de base de datos también tiene una muy importante influencia en el rendimiento de la base de datos. La correcta configuración de las variables será determinante en el rendimiento general de la base de datos y una incorrecta configuración podría traducirse en un pésimo rendimiento general de la misma. Más allá de la configuración inicial es necesario considerar las variables del motor deberían estar sujetas a regulares análisis para evaluar posibles mejoras de performance.

Mediante las estadísticas de MySQL es sencillo encontrar variables a optimizar, optimizaciones que serán posibles sólo en función del escenario en el que nos encontremos ya que obviamente dependerá de como se escribieron las consultas, de como se diseñaron las tablas y de hasta como trabajan los usuarios que realizan las diferentes consultas.

Algunas variables de las estado del servidor MySQL que sería importante tener en cuenta son las siguientes:

  • Handler_read_rnd: indica el número de solicitudes hechas para leer una fila basado en una posición fija. En caso de que tenga un valor alto habría que evaluar consultas que escaneen tablas enteras o joins que no aprovechen correctamente los índices.
  • Handler_read_rnd_next: es el número de solicitudes hechas para leer la siguiente fila en el archivo de datos. Esto indica que se hacen muchos escaneos de tablas. Habría que considerar utilizar índices o bien reescribir las consultas para utilizarlos correctamente.
  • Qcache_lowmem_prunes: muestra el número de consultas que han sido removidos del cache para liberar la memoria para poner nuevas consultas en el cache. Mediante este número se puede afinar el tamaño de la caché de consultas.
  • Threads_cached: el número de procesos en el caché de procesos. Un número alto podría requerir de un aumento del thread_cache_size.
  • Created_tmp_disk_tables: el número de tablas temporales en el disco que fueron creados automáticamente por el servidor mientras ejecuta los enunciados. Un alto número en esta variable requeriría de un aumento de la variable tmp_table_size a fines de evitar que se deba recurrir al disco para las tablas temporales.
  • Key_reads: El número de lecturas físicas del key block desde el disco. Si este número es grande se debería aumentar  key_buffer_size para así disminuir la tasa de fallos de caché para los índices.

 

Otros factores a considerar

Con los principales aspectos ya revisados vamos a analizar unas últimas cuestiones que, en función del escenario, podrían también influenciar la performance general de la base de datos.

En primer lugar me gustaría mencionar a la carga del servidor que hospeda el motor MySQL. A modo de ejemplo es de destacar que una instalación de MySQL 5.5.20 tiene una performance un 24% inferior si se instala un antivirus (Nod32 4.2) y un firewall (Outpost Firewall v7) en el sistema, ambos con la configuración por defecto. La performance decae otro 20% aproximadamente si se compara a una instalación de Windows 2003 server original con una actualizada al día de la fecha mediante Windows Update. Bajo ningún concepto, salvo el de la performance, sugiero mantener un servidor sin actualizaciones del sistema, antivirus o firewall pero es sumamente importante tener en cuenta su influencia en el rendimiento del sistema y configurar estos aplicativos correctamente de forma de minimizarla.

El otro punto a analizar es el uso de otros tipos de caché. Más allá del Query Cache analizado anteriormente, también existen otros caches que en algunos escenarios brindarán una mejora superior. Si bien en bases de datos pequeñas su instalación puede no ser necesaria o hasta contraproducente desde el punto de vista administrativo, cada instalación deberá investigar los beneficios del Array cache, del APC cache, del File cache y del Memcached cache.

Me gustaría por último sugerir el sitio http://dev.mysql.com/doc/refman/5.0/es/mysql-optimization.html en donde podrán encontrar información muy valiosa con referencia a la optimización de bases de datos MySQL.

 

Resumen

A modo de resúmen me gustaría indicar cuáles son los factores más importantes que pueden modificarse en un sistema para obtener una mejora de la performance:

  1. Uso de un sistema operativo de 64bits: provee una mejora de aproximadamente 50% con respecto al mismo sistema de 32bits
  2. Uso de algún sistema operativo Linux: en las pruebas se utilizó Ubuntu Server 11.10 y demostró una performance muy superior a los demás
  3. Uso de un motor de MySQL de 64bits y actualizado: la mejora promedio será del orden del 25%
  4. Utilizar un procesador multinúcleo

Además sería importante optimizar todo lo posible las consultas que realiza la aplicación y tener en cuenta de no sobrecargar con diversas aplicaciones el servidor en donde se instalará el motor de base de datos.

 

Más información

Comments are closed.