Soluciona problemas de memoria de MySQL

Una condición de falta de memoria (OOM) ocurre cuando un proceso intenta asignar memoria y falla porque el servidor no tiene memoria disponible. Esto suele dar como resultado el OOM Killer de Linux, un proceso que emplea el kernel de Linux cuando el sistema tiene muy poca memoria, lo que finaliza el proceso de la base de datos. 

Una falla de la base de datos debido a una condición OOM no es ideal porque el proceso de la base de datos se finaliza con la directiva SIGKILL, lo que no permite que la aplicación se cierre con MySQL normal. Causa los siguientes problemas:

• El servidor MySQL no funciona (mientras completa la recuperación ante fallas, que puede tardar varios minutos), lo que afecta a los usuarios y la empresa.

• El servidor MySQL tarda más en preparar por completo los búferes y cachés para alcanzar el rendimiento máximo.
• Posibilidad de daños en los datos debido a una falla inesperada

MySQL agregó instrumentación de memoria en performance_schema a partir de la versión 5.7, lo que ayuda a comprender la asignación de memoria de MySQL. En este artículo, encontrarás ayuda para solucionar problemas de memoria de MySQL mediante performance_schema.

Cada instancia de Cloud SQL funciona con una máquina virtual (VM) que se ejecuta en un servidor host de Google Cloud. Cada VM opera el programa de base de datos, como MySQL Server y los agentes de servicio que proporcionan servicios de asistencia, como registro y supervisión. Cloud SQL reserva una parte de la memoria para el sistema operativo (SO), los agentes de servicio y asigna la memoria restante a programas de bases de datos, como mysqld. 

En la siguiente tabla, se muestra la asignación de memoria para el proceso mysqld y la configuración predeterminada del grupo de búferes de InnoDB para cada tipo de máquina.

En el caso de las instancias que tienen 10 GB de RAM o más, el proceso mysqld está limitado a utilizar el 92% de la RAM de la instancia y el grupo de búferes de InnoDB se configura en el rango del 75 al 80% de la memoria disponible para el proceso mysqld. Además del grupo de búferes de InnoDB, MySQL asigna varios búferes y cachés para mejorar el rendimiento. Consulta este artículo para comprender la asignación de memoria en MySQL.

Cuando el uso de memoria de mysqld aumenta más allá del límite configurado del proceso mysqld, el eliminador de OOM de Linux finaliza el proceso mysqld, ya que es el mayor consumidor de memoria de la instancia.

performance_schema es una función de MySQL para monitorear la ejecución del servidor a bajo nivel. Cloud SQL para MySQL permite habilitar performance_schema en las instancias que tengan un tamaño de RAM de 3 GB o más. Está habilitado de forma predeterminada en instancias con al menos 15 GB de RAM, a partir de la versión MySQL 8.0.

Performance_schema está inhabilitado de forma predeterminada para las versiones de MySQL 5.6 y 5.7, y se puede habilitar con las marcas de base de datos.

La habilitación de performance_schema implica cierta sobrecarga de rendimiento y también implicaciones en la memoria. Consulta El modelo de asignación de memoria del esquema de rendimiento para comprender su asignación de memoria.

La instrumentación de memoria performance_schema ayuda a supervisar el uso de memoria en MySQL. Los instrumentos de memoria tienen nombres con la forma de memory/code_area/instrument_name , donde área_de_código es un valor como SQL o Innodb ynombre_instrumento es el detalle del instrumento.

MySQL 5.7 habilita algunos instrumentos de memoria de forma predeterminada, pero no todos. Para habilitarlos, actualiza la columna ENABLED de la tabla performance_schema.setup_instruments. 

mysql> select version();

+-------------------+

| version()         |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |       70 |

| NO      |      306 |

+---------+----------+

2 rows in set (0.00 sec)

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Query OK, 306 rows affected (0.00 sec)

Rows matched: 376  Changed: 306  Warnings: 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      376 |

+---------+----------+

1 row in set (0.00 sec)

La habilitación de instrumentos de memoria en el tiempo de ejecución solo contará los objetos de memoria creados después de habilitar la instrumentación. Por lo tanto, te recomendamos que esperes a que el servidor recopile suficientes datos para solucionar problemas.

MySQL 8.0 habilita todos los instrumentos de memoria de forma predeterminada.

mysql> select version();

+---------------+

| version()     |

+---------------+

| 8.0.28-google |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES     |      490 |

+---------+----------+

1 row in set (0.00 sec)

El esquema Sys contiene muchas vistas que resumen los datos de performance_schema en formatos fáciles de entender. La mayoría de estas vistas se dividen en parejas. Comparten el mismo nombre y uno de ellos tiene el prefijo x$. Las siguientes vistas están disponibles en MySQL 5.7 y 8.0 para ver la asignación de memoria en diferentes niveles.

mysql> usar sys;

Database changed

mysql> show tables like '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)            |

+-------------------------------------+

| Memorystore_by_host_by_current_bytes     |

| Memorystore_by_thread_by_current_bytes   |

| Memorystore_by_user_by_current_bytes     |

| Memorystore_global_by_current_bytes      |

| memory_global_total                 |

| x$memory_by_host_by_current_bytes   |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes   |

| x$memory_global_by_current_bytes    |

| x$memory_global_total               |

+-------------------------------------+

10 rows in set (0.02 sec)

En la vista sys.memory_global_total, se resume el uso total de memoria en un formato legible.

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB       |

+-----------------+

1 row in set (0.44 sec)

La vista sys.x$memory_global_total muestra la misma información, pero en bytes.

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|     13351482992 |

+-----------------+

1 row in set (1.39 sec)

La vista sin el prefijo x$ pretende proporcionar un resultado más fácil de usar y de leer. La vista con el prefijo x$ que muestra los mismos valores sin procesar está diseñada más para usarse con otras herramientas que realizan su propio procesamiento en los datos.

La vista sys.memory_global_by_current_bytes muestra el uso de memoria actual agrupado por el tipo de asignación (event_name), por ejemplo, memory/innodb/buf_buf_pool (grupo de búferes de InnoDB). De forma predeterminada, las filas se ordenan por la cantidad de memoria usada en orden descendente. 

Antes de desglosar cada tipo de asignación, es conveniente tener una descripción general del uso de memoria para cada área de código, como Innodb, sql, performance_schema, entre otros. 

La siguiente consulta agrega la memoria asignada actualmente por el área de código. 

SELECT SUBSTRING_INDEX(event_name,'/',2) AS código_área,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

DE sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(nombre_evento,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area                 | current_alloc |

+---------------------------+---------------+

| memory/innodb             | 11.28 GiB     |

| memory/performance_schema | 520.02 MiB    |

| memory/temptable          | 514.00 MiB    |

| memory/sql                | 106.12 MiB    |

| memory/mysys              | 43.25 MiB     |

| memory/vio                | 372.15 KiB    |

| memory/myisam             |  696 bytes    |

| memory/csv                |   88 bytes    |

| memory/blackhole          |   88 bytes    |

+---------------------------+---------------+

9 rows in set (1.02 sec)

Este resultado muestra que innodb es el mayor consumidor de memoria, seguido de performance_schema y las tablas temporales.

Ahora, cuando se consulta sys.memory_global_by_current_bytes, se muestra directamente el uso de memoria de las estructuras internas de MySQL, como se describe en la columna event_name. Si quieres solucionar el problema del aumento anormal de memoria, puedes identificar la fuente observando el resultado de esta vista.

mysql> select event_name,current_alloc,high_alloc desde sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name                    | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool    | 10.72 GiB     | 10.72 GiB  |

| memory/temptable/physical_ram | 514.00 MiB    | 515.00 MiB |

| memory/innodb/hash0hash       | 292.69 MiB    | 292.69 MiB |

| memory/innodb/memory          | 191.77 MiB    | 197.94 MiB |

| memory/sql/TABLE              | 60.39 MiB     | 62.35 MiB  |

+-------------------------------+---------------+------------+

5 rows in set (0.45 sec)

A continuación, se muestran los detalles de cada tipo de asignación.

Esta vista resume el uso de memoria agrupado por el usuario. Si un usuario tiene múltiples conexiones, suma el uso de memoria de todas las instancias del usuario.

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user            | current_allocated |

+-----------------+-------------------+

| sbtest          | 60.30 MiB         |

| background      | 2.68 MiB          |

| root            | 1.52 MiB          |

| event_scheduler | 16,38 KiB         |

+-----------------+-------------------+

4 rows in set (1.16 sec)

Si usas un usuario diferente para cada aplicación o trabajo (recomendado), esta vista puede ayudar a identificar si una aplicación o un trabajo en particular consume mucha memoria.

En esta vista, se resume el uso de memoria agrupado por el nombre de host.

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host       | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB         |

| background | 2.70 MiB          |

| 127.0.0.1  | 311.01 KiB        |

| localhost  | 16,38 KiB         |

+------------+-------------------+

4 rows in set (1.58 sec)

Esta vista es útil para identificar el uso de memoria por host de cliente. En estos días, tener varios servidores de clientes o aplicaciones es muy común. Esta vista ayudará a identificar si algún cliente o carga de trabajo en particular está causando un alto uso de la memoria. También puedes implementar el cambio en un servidor de cliente o aplicación primero, supervisar su impacto en la memoria (comparando el uso de memoria con el de otros hosts cliente) y tomar decisiones fundamentadas.

Esta vista resume el uso de memoria, agrupado por subproceso, y ayuda a encontrar los subprocesos que ocupan la mayor parte de la memoria.

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user              | current_allocated |

+-----------+-------------------+-------------------+

|     12,999 | sbtest@10.128.0.8 | 3.80 MiB          |

|     12888 | sbtest@10.128.0.8 | 3.75 MiB          |

|     12760 | sbtest@10.128.0.8 | 3.68 MiB          |

|     13128 | sbtest@10.128.0.8 | 3.67 MiB          |

|     13221 | sbtest@10.128.0.8 | 3.47 MiB          |

+-----------+-------------------+-------------------+

5 rows in set (2.29 sec)

Si descubres que un subproceso en particular está consumiendo mucha memoria, puedes filtrar los registros de consultas lentos de MySQL o los registros generales con el subproceso_id y, luego, identificar la sesión o las consultas que generaron el crecimiento de la memoria.

La solución de problemas de alto uso de memoria es una tarea difícil. Afortunadamente, la instrumentación de memoria performance_schema ayuda a los usuarios a comprender cómo las estructuras internas de MySQL usan la memoria. Las vistas de esquema del sistema facilitan la interpretación de la información. Si tienes problemas relacionados con la memoria, esta función te ayudará a encontrar el origen del problema y podrás solucionarlo realizando cambios en la configuración o la consulta. 

Por ejemplo:

1. Si las tablas temporales internas requieren una gran cantidad de memoria, puedes limitar el tamaño de las tablas temporales mediante tmp_table_size o reducir innodb_buffer_pool_size para hacer espacio para los búferes por sesión.
2. Si un subproceso en particular consume mucha memoria, puedes filtrar los registros de consultas lentos mediante thread_id para identificar las consultas problemáticas y ajustarlas a fin de reducir el uso de memoria para ese subproceso o sesión.

Si te encuentras con una situación en la que una instancia de MySQL usa una memoria más alta de lo que debería y el uso aumenta continuamente hasta que el servidor se queda sin memoria y el performance_schema muestra un espacio entre la memoria total y la instrumentada, o no te dice dónde podría ser un síntoma de una fuga de memoria. Si sospechas que hay una fuga de memoria, puedes seguir estos pasos:

• Revisa detenidamente la configuración de MySQL y confirma que no haya sobreasignaciones de búferes ni cachés. Este artículo te ayudará a comprender cómo MySQL asigna memoria para diferentes búferes y cachés.
• Identifica la función, configuración o consulta que causa el problema de memoria; intenta reproducir el problema.
• Revisa las notas de la versión de MySQL de las siguientes versiones secundarias de la misma serie para verificar si se corrigieron algún error con comportamiento similar. 
• Actualiza MySQL a la versión secundaria más reciente para ver si eso soluciona el problema de memoria.
• Busca en la base de datos de errores de MySQL para comprobar si otros usuarios de la comunidad informaron algún problema similar.
• Crea un error nuevo con un caso de prueba reproducible si no existe un error para el mismo problema.