Cedido por MySQL Hispano.
Como resultado del creciente papel de MySQL en grandes organizaciones, su uso se está convirtiendo más y más de alto rendimiento. Esto significa, por supuesto, que MySQL necesita proporcionar más capacidad de respuesta, alto rendimiento, y fiabilidad. Ya conocido en la industria por ser un servidor de bases de datos increíblemente rápido, muy a menudo MySQL está ya preparado para la tarea directamente después de instalarlo. Sin embargo hay un par de cosas que pueden fácilmente hacerlo lento, aunque no es extraño, que esto se deba también a un pobre diseño de la aplicación. Otras veces la configuración por defecto de MySQL no es lo suficientemente buena como para realizar la tarea que tiene entre manos. Y otras veces se necesita tener un poco más de hardware.
Cuando intentamos hacer más rápida una aplicación de bases de datos, hay que empezar con la aplicación en sí y asegurarse de que las tablas están normalizadas de forma adecuada, y las columnas están indexadas, esto es siempre un buen comienzo. Pero si ya se ha hecho todo lo anterior y las cosas siguen siendo lentas, llega el momento de echarle un ojo al servidor MySQL en sí.
No es tecnología aeroespacial
Aunque puede ser que suene intimidante, el ajuste del rendimiento sólo trata de sacar el mayor rendimiento posible de un sistema. Para hacer esto es necesario entender cuales son las variables que están involucradas y como pueden afectar el buen funcionamiento del servidor.
Antes de entrar en los detalles, vale la pena reiterar un hecho importante: las técnicas que se van a mostrar no arreglarán búsquedas mal escritas o sin optimizar, un mal diseño de la base de datos, u otros problemas del diseño de la aplicación. Puede que sirvan para aliviar el esfuerzo de un servidor ocupado, pero simplemente se está posponiendo lo inevitable. La única solución a una aplicación mal escrita o un diseño pobre de la base de datos es irse al código y arreglarlo. Realmente, arreglar las consultas lentasy/o una aplicación pobremente diseñada conseguirá mejores resultados que perder el tiempo con el ajuste del servidor.
Si no se está seguro por donde empezar, se tiene que habilitar el archivo de registro para las búsquedas lentas (slow query log) tal y como se explica en el manual de MySQL, y luego simplemente observar y revisar cualquier búsqueda que no se esperaba que sea lenta. También es posible que se encuentren algunas búsquedas rápidas en el archivo de las búsquedas lentas. Esto se debe a que MySQL considera cualquier búsqueda como lenta si no usa un índice.
Uso de memoria
Del lado del servidor, el único y más importante factor en determinar cómo de bien rendirá MySQL, es la memoria. MySQL es capaz de ejecutar varios subprocesos a la vez. Esto significa que cada vez que se realiza una conexión, MySQL crea un subproceso. Cada subproceso consume memoria. El almacenamiento en caché de los resultados también consume memoria. Se puede pensar entonces, que entre más memoria tengamos en el servidor, será lo mejor. Sin embargo no es suficiente con tener mucha memoria disponible, es necesario indicarle a MySQL como queremos que use la memoria.
Las configuraciones por defecto de MySQL son bastante conservadoras para el hardware de hoy en día, sin embargo, si se tiene un servidor MySQL dedicado con varios cientos de mega bytes de RAM, se debe ser capaz de darle a MySQL una porción bastante grande de ella para trabajar. Por defecto, sólo usará una pequeña porción de lo que haya disponible; esto se debe a que no hay ninguna forma de saber si está corriendo en un servidor dedicado donde será usado de forma continua o si está corriendo en un esforzado portátil donde sólo se usa para almacenar una pequeña aplicación.
Mucha de la información presentada a continuación se centrará en el uso de la memoria y se asume que se está usando el tipo de tabla por defecto de MySQL, MyISAM. Actualmente existen otros tipos de tablas transaccionales más avanzadas, tales como InnoDB o Gemini.
MySQL usa la memoria para una variedad de búfferes internos y cachés que influyen en el número de veces que se ha de acceder a archivos que residen en el disco. Cuanto más a menudo tenga que esperar a que responda un disco, más lento será. Aún los discos duros más modernos siguen siendo un orden de magnitud más lentos que la memoria RAM, y dado la reciente baja en los precios de la memoria, es muy factible que se pueda añadir más memoria al servidor y así acelerar los procesos. Actualizar a discos duros más rápidos debería ser la última opción.
Los búfferes y cachés de MySQL son de dos tipos: globales, y por hilo.
Globales: tal y como sugiere el nombre, estas áreas de memoria son reservadas una vez y son compartidas a través de todos los hilos de MySQL. Dos de los más importantes son el búffer de claves y la caché de tablas. Debido a que son búfferes compartidos, el objetivo es que sean lo más grandes posibles.
Por hilo: estos búfferes reservan memoria individualmente a medida que necesitan realizar operaciones particulares, tales como ordenar o agrupar datos. A propósito, la mayoría de los búfferes MySQL se reservan en esta forma.
A continuación se examina primero que función tienen cada uno de los búfferes y como configurar e inspeccionar sus valores, posteriormente se mostrará como examinar contadores de rendimiento de MySQL y juzgar si los cambios que se realizan tienen implicaciones o no.
Búffer de claves
El búffer de claves es donde MySQL cachea los bloques de índices para tablas MyISAM. Cada vez que una búsqueda usa un índice, MySQL mirará antes de nada a ver si el índice relevante está o no en memoria. El parámetro key_buffer en el archivo my.cnf determina que tan grande puede ser este búffer. Una vez que el búffer este lleno, MySQL hará sitio para nuevos datos reemplazando datos antiguos que no hayan sido usados recientemente.
El tamaño del búffer de claves aparece como key_buffer_size en la salida de SHOW VARIABLES. Con un búfferde claves 384 Mega Bytes, se vería algo como:
key_buffer_size 402649088
como una recomendación general, en un servidor MySQL dedicado se debería reservar entre el 20 y el 50 por ciento de la memoria RAM para el búffer de claves de MySQL. Si se tiene un giga byte de memoria se puede empezar con algo como:
set-variable= key_buffer= 128M
ó incluso:
set-variable= key_buffer= 256M
Si sólo se permitiera modificar un parámetro en el servidor MySQL, el búffer de claves sería lo primero que se tendría que considerar. Los índices son también muy importantes para el rendimiento global de cualquier servidor de bases de datos por lo que es difícil equivocarse al hacer más espacio en su memoria para ellos.
Si no se especifica un tamaño al búffer de claves, MySQL usará su tamaño por defecto que está cerca de los 8MB. Pero claro, tiene muy poco sentido configurar el valor del búffer de claves tan alto, hacerlo podría matar de hambre al sistema operativo respecto a la memoria que necesita para escrituras de disco y otras tareas.
Caché de tablas
Las tablas MyISAM están compuestas de tres archivos en disco:
El archivo de datos nombredetabla.MYD, el archivo índice nombredetabla.MYI, y finalmente, el archivo de definición de la tabla llamado nombredetabla.FRM. Para poder usar una única tabla, MySQL necesita de hecho abrir los tres archivos. El archivo .FRM se cerrará después de que lea el esquema, pero los demás permanecerán abiertos, MySQL no los cerrará hasta que lo necesite. Esto evita una sobrecarga asociada con la apertura y cierre de los archivos si la tabla se usa frecuentemente. Los archivos normalmente no se suelen cerrar hasta que ocurre uno de los siguientes eventos:
- La tabla se ha cerrado de forma explícita mediante FLUSH TABLES.
- La tabla se ha desechado
- El servidor esta siendo reiniciado
- El número total de tablas abiertas ha alcanzado el valor del parámetro table_cache
El último evento es particularmente importante si se tienen muchas tablas que se usan a menudo entre todas las bases de datos. El valor por defecto de table_cache es de 64, así que si se tienen unos cientos de tablas que se usen de forma activa, MySQL va a desperdiciar mucho tiempo y esfuerzo abriendo y cerrando innecesariamente estos archivos.
Incrementar el tamaño de la caché de tablas ciertamente ayudará en esta situación, pero se debe tener cuidado de no hacer el valor demasiado grande, ya que todos los sistemas operativos tienen un límite en el número de los archivos abiertos por un mismo proceso. De hecho. algunos también tienen limitado el número total de archivos abiertos que puede tener un único usuario. Si MySQL intenta abrir demasiados archivos, el sistema operativo se negará a permitirlo y MySQL generará un mensaje de error en el archivo de registro de errores. Ante la duda, se tienen que comprobar las limitaciones del sistema operativo.
En casos extremos, se puede incrementar el número de descriptores de archivos disponibles por medio de las opciones de configuración del kernel. Los descriptores de archivos abiertos están reservados por un únicoproceso y compartidos por todos sus hilos. Al contrario que muchos de los demás parámetros, la caché de tablas se aplica a todos los tipos de tablas basadas en disco de MySQL.
Búfferes de registro
Siempre que MySQL ha de escanear una tabla, el hilo que realiza el escaneo reservará un búffer de registro para cada tabla que ha de escanear. Esto sucede típicamente cuando MySQL decide que es más eficiente escanear la tabla que usar un índice para una búsqueda. También ocurre cuando simplemente no hay un índice que se pueda usar.
Al incrementar el valor de record_buffer en el archivo my.cnf, se permite que MySQL lea las tablas en trozos más grandes. Es probable que esto reduzca el número de búsquedas en el disco y haga que el escaneo sea significativamente más rápido en un servidor muy atareado.
Sin embargo, se tiene que ser muy cuidadoso con el búffer de registro si se tienen muchos clientes que realizan búsquedas completas sobre tablas. Debido a que el búffer de registro se reserva por cada hilo, se puede acabar en una situación donde clientes individuales hagan que se reserven búfferes de registro al mismo tiempo. Si el resto de la memoria está limitada es probable que se empiece a hacer uso de la memoria de intercambio y se verá dramáticamente reducido el rendimiento. En la versión 3.23.41 se introdujo un parámetro relacionado denominado record_rnd_buffer.
Al igual que record_buffer, se usa para escanear un gran número de filas. El record_rnd_buffer se usa para búsquedas que resultan en una ordenación intermedia del archivo además de algunas lecturas de registro no secuenciales. Afortunadamente, si no se fija el valor de record_rnd_buffer se establecerá por defecto el valor de record_buffer.
Búffer de ordenación
Tal y como implica su nombre, el búffer de ordenación se usa para responder a búsquedas que involucren elordenamiento de los datos -aquellas con una sentencia ORDER BY en ellas. Además, el búffer de ordenaciónse usa para las búsquedas que involucren agrupar datos -aquellas con una sentencia GROUP BY. Al igual quelos demás búfferes que se han visto, el búffer de ordenación es relativamente pequeño por defecto. Al ajustar la entrada de sort_buffer en el archivo my.cnf:
set-variable= sort_buffer= 8M
Puedes reducir dramáticamente la cantidad de tiempo que se usa para ordenar grandes grupos de resultados.El búffer de ordenación aparece como sort_buffer en la sálida de SHOW VARIABLES, por ejemplo:
sort_buffer 8388600
El mismo tipo de aviso se aplica al búffer de ordenación que para el búffer de registros. Es un búffer que MySQL reserva frecuentemente y se reserva por hilo. Así que, hay que incrementarlo con cuidado en un servidor que ejecute muchas búsquedas concurrentes.
Guías generales de ajusteo
Antes de discutir como medir o juzgar los efectos de cualquier cambio que se realice, se debe considerar brevemente un acercamiento a la afinación del rendimiento. Hay unas cuantas cosas que se deben tener en mente cuando se empiezan hacer y probar cambios:
- Sólo cambiar un parámetro cada vez. Puede que los cambios no resulten siempre en el comportamiento esperado. Si se cambian demasiados parámetros a la vez, se corre el riesgo de asignar un cambio en el comportamiento al parámetro equivocado.
- No hacer cambios en sistemas en producción. Si es del todo posible, se debe tener un servidor de pruebas disponible que sea parecido en naturaleza al servidor de bases de datos de producción. Hacer cambios en la configuración de MySQL seguramente requerirá que se pare y reinicie el servidor, lo que hará que los usuarios experimenten interrupciones en el servicio.
- Usar datos reales. El tipo de datos que se estén usando afecta a como responde MySQL a las búsquedas. Idealmente, se debería usar una copia de las bases de datos de producción. Si no es posible hacer esto; entonces se debería intentar construir un subconjunto representativo de datos.
- Realizar pruebas realistas. Es fácil asumir que se sabe que pruebas aplicar simplemente porque se sabecuales son las áreas problemáticas. Sin embargo, algunos cambios de la configuración acelerán partes lentas de una aplicación al mismo tiempo que ralentizan cosas que antes eran bastante rápidas.
- Ser sistemático y registrar descubrimientos. Es importante que se mantenga la pista de los cambios quese realizan y como afectan al rendimiento. Después de varias horas (o incluso días) de pruebas, es más que probable que no se recuerede exactamente que es lo que se ha cambiado y si los cambios fueron positivos o negativos.
Observando los números de rendimiento de la base de datos
Con los pocos puntos de partida en mente y un concepto de cómo hacer pruebas, ahora se debe considerar cómo monitorizar el progreso. Afortunadamente, MySQL tiene más de 50 contadores internos (o variables de estado), que mantienen la pista de cuántas veces ocurren varios tipos de eventos.
Dado que el espacio en este artículo sirve para comentar solamente algunas de las variables de estado de MySQL, en el manual de MySQL se describen todas y cada una de ellas en mayor detalle. Para ver estos números, se puede usar la sentencia SHOW STATUS. En este caso se mencionan únicamente las variables relacionadas con el búffer de claves:
SHOW STATUS LIKE 'Key%' Key_read_requests 3844786889 key_reads 16525182 Key_write_requests 303516563 Key_writes 152315649
Estas cuatro variables dicen mucho sobre el rendimiento del búffer de claves de MySQL. Cada vez que MySQL sea capaz de leer una clave (o índice) del búffer de claves (en vez de ir a disco), incrementará automáticamente el valor de key_read_requests. Si MySQL ha de leer la clave del disco porque no estaba ya en la caché, incrementará key_reads. La misma lógica se aplica para las escrituras de disco. Sabiendo esto, podemos calcular la eficiencia (o hit rate) para el búffer de claves.
Usando una fórmula como:
100 - ((Key_reads / Key_read_requests) * 100)
podemos obtener un porcentaje que representa cómo a menudo es capaz MySQL de leer las claves directamente de la caché en vez de irse a disco. Cuanto más cerca esté el valor de 100, mucho mejor. Usando los números de arriba, se tiene un hit rate de cerca del 99.57 por ciento. Generalmente, suele ser una buena idea mantener este porcentaje por encima del 90 por ciento. A fin de cuentas, de lo que se trata, es de tener una mejora medible del rendimiento de MySQL.
Observando los números de rendimiento del sistema
Monitorear los cambios de rendimiento en MySQL es sólo una parte de la labor, también es necesario ver qué es lo que está pasando desde el punto de vista del sistema operativo, ya que como cualquier otra aplicación, está a merced de lo que el sistema operativo quiera permitirle hacer, así que es importante que se mantenga una vista global sobre toda la actividad del sistema operativo.
Se debe tener una idea de la actividad actual del sistema y características del rendimiento de MySQL antes de empezar a hacer pruebas. Sin una base para la comparación, realmente no se sabrá como ha cambiado el impacto de MySQL en el sistema. Finalmente, cabe mencionar que únicamente se ha descrito una mínima parte de lo que representa el rendimiento en el lado del servidor para MySQL. El manual de MySQL contiene muchas otras ideas sobre cómo incrementar el rendimiento de MySQL y monitorizar los progresos.