Solución de problemas de memoria baja en Azure Database for MySQL: servidor flexible
Para ayudar a garantizar que una instancia de servidor flexible de Azure Database for MySQL funciona de forma óptima, es muy importante tener la asignación y el uso de memoria adecuados. De manera predeterminada, al crear una instancia de servidor flexible de Azure Database for MySQL, la memoria física disponible depende del nivel y el tamaño que seleccione para la carga de trabajo. Además, se asigna memoria para los búferes y las memorias caché para mejorar las operaciones de la base de datos. Para más información, consulte Uso de la memoria en MySQL.
El servidor flexible de Azure Database for MySQL consume memoria para lograr la mayor cantidad de aciertos de caché posible. Como resultado, el uso de memoria a menudo puede mantenerse entre el 80 % y el 90 % de la memoria física disponible de una instancia. A menos que haya un problema con el progreso de la carga de trabajo de consulta, no es un problema. Pero puede encontrarse con problemas de memoria insuficiente por motivos como los siguientes:
- Se han configurado búferes demasiado grandes.
- Consultas poco óptimas en ejecución.
- Consultas que realizan combinaciones y ordenan grandes conjuntos de datos.
- Se han establecido las conexiones máximas de un servidor de base de datos en un valor demasiado alto.
La mayoría de la memoria de un servidor la usan los búferes y la memoria caché globales de InnoDB, que incluyen componentes como innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size y query_cache_size.
El valor del parámetro innodb_buffer_pool_size especifica el área de memoria en la que InnoDB almacena en caché las tablas de base de datos y los datos relacionados con los índices. MySQL intenta acomodar tantos datos relacionados con las tablas y los índices en el grupo de búferes como sea posible. Un grupo de búferes más grande requiere que se desvíen al disco menos operaciones de E/S.
Controla el uso de la memoria
El servidor flexible de Azure Database for MySQL proporciona una variedad de métricas para medir el rendimiento de la instancia de base de datos. Para comprender mejor el uso de memoria del servidor de bases de datos, examine las métricas Porcentaje de memoria del host o Porcentaje de memoria.
Si observa que el uso de memoria ha aumentado repentinamente y que la memoria disponible disminuye rápidamente, supervise otras métricas, como Porcentaje de CPU del host, Conexiones totales y Porcentaje de E/S, para determinar si un pico repentino en la carga de trabajo es el origen del problema.
Es importante tener en cuenta que cada conexión establecida con el servidor de bases de datos necesita la asignación de cierta cantidad de memoria. Como resultado, un aumento en las conexiones de base de datos puede provocar escasez de memoria.
Causas de un uso elevado de memoria
Ahora se examinarán algunas causas más del uso elevado de memoria en MySQL. Estas causas dependen de las características de la carga de trabajo.
Aumento de las tablas temporales
MySQL usa "tablas temporales", que son un tipo especial de tabla diseñada para almacenar un conjunto de resultados temporal. Las tablas temporales se pueden reutilizar varias veces durante una sesión. Dado que las tablas temporales creadas son locales para una sesión, las distintas sesiones pueden tener tablas temporales diferentes. En los sistemas de producción con muchas sesiones que realizan compilaciones de grandes conjuntos de resultados temporales, debe comprobar periódicamente el contador de estado global created_tmp_tables, que realiza un seguimiento del número de tablas temporales que se crean durante las horas punta. Un gran número de tablas temporales en memoria puede provocar rápidamente una memoria baja disponible en una instancia de servidor flexible de Azure Database for MySQL.
Con MySQL, el tamaño de la tabla temporal viene determinado por los valores de dos parámetros, como se describe en la tabla siguiente.
| Parámetro | Descripción |
|---|---|
| tmp_table_size | Especifica el tamaño máximo de las tablas temporales internas en memoria. |
| max_heap_table_size | Especifica el tamaño máximo al que pueden crecer las tablas MEMORY creadas por el usuario. |
Nota
Al determinar el tamaño máximo de una tabla temporal interna en memoria, MySQL considera el menor de los valores establecidos para los parámetros tmp_table_size y max_heap_table_size.
Recomendaciones
Para solucionar problemas de poca memoria relacionados con las tablas temporales, tenga en cuenta las siguientes recomendaciones.
- Antes de aumentar el valor de tmp_table_size, compruebe que la base de datos esté indexada correctamente, especialmente para las columnas implicadas en operaciones de combinación y agrupación. El uso de los índices adecuados en las tablas subyacentes limita el número de tablas temporales que se crean. Aumentar el valor de este parámetro y el parámetro max_heap_table_size sin comprobar los índices puede permitir que las consultas ineficaces se ejecuten sin índices y creen más tablas temporales de las necesarias.
- Ajuste los valores de los parámetros max_heap_table_size y tmp_table_size para satisfacer las necesidades de la carga de trabajo.
- Si los valores establecidos para los parámetros max_heap_table_size y tmp_table_size son demasiado bajos, las tablas temporales se pueden volcar regularmente en el almacenamiento y agregar latencia a las consultas. Puede realizar un seguimiento del volcado de tablas temporales en el disco mediante el contador de estado global created_tmp_disk_tables. Al comparar los valores de las variables created_tmp_disk_tables y created_tmp_tables, se ve el número de tablas temporales internas en disco creadas con el número total de tablas temporales internas creadas.
Caché de tablas
Como sistema multiproceso, MySQL mantiene una caché de descriptores de archivos de tablas para que varias sesiones puedan abrir simultáneamente las tablas de forma independiente. MySQL usa cierta cantidad de descriptores de archivo del sistema operativo y de memoria para mantener esta caché de tablas. La variable table_open_cache define el tamaño de la caché de tablas.
Recomendaciones
Para solucionar problemas de poca memoria relacionados con las caché de tablas, tenga en cuenta las siguientes recomendaciones.
- El parámetro table_open_cache especifica el número de tablas abiertas para todos los subprocesos. Aumentar este valor aumenta el número de descriptores de archivo que requiere mysqld. Puede comprobar si necesita aumentar la caché de tablas comprobando la variable de estado opened_tables en el contador show global status. Aumente el valor de este parámetro en incrementos para dar cabida a la carga de trabajo.
- Es posible que establecer table_open_cache en un valor demasiado bajo haga que el servidor flexible de Azure Database for MySQL dedique más tiempo a abrir y cerrar tablas necesarias para el procesamiento de consultas.
- Es posible que establecer este valor demasiado alto provoque el uso de más memoria y que el sistema operativo se quede sin descriptores de archivo, lo que conduce a conexiones rechazadas o a que se produzcan errores al procesar las consultas.
Otros búferes y la caché de consultas
Al solucionar problemas relacionados con poca memoria, puede trabajar con algunos búferes más y una memoria caché para ayudar con la resolución.
Búfer de red (net_buffer_length)
El búfer de red es el tamaño de los búferes de conexión y subprocesos para cada subproceso de cliente y puede crecer hasta el valor especificado para max_allowed_packet. Si una instrucción de consulta es grande, por ejemplo, todas las inserciones o actualizaciones tienen un valor muy grande, aumentar el valor del parámetro net_buffer_length ayudará a mejorar el rendimiento.
Búfer de combinación (join_buffer_size)
El búfer de combinación se asigna para almacenar en caché filas de tablas cuando una combinación no puede usar un índice. Si la base de datos tiene muchas combinaciones realizadas sin índices, considere la posibilidad de agregar índices para tener combinaciones más rápidas. Si no puede agregar índices, considere la posibilidad de aumentar el valor del parámetro join_buffer_size, que especifica la cantidad de memoria asignada por conexión.
Búfer de ordenación (sort_buffer_size)
El búfer de ordenación se usa para realizar ordenaciones para algunas consultas ORDER BY y GROUP BY. Si ve muchos elementos Sort_merge_passes por segundo en la salida de SHOW GLOBAL STATUS, considere la posibilidad de aumentar el valor de sort_buffer_size para acelerar las operaciones ORDER BY o GROUP BY que no se pueden mejorar mediante la optimización de consultas o una mejor indexación.
Evite aumentar arbitrariamente el valor de sort_buffer_size a menos que tenga información relacionada que indique lo contrario. La memoria de este búfer se asigna por conexión. En la documentación de MySQL, el artículo Variables de sistema del servidor destaca que en Linux hay dos umbrales, 256 KB y 2 MB, y que el uso de valores mayores puede ralentizar significativamente la asignación de memoria. Como resultado, evite aumentar el valor de sort_buffer_size más allá de 2 MB, ya que la penalización del rendimiento superará las ventajas.
Caché de consultas (query_cache_size)
La caché de consultas es un área de memoria que se usa para almacenar en caché los conjuntos de resultados de las consultas. El parámetro query_cache_size determina la cantidad de memoria asignada para almacenar en caché los resultados de las consultas. La caché de consultas está desactivada de manera predeterminada. Además, la caché de consultas ha entrado en desuso en la versión 5.7.20 de MySQL y se ha quitado en la versión 8.0 de MySQL. Si la caché de consulta está habilitada actualmente en la solución, antes de deshabilitarla, compruebe que no haya ninguna consulta que dependa de ella.
Cálculo de la proporción de aciertos de caché del búfer
La proporción de aciertos de caché del búfer es importante en el entorno del servidor flexible de Azure Database for MySQL para comprender si el grupo de búferes puede dar cabida o no a las solicitudes de la carga de trabajo, y como regla general, es recomendable tener siempre una proporción de aciertos de caché del grupo de búferes superior al 99 %.
A fin de calcular la proporción de aciertos del grupo de búferes de InnoDB para las solicitudes de lectura, puede ejecutar SHOW GLOBAL STATUS para recuperar contadores "Innodb_buffer_pool_read_requests" e "Innodb_buffer_pool_reads" y, después, calcular el valor con la fórmula que se muestra a continuación.
InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100
Considere los ejemplos siguientes.
show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name | Value |
| +--------------------------+-------+ |
| Innodb_buffer_pool_reads | 197 |
| +--------------------------+-------+ |
| 1 row in set (0.00 sec) |
show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name | Value |
| +----------------------------------+----------+ |
| Innodb_buffer_pool_read_requests | 22479167 |
| +----------------------------------+----------+ |
| 1 row in set (0.00 sec) |
Con los valores anteriores, calcular la proporción de aciertos del grupo de búferes de InnoDB para las solicitudes de lectura produce el siguiente resultado:
InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100
Buffer hit ratio = 99.99%
Además de la proporción de aciertos de caché del búfer para las instrucciones select, para las instrucciones DML, las escrituras en el grupo de búferes de InnoDB se producen en segundo plano. Sin embargo, si es necesario leer o crear una página y no hay páginas limpias disponibles, también es necesario esperar a que las páginas se vacíen primero.
El contador Innodb_buffer_pool_wait_free cuenta el número de veces que ha ocurrido esto. Un valor de Innodb_buffer_pool_wait_free mayor que 0 es un indicador serio de que el grupo de búferes de InnoDB es demasiado pequeño y es necesario aumentar el tamaño del grupo de búferes o el tamaño de instancia para dar cabida a las escrituras que entran en la base de datos.
Recomendaciones
- Asegúrese de que la base de datos tenga suficientes recursos asignados para ejecutar las consultas. En ocasiones, es posible que tenga que escalar verticalmente el tamaño de la instancia a fin de obtener más memoria física para que los búferes y las memorias caché se adapten a la carga de trabajo.
- Evite transacciones grandes o de larga duración dividiéndolas en transacciones más pequeñas.
- Use las alertas de "Porcentaje de memoria del host" para recibir notificaciones si el sistema supera cualquiera de los umbrales especificados.
- Use Información de rendimiento de consultas o libros de Azure para identificar las consultas problemáticas o que se ejecutan lentamente y, a continuación, optimizarlas.
- En el caso de los servidores de bases de datos de producción, recopile diagnósticos a intervalos regulares para asegurarse de que todo se ejecuta sin problemas. Si no es así, solucione y resuelva los problemas que identifique.