Hoy en día, los desarrolladores continúan desarrollando e implementando aplicaciones utilizando la arquitectura LAMP (Linux®, Apache, MySQL y PHP/Perl). Sin embargo, los administradores de servidores a menudo tienen poco control sobre las aplicaciones mismas porque alguien más las escribió. Esta serie de tres partes analiza una serie de problemas de configuración del servidor que pueden afectar el rendimiento de las aplicaciones. Este artículo, la tercera y última parte de esta serie, se centrará en ajustar la capa de base de datos para lograr la máxima eficiencia.
Con respecto al ajuste de MySQL,
existen tres métodos para acelerar la velocidad de ejecución del servidor MySQL. El orden de eficiencia de menor a mayor es:
Reemplazar el hardware problemático. Ajuste la configuración del proceso MySQL. Optimice la consulta.
Reemplazar el hardware problemático suele ser nuestra primera consideración, principalmente porque las bases de datos pueden consumir muchos recursos. Pero esta solución sólo llega hasta cierto punto. De hecho, a menudo puedes duplicar la velocidad de tu unidad central de procesamiento (CPU) o disco y aumentar la memoria de 4 a 8 veces.
El segundo método consiste en ajustar el servidor MySQL (también conocido como mysqld). Ajustar este proceso significa asignar memoria apropiadamente y permitirle a mysqld saber a qué tipo de carga estará sujeto. Acelerar el funcionamiento del disco no es tan importante como reducir la cantidad de accesos necesarios al disco. De manera similar, garantizar que el proceso MySQL funcione correctamente significa que dedica más tiempo a atender consultas que a tareas en segundo plano, como trabajar con tablas de disco temporales o abrir y cerrar archivos. El objetivo de este artículo es ajustar mysqld.
El mejor enfoque es asegurarse de que la consulta se haya optimizado. Esto significa que se aplican índices apropiados a la tabla y las consultas se escriben de manera que aproveche al máximo las capacidades de MySQL. Aunque este artículo no cubre el ajuste de consultas (un tema que se ha tratado en muchos libros), sí configura mysqld para informar consultas que pueden necesitar ajuste.
Aunque se ha asignado el orden a estas tareas, aún debe prestar atención a la configuración del hardware y de mysqld para ajustar correctamente la consulta. Está bien si la máquina es lenta, he visto máquinas muy rápidas fallar debido a una carga pesada cuando se ejecutan consultas bien diseñadas porque mysqld estaba ocupado con mucho trabajo y no podía atender la consulta.
Registro de consultas lentas
En un servidor SQL, las tablas de datos se almacenan en el disco. Los índices proporcionan al servidor una forma de encontrar filas específicas de datos en una tabla sin tener que buscar en toda la tabla. Cuando se debe buscar en toda la tabla, se denomina exploración de tabla. En términos generales, es posible que solo desee obtener un subconjunto de los datos de la tabla, por lo que un escaneo completo de la tabla desperdiciará una gran cantidad de E/S de disco y, por lo tanto, mucho tiempo. Este problema se agrava cuando se deben unir datos, porque se deben comparar varias filas de datos en ambos lados de la combinación.
Por supuesto, los escaneos de tablas no siempre causan problemas; a veces es más eficiente leer la tabla completa que seleccionar un subconjunto de datos (el planificador de consultas en el proceso del servidor se utiliza para tomar estas decisiones). Si el índice se usa de manera ineficiente o no se puede usar en absoluto, ralentizará las consultas y este problema se volverá más significativo a medida que aumente la carga en el servidor y el tamaño de la tabla. Las consultas que tardan más en ejecutarse que un intervalo de tiempo determinado se denominan consultas lentas.
Puede configurar mysqld para registrar estas consultas lentas en un registro de consultas lentas con el nombre apropiado. Luego, los administradores revisarán este registro para ayudarlos a determinar qué partes de la aplicación requieren más investigación. El Listado 1 muestra la configuración que se debe realizar en my.cnf para permitir el registro de consultas lento.
Listado 1. Habilitando el registro de consultas lentas de MySQL
[mysqld]; habilita el registro de consultas lentas, por defecto 10 segundos-slow-queries; las consultas de registro tardan más de 5 segundoslong_query_time = 5; consultas de registro que no utilizan índices incluso si tardan menos de long_query_time en MySQL 4.1 y versiones posteriores; consultas-que-no-utilizan-índices
Estas tres configuraciones se utilizan juntas para registrar consultas que tardan más de 5 segundos en ejecutarse y no utilizan índices. Tenga en cuenta la advertencia sobre consultas de registro que no utilizan índices: debe utilizar MySQL 4.1 o superior. Los registros de consultas lentas se guardan en el directorio de datos de MySQL y se denominan hostname-slow.log. Si desea utilizar un nombre o ruta diferente, puede usar log-slow-queries = /new/path/to/file en my.cnf para lograrlo.
La mejor manera de leer registros de consultas lentas es mediante el comando mysqldumpslow. Al especificar la ruta al archivo de registro, puede ver una lista ordenada de consultas lentas, junto con la cantidad de veces que ocurren en el archivo de registro. Una característica muy útil es que mysqldumpslow elimina cualquier dato especificado por el usuario antes de comparar los resultados, por lo que las diferentes llamadas a la misma consulta se cuentan como una sola; esto puede ayudar a identificar la consulta que requiere más trabajo.
Consultas de almacenamiento en caché
Muchas aplicaciones LAMP dependen en gran medida de bases de datos pero ejecutan las mismas consultas una y otra vez. Cada vez que se ejecuta una consulta, la base de datos debe realizar el mismo trabajo: analizar la consulta, determinar cómo ejecutarla, cargar la información desde el disco y devolver los resultados al cliente. MySQL tiene una característica llamada caché de consultas, que almacena los resultados de la consulta (que se usarán más adelante) en la memoria. En muchos casos, esto mejorará enormemente el rendimiento. Sin embargo, el problema es que el almacenamiento en caché de consultas está deshabilitado de forma predeterminada.
Agregue query_cache_size = 32M a /etc/my.conf para habilitar una caché de consultas de 32 MB.
Monitoreo de la caché de consultas
Después de habilitar la caché de consultas, es importante comprender si se está utilizando de manera efectiva. MySQL tiene varias variables que puedes consultar para comprender qué sucede en el caché. El Listado 2 muestra el estado del caché.
Listado 2. Visualización de estadísticas de caché de consultas
mysql> MOSTRAR ESTADO COMO 'qcache%';+-------------------------+------------+ | Nombre_variable | Valor |+------------------------+------------+| Qcache_free_blocks | | Qcache_free_memory || Qcache_hits || Qcache_inserts || Qcache_total_blocks |+------------- --- ----- ---+----------------+8 filas en conjunto (0,00 segundos)
La explicación de estos ítems se muestra en la Tabla 1.
Tabla 1. Descripción del nombre de la variable de la variable de caché de consultas de MySQL
Qcache_free_blocks El número de bloques de memoria adyacentes en la caché. Un número grande indica que puede haber fragmentos. FLUSH QUERY CACHE desfragmenta el caché para obtener un bloque libre.
Qcache_free_memory Memoria libre en caché.
Qcache_hits se incrementa cada vez que una consulta llega al caché.
Qcache_inserts se incrementa cada vez que se inserta una consulta. La tasa de errores es el número de aciertos dividido por el número de inserciones; reste este valor de 1 para obtener la tasa de aciertos. En el ejemplo anterior, aproximadamente el 87% de las consultas llegan al caché.
Qcache_lowmem_prunes La cantidad de veces que el caché se quedó sin memoria y tuvo que ser purgado para dejar espacio para más consultas. Este número se ve mejor durante un largo período de tiempo; si el número está creciendo, puede indicar una fragmentación severa o poca memoria. (Los free_blocks y free_memory anteriores pueden indicarle en qué caso se trata).
Qcache_not_cached El número de consultas que no son adecuadas para el almacenamiento en caché, normalmente porque no son sentencias SELECT.
Qcache_queries_in_cache El número de consultas (y respuestas) actualmente almacenadas en caché.
Qcache_total_blocks Número de bloques en la caché.
A menudo, la diferencia se puede ver mostrando estas variables con unos segundos de diferencia, lo que puede ayudar a determinar si el caché se está utilizando de manera eficiente. La ejecución de FLUSH STATUS puede restablecer algunos contadores, lo que puede resultar muy útil si el servidor ha estado funcionando durante un tiempo.
Es muy tentador utilizar un caché de consultas muy grande y esperar almacenarlo todo en caché. Debido a que mysqld debe realizar mantenimiento en el caché, como realizar poda cuando la memoria es baja, el servidor puede atascarse al intentar administrar el caché. Como regla general, si FLUSH QUERY CACHE tarda mucho tiempo, la caché es demasiado grande.
Hacer cumplir los límites
Puede hacer cumplir los límites en mysqld para garantizar que la carga del sistema no provoque el agotamiento de los recursos. El Listado 3 muestra algunas configuraciones importantes relacionadas con recursos en my.cnf.
Listado 3. Configuración de recursos MySQL
set-variable=max_connections=500set-variable=wait_timeout=10max_connect_errors = 100
El número máximo de conexiones se gestiona en la primera línea. Al igual que MaxClients en Apache, la idea es garantizar que solo se realice la cantidad de conexiones permitidas por el servicio. Para determinar el número máximo de conexiones establecidas actualmente en el servidor, ejecute MOSTRAR ESTADO COMO 'max_used_connections'.
La línea 2 le dice a mysqld que finalice cualquier conexión que haya estado inactiva durante más de 10 segundos. En una aplicación LAMP, el tiempo que lleva conectarse a la base de datos suele ser el tiempo que le toma al servidor web procesar la solicitud. A veces, si la carga es demasiado pesada, la conexión se colgará y ocupará espacio en la mesa de conexiones. Si tiene varios usuarios interactivos o utiliza conexiones persistentes a la base de datos, no es recomendable establecer este valor más bajo.
La última línea es un método seguro. Si un host tiene problemas para conectarse al servidor y lo vuelve a intentar muchas veces antes de darse por vencido, el host se bloqueará y no podrá ejecutarse hasta después de FLUSH HOSTS. De forma predeterminada, 10 fallos son suficientes para provocar un bloqueo. Cambiar este valor a 100 le dará al servidor tiempo suficiente para recuperarse del problema. Si la conexión no se puede establecer después de 100 intentos, entonces usar un valor más alto no ayudará mucho y es posible que no se conecte en absoluto.
Búfers y almacenamiento en caché
MySQL admite más de 100 configuraciones ajustables pero, afortunadamente, dominar algunas satisfará la mayoría de las necesidades; Para encontrar los valores correctos para estas configuraciones, puede ver las variables de estado mediante el comando MOSTRAR ESTADO, que puede determinar si mysqld está funcionando como esperamos. La memoria asignada a los buffers y cachés no puede exceder la memoria disponible en el sistema, por lo que el ajuste generalmente requiere algún compromiso.
Las configuraciones ajustables de MySQL se pueden aplicar a todo el proceso mysqld o a sesiones de clientes individuales.
Configuración del lado del servidor
Cada tabla se puede representar como un archivo en el disco, que debe abrirse primero y luego leerse. Para acelerar el proceso de lectura de datos de archivos, mysqld almacena en caché estos archivos abiertos hasta un número máximo especificado por table_cache en /etc/mysqld.conf. El Listado 4 muestra una forma de mostrar la actividad relacionada con la apertura de una mesa.
Listado 4. Mostrando actividades que abren tablas
mysql> MOSTRAR ESTADO COMO 'open%tables';+---------------+-------+Valor de variable |+-------- -------+-------+| Tablas_abiertas || Tablas_abiertas 195 |+---------------+------- +2 filas en conjunto (0,00 seg)
El Listado 4 muestra que actualmente hay 5000 tablas abiertas y es necesario abrir 195 tablas porque no hay descriptores de archivos disponibles en la caché (dado que las estadísticas se borraron anteriormente, es posible que solo haya 5000 tablas abiertas). . Si Opened_tables aumenta rápidamente al volver a ejecutar el comando SHOW STATUS, indica que la tasa de aciertos de la caché es insuficiente. Si Open_tables es mucho más pequeño que la configuración de table_cache, el valor es demasiado grande (pero tener espacio para crecer nunca es malo). Por ejemplo, use table_cache = 5000 para ajustar el caché de la tabla.
De manera similar al caché de la tabla, también hay un caché para subprocesos. mysqld genera subprocesos según sea necesario al recibir conexiones. En un servidor ocupado donde las conexiones cambian rápidamente, almacenar en caché los subprocesos para su uso posterior puede acelerar la conexión inicial.
El Listado 5 muestra cómo determinar si se almacenan en caché suficientes subprocesos.
Listado 5. Visualización de estadísticas de uso de subprocesos
mysql> MOSTRAR ESTADO COMO 'threads%';+-------------------+--------+| Nombre_variable |+---- ---------------+--------+| Hilos_en caché || Hilos_conectados || Hilos_creados || ---------------+--------+4 filas en conjunto (0,00 seg)
El valor importante aquí es Threads_created, este valor se incrementa cada vez que mysqld necesita crear un nuevo hilo. Si este número aumenta rápidamente al ejecutar comandos MOSTRAR ESTADO sucesivos, debería intentar aumentar la caché de subprocesos. Por ejemplo, puedes usar thread_cache = 40 en my.cnf para lograr esto.
El búfer de claves contiene el bloque de índice de la tabla MyISAM. Idealmente, las solicitudes de estos bloques deberían provenir de la memoria y no del disco. El Listado 6 muestra cómo determinar cuántos bloques se leyeron del disco y cuántos se leyeron de la memoria.
Listado 6. Determinación de la eficiencia de las palabras clave
mysql> muestra el estado como '%key_read%';+-------------------+-----------+| Nombre_variable |+ ------------------+-----------+| Solicitudes de lectura de claves || --+-----------+2 filas en conjunto (0,00 segundos)
Key_reads representa la cantidad de solicitudes que llegan al disco y Key_read_requests es el número total. La tasa de errores es la cantidad de solicitudes de lectura que llegan al disco dividida por la cantidad total de solicitudes de lectura; en este caso, aproximadamente 0,6 errores en la memoria por cada 1000 solicitudes. Si el número de visitas al disco supera 1 por cada 1000 solicitudes, debería considerar aumentar el búfer de palabras clave. Por ejemplo, key_buffer = 384M establecerá el búfer en 384 MB.
Las tablas temporales se pueden utilizar en consultas más avanzadas donde los datos deben guardarse en una tabla temporal antes de su posterior procesamiento (como una cláusula GROUP BY, idealmente la tabla temporal se crea en la memoria); Pero si la tabla temporal se vuelve demasiado grande, es necesario escribirla en el disco. El Listado 7 proporciona estadísticas relacionadas con la creación de tablas temporales.
Listado 7. Determinando el uso de tablas temporales
mysql> MOSTRAR ESTADO COMO 'created_tmp%';+--------------------------+---------------+| Nombre_variable | ||------------------------+-------+| Creado_tmp_disk_tables || Creado_tmp_files || | +---------------------+---------------+3 filas en conjunto (0,00 seg)
Create_tmp_tables aumentará cada vez que se utilice una tabla temporal; Create_tmp_disk_tables también aumentará para las tablas basadas en disco. No existen reglas estrictas para esta relación, ya que depende de la consulta de que se trate. Observar Create_tmp_disk_tables a lo largo del tiempo le mostrará la proporción de tablas de discos creadas y podrá determinar la eficiencia de su configuración. Tanto tmp_table_size como max_heap_table_size controlan el tamaño máximo de las tablas temporales, así que asegúrese de que ambos valores estén configurados en my.cnf.
Configuración por sesión
Las siguientes configuraciones son específicas de cada sesión. ¡Tenga mucho cuidado al configurar estos números porque al multiplicarlos por la cantidad de conexiones que pueden existir, estas opciones representan una gran cantidad de memoria! Puede modificar estos números dentro de una sesión mediante código o modificar esta configuración en my.cnf para todas las sesiones.
Cuando MySQL debe ordenar, asigna un búfer de clasificación para contener las filas de datos a medida que se leen desde el disco. Si los datos a ordenar son demasiado grandes, deben guardarse en un archivo temporal en el disco y ordenarse nuevamente. Si la variable de estado sort_merge_passes es grande, esto indica actividad del disco. El Listado 8 muestra información del contador de estado relacionada con la clasificación.
Listado 8. Mostrando estadísticas de clasificación
mysql> MOSTRAR ESTADO COMO "sort%";+------------------+---------+| Nombre_variable |+--- ----------------+--------+| Sort_merge_passes || Sort_range || Sort_rows || ------------------+---------+4 filas en conjunto (0,00 segundos)
Si sort_merge_passes es grande, significa que debes prestar atención a sort_buffer_size. Por ejemplo, sort_buffer_size = 4M establece el búfer de clasificación en 4 MB.
MySQL también asigna algo de memoria para leer la tabla. Idealmente, un índice proporciona suficiente información para leer solo las filas que necesita, pero a veces una consulta (mal diseñada o debido a la naturaleza de los datos) necesita leer una gran cantidad de datos de la tabla. Para comprender este comportamiento, necesita saber cuántas instrucciones SELECT se ejecutaron y cuántas veces fue necesario leer la siguiente fila de datos de la tabla (en lugar de acceder directamente a través del índice). El comando para lograr esta funcionalidad se muestra en el Listado 9.
Listado 9. Determinación del ratio de escaneo de la tabla
mysql> MOSTRAR ESTADO COMO "com_select";+---------------+--------+| Nombre_variable |+--------- ------+--------+| Com_select | 318243 |+----------+----------------+1 fila en el conjunto (0,00 seg) mysql> MOSTRAR ESTADO COMO "handler_read_rnd_next";+-----------------------+----------- +| Nombre_variable | |+-----------------------+-----------+| Handler_read_rnd_next |+- ----- ------------------+----------+1 fila en conjunto (0,00 seg)
Handler_read_rnd_next/Com_select da como resultado una relación de escaneo de tabla, en este caso 521:1. Si el valor excede 4000, debe verificar read_buffer_size, por ejemplo read_buffer_size = 4M. Si este número supera los 8 millones, ¡es hora de discutir el ajuste de estas consultas con los desarrolladores!
3 Herramientas esenciales
Aunque el comando MOSTRAR ESTADO puede ser muy útil para comprender una configuración específica, también necesitará algunas herramientas para interpretar las grandes cantidades de datos proporcionados por mysqld. Hay tres herramientas que considero esenciales; puede encontrar enlaces a ellas en la sección Recursos.
La mayoría de los administradores de sistemas están familiarizados con el comando top, que proporciona una vista continuamente actualizada de la CPU y la memoria consumidas por las tareas. mytop emula a top; proporciona una vista de todos los clientes conectados y las consultas que están ejecutando. mytop también proporciona datos históricos y en vivo sobre la eficiencia del búfer de palabras clave y del caché de consultas, así como estadísticas sobre consultas en ejecución. Esta es una herramienta útil para ver qué sucede en su sistema (digamos en 10 segundos), puede obtener una vista de la información del estado del servidor y mostrar cualquier conexión que esté causando problemas.
mysqlard es un demonio conectado al servidor MySQL, responsable de recopilar datos cada 5 minutos y almacenarlos en una base de datos Round Robin en segundo plano. Hay una página web que muestra datos como el uso de la caché de tablas, la eficiencia de las palabras clave, los clientes conectados y el uso de tablas temporales. Aunque mytop proporciona una instantánea de la información sobre el estado del servidor, mysqlard proporciona información sobre el estado a largo plazo. Como beneficio adicional, mysqlard utiliza parte de la información que ha recopilado para dar algunas sugerencias sobre cómo ajustar el servidor.
Otra herramienta para recopilar información de MOSTRAR ESTADO es mysqlreport. Sus informes son mucho más complejos que los de mysqlard porque es necesario analizar cada aspecto del servidor. Esta es una gran herramienta para ajustar su servidor porque realiza cálculos apropiados sobre las variables de estado para ayudar a determinar qué problemas deben solucionarse.
Conclusión
Este artículo introdujo algunos conocimientos básicos sobre el ajuste de MySQL y concluyó esta serie de 3 partes sobre el ajuste de componentes LAMP. El ajuste implica en gran medida comprender cómo funcionan los componentes, determinar si funcionan correctamente, hacer algunos ajustes y reevaluarlos. Cada componente (Linux, Apache, PHP o MySQL) tiene varios requisitos. Comprender cada componente individualmente puede ayudar a reducir los cuellos de botella que pueden ralentizar su aplicación.