Prácticas recomendadas para configurar el uso óptimo de la memoria de MySQL

En esta publicación de blog, discutimos algunas de las mejores prácticas para configurar el uso óptimo de la memoria MySQL.

La correcta configuración del uso de los recursos de memoria disponibles es una de las cosas más importantes que tienes que hacer con MySQL para un rendimiento y estabilidad óptimos. Desde MySQL 5.7, la configuración predeterminada usa una cantidad muy limitada de memoria; dejar los valores predeterminados es una de las peores cosas que puede hacer. Pero una configuración incorrecta puede resultar en un rendimiento deficiente (o incluso bloqueos).

La primera regla para configurar el uso de la memoria MySQL es usted No quiero que tu MySQL provoque que el sistema operativo se bloquee. Incluso la actividad de intercambio menor puede reducir drásticamente el rendimiento de MySQL. Tenga en cuenta la clave de «actividad» aquí. Es bueno tener algo de espacio usado en su archivo de intercambio, ya que probablemente haya partes del sistema operativo que no se usen cuando MySQL se está ejecutando, y es una buena idea intercambiar. Lo que no desea es el intercambio constante durante la operación, que se ve fácilmente en las columnas «si» y «entonces» en el
vmstat producción.

Uso óptimo de la memoria MySQL

Ejemplo: Sin intercambio significativo

Uso óptimo de la memoria MySQL

Ejemplo: Intercambio pesado en marcha

Si tu corres Supervisión y Gestión de Percona, también puede ver el gráfico de la actividad comercial en el panel de información general del sistema.

Uso óptimo de la memoria MySQL

Si tiene picos de más de 1 MB / seg, o actividad de intercambio constante, es posible que deba revisar la configuración de su memoria.

La asignación de memoria MySQL es complicada. Hay búferes globales, búferes de conexión (dependiendo de la carga de trabajo) y algunas asignaciones de memoria no controladas (es decir, en procedimientos almacenados), todo lo cual contribuye a la dificultad de calcular cuánta memoria MySQL debe usarse para su carga de trabajo. Es mejor verificar observando el tamaño de la memoria virtual (VSZ) que usa MySQL. Puedes obtenerlo desde la «cima», o desde la carrera.
PD auxiliar | grep mysqld.

La quinta columna aquí muestra el uso de VSZ (alrededor de 11 GB).

Tenga en cuenta que es probable que el VSZ cambie con el tiempo. A menudo es una buena idea rastrearlo en su sistema de vigilancia y configurar una alerta para que le haga ping cuando alcance un límite específico. No permiten que el proceso mysqld VSZ supere el 90% de la memoria del sistema (y menos si eres más que MySQL en el sistema).

Es una buena idea comenzar con seguridad configurando de manera conservadora el búfer general y el nivel de conexión, y luego crecer a medida que avanza. Muchos se pueden establecer en línea, inclusive innodb_buffer_pool_size en MySQL 5.7.

¿Cuánta memoria asignar a MySQL?

Entonces, ¿cómo decide cuánta memoria asignar a MySQL frente a todo lo demás? En la mayoría de los casos, no debería consumir más del 90% de su memoria física en MySQL, ya que necesita tener algunas reservas para el sistema operativo y cosas como el almacenamiento en caché de archivos de registro binarios, archivos de tiempo, etc.

Hay casos en los que MySQL debería usar significativamente menos del 90% de la memoria:

  • Si hay otros procesos importantes ejecutándose en el mismo servidor, ya sea todo el tiempo o periódicamente. Si tiene trabajos por lotes pesados ​​de cron, que requieren mucha memoria, debe contar con ello.
  • Si desea utilizar la memoria caché del sistema operativo para algunos motores de almacenamiento. Con InnoDB, lo recomendamos encarecidamente.
    innodb_flush_método=O_DIRECTO
    en la mayoría de los casos, no utilizan la caché de archivos del sistema operativo. Sin embargo, hubo momentos en los que tenía sentido usar E/S almacenada en búfer con InnoDB. Si todavía es MyISAM, necesita almacenar en caché el sistema operativo para la parte de «datos» de sus tablas.
  • Si su carga de trabajo tiene requisitos importantes, la memoria caché del sistema operativo: MyISAM en tablas de discos temporales, clasificación de archivos y algunos otros archivos temporales que crea MySQL deben estar bien almacenados en caché para un rendimiento óptimo.

Una vez que sepa cuánta memoria desea que tenga el proceso de MySQL en total, debe pensar para qué propósito debe usarse la memoria en MySQL. La primera parte del uso de la memoria en MySQL está relacionada con la carga de trabajo: si tiene varias conexiones activas al mismo tiempo y está ejecutando selecciones pesadas usando mucha memoria para clasificaciones o tablas temporales, es posible que necesite mucha memoria (en particular si el esquema de rendimiento está habilitado). En otros casos, esta cantidad de memoria es mínima. Generalmente, necesitas un lugar entre 1 y 10 GB para este propósito.

Otra cosa que debes considerar es fragmentación de la memoria. Según la biblioteca de asignación de memoria que esté utilizando (glibc, TCMalloc, jemalloc, etc.), la configuración del sistema operativo, como Transparent Huge Pages (THP) y la carga de trabajo, pueden aumentar a medida que aumenta el uso de la memoria con el tiempo (hasta que alcance un estado estable). . ). La fragmentación de la memoria también puede representar el 10 % o más del uso de memoria adicional.

Finalmente, pensamos en varios búferes globales y almacenamiento en caché. En casos típicos, principalmente solo tiene
innodb_buffer_pool_size preocuparse por. Pero es posible que también deba considerarlo
tamaño_búfer_clave,
tamaño_caché_consulta así como también
table_cache
y
table_open_cache. Estos también son responsables de la asignación de memoria global, incluso si no se cuentan en bytes. Performance _Schema también puede ocupar mucha memoria, especialmente si tiene una gran cantidad de conexiones o tablas en el sistema.

Al especificar el tamaño de los búferes y cachés, debe determinar lo que especifica. En orden
innodb_buffer_pool_size, recuerde que hay otro 5-10% de la memoria que se asigna para estructuras de datos adicionales, y ese número es mayor si usa compresión o configuración.
innodb_page_size más pequeño que 16K.

Para sistemas con una gran cantidad de memoria, la memoria caché de la base de datos será, con mucho, el mayor consumidor de memoria y usted asignará la mayor parte de su memoria. Cuando agrega memoria adicional al sistema, normalmente es para aumentar el tamaño de la memoria caché de la base de datos.

Hagamos algunas matemáticas para un ejemplo específico. Suponga que tiene un sistema (físico o virtual) con 16 GB de memoria. Solo ejecutamos MySQL en este sistema, con un motor de almacenamiento InnoDB y usamos
innodb_flush_método=O_DIRECTO, por lo que podemos asignar el 90 % (o 14,4 GB) de memoria a MySQL. Para nuestra carga de trabajo, asumimos que la administración de conexiones y otras conexiones MySQL basadas en sobrecarga ocupan 1 GB (lo que deja 13,4 GB). 0,4 GB es probablemente consumido por muchos otros búferes globales (
innodb_log_buffer_size, cachés de tablas, varias otras necesidades, etc.), que ahora deja 13 GB. Teniendo en cuenta la sobrecarga del 5-7% que tiene InnodB Buffer Pool, un parámetro sensato es
innodb_buffer_pool_size=12G – lo que vemos muy comúnmente funciona bien para sistemas con 16 GB de memoria.

Ahora que hemos configurado el uso de la memoria MySQL, también debemos guardar la configuración del sistema operativo. La primera pregunta que debemos hacernos es si no queremos que MySQL intercambie, ¿debemos tener también habilitado el archivo de intercambio? En la mayoría de los casos, la respuesta es sí – desea tener habilitado el archivo de intercambio (busque un mínimo de 4 GB y no menos del 25% de la memoria instalada) por dos razones:

  • Es muy probable que el sistema operativo tenga algunas partes que no se utilizan cuando se ejecuta como un servidor de base de datos. Es mejor dejarlos salir en lugar de obligarte a mantenerlos en la memoria.
  • Si cometió un error al configurar MySQL, o tiene algún proceso no autorizado que ocupa mucha más memoria de lo esperado, generalmente es una situación mucho mejor perder rendimiento debido a un intercambio que eliminar MySQL sin memoria (OOM). ) error – potencialmente causando tiempo de inactividad.

Dado que solo queremos que el archivo de intercambio se use en emergencias, como cuando no hay memoria disponible o para intercambiar procesos inactivos, queremos reduce la tendencia del sistema operativo a cambiar (
ecus 1 > /proceso/sistema/máquina virtual/intercambio). Sin esta configuración, es posible que el sistema operativo cambie partes de MySQL solo porque siente la necesidad de aumentar la cantidad de caché de archivos disponible (que casi siempre es una elección incorrecta para MySQL).

Lo siguiente cuando se trata de la configuración del sistema operativo es establecer el asesino fuera de memoria. Es posible que haya visto un mensaje como este en su archivo de registro del kernel:

Abr. 24 02:43:18 db01 núcleo: Fuera de de memoria: matar proceso 22211 (mysqld) puntaje 986 o sacrificio chico

Cuando MySQL mismo tiene la culpa, es algo bastante razonable. Sin embargo, también es posible que el problema real fuera parte de la actividad por lotes que estaba ejecutando: secuencias de comandos, copias de seguridad, etc. En este caso, probablemente desee que los procesos finalicen si el sistema no tiene suficiente memoria en lugar de MySQL.

Para hacer que MySQL sea un candidato menos probable de ser asesinado por el asesino OOM, puede ajustar el comportamiento para hacer que MySQL sea menos preferible con lo siguiente:

ecus ‘-800’ > /proceso/PS(pidof mysqld)/oom_score_adj

Esto hará que el kernel de Linux prefiera matar primero a otros consumidores de memoria pesados.

Finalmente, en un sistema con más de un zócalo de CPU, debe atención NUMA cuando se trata de la asignación de memoria MySQL. En las últimas versiones de MySQL, desea habilitarlo
innodb_numa_interleave=1. En versiones anteriores, puede ejecutarlo manualmente
numactl tejido=todos antes de iniciar el servidor MySQL, o utilice la opción de configuración number_interleave en Percona Server.

Más recursos:

Correo

seminarios web

Presentaciones

Libros electrónicos gratuitos

Instrumentos

Author: Ing. Luis

A lo largo de conocer Windows y otros sistemas operativos me eh encontrado con diversos tipos de error, ahora brindo soluciones según mi experiencia-

Deja un comentario