Cómo MySQL usa la memoria
Las instancias de MySQL que consumen mucha memoria o se encuentran con problemas de falta de memoria (OOM) son un problema común. Una instancia de base de datos que se ejecuta con presión de memoria suele causar problemas de rendimiento, bloqueos o, incluso, tiempo de inactividad de la aplicación.
Antes de comenzar a asignar memoria para instancias de MySQL, es importante comprender cómo MySQL usa la memoria. En este artículo, se destacan las características de MySQL que consumen memoria, lo que a menudo suele generar problemas de OOM, y también se analiza la configuración de Cloud SQL para MySQL, una oferta completamente administrada de Google Cloud.
Búferes globales
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.
Grupo de búferes de InnoDB
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 datos de la tabla, el búfer de cambio, el índice de 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 total asignada es aproximadamente un 10% mayor que el tamaño del grupo de búferes especificado. Puedes verificar el uso de la memoria del grupo de búferes de InnoDB en la opción de mostrar el estado de innodb del motor del programa.
|
mysql> mostrar estado innodb del motor … ---------------------- BOLETO Y MEMORIA ---------------------- Total de memoria grande asignada 11511349248 … |
Búfer de registro de InnoDB
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 la comunidad de MySQL es de 16 MB, y Cloud SQL para MySQL usa lo mismo.
Tamaño del búfer de clave
MySQL usa el búfer de clave 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 lo mismo. Cloud SQL para MySQL no admite tablas MyISAM, por lo que está bien dejarlo con su configuración predeterminada.
Query cache size
La variable de configuración query_cache_size define la cantidad de memoria asignada para los resultados de la consulta en caché. Está inhabilitado de forma predeterminada en MySQL Community 5.7 y Cloud SQL para MySQL 5.7.
La caché de consultas era conocida por problemas de escalabilidad graves, por lo que dejó de estar disponible en MySQL 5.7.20 y se quitó en MySQL 8.0. Si aún la usas para tu instancia de MySQL 5.7, verifica si es realmente útil para tu carga de trabajo. Consulta este blog para obtener más información.
Cachés globales
MySQL asigna cachés globales que se comparten entre todas las conexiones, que se asignan de forma dinámica y las variables de configuración definen el límite máximo.
Caché de la tabla
MySQL usa la caché de tablas 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, configuradas con table_open_cache y . table_definition_cache respectivamente.
El valor de table_open_cache es una caché de memoria para almacenar el descriptor de archivos de las tablas abiertas en todos los subprocesos conectados. Aumentar este valor aumenta la cantidad de descriptores de archivos que el programa mysqld, también conocido como servidor MySQL, requiere. Asegúrate de que tu sistema operativo pueda controlar la cantidad de descriptores de archivos abiertos que implica la configuración table_open_cache.
Varias sesiones de cliente pueden acceder a la tabla determinada de manera simultánea, y la tabla se abre de forma independiente por cada sesión cliente simultánea. Esta es la razón por la que es posible que el recuento de tablas abiertas sea 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, a partir de la tabla menos usada.
El valor de table_definition_cache es una caché de memoria para almacenar las definiciones de la tabla. Es global y se comparte entre todas las conexiones.
Cloud SQL para MySQL 5.7 usa 2,000 y 1,400 como valores predeterminados para table_open_cache y table_definition_cache.
Cloud SQL para MySQL 8.0 usa 4,000 y 2,000 como valores predeterminados para table_open_cache y table_definition_cache.
Caché de conversación
Para cada conexión con el cliente, MySQL asigna un subproceso dedicado que ejecuta todas las consultas y muestra el resultado al cliente hasta que este se desconecta. MySQL almacena en caché los subprocesos a fin de que no tenga que crear y destruir subprocesos para cada conexión. La cantidad de subprocesos en la caché del subproceso se configura con la variable thread_cache_size.
Cloud SQL para MySQL usa 48 de forma predeterminada para thread_cache_size.
Caché del diccionario de datos de InnoDB
InnoDB tiene su propia caché para almacenar definiciones, que son diferentes de la caché abierta y la caché de definición de tablas. Puedes verificar la memoria asignada para el diccionario de datos de InnoDB en el estado del motor innodb del programa.
|
---------------------- BOLETO Y MEMORIA ---------------------- … Memoria del diccionario asignada 65816817 |
La configuración de table_definition_cache establece un límite flexible en el número de instancias de tabla en la caché del diccionario de datos de InnoDB, si el número de instancias de tablas en la caché del diccionario de datos de InnoDB. excede el límite de table_definition_cache, el mecanismo LRU comienza a marcar las instancias de tabla para su expulsión y, luego, las quita de esta caché.
¿Significa que la cantidad de instancias de tablas en la caché del diccionario de datos de InnoDB siempre será inferior al límite de table_definition_cache? Este no es el caso, las instancias de tablas con relaciones de clave externa no se ubican en la lista LRU. Quedan almacenadas en caché y hacen 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 claves externas se libera solo en el evento de apagado y reinicio de MySQL. Este problema existe en MySQL 5.7 y en 8.0, y existe un error 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. A menudo, se pasa por alto mientras se configuran los búferes o las memorias caché de MySQL, y podría ser uno de los motivos de los problemas inesperados de uso elevado de memoria o de falta de memoria (OOM).
Un resultado de muestra que muestra una instancia pequeña que consume 4.16 GB para la caché del diccionario de datos de InnoDB.
|
$ mysql -e "mostrar motor innodb status\G" | grep -i de memoria BOLETO Y MEMORIA Total de memoria grande asignada 7696023552 Memoria del diccionario asignada 4465193358 |
Búferes de sesión
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 (en particular, Join_buffer_size).
Estos búferes se asignan solo cuando una consulta los necesita (para ordenar, unir, analizar índices y tablas completas, etc.), pero cuando son necesarios, se asignan a su tamaño original, incluso si se requiere una porción muy pequeña. Establecer 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 Cloud SQL para MySQL.
Caché de registro binario
MySQL usa la caché de registro binario para contener los cambios realizados en el registro binario mientras se ejecuta una transacción. Se configura mediante 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.
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 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 la comunidad de MySQL y Cloud SQL para MySQL.
Nota : Como los búferes por sesión y las tablas temporales en la memoria asignan memoria por separado para cada conexión, el uso general de la memoria puede ser muy alto si hay una gran cantidad de conexiones. los necesitan. Se recomienda no establecer estos valores demasiado altos. Experimenta cuando encuentres el mejor valor para tu carga de trabajo.
Por memoria de conexión
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 pila para cada subproceso es el predeterminado 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 hasta el tamaño de max_allowed_packet.
Esquema de rendimiento
Si el performance_schema está habilitado, ayuda a supervisar la ejecución del servidor MySQL en un nivel bajo. Performance_schema asigna memoria de forma dinámica y se libera solo cuando se cierra o se reinicia 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 de MySQL 8.0.26. Performance_schema está inhabilitado de forma predeterminada para las versiones 5.6, 5.7 y 8.0.18 de MySQL. Se puede habilitar mediante marcas de base de datos.
Conclusión
Cloud SQL para MySQL configura automáticamente los parámetros relacionados con la memoria a fin de obtener un buen rendimiento listo para usar. Tu instancia aún puede tener problemas de OOM si la carga de trabajo requiere almacenamiento en caché más grande a fin de admitir una gran cantidad de tablas o conexiones. Muchos subprocesos que asignan búferes de sesión de forma simultánea también pueden generar problemas relacionados con la memoria. Cuanta más memoria se asigne para los búferes o las memorias caché globales, menor será la cantidad de memoria disponible para las conexiones, los búferes de sesión y viceversa, y la clave es encontrar un buen equilibrio.
Si tu carga de trabajo requiere almacenamiento en caché más grande 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 mediante 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.
Productos y servicios relacionados
Google Cloud ofrece una base de datos administrada de MySQL que se adapta a las necesidades de tu empresa, desde la eliminación de tu centro de datos local hasta la ejecución de aplicaciones SaaS y la migración de los sistemas empresariales principales.
