Cómo MySQL usa la memoria

MySQL asigna búferes globales en el inicio del servidor, que se comparten entre todas las conexiones. Los búferes globales consumen la mayor parte de la memoria de MySQL, p.ej., innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size, etcétera.

El grupo de búferes de InnoDB suele ser el consumidor de memoria más grande de una instancia de MySQL. Se configura con el parámetro innodb_buffer_pool_size. Se usa para almacenar en caché los datos y los índices de la tabla, el búfer de cambio, el índice hash adaptable y otras estructuras internas. Cloud SQL para MySQL, la oferta administrada de MySQL de Google Cloud, configura innodb_buffer_pool_size hasta el 72% de la memoria de la instancia, según el 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% mayor que el tamaño del grupo de búferes especificado. Puedes verificar el uso de memoria del grupo de búferes de InnoDB en el resultado show engine innodb status\G.

mysql> show engine innodb status\G

…

----------------------

GRUPO DE BÚFER Y MEMORIA

----------------------

Total de memoria grande asignada 11511349248

…

El búfer de registro de InnoDB se usa para contener los cambios que se escribirán en los archivos de registro de rehacer de InnoDB en el disco. Se configura con innodb_log_buffer_size. El valor predeterminado de MySQL Community es de 16 MB y Cloud SQL para MySQL usa lo mismo.

MySQL usa el búfer de claves para almacenar en caché los índices de MyISAM en la memoria. Se configura con key_buffer_size. El valor predeterminado de MySQL Community es de 8 MB y Cloud SQL para MySQL usa lo mismo. Cloud SQL para MySQL no admite tablas MyISAM, por lo que está bien dejar la configuración predeterminada.

La variable de configuración query_cache_size define la cantidad de memoria asignada para almacenar en caché los resultados de la consulta. Está inhabilitado de forma predeterminada en MySQL Community 5.7 y Cloud SQL para MySQL 5.7. 

El almacenamiento en caché de la consulta era conocido por tener graves problemas de escalabilidad; por lo tanto, dejó de estar disponible en MySQL 5.7.20 y se quitó en MySQL 8.0. Si todavía la estás usando para tu instancia de MySQL 5.7, comprueba si es realmente útil para tu carga de trabajo. Consulta este blog para obtener más información. 

MySQL asigna cachés globales que se comparten entre todas las conexiones, estas se asignan de forma dinámica y las variables de configuración definen el límite máximo para ellas.

MySQL usa la caché de la tabla para acelerar la apertura de las tablas. La caché de la tabla de MySQL se divide en dos partes, una caché de tablas abiertas y una caché de definiciones de tablas, y se configura con table_open_cache y table_definition_cache, respectivamente. 

table_open_cache es una memoria caché para almacenar el descriptor de archivo de las tablas abiertas por todos los subprocesos conectados. Aumentar este valor aumenta la cantidad de descriptores de archivos que requiere el programa mysqld, también conocido como servidor MySQL. Asegúrate de que tu sistema operativo pueda admitir la cantidad de descriptores de archivos abiertos que involucra el parámetro de configuración table_open_cache.

Varias sesiones de cliente pueden acceder a la tabla determinada de forma simultánea, y la tabla se abre de forma independiente en cada sesión de cliente simultánea. Esta es la razón por la que podrías ver que el recuento de tablas abiertas es mayor que el número de tablas en el servidor. Cuando la caché de la tabla está llena, el servidor libera las tablas que no están en uso actualmente, comenzando con la tabla que se usó menos recientemente.

table_definition_cache es una memoria caché para almacenar las definiciones de la tabla. 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 4000 y 2000 como valores predeterminados para table_open_cache y table_definition_cache.

Para cada conexión de cliente, MySQL asigna un subproceso dedicado que ejecuta todas las consultas y le muestra el resultado al cliente hasta que este se desconecta. MySQL almacena en caché los subprocesos para que no tenga que crear y destruir subprocesos para cada conexión. La cantidad de subprocesos en la caché de subprocesos se configura con la variable thread_cache_size.

Cloud SQL para MySQL usa 48 como valor predeterminado para thread_cache_size.

InnoDB tiene su propia caché para almacenar definiciones de tablas, esto es diferente de la caché abierta de tabla y la caché de definición de tablas. Puedes verificar la memoria asignada para el diccionario de datos de InnoDB en la salida show engine innodb status\G.

----------------------

GRUPO DE BÚFER Y MEMORIA

----------------------

…

Dictionary memory allocated 65816817

El parámetro de configuración table_definition_cache establece un límite flexible en la cantidad de instancias de la tabla en la caché del diccionario de datos de InnoDB. Si la cantidad de instancias de la tabla en la caché del diccionario de datos de InnoDB supera el límite de table_definition_cache, el mecanismo de LRU comienza a marcar las instancias de la tabla para su expulsión y, finalmente, las quita de esta caché. 

¿Esto significa que la cantidad de instancias de tablas en la caché del diccionario de datos de InnoDB siempre será menor que el límite de table_definition_cache? Este no es el caso, las instancias de tabla con relaciones de clave externa no se colocan en la lista de LRU. Permanecen en caché y provocan que las instancias de la tabla crezcan más allá del límite de table_definition_cache, lo que genera un uso de memoria adicional. La memoria que consumen las tablas con relaciones de clave externa se libera solo en el evento de cierre/reinicio de MySQL. Este problema existe en MySQL 5.7 y 8.0, y hay un error verificado conocido.

Si tu instancia de MySQL tiene una gran cantidad de tablas con relaciones de claves externas, la caché del diccionario de datos de InnoDB puede consumir varios GB de memoria. A menudo, se pasa por alto cuando se configuran los búferes o cachés de MySQL, y podría ser una de las causas de los problemas inesperados de uso de memoria alto o de falta de memoria (OOM).

Resultado de ejemplo que muestra una instancia pequeña que consume 4.16 GB para la caché del diccionario de datos de InnoDB.

$ mysql -e "show engine innodb status\G" | grep -i memory

GRUPO DE BÚFER Y MEMORIA

Total de memoria grande asignada 7696023552

Memoria asignada del diccionario 4465193358

Otra función de MySQL que consume memoria son los búferes de sesión. Estos búferes se asignan por sesión y, en algunos casos, se pueden asignar varias instancias de ellos a una sola consulta (join_buffer_size, en particular).

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

Estos búferes se asignan solo cuando una consulta los necesita (para el ordenamiento, las uniones, el análisis del índice/completo de la tabla y más), pero cuando son necesarios se asignan con su tamaño original incluso si se requiere una parte muy pequeña. Configurar estos búferes en un valor alto puede provocar un desperdicio de memoria.

Los valores predeterminados son los mismos en la comunidad de MySQL y en Cloud SQL para MySQL.

MySQL usa la caché de registro binario para contener los cambios realizados en el registro binario mientras se ejecuta una transacción. Se configura con binlog_cache_size. Se asigna para cada cliente si el registro binario está habilitado (log_bin=ON).

El valor predeterminado para binlog_cache_size es el mismo en MySQL Community y en Cloud SQL para MySQL.

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 en el disco cuando se alcanza el tamaño máximo. El tamaño máximo de las tablas temporales internas se determina como el mínimo de las variables tmp_table_size y max_heap_table_size.

Los valores predeterminados para tmp_table_size y max_heap_table_size son los mismos en MySQL Community y en Cloud SQL para MySQL.

Nota: Debido a que los búferes por sesión y las tablas temporales en la memoria asignan memoria de forma separada para cada conexión, el uso general de memoria puede ser muy alto si una gran cantidad de conexiones lo necesitan. Se recomienda no establecer estos valores demasiado altos; experimenta hasta encontrar el mejor valor para tu carga de trabajo.

Cada subproceso requiere poca memoria para administrar la conexión del cliente. Las siguientes variables controlan su tamaño.

• thread_stack: el tamaño de la pila para cada subproceso. El valor predeterminado 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 aún más hasta el tamaño de max_allowed_packet.

Si performance_schema está habilitado, esto ayuda a supervisar la ejecución del servidor MySQL a un nivel bajo. Performance_schema asigna memoria de forma dinámica y solo se libera cuando se cierra y se reinicia MySQL.

Cloud SQL para MySQL permite habilitar rendimiento_schema en las instancias que tienen un tamaño de RAM de 15 GB o más. Está habilitado de forma predeterminada a partir de la versión de MySQL 8.0.26. Performance_schema está inhabilitado de forma predeterminada para las versiones de MySQL 5.6, 5.7 y 8.0.18 y se puede habilitar con las marcas de base de datos.

Cloud SQL para MySQL configura automáticamente parámetros relacionados con la memoria para lograr un buen rendimiento listo para usar. Tu instancia puede tener problemas de OOM si la carga de trabajo requiere cachés más grandes para admitir una gran cantidad de tablas o conexiones. Muchos subprocesos que asignan los búferes de sesión de forma simultánea también pueden provocar problemas relacionados con la memoria. Mientras más memoria se asigne a los búferes o cachés globales, menor será la memoria 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 búferes de sesión o cachés más grandes, 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 las marcas de base de datos. Si aún tienes problemas de OOM o de rendimiento, puedes actualizar el tamaño de la instancia para aumentar la memoria.