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 índices y los datos 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 los búferes y las 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.

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 dejarla como predeterminada.

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

La caché de consultas era conocida por sus graves problemas de escalabilidad, por lo que dejó de estar disponible en MySQL 5.7.20 y se quitó en MySQL 8.0. Si aún la usas en tu instancia de MySQL 5.7, comprueba si es realmente útil para tu carga de trabajo. Para obtener más información, consulta este blog. 

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 tablas de MySQL se divide en dos partes, una caché de tablas abiertas y una de definiciones de tablas, configuradas con table_open_cache y table_definition_cache, respectivamente. 

table_open_cache es una memoria caché para almacenar el descriptor de archivos de las tablas abiertas por todos los subprocesos conectados. El aumento de 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 administrar la cantidad de descriptores de archivos abiertos que implica el parámetro de configuración table_open_cache.

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

table_definition_cache es una memoria caché para almacenar las definiciones de 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 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 devuelve el resultado al cliente hasta que este se desconecte. MySQL almacena los subprocesos en caché de modo que no tenga que crear ni 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 predeterminado para thread_cache_size.

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

La configuración table_definition_cache establece un límite flexible en la cantidad de instancias de tablas en la caché del diccionario de datos de InnoDB si la cantidad de instancias de 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é.

¿Significa que la cantidad de instancias de tabla 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 LRU. Permanecen almacenados en caché y provocan que las instancias de la tabla superen el límite de table_definition_cache, lo que genera un uso de memoria adicional. La memoria que consumen las tablas con relaciones de claves externas 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 conocido verificado.

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. Con frecuencia, se pasa por alto cuando se configuran los búferes o cachés de MySQL, y podría ser uno de los motivos de los imprevistos de alto uso de memoria o de problemas de falta de memoria (OOM).

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

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 para 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 ordenar, uniones, análisis de tablas completas/índices, y más), pero cuando son necesarios, se asignan a su tamaño completo, incluso si se requiere una porción muy pequeña. Si se configuran con un valor alto, es posible que se desperdicie memoria.

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

MySQL usa la caché del registro binario para conservar 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 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 tamaño 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: Como los búferes por sesión y las tablas temporales en memoria asignan memoria por separado para cada conexión, el uso general de memoria puede ser muy alto si una gran cantidad de conexiones lo necesita. Se recomienda no establecer estos valores demasiado altos y experimentar para 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.