Soluciona problemas de memoria de MySQL

Una condición de memoria insuficiente (OOM) ocurre cuando un proceso intenta asignar memoria y falla porque el servidor no tiene memoria disponible. Esto suele dar como resultado el Linux OOM Killer, un proceso que el kernel de Linux utiliza cuando el sistema tiene 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 le da a la aplicación la posibilidad de cerrar MySQL sin problemas. Esto provoca los siguientes problemas:

• El servidor MySQL no está disponible (mientras se 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, se te ayudará a solucionar problemas de memoria de MySQL mediante el uso de 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 el registro y la supervisión. Cloud SQL reserva una parte de la memoria para el sistema operativo (SO) y los agentes de servicio, y asigna la memoria restante a los 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.

Para las instancias que tienen 10 GB de RAM o más, el proceso mysqld está limitado a usar 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 supera el límite configurado del proceso mysqld, el cancelador 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 afecta 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 code_area es un valor como sql o Innodb y instrument_name 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, se recomienda esperar 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 datos de performance_schema en formas fáciles de comprender. La mayoría de estas vistas vienen en pares. Comparten el mismo nombre y una de ellas 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> use sys;

Database changed

mysql> show tables like '%memory%';

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

| Tables_in_sys (%memory%)      |

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

| memory_by_host_by_current_bytes  |

| memory_by_thread_by_current_bytes |

| memory_by_user_by_current_bytes  |

| memory_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)

El objetivo de la vista sin el prefijo x$ es proporcionar resultados más fáciles de leer y usar. La vista con el prefijo x$ que muestra los mismos valores sin procesar es 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 de forma descendente por la cantidad de memoria usada. 

Antes de desglosar cada tipo de asignación, es bueno tener una descripción general del uso de memoria para cada área de código, como Innodb, SQL y performance_schema, entre otras. 

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

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',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)

En este resultado, se 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 directamente, se muestra el uso de memoria para las estructuras internas de MySQL, como se describe en la columna event_name. Si estás solucionando problemas relacionados con el aumento anormal de la memoria, puedes identificar la fuente observando el resultado de esta vista.

mysql> select event_name,current_alloc,high_alloc from 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 presentan los detalles de cada tipo de asignación.

En esta vista, se resume el uso de memoria agrupado por el usuario. Si un usuario tiene varias conexiones, se 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 ayudarte 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 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 del cliente. Es muy común tener varios clientes o servidores de aplicaciones en estos días. Esta vista te ayudará a identificar si algún cliente o carga de trabajo en particular está provocando un uso elevado de memoria. También puedes implementar el cambio primero en un cliente o servidor de aplicación, supervisar su impacto en la memoria (comparando el uso de memoria con el de otros hosts del cliente) y tomar decisiones fundamentadas.

En esta vista, se resume el uso de memoria, agrupado por subproceso, que es útil para 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 |

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

|  12999 | 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 lentos de consultas de MySQL o los registros generales con thread_id e identificar la sesión o las consultas que llevaron al crecimiento de la memoria.

Solucionar problemas con el uso elevado 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 del esquema sys 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 con tmp_table_size o reducir el innodb_buffer_pool_size para liberar espacio para los búferes por sesión.
2. Si un subproceso en particular consume mucha memoria, puedes filtrar los registros de consultas lentas con thread_id para identificar las consultas problemáticas y ajustarlas para reducir el uso de memoria en ese subproceso o sesión.

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

• Revisa la configuración de MySQL con cuidado y confirma que no haya búferes ni cachés sobreasignados. Este artículo te ayudará a comprender cómo MySQL asigna memoria a diferentes búferes o cachés.
• Identifica la función, la configuración o la consulta que causa el problema de memoria y trata de reproducirlo.
• Revisa las notas de la versión de MySQL de las siguientes versiones secundarias de la misma serie para verificar si se corrigió algún error con un 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 verificar si otros usuarios de la comunidad informaron algún problema similar.
• Crea un error nuevo con un caso de prueba reproducible si no hay un error existente relacionado con el mismo problema.