Optimizar el uso elevado de memoria en las instancias

MySQL   |  PostgreSQL   |  SQL Server

Es habitual que las instancias consuman mucha memoria o que tengan problemas con la falta de memoria (OOM). Cuando una instancia de base de datos se ejecuta con un uso elevado de memoria, suele provocar problemas de rendimiento, estancamiento o incluso periodos de inactividad en las bases de datos.

Algunos bloques de memoria de MySQL se usan de forma global. Esto significa que todas las cargas de trabajo de las consultas comparten ubicaciones de memoria, están ocupadas todo el tiempo y solo se liberan cuando se detiene el proceso de MySQL. Algunos bloques de memoria se basan en sesiones, lo que significa que, en cuanto se cierra una sesión, la memoria utilizada por esa sesión también se libera y vuelve al sistema.

Cuando una instancia de Cloud SQL para MySQL usa mucha memoria, Cloud SQL recomienda que identifiques la consulta o el proceso que está usando mucha memoria y la liberes. El consumo de memoria de MySQL se divide en tres partes principales:

• Consumo de memoria de procesos y subprocesos
• Consumo de memoria de búfer
• Consumo de memoria caché

Consumo de memoria de procesos y subprocesos

Cada sesión de usuario consume memoria en función de las consultas en ejecución, los búferes o la caché que utilice esa sesión, y se controla mediante los parámetros de sesión de MySQL. Entre los parámetros principales se incluyen los siguientes:

• thread_stack
• net_buffer_length
• read_buffer_size
• read_rnd_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

Si hay N consultas en ejecución en un momento concreto, cada consulta consumirá memoria según estos parámetros durante la sesión.

Consumo de memoria de búfer

Esta parte de la memoria es común para todas las consultas y se controla mediante parámetros como innodb_buffer_pool_size, innodb_log_buffer_size y key_buffer_size.

El grupo de búferes de InnoDB, que se configura con la marca innodb_buffer_pool_size, ocupa una cantidad significativa de memoria en tu instancia de Cloud SQL para MySQL y sirve como caché para mejorar el rendimiento. Para reducir el riesgo de que se produzcan eventos de falta de memoria (OOM), puedes habilitar el grupo de búferes gestionado (vista previa).

Consumo de memoria caché

La memoria caché incluye una caché de consultas, que se usa para guardar las consultas y sus resultados para que se puedan recuperar los datos más rápido en las mismas consultas posteriores. También incluye la caché binlog para retener los cambios realizados en el registro binario mientras se ejecuta la transacción, y está controlada por binlog_cache_size.

Otro consumo de memoria

La memoria también se usa en las operaciones de unión y ordenación. Si tus consultas usan operaciones de unión u ordenación, esas consultas usan memoria en función de join_buffer_size y sort_buffer_size.

Además, si habilitas performance_schema, consumirá memoria. Para comprobar el uso de memoria del esquema de rendimiento, utiliza la siguiente consulta:

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

En MySQL hay muchos instrumentos que puedes configurar para monitorizar el uso de memoria a través del esquema de rendimiento. Para obtener más información, consulta la documentación de MySQL.

El parámetro relacionado con MyISAM para la inserción de datos en bloque es bulk_insert_buffer_size.

Para obtener información sobre cómo usa MySQL la memoria, consulta la documentación de MySQL.

Recomendaciones

En las secciones siguientes se ofrecen algunas recomendaciones para optimizar el uso de la memoria.

Habilitar el grupo de búferes gestionado

Vista previa

Este producto o función está sujeto a los "Términos de las Ofertas de Acceso Previo a la Disponibilidad General" de la sección Términos Generales de los Servicios de los Términos Específicos de los Servicios. Los productos y las funciones previos a la disponibilidad general están disponibles tal cual y pueden tener una compatibilidad y asistencia limitadas. Para obtener más información, consulta las descripciones de las fases de lanzamiento.

Si el uso de memoria es elevado, tu instancia puede experimentar eventos de falta de memoria (OOM). Para evitar eventos de falta de memoria, habilita el grupo de búferes gestionado para reducir el valor de innodb_buffer_pool_size y liberar memoria. Cuando el uso de memoria se estabiliza en un valor inferior, MySQL aumenta el valor de innodb_buffer_pool_size de forma incremental hasta su valor original.

No puedes habilitar el pool de búfer gestionado en instancias de núcleo compartido ni en MySQL 5.6 o MySQL 5.7.

Para habilitar el grupo de búferes gestionado en tu instancia, asigna el valor on a la marca innodb_cloudsql_managed_buffer_pool. Para obtener más información sobre cómo definir marcas de bases de datos, consulta Definir una marca de base de datos.

Para cambiar el valor de la marca innodb_cloudsql_managed_buffer_pool, no es necesario reiniciar la instancia de Cloud SQL.

De forma predeterminada, si tu base de datos MySQL supera el 95% de la memoria asignada, Cloud SQL empezará a reducir el tamaño de su innodb_buffer_pool_size. Para modificar el umbral del 95 %, asigna a la marca innodb_cloudsql_managed_buffer_pool_threshold_pct otro valor porcentual. Por ejemplo, para ajustar el umbral al 97%, usa el siguiente comando:

gcloud sql instances patch INSTANCE_NAME
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

Puedes asignar al valor de la marca innodb_cloudsql_managed_buffer_pool_threshold_pct un número entero entre 50 y 99. No es necesario reiniciar la instancia de Cloud SQL para cambiar el valor del umbral de uso de memoria.

Cuando el pool de búfer gestionado ajusta el valor de innodb_buffer_pool_size, los cambios no se reflejan en el valor de la marca que se muestra en la consola Google Cloud . Para ver el valor actual de innodb_buffer_pool_size cuando el grupo de búferes gestionado está habilitado, puedes consultar el valor de la marca mediante el cliente de MySQL:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

Reducir el tamaño del grupo de búferes no puede evitar los errores de falta de memoria en todos los casos. Por ejemplo, algunas cargas de trabajo pueden consumir memoria de forma insostenible o aumentar a un ritmo repentino, algunas instancias de Cloud SQL pueden tener un aprovisionamiento insuficiente o el grupo de búferes puede no estar calentado. Es posible que Cloud SQL no pueda liberar memoria lo suficientemente rápido como para adaptarse a los cambios repentinos en la carga de trabajo de la memoria. Además, Cloud SQL no puede admitir valores mal configurados de otras marcas de memoria.

Nota: Si disminuye el valor de innodb_buffer_pool_size_size, puede afectar a la latencia y al rendimiento de las consultas. Sin embargo, habilitar el grupo de búferes gestionado reduce el valor de innodb_buffer_pool_size_size solo cuando la instancia corre el riesgo de que se produzca un evento OOM. Cuando el uso de memoria se estabiliza, el valor de innodb_buffer_pool_size_size se restaura gradualmente a su valor original.

Usar el explorador de métricas para identificar el uso de memoria

Puedes consultar el uso de memoria de una instancia con la métrica database/memory/components.usage en el explorador de métricas.

Por lo general, si tienes menos del 10% de memoria en database/memory/components.cache y database/memory/components.free combinados, el riesgo de que se produzca un evento OOM es alto. Para monitorizar el uso de memoria y evitar eventos de falta de memoria, te recomendamos que configures una política de alertas con una condición de umbral de métrica en database/memory/components.usage.

En la siguiente tabla se muestra la relación entre la memoria de tu instancia y el umbral de alerta recomendado:

Memoria de la instancia	Umbral de alerta recomendado
Menor o igual que 16 GB	90 %
Más de 16 GB	95 %

Calcular el consumo de memoria

Calcula el uso máximo de memoria de tu base de datos MySQL para seleccionar el tipo de instancia adecuado. Usa la siguiente fórmula:

Uso máximo de memoria de MySQL = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) × max_connections)

Estos son los parámetros que se usan en la fórmula:

• innodb_buffer_pool_size: tamaño en bytes del grupo de búferes, el área de memoria donde InnoDB almacena en caché los datos de tablas e índices.
• innodb_additional_mem_pool_size: tamaño en bytes de un grupo de memoria que usa InnoDB para almacenar información del diccionario de datos y otras estructuras de datos internas.
• innodb_log_buffer_size: tamaño en bytes del búfer que usa InnoDB para escribir en los archivos de registro del disco.
• tmp_table_size: El tamaño máximo de las tablas temporales internas en memoria creadas por el motor de almacenamiento MEMORY y, a partir de MySQL 8.0.28, el motor de almacenamiento TempTable.
• key_buffer_size: tamaño del búfer usado para los bloques de índice. Los bloques de índice de las tablas MyISAM se almacenan en búfer y se comparten entre todos los hilos.
• read_buffer_size: cada subproceso que realiza un análisis secuencial de una tabla MyISAM asigna un búfer de este tamaño (en bytes) a cada tabla que analiza.
• read_rnd_buffer_size: esta variable se usa para las lecturas de tablas MyISAM, para cualquier motor de almacenamiento y para la optimización de lectura de varios intervalos.
• sort_buffer_size: cada sesión que debe realizar una ordenación asigna un búfer de este tamaño. sort_buffer_size no es específico de ningún motor de almacenamiento y se aplica de forma general para la optimización.
• join_buffer_size: tamaño mínimo del búfer que se usa para los análisis de índice sin formato, los análisis de índice de intervalo y las uniones que no usan índices y, por lo tanto, realizan análisis de tabla completos.
• max_connections: número máximo permitido de conexiones de cliente simultáneas.

Nota: Hay muchos otros componentes pequeños que consumen memoria y que dependen de las consultas, por lo que también debe haber espacio para ellos.

Solucionar problemas de consumo elevado de memoria

• Ejecuta SHOW PROCESSLIST para ver las consultas en curso que están consumiendo memoria. Muestra todos los subprocesos conectados y sus instrucciones SQL en ejecución, e intenta optimizarlos. Presta atención a las columnas de estado y duración.

  mysql> SHOW [FULL] PROCESSLIST;
  
  

• Consulta SHOW ENGINE INNODB STATUS en la sección BUFFER POOL AND MEMORY para ver el uso actual del grupo de búferes y de la memoria, lo que puede ayudarte a definir el tamaño del grupo de búferes.

  mysql> SHOW ENGINE INNODB STATUS \G
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total memory allocated 398063986; in additional pool allocated 0
  Dictionary memory allocated 12056
  Buffer pool size 89129
  Free buffers 45671
  Database pages 1367
  Old database pages 0
  Modified db pages 0
  

• Usa el comando SHOW variables de MySQL para consultar los valores del contador, que te proporcionan información como el número de tablas temporales, el número de subprocesos, el número de cachés de tablas, las páginas sucias, las tablas abiertas y el uso del grupo de búferes.

  mysql> SHOW variables like 'VARIABLE_NAME'
  

Aplicar cambios

Después de analizar el uso de memoria de los diferentes componentes, define la marca adecuada en tu base de datos MySQL. Para cambiar la marca en una instancia de Cloud SQL para MySQL, puedes usar la Google Cloud consola o gcloud CLI. Para cambiar el valor de la marca mediante la Google Cloud consola, edita la sección Marcas, selecciona la marca e introduce el nuevo valor.

Por último, si el uso de memoria sigue siendo elevado y crees que las consultas y los valores de las marcas están optimizados, considera la posibilidad de aumentar el tamaño de la instancia para evitar errores de falta de memoria.

Siguientes pasos

• Google Cloud recomendadores