Cómo usa MySQL la memoria

MySQL asigna búferes 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.

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 memoria del grupo de búfer InnoDB en mostrar el estado innodb del motor\salida.

mysql> show engine innodb status\G

…

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

GRUPO DE BÚFERES Y MEMORIA

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

Total de memoria grande asignada 11511349248

…

El búfer de registro de InnoDB se utiliza para alojar los cambios que tendrán que escribirse en los archivos de registro para rehacer de InnoDB en el disco. Se configura mediante innodb_log_buffer_size. El valor predeterminado de la comunidad de MySQL es de 16 MB y Cloud SQL para MySQL usa el mismo.

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 usa el mismo. Cloud SQL para MySQL no es compatible con las tablas de MyISAM, por lo que puedes dejarla como predeterminada.

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 era conocida por sus problemas graves de escalabilidad, por lo que quedó obsoleta en MySQL 5.7.20 y se eliminó en MySQL 8.0. Si todavía la usas para tu instancia de MySQL 5.7, comprueba si de verdad es útil para la carga de trabajo. Consulta más información en este blog. 

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.

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, configuradas mediante table_open_cache y table_definition_cache, respectivamente.

El ajuste table_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 para 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 ajuste "table_definition_cache" es una memoria caché que sirve para almacenar las definiciones de las tablas. Es global y se comparte entre todas las conexiones.

Cloud SQL para MySQL 5.7 usa los valores 2000 y 1400 como los valores predeterminados de 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.

Para cada conexión de cliente, MySQL asigna un hilo especializado 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 usa 48 de forma predeterminada para thread_cache_size.

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 que se muestra en el motor innodb status/G.

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

GRUPO DE BÚFERES 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 el número de instancias de la caché de diccionario de InnoDB supera el límite de table_definition_cache, el mecanismo de LRU empieza a marcar instancias de tabla para su desalojo y acaba retirándolas 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 almacenan 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 caso 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. Se suele pasar 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 problemas de memoria insuficiente.

Ejemplo de resultado que muestra una pequeña instancia 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ÚFERES Y MEMORIA

Total de memoria grande asignada 7696023552

Memoria de diccionario asignada 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 para una misma consulta (en particular, join_buffer_size).

• 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 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, la memoria podría desaprovecharse.

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

MySQL usa la caché de registro binaria para retener los cambios hechos 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.

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 una gran cantidad de conexiones las necesitan. Te recomendamos que no asignes un valor demasiado alto. Haz pruebas para encontrar el valor óptimo para tu carga de trabajo.

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 pila 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 seguir creciendo hasta alcanzar el tamaño max_allowed_packet.

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 performance_schema 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. Performance_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.

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 cachés de mayor capacidad para gestionar 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. Cuanta 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 o búferes de sesión 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 mediante marcas de bases de datos. Si sigues teniendo problemas de OOM o de rendimiento, puedes cambiar el tamaño de la instancia para aumentar la memoria.