Cómo usa MySQL la memoria
Las instancias de MySQL que consumen mucha memoria o que tienen problemas con la falta de memoria (OOM) son un problema común. Cuando una instancia de base de datos se ejecuta con presión de memoria, suele provocar problemas de rendimiento, estancamiento o incluso un periodo de inactividad de las aplicaciones.
Antes de empezar a asignar memoria a las instancias de MySQL, es importante comprender cómo MySQL utiliza la memoria. En este artículo se destacan las características de MySQL que consumen memoria, lo que a menudo provoca problemas de OOM y también se describe cómo se configura Cloud SQL para MySQL, la oferta totalmente gestionada de Google Cloud, para la gestión de la memoria.
Búferes globales
MySQL asigna búfers globales al iniciar el servidor y los comparte entre todas las conexiones. Los búferes globales consumen la mayor parte de la memoria de MySQL, por ejemplo, innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size, etc.
Grupo de búferes de InnoDB
El grupo de búferes InnoDB suele ser el mayor consumidor de memoria de las instancias de MySQL. Se configura con el parámetro innodb_buffer_pool_size. Se utiliza para almacenar en caché los datos y los índices de la tabla, el búfer de cambio, el índice de hash adaptativo y otras estructuras internas. Cloud SQL para MySQL, la solución gestionada de MySQL de Google Cloud, configura innodb_buffer_pool_size hasta un 72 % de la memoria de la instancia, en función del tamaño de la instancia.
InnoDB reserva memoria adicional para búferes y estructuras de datos asociadas. La memoria asignada total es aproximadamente un 10 % superior al tamaño del grupo de búferes especificado. Puedes comprobar el uso de la memoria del grupo de búfer InnoDB en mostrar el estado innodb del motor\salida.
|
mysql> mostrar estado innodb del motor\G … ---------------------- BUSCADOR Y MEMORIA ---------------------- Total de memoria grande asignada 11511349248 … |
Búfer de registro InnoDB
El búfer de registro de InnoDB se utiliza para alojar los cambios que se escriben en los archivos de registro de InnoDB en el disco. Se configura mediante innodb_log_buffer_size. El valor predeterminado de la comunidad de MySQL es de 16 MB, mientras que Cloud SQL para MySQL utiliza el mismo.
Tamaño del búfer de clave
El búfer de clave lo usa MySQL para almacenar en caché los índices de MyISAM en la memoria. Se configura con key_buffer_size. El valor predeterminado de la comunidad de MySQL es de 8 MB y Cloud SQL para MySQL utiliza el mismo. Cloud SQL para MySQL no es compatible con las tablas de MyISAM, por lo que puedes dejarla como predeterminada.
Tamaño de la caché de las consultas
La variable de configuración query_cache_size define la cantidad de memoria asignada para los resultados de las consultas en caché. Está inhabilitada de forma predeterminada en la comunidad de MySQL 5.7 y en Cloud SQL para MySQL 5.7.
La caché de consulta se sabía por problemas de escalabilidad graves, por lo que quedó obsoleta en MySQL 5.7.20 y se eliminó en MySQL 8.0. Si todavía lo usas para tu instancia de MySQL 5.7, comprueba si es muy útil para la carga de trabajo. Consulta más información en este blog.
Cachés globales
MySQL asigna las cachés globales que se comparten entre todas las conexiones. Se asignan de forma dinámica y las variables de configuración definen el límite máximo.
Caché de tabla
MySQL usa la caché de tablas para acelerar la apertura de tablas. La caché de tablas de MySQL se divide en dos partes, una caché de tablas abiertas y una caché de definiciones de tablas, configurada mediante table_open_cache y table_Definition_cache, respectivamente.
La tabla_open_cache es una memoria caché donde se almacena el descriptor de archivo de las tablas abiertas por todas las conversaciones conectadas. Al aumentar este valor, aumenta el número de descriptores de archivo que requiere el programa mysqld, también conocido como servidor MySQL. Comprueba que tu sistema operativo pueda gestionar el número de descriptores de archivo abiertos implícitos en el ajuste table_open_cache.
Cuando varias sesiones de cliente tienen acceso a una tabla determinada, esta se abre de forma independiente por cada sesión de cliente simultánea. Por este motivo, puede que veas que el recuento de tablas abiertas es superior al número de tablas del servidor. Cuando la caché de tabla está llena, el servidor libera las tablas que no se están usando, empezando por la que menos se ha usado.
El parámetro "table_Definition_cache" es una memoria caché para almacenar las definiciones de las tablas. Es global y se comparte entre todas las conexiones.
Cloud SQL para MySQL 5.7 usa 2000 y 1400 como valores predeterminados para table_open_cache y table_Definition_cache.
Cloud SQL para MySQL 8.0 usa los valores 4000 y 2000 como los valores predeterminados de table_open_cache y table_Definition_cache.
Caché de conversación
Para cada conexión de cliente, MySQL asigna un hilo dedicado que ejecuta todas las consultas y devuelve el resultado al cliente hasta que este se desconecte. MySQL almacena en caché las conversaciones para que no tenga que crear ni destruir conversaciones para cada conexión. El número de hilos en la caché de conversaciones se configura mediante la variable thread_cache_size.
Cloud SQL para MySQL utiliza 48 de forma predeterminada para thread_cache_size.
Caché del diccionario de datos InnoDB
InnoDB tiene su propia caché para almacenar definiciones de tablas, que es distinta a la caché de apertura de tablas y a la caché de definición de tablas. Puedes comprobar la memoria asignada para el diccionario de datos InnoDB en el resultado del programa Innodb del programa de programas de display\G.
|
---------------------- BUSCADOR Y MEMORIA ---------------------- … Memoria de diccionario asignada 65816817 |
El ajuste table_Definition_cache define un límite no modificable del número de instancias de la tabla en la caché del diccionario de datos de InnoDB, si ese es el número de instancias de la tabla. supera el límite de la tabla_determinada_la_cache, el mecanismo de LRU empieza a marcar instancias de tabla de la fuga y las retira de la caché.
¿Significa esto que el número de instancias de la tabla en la caché de diccionario de datos InnoDB siempre será inferior al límite de table_Definition_cache? En caso contrario, las instancias de tabla con relaciones de clave externa no se incluirán en la lista de LRU. Se mantienen en caché y hacen que las instancias de la tabla superen el límite de table_Definition_cache, lo que provoca un uso de memoria adicional. La memoria que consumen las tablas con relaciones de claves externas se libera únicamente en el evento de apagado o reinicio de MySQL. Este problema está presente en MySQL 5.7 y 8.0, y hay un error verificado.
Si tu instancia de MySQL tiene una gran cantidad de tablas con relaciones de clave externa, la memoria caché del diccionario de datos de InnoDB puede consumir varios GB de memoria. A menudo se pasa por alto al configurar búferes o cachés de MySQL, y podría ser uno de los motivos por los que se produce un uso de memoria muy alto o de memoria insuficiente.
Ejemplo de resultado que muestra una pequeña instancia que consume 4,16 GB por la caché del diccionario de datos de InnoDB.
|
$ mysql -e "mostrar motor innodb estado\G" | grep -i memoria BUSCADOR Y MEMORIA Total de memoria grande asignada 7696023552 Memoria de diccionario asignada 4465193358 |
Búferes de sesión
Otra función de MySQL que consume memoria es los búferes de sesión. Estos búferes se asignan por sesión y, en algunos casos, se pueden asignar varias instancias para una misma consulta (en particular, Join_buffer_size).
Estos búferes se asignan solo cuando una consulta los necesita (para ordenarlos, unirlos, analizar índices o tablas completas, etc.), pero cuando son necesarios, se asignan a su tamaño completo, incluso si se necesita una parte muy pequeña. Si defines estos búferes como un valor alto, puede perder memoria.
Los valores predeterminados son los mismos en la comunidad de MySQL y Cloud SQL para MySQL.
Caché de registro binario
MySQL usa la caché de registro binaria para retener los cambios realizados en el registro binario mientras se ejecuta una transacción. Se configura con binlog_cache_size. Se asigna a cada cliente si el registro binario está habilitado (log_bin=ON).
El valor predeterminado de binlog_cache_size es el mismo en la comunidad de MySQL y en Cloud SQL para MySQL.
Tablas temporales
MySQL crea tablas temporales internas para almacenar el resultado intermedio mientras procesa algunos tipos de consultas, como GROUP BY, ORDER BY, DISTINCT y UNION. Estas tablas temporales internas se crean primero en la memoria y se convierten en tablas de disco cuando se alcanza el tamaño máximo. El tamaño máximo de las tablas temporales internas se determina como el valor mínimo de las variables tmp_table_size y max_heap_table_size.
Los valores predeterminados de tmp_table_size y max_heap_table_size son los mismos en la comunidad de MySQL y Cloud SQL para MySQL.
Nota: Como los búferes por sesión y las tablas temporales en memoria asignan memoria por separado a cada conexión, el uso general de memoria puede ser muy alto si hay una gran cantidad de conexiones las necesitamos. Te recomendamos que no asignes un valor demasiado alto. Haz pruebas para encontrar el valor óptimo para tu carga de trabajo.
Por memoria de conexión
Cada hilo requiere poca memoria para gestionar la conexión del cliente. Las siguientes variables controlan su tamaño.
- thread_stack: el tamaño predeterminado de cada conversación es de 256 kB.
- net_buffer_length: cada cliente está asociado con un búfer de conexión y un búfer de resultado de net_buffer_length. Esto puede aumentar el tamaño de max_allowed_packet.
Esquema de rendimiento
Si performance_schema está habilitado, ayuda a monitorizar la ejecución del servidor MySQL en un nivel bajo. Performance_schema asigna la memoria de forma dinámica y solo se libera al cerrar o reiniciar MySQL.
Cloud SQL para MySQL permite habilitar el rendimiento de la instancia en las instancias que tengan un tamaño de RAM de 15 GB o superior. Se habilita de forma predeterminada a partir de la versión 8.0.26 de MySQL. El rendimiento de schema_schema está inhabilitado de forma predeterminada en las versiones de MySQL 5.6, 5.7 y 8.0.18, y se puede habilitar mediante marcas de bases de datos.
Conclusiones
Cloud SQL para MySQL configura automáticamente parámetros relacionados con la memoria para que el rendimiento empiece desde el primer momento. Aun así, la instancia puede tener problemas de OOM si la carga de trabajo requiere el almacenamiento en caché de más capacidad para un gran número de tablas o conexiones. Muchos hilos que asignan búferes de sesión simultáneamente pueden provocar problemas relacionados con la memoria. Cuanto más memoria se asigna a los búferes y cachés de todo el mundo, menos memoria hay disponible para las conexiones, los búferes de sesión y viceversa; la clave es encontrar un buen equilibrio.
Si tu carga de trabajo requiere cachés más grandes o búferes de sesión, puedes reducir el tamaño del búfer global con innodb_buffer_pool_size. Puedes cambiar los parámetros de configuración de tus instancias de Cloud SQL para MySQL con marcas de bases de datos. Si sigues teniendo problemas de rendimiento o de OOM, puedes cambiar el tamaño de la instancia para aumentar la memoria.
Productos y servicios relacionados
Google Cloud ofrece una base de datos MySQL gestionada que se adapta a las necesidades de tu negocio, desde la retirada de tu centro de datos on‑premise, la ejecución de aplicaciones de software como servicio o la migración de sistemas empresariales fundamentales.
