Solucionar problemas de memoria de MySQL

Una condición de falta de memoria (OOM) se produce cuando un proceso intenta asignar memoria y falla porque el servidor no tiene memoria disponible. Esto suele desencadenar la ejecución de Linux OOM Killer, un proceso que el kernel de Linux utiliza cuando el sistema tiene poca memoria y que finaliza el proceso de base de datos.

Un fallo en la base de datos debido a una condición de falta de memoria no es lo ideal, ya que el proceso de la base de datos se finaliza mediante la directiva SIGKILL, que no da a la aplicación ninguna posibilidad de que lleve a cabo un cierre normal de MySQL. Esto da lugar a los siguientes problemas:

• El servidor MySQL está inactivo (mientras completa la recuperación tras el fallo, que puede tardar varios minutos), lo cual afecta a los usuarios y al negocio.

• El servidor MySQL tarda más tiempo en preparar por completo los búferes y las cachés para alcanzar el máximo rendimiento.
• Posibilidad de que se dañen datos debido a un fallo inesperado.

MySQL ha añadido instrumentación de memoria en performance_schema a partir de la versión 5.7, que ayuda a comprender la asignación de memoria de MySQL. En este artículo se explica cómo solucionar problemas de memoria de MySQL con performance_schema.

Cada instancia de Cloud SQL funciona con una máquina virtual que se ejecuta en un servidor host de Google Cloud. Cada máquina virtual gestiona el programa de base de datos, como el servidor MySQL y los agentes de servicio que prestan servicios de asistencia, como almacenamiento de registros y monitorizació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 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 las instancias que tienen 10 GB de RAM o más, el proceso mysqld tiene un límite de uso del 92 % de la RAM de la instancia, y el grupo de búferes de InnoDB se configura entre el 75 % y el 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 obtener información sobre la asignación de memoria en MySQL.

Cuando el uso de la memoria de mysqld supera el límite configurado en el proceso mysqld, Linux OOM Killer cancela el proceso mysqld, ya que es el mayor consumidor de memoria de la instancia.

performance_schema es una característica de MySQL que permite monitorizar la ejecución del servidor a nivel bajo. Cloud SQL para MySQL permite habilitar performance_schema en las instancias que tienen una RAM de 3 GB o más. Esta característica viene habilitada de forma predeterminada en instancias con al menos 15 GB de RAM, a partir de la versión 8.0. de MySQL. 

El rendimiento de performance_schema está inhabilitado de forma predeterminada en las versiones 5.6 y 5.7 de MySQL. Se puede habilitar mediante marcas de bases de datos. 

La habilitación de performance_schema implica una sobrecarga del rendimiento y también afecta a la memoria. Consulta el modelo de asignación de memoria de performance_schema para entender la asignación de memoria.

La instrumentación de memoria performance_schema ayuda a monitorizar el uso de memoria en MySQL. Los instrumentos de memoria tienen nombres que siguen el formato memory/code_area/instrument_name, donde code_area es un valor como sql o Innodb, y instrument_name es la información 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)

Al habilitar instrumentos de memoria en el tiempo de ejecución, solo se contarán los objetos de memoria creados después de habilitar la instrumentación. Por lo tanto, se recomienda esperar a que el servidor recoja suficientes datos para solucionar el problema.

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 numerosas vistas que sintetizan los datos de performance_schema en formatos fáciles de entender. La mayoría de estas vistas están agrupadas por parejas. Tienen el mismo nombre y una de ellas incluye 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)

La vista sys.memory_global_total 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)

El archivo 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$ tiene como objetivo proporcionar resultados más fáciles de leer y de usar. La vista con el prefijo x$, que muestra los mismos valores en formato sin procesar, está más destinada a su uso con otras herramientas que llevan a cabo su propio procesamiento de los datos.

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

Antes de desglosar cada tipo de asignación, se recomienda obtener una descripción general del uso de la memoria en cada área de código, como Innodb, sql, performance_schema, etc.

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)

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

Ahora bien, si se consulta sys.memory_global_by_current_bytes, se muestra directamente el uso de memoria de las estructuras internas de MySQL, tal como se describe en la columna event_name. Si estás solucionando el aumento anómalo de la memoria, puedes identificar el origen 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 incluye información detallada de cada tipo de asignación.

En esta vista se resume el uso de memoria agrupado según 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 tarea (recomendado), esta vista puede ayudarte a identificar si una aplicación o un trabajo determinados consumen 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 de cliente. Hoy en día, es muy habitual tener varios servidores de aplicaciones o cliente. Esta vista te ayudará a identificar si un cliente o carga de trabajo en particular está provocando un uso elevado de la memoria. También puedes implementar primero el cambio en un cliente/servidor de aplicaciones, monitorizar su impacto en la memoria (comparando el uso de memoria con otros hosts de cliente) y tomar decisiones fundamentadas.

En esta vista se resume el uso de la memoria, agrupado por hilos, para poder detectar los hilos que ocupan más 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 detectas que un hilo determinado consume mucha memoria, puedes filtrar los registros de consultas generales o de consultas lentas de MySQL con thread_id, e identificar las sesiones o las consultas que provocaron el crecimiento de la memoria.

Resolver el uso elevado de la memoria es una tarea complicada. Afortunadamente, la instrumentación de la memoria performance_schema ayuda a los usuarios a entender cómo utilizan la memoria las estructuras internas de MySQL. Las vistas del esquema sys facilitan la interpretación de la información. Si se producen problemas relacionados con la memoria, esta característica te ayudará a detectar el origen del problema y resolverlo haciendo cambios en la configuración o en la consulta.

Por ejemplo:

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

Si detectas una situación en la que una instancia de MySQL utiliza más memoria de la que debería, el uso de la memoria aumenta continuamente hasta que el servidor se queda sin memoria y el parámetro performance_schema muestra una diferencia en el total frente a la memoria instrumentada o no indica a qué se asigna la memoria, podría tratarse de un síntoma de pérdida de memoria. Si sospechas que se ha producido una pérdida de memoria, puedes seguir estos pasos:

• Revisa detenidamente la configuración de MySQL y confirma que no haya búferes ni cachés sobreasignados. En este artículo se explica cómo MySQL asigna la memoria para diferentes búferes y cachés.
• Identifica la función, la configuración o la consulta que provoca el problema de memoria e intenta reproducirlo.
• Consulta las notas de la versión de MySQL de las siguientes versiones secundarias de la misma serie para comprobar si se ha solucionado algún error con un comportamiento similar.
• Actualiza MySQL a la última versión secundaria para ver si se soluciona el problema de memoria.
• Busca en la base de datos de errores de MySQL para comprobar si otros usuarios de la comunidad han informado acerca de algún problema similar.
• Si no hay ningún error que corresponda al mismo problema, crea un error con un caso de prueba que se pueda reproducir.