Asignación de RAM para MariaDB: la respuesta corta

Si solo usa MyISAM, configure key_buffer_size al 20% de disponible RAM. (Más innodb_buffer_pool_size = 0)

Si solo usa InnoDB, configure innodb_buffer_pool_size al 70% de disponible RAM. (Más key_buffer_size = 10M, pequeño, pero no cero).

Regla de oro para la afinación:

  • Comience con una copia publicada de my.cnf / my.ini.
  • Cambie key_buffer_size e innodb_buffer_pool_size según el uso del motor y la RAM.
  • Las consultas lentas generalmente se pueden ‘arreglar’ a través de índices, cambios de esquema o cambios SELECT, no mediante ajustes.
  • No se deje llevar por la caché de consultas hasta que comprenda lo que puede y no puede hacer.
  • No cambie nada más a menos que tenga problemas (por ejemplo, conexiones máximas).
  • Asegúrese de que los cambios estén bajo [mysqld] sección, no alguna otra sección.

El 20% / 70% asume que tiene al menos 4 GB de RAM. Si tiene una antigüedad pequeña o una máquina virtual pequeña, entonces esos porcentajes son demasiado altos.

Ahora para los detalles sangrientos.

¿Qué es el búfer de claves?

MyISAM hace dos cosas diferentes para el almacenamiento en caché.

  • Los bloques de índice (1 KB cada uno, estructurados en BTree, del archivo .MYI) viven en el “búfer de claves”.
  • El almacenamiento en caché de bloques de datos (del archivo .MYD) se deja en el sistema operativo, así que asegúrese de dejar un montón de espacio libre para esto. Advertencia: algunas versiones de SO siempre afirman utilizar más del 90%, incluso cuando hay mucho espacio libre. 
SHOWGLOBALSTATUSLIKE'Key%';

luego calcule Key_read_requests / Key_reads. Si es alto (digamos, más de 10), entonces el búfer de clave es lo suficientemente grande; de ​​lo contrario, debe ajustar el valor de key_buffer_size.

¿Qué es el Buffer Pool?

InnoDB hace todo su almacenamiento en caché en un grupo de búfer, cuyo tamaño está controlado por innodb_buffer_pool_size. De forma predeterminada, contiene bloques de índice y datos de 16 KB de las tablas abiertas (consulte innodb_page_size), además de algunos gastos generales de mantenimiento.

Desde MariaDB 5.5, se permiten varios grupos de búferes; esto puede ayudar porque hay un mutex por grupo, lo que alivia parte del cuello de botella del mutex.

Más sobre el ajuste de InnoDB

Otro algoritmo

Esto establecerá la configuración de la caché principal al mínimo; podría ser importante para sistemas con muchos otros procesos y / o la RAM es de 2GB o menos.

MOSTRAR ESTADO DE LA TABLA para todas las tablas en todas las bases de datos.

Sume Index_length para todas las tablas MyISAM. Establezca key_buffer_size no más grande que ese tamaño.

Sume Data_length + Index_length para todas las tablas InnoDB. Establezca innodb_buffer_pool_size en no más del 110% de ese total.

Si eso lleva a un cambio, recorta ambas configuraciones. Sugiera reducirlos proporcionalmente.

Ejecute esto para ver los valores de su sistema. (Si tiene muchas mesas, puede llevar minuto (s)).

SELECTENGINE,ROUND(SUM(data_length)/1024/1024,1)AS"Data MB",ROUND(SUM(index_length)/1024/1024,1)AS"Index MB",ROUND(SUM(data_length + index_length)/1024/1024,1)AS"Total MB",COUNT(*)"Num Tables"FROM  INFORMATION_SCHEMA.TABLESWHERE  table_schema notin("information_schema","PERFORMANCE_SCHEMA","SYS_SCHEMA","ndbinfo")GROUPBYENGINE;

Asignación de memoria de consultas

Hay dos variables que dictan cómo MariaDB asigna la memoria mientras analiza y ejecuta una consulta. query_prealloc_size define el búfer estándar para la memoria utilizada para la ejecución de consultas y query_alloc_block_size que es el tamaño de los bloques de memoria si query_prealloc_size no era lo suficientemente grande. Obtener estas variables correctamente reducirá la fragmentación de la memoria en el servidor.

Cuello de botella de Mutex

MySQL se diseñó en los días de las máquinas con una sola CPU y se diseñó para ser fácilmente adaptado a muchas arquitecturas diferentes. Desafortunadamente, eso lleva a un descuido en cómo entrelazar acciones. Hay una pequeña cantidad (demasiado pequeña) de “mutex” para obtener acceso a varios procesos críticos. De nota:

  • Key_buffer de MyISAM
  • La caché de consultas
  • Buffer_pool de InnoDB Con cajas multinúcleo, el problema de mutex está causando problemas de rendimiento. En general, después de 4-8 núcleos, MySQL se vuelve más lento, no más rápido. MySQL 5.5 y XtraDB de Percona lo hicieron algo mejor en InnoDB; el límite práctico para los núcleos es más como 32, y el rendimiento tiende a estancarse después de eso en lugar de disminuir. 5.6 afirma escalar hasta unos 48 núcleos.

HyperThreading y núcleos múltiples (CPU)

Respuestas cortas (para versiones anteriores de MySQL y MariaDB):

  • Apague HyperThreading
  • Apague los núcleos más allá de 8
  • HyperThreading es principalmente cosa del pasado, por lo que es posible que esta sección no se aplique.

HyperThreading es excelente para el marketing, pésimo para el rendimiento. Implica tener dos unidades de procesamiento que comparten una sola caché de hardware. Si ambas unidades están haciendo lo mismo, el caché será razonablemente útil. Si las unidades están haciendo cosas diferentes, estarán golpeando las entradas de caché de las demás.

Además, MySQL no es excelente para usar múltiples núcleos. Entonces, si apaga HT, los núcleos restantes se ejecutan un poco más rápido.

SO de 32 bits y MariaDB

Primero, el sistema operativo (¿y el hardware?) Puede conspirar para no permitirle usar los 4GB, si eso es lo que tiene. Si tiene más de 4 GB de RAM, el exceso de más de 4 GB es _totalmente_ inaccesible e inutilizable en un sistema operativo de 32 bits.

En segundo lugar, es probable que el sistema operativo tenga un límite en la cantidad de RAM que permitirá que utilice cualquier proceso.

Ejemplo: maxdsiz de FreeBSD, cuyo valor predeterminado es 512 MB.

Ejemplo:

$ ulimit -a
...
max memory size (kbytes,-m)524288

Entonces, una vez que haya determinado cuánta RAM está disponible para mysqld, aplique el 20% / 70%, pero redondee un poco.

Si recibe un error como [ERROR] /usr/libexec/mysqld: Out of memory (Needed xxx bytes), probablemente significa que MySQL superó lo que el sistema operativo está dispuesto a ofrecer. Disminuya la configuración de la caché.

SO de 64 bits con MariaDB de 32 bits

El sistema operativo no está limitado por 4 GB, pero MariaDB sí lo está.

Si tiene al menos 4 GB de RAM, entonces tal vez estos sean buenos:

  • key_buffer_size = 20% de _toda_ de RAM, pero no más de 3G
  • innodb_buffer_pool_size = 3G

Probablemente debería actualizar MariaDB a 64 bits.

SO de 64 bits y MariaDB

Solo MyISAM: key_buffer_size: Utiliza aproximadamente el 20% de RAM. Establezca (en my.cnf / my.ini) innodb_buffer_pool_size = 0 = 0.

Solo InnoDB: innodb_buffer_pool_size = 0 = 70% de RAM. Si tiene mucha RAM y está usando 5.5 (o posterior), considere tener varios grupos. Recomendamos 1-16 innodb_buffer_pool_instances, de modo que cada uno no sea menor a 1GB. (Lo siento, no hay métricas sobre cuánto ayudará esto; probablemente no mucho).

Mientras tanto, establezca key_buffer_size = 20M (pequeño, pero distinto de cero)

Si tiene una mezcla de motores, baje ambos números.

max_connections, thread_stack Cada “hilo” requiere cierta cantidad de RAM. Esto solía ser de unos 200 KB; 100 subprocesos serían 20 MB, no un tamaño significativo. Si tiene max_connections = 1000, entonces está hablando de 200 MB, tal vez más. Tener tantas conexiones probablemente implica otros problemas que deberían abordarse.

En 5.6 (o MariaDB 5.5), la agrupación de subprocesos opcional interactúa con max_connections. Este es un tema más avanzado.

El desbordamiento de la pila de subprocesos ocurre raramente. Si es así, haz algo como thread_stack = 256K

Más sobre max_connections, wait_timeout, agrupación de conexiones, etc.

table_open_cache

(En versiones anteriores esto se llamaba table_cache)

El sistema operativo tiene un límite en la cantidad de archivos abiertos que permitirá que tenga un proceso. Cada tabla necesita de 1 a 3 archivos abiertos. Cada PARTICIÓN es efectivamente una mesa. La mayoría de las operaciones en una tabla particionada abren _todas_ las particiones.

En * nix, ulimit le dice cuál es el límite de archivos. El valor máximo está en decenas de miles, pero a veces se establece en solo 1024. Esto lo limita a unas 300 tablas. Más discusión sobre ulimit

(Este párrafo está en disputa). Por otro lado, la caché de la tabla está (fue) implementada de manera ineficiente: las búsquedas se realizaron con un escaneo lineal. Por lo tanto, establecer table_cache en miles podría ralentizar mysql. (Los puntos de referencia han demostrado esto).

Puede ver qué tan bien está funcionando su sistema a través de MOSTRAR ESTADO GLOBAL; y calcular las aperturas / segundo a través de Open_files / Uptime Si esto es más de, digamos, 5, table_open_cache debería aumentarse. Si es menor que, digamos, 1, podría obtener una mejora al disminuir table_open_cache.

Desde MariaDB 10.1, table_open_cache tiene el valor predeterminado 2000.

Caché de consultas

Respuesta corta: query_cache_type = OFF y query_cache_size = 0

Query Cache (QC) es efectivamente un hash que correlaciona las sentencias SELECT con los conjuntos de resultados.

Respuesta larga … Hay muchos aspectos de la “caché de consultas”; muchos son negativos.

  • ¡Alerta de principiante! El control de calidad no tiene ninguna relación con key_buffer y buffer_pool.
  • Cuando es útil, el control de calidad es increíblemente rápido. No sería difícil crear un punto de referencia que se ejecute 1000 veces más rápido.
  • Hay un solo mutex que controla el control de calidad.
  • El control de calidad, a menos que esté en OFF & 0, se consulta para _todos_ SELECT.
  • Sí, el mutex se activa incluso si query_cache_type = DEMAND (2).
  • Sí, el mutex se activa incluso para SQL_NO_CACHE.
  • Cualquier cambio en una consulta (incluso agregar un espacio) conduce (potencialmente) a una entrada diferente en el CC.
  • Si my.cnf dice type = ON y luego lo apaga, no está completamente APAGADO. Árbitro: https://bugs.mysql.com/bug.php?id=60696

La “poda” es costosa y frecuente:

  • Cuando _cualquier_ escritura ocurre en una tabla, _todas_ las entradas en el CC para _ esa_ tabla se eliminan.
  • Sucede incluso en un esclavo de solo lectura.
  • Las purgas se realizan con un algoritmo lineal, por lo que un gran control de calidad (incluso 200 MB) puede ser notablemente lento.

Para ver qué tan bien se está desempeñando su QC, MOSTRAR ESTADO GLOBAL COMO ‘Qc%’; luego calcule la tasa de aciertos de lectura: Qcache_hits / Qcache_inserts Si ha terminado, digamos, 5, valdría la pena conservar el control de calidad.

Si decide que el control de calidad es adecuado para usted, le recomiendo

  • query_cache_size = no más de 50 M
  • query_cache_type = DEMAND
  • SQL_CACHE o SQL_NO_CACHE en todos los SELECT, según las consultas que probablemente se beneficien del almacenamiento en caché.

thread_cache_size

No es necesario ajustar thread_cache_size desde MariaDB 10.2.0. Anteriormente, era una variable sintonizable menor. Zero ralentizará la creación de subprocesos (conexiones). Un número pequeño (digamos, 10) distinto de cero es bueno. Básicamente, la configuración no tiene ningún impacto en el uso de RAM.

Es la cantidad de procesos adicionales a los que hay que aferrarse. No restringe el número de subprocesos; max_connections lo hace.

Registros binarios

Si ha activado el registro binario (a través de log_bin) para la replicación y / o la recuperación en un momento determinado, el sistema creará registros binarios para siempre. Es decir, pueden lentamente llena el disco. Sugiera configurar expire_logs_days = 14 para mantener solo 14 días de registros.

Swappiness

RHEL, en su sabiduría infinita, decidió permitirle controlar la agresividad con la que el sistema operativo intercambiará RAM de forma preventiva. Esto es bueno en general, pero pésimo para MariaDB.

A MariaDB le encantaría que las asignaciones de RAM fueran razonablemente estables: las cachés están (en su mayoría) preasignadas; los hilos, etc., son (en su mayoría) de alcance limitado. Es probable que CUALQUIER intercambio perjudique gravemente el rendimiento de MariaDB.

Con un valor alto para el intercambio, pierde algo de RAM porque el sistema operativo está tratando de mantener una gran cantidad de espacio libre para asignaciones futuras (que es probable que MySQL no necesite).

Con swappiness = 0, el sistema operativo probablemente se bloqueará en lugar de intercambiarse. Preferiría que MariaDB cojeara antes que morir. La última recomendación es swappiness = 1. (2015)

Más confirmación

En algún punto intermedio (digamos, ¿5?) Podría ser un buen valor para un servidor solo de MariaDB.

NUMA

Bien, es hora de complicar la arquitectura de cómo una CPU habla con la RAM. NUMA (Acceso a memoria no uniforme) entra en escena. Cada CPU (o tal vez zócalo con varios núcleos) tiene una parte de la RAM colgando de cada uno. Esto hace que el acceso a la memoria sea más rápido para la RAM local, pero más lento (decenas de ciclos más lento) para la RAM colgando de otras CPU.

Entonces el sistema operativo entra en escena. En al menos un caso (¿RHEL?), Parece que se hacen dos cosas:

  • Las asignaciones de SO se fijan a la RAM de la “primera” CPU.]
  • Otras asignaciones van de forma predeterminada a la primera CPU hasta que esté llena.

Ahora el problema.

  • El sistema operativo y MariaDB han asignado toda la ‘primera’ RAM.
  • MariaDB ha asignado parte de la segunda RAM.
  • El sistema operativo necesita asignar algo. Ouch, está fuera de espacio en la única CPU donde está dispuesto a asignar sus cosas, por lo que intercambia parte de MariaDB. Malo.

dmesg | grep -i numa # para ver si tienes numa

Solución probable: configure el BIOS para “intercalar” las asignaciones de RAM. Esto debería evitar el intercambio prematuro, a costa de accesos a RAM fuera de la CPU la mitad del tiempo. Bueno, tienes los costosos accesos de todos modos, ya que realmente quieres usar toda la RAM. Versiones anteriores de MySQL: numactl –interleave = all. O: innodb_numa_interleave = 1

Otra posible solución: apague numa (si el sistema operativo tiene una forma de hacerlo)

Pérdida / ganancia de rendimiento general: un pequeño porcentaje.

Páginas enormes

Este es otro truco de rendimiento del hardware.

Para que una CPU acceda a la RAM, especialmente asignando una dirección de 64 bits a algún lugar de, digamos, 128 GB o RAM “real”, se utiliza la TLB. (TLB = Búfer de búsqueda de traducción). Piense en TLB como una tabla de búsqueda de memoria asociativa de hardware; dada una dirección virtual de 64 bits, cuál es la dirección real.

Debido a que es una memoria asociativa de tamaño finito, a veces habrá “errores” que requieran acceder a la RAM real para resolver la búsqueda. Esto es costoso, por lo que debe evitarse.

Normalmente, la RAM se ‘pagina’ en piezas de 4 KB; el TLB en realidad mapea los bits superiores (64-12) en una página específica. Luego, los 12 bits inferiores de la dirección virtual se transfieren intactos.

Por ejemplo, 128 GB de RAM de páginas de 4 KB rotas significan 32 millones de entradas en la tabla de páginas. Esto es mucho y probablemente excede con creces la capacidad del TLB. Entonces, ingrese el truco “Página enorme”.

Con la ayuda tanto del hardware como del sistema operativo, es posible tener algo de RAM en páginas enormes, por ejemplo, 4 MB (en lugar de 4 KB). Esto conduce a muchas menos entradas de TLB, pero significa que la unidad de paginación es de 4 MB para esas partes de la RAM. Por lo tanto, las páginas grandes tienden a no ser paginables.

Ahora la RAM está dividida en partes paginables y no paginables; ¿Qué partes pueden ser razonablemente no paginables? En MariaDB, Innodb Buffer Pool es un candidato perfecto. Entonces, al configurarlos correctamente, InnoDB puede ejecutarse un poco más rápido:

  • Páginas enormes habilitadas
  • Dígale al sistema operativo que asigne la cantidad correcta (es decir, que coincida con el buffer_pool)
  • Dile a MariaDB que use páginas enormes

Ese hilo tiene más detalles sobre qué buscar y qué configurar.

Aumento de rendimiento general: un pequeño porcentaje. Bostezo. Demasiada molestia por muy poco beneficio.

¿Páginas gigantes? Apagar.

MOTOR = MEMORIA

El motor de almacenamiento de memoria es una alternativa poco utilizada a MyISAM e InnoDB. Los datos no son persistentes, por lo que tienen usos limitados. El tamaño de una tabla de MEMORIA está limitado a max_heap_table_size, que por defecto es de 16 MB. Lo menciono en caso de que haya cambiado el valor a algo enorme; esto robaría otros usos posibles de la RAM.

Cómo configurar variables

En el archivo de texto my.cnf (my.ini en Windows), agregue o modifique una línea para decir algo como

innodb_buffer_pool_size = 5G

Es decir, nombre de VARIABLE, “=” y un valor. Se permiten algunas abreviaturas, como M por millón (1048576), G por mil millones.

Para que el servidor lo vea, la configuración debe estar en el “[mysqld]”sección del archivo.

La configuración de my.cnf o my.ini no surtirá efecto hasta que reinicie el servidor.

La mayoría de las configuraciones se pueden cambiar en el sistema en vivo conectándose como usuario raíz (u otro usuario con privilegio SUPER) y haciendo algo como

SET @@global.key_buffer_size=77000000;

Nota: Aquí no se permite ningún sufijo M o G.

Para ver la configuración de una VARIABLE global, haga algo como

SHOWGLOBAL VARIABLES LIKE"key_buffer_size";+-----------------+----------+| Variable_name   |Value|+-----------------+----------+| key_buffer_size |76996608|+-----------------+----------+

Tenga en cuenta que esta configuración en particular se redondeó a un múltiplo que le gustó a MariaDB.

Es posible que desee hacer ambas cosas (SET y modificar my.cnf) para realizar el cambio de inmediato y tenerlo para que el próximo reinicio (por cualquier motivo) vuelva a obtener el valor.

Servidor web

Un servidor web como Apache ejecuta varios subprocesos. Si cada hilo abre una conexión a MariaDB, podría quedarse sin conexiones. Asegúrese de que MaxClients (o equivalente) esté configurado en un número civilizado (menos de 50).

Instrumentos

  • MySQLTuner
  • TUNING-PRIMER

Existen varias herramientas que aconsejan sobre la memoria. Una entrada engañosa que se les ocurrió

Uso máximo de memoria posible: 31,3 G (266% de la RAM instalada)

No dejes que te asuste, las fórmulas utilizadas son excesivamente conservadoras. Asumen que todas las max_connections están en uso y activas, y están haciendo algo que consume mucha memoria.

Total de tablas fragmentadas: 23 Esto implica que OPTIMIZE TABLE _podría_ ayudar. Lo sugiero para tablas con un alto porcentaje de “espacio libre” (ver MOSTRAR ESTADO DE LA TABLA) o donde sabe que hace muchas BORRADAS y / o ACTUALIZACIONES. Aún así, no se moleste en OPTIMIZAR con demasiada frecuencia. Una vez al mes podría ser suficiente.

MySQL 5.7

5.7 almacena mucha más información en la RAM, lo que hace que la huella sea quizás medio GB más que 5.6. Ver Aumento de memoria en 5.7.

Postlog

Creado 2010; Actualizado Oct, 2012, Ene, 2014

Los consejos de este documento se aplican a MySQL, MariaDB y Percona.

Ver también

  • Más en profundidad: el ajuste de Tocker para 5.6
  • Conceptos básicos de optimización del rendimiento InnoDB de Irfan (redux)
  • 10 configuraciones de MySQL para ajustar después de la instalación
  • Magento
  • La opinión de Peter Zaitsev sobre tal (5/2016)

Rick James amablemente nos permitió usar este artículo en la Base de conocimientos.

El sitio de Rick James tiene otros consejos útiles, procedimientos, optimizaciones y consejos de depuración.

Fuente original: http://mysql.rjweb.org/doc.php/random

El contenido reproducido en este sitio es propiedad de sus respectivos dueños, y MariaDB no revisa este contenido con anticipación. Los puntos de vista, la información y las opiniones expresadas por este contenido no representan necesariamente las de MariaDB o de cualquier otra parte.