Elija innodb_buffer_pool_size – Blog de rendimiento de base de datos de Percona

Mi última publicación sobre Innodb Performance Optimization tuvo muchos comentarios al elegirlo innodb_buffer_pool_size y realmente simplifiqué las cosas demasiado, así que permítanme escribir una descripción un poco mejor.

Innodb Buffer Pool es, con mucho, la opción más importante para Innodb Performance y debe configurarse correctamente. He visto a muchos clientes que han pasado por dificultades extremas dejándolo en el valor predeterminado (8M). Entonces, si tiene MySQL Box dedicado y solo está usando Innodb Buffer Pools, querrá darles toda la memoria que no necesita para otras necesidades de Innodb Buffer Pool.

Esto, por supuesto, supone que su base de datos es grande, por lo que necesita un grupo de búfer grande; de ​​lo contrario, bastará con colocar un grupo de búfer un poco más grande que el tamaño de su base de datos. Por supuesto, también hay que contar el crecimiento. Necesita un grupo de búfer de sus datos un poco más grande (digamos un 10 %) (tamaño total de Innodb TableSpaces) porque no solo contiene páginas de datos, sino que también contiene índices hash adaptables, inserciones de búfer, bloqueos que ocupan incluso poco tiempo. Incluso si no es tan crítico, para la mayoría de las cargas de trabajo, si tiene su Innodb Buffer Pool un 10 % más pequeño que el tamaño de su base de datos, no perderá mucho de todos modos.

También puede optar por configurar un grupo de búfer como si el tamaño de su base de datos ya fuera mayor que la cantidad de memoria que tiene, así que no olvide reajustar más tarde. Este también es un enfoque válido, ya que si se trata de un servidor MySQL dedicado, es posible que no tenga un buen uso para esa memoria de todos modos.

Otra cosa a tener en cuenta es que Innodb asigna más memoria en estructuras relacionadas con el grupo de búfer de lo que especifica para esto: solo verifiqué nuestro conjunto de casillas que usan un grupo de búfer de 12 GB (con un grupo adicional muy pequeño y un búfer de registro) y la cantidad total. de memoria asignada por Innodb suele ser un poco más de 13 GB. Vadim publicó algunos números sobre este tiempo atrás.

Una vez que haya decidido el tamaño de la base de datos, debe verificar si hay alguna restricción en el tamaño del búfer de Innodb que pueda usar. Por lo general, verá restricciones que solo se aplican a los sistemas de 32 bits, pero vemos que también se pueden detectar en la naturaleza, especialmente en el mundo de Windows. La restricción normalmente se aplica a la cantidad total de proceso de memoria que puede asignar, así que asegúrese de dejar espacio para otras necesidades de MySQL al considerar esto.

El siguiente paso sería decidir ¿Cuánta memoria necesitas para otras necesidades?. Estas necesidades serán las necesidades del sistema operativo: los procesos de su sistema, las tablas de páginas, los búferes de socket, etc., todos necesitan memoria. Puedo configurar esto en 256M para cajas pequeñas al 5% del tamaño de la memoria en cajas grandes, aunque puede ser incluso menos que eso. Además de las necesidades de su sistema operativo, también necesita MySQL, que incluyen búferes de MySQL, caché de consultas, key_buffer, subprocesos mysql, tablas temporales, para el tipo de búferes de subprocesos que se pueden asignar. También hay cosas como el grupo de memoria adicional de innodb (que puede aumentar más memoria de la que ha asignado para esto, especialmente en caso de que tenga una gran cantidad de tablas).

Podría decir algunos números, por ejemplo, resumir todos sus búferes globales y agregar 1 MB por cada conexión que planee tener, pero en realidad el número puede variar significativamente según la carga. Las conexiones inactivas, por ejemplo, consumen significativamente menos memoria que las conexiones que funcionan con tablas temporales enormes y ejecutan solicitudes complejas. Por lo general, es mucho mejor consultar solo. Inicie MySQL con 10 GB de búfer Innodb, por ejemplo, y vea cuántas salidas grandes RSS y VSZ «ps» emiten en sistemas Unix. Si se trata de 12 GB, entonces necesita 2 GB para otras cosas, y puede aumentarlo un poco para tener un tamaño seguro y escalar en consecuencia.

El tercer consumidor de memoria importante sería la memoria caché del sistema operativo. Desea borrar el caché de sus tablas Innodb, pero hay otras cosas que necesita saber sobre el caché del sistema operativo: tablas MyISAM (base de datos mysql, temporal, etc.) que necesitará, archivos .frm, registros binarios o registros de retransmisión, A los registros transaccionales de Innodb también les gusta. se almacenará en caché, de lo contrario, el sistema operativo deberá leer para servir la escritura en estos archivos de registro, ya que la E/S de los archivos de registro no está alineada con el borde de la página. Por último, es probable que tenga algunos scripts/procesos del sistema ejecutándose en su sistema que también necesitan algo de almacenamiento en caché. El número puede ser muy diferente dependiendo de la carga de trabajo del sistema, pero generalmente se ven valores desde 200 MB hasta 1 GB bien estimados para este número.

Eliminar el doble almacenamiento en búfer – Esto es nuevamente muy importante para elegir el tamaño del grupo de almacenamiento intermedio. No desea que el sistema operativo almacene en caché lo que Innodb ya está almacenando en caché. El caché de Innodb es más eficiente en comparación con el caché del sistema operativo porque no hay copia, debido a los índices hash adaptativos, la capacidad de escritura del búfer y una serie de otros factores, por lo que solo desea que su sistema operativo dé paso a Innodb. Tenga en cuenta que no es suficiente bloquear el sistema operativo para que no se intercambie; como ya mencioné, la caché del sistema operativo será necesaria para otras cosas y si no habilita Innodb para omitir el búfer del sistema operativo, Innodb TableSpace IO eliminará la caché porque generalmente hace la mayor parte del IO. en el sistema En Windows no necesitas hacer nada. En Linux, FreeBSD, Solaris necesita establecer innodb_flush_method = O_DIRECTO. En otros sistemas operativos, es posible que se le seleccione en el nivel del sistema operativo, pero asegúrese de hacerlo. Hay un pequeño caso de nicho cuando duele: cuando no tiene RAID con BBU y su carga de trabajo es muy intensiva en escritura, pero siempre hay excepciones.

Haz que tu sistema operativo sea feliz El otro desafío que puede tener es hacer que su sistema operativo sea feliz y evitar tener que cambiar el proceso MySQL u otros procesos importantes para dejar espacio para la caché de archivos. El sistema operativo puede encontrar injusto que haya un proceso MySQL que consuma el 95% de la memoria y el caché sea solo un dos por ciento. Algunas personas intentan solucionar esto deshabilitando el archivo de intercambio, pero puede dañar de otra manera: el sistema operativo puede eliminar el proceso de falta de memoria de MySQL (o pensar que está sin memoria), lo que puede suceder, es decir, en caso de un pico de conexión inesperado. . Además, no todos los núcleos funcionan lo suficientemente bien con el intercambio deshabilitado y existen otras razones en contra. Para algunas personas que no tienen trabajos de intercambio, incluso si generalmente juegan en el lado seguro, tener suficiente memoria «libre» utilizada como caché y búfer. Kevin Burton escribió una buena publicación sus experimentos.

Según el sistema operativo, es posible que desee realizar varios ajustes en la memoria de la máquina virtual. Es posible que desee habilitar MySQL para usar páginas grandes para la asignación de grupos de búfer de Innodb y algunos otros búfer, que también pueden tener otros beneficios de rendimiento. Sintonice su VM para que estén menos ansiosos por intercambiar cosas echo 0>/proc/sys/vm/intercambio es otro cambio útil, incluso si no siempre te salva del intercambio. Algunas versiones específicas del kernel pueden tener otros parámetros con los que jugar. Finalmente, puede intentar bloquear MySQL en la memoria usando -memlock; solo tenga cuidado, ya que en caso de que tenga un uso bajo de la memoria, es posible que el sistema operativo elimine el servidor MySQL en lugar de intercambiar temporalmente algunas cosas.

Dos cosas a tener en cuenta sobre el intercambio de SO. Antes de guardar, el «intercambio usado» no es realmente útil porque no sabe lo que hay en el intercambio: hay partes del proceso MySQL y otros procesos que no necesita durante el funcionamiento normal, por lo que es bueno obtenerlo a cambio. . Sin embargo, asegúrese de que el intercambio no ocurra, lo que significa que las columnas «sí / no» de su VMSTAT son cero en Linux. Un par de intercambios por minuto no hace daño, pero si realiza más de 100 páginas por segundo de intercambio de E/S, está en problemas.

En segundo lugar, mucha gente piensa: que importa si se intercambia parte del grupo de búfer, tendría un IO para tomar la página si tuviera un grupo de búfer pequeño y ahora tengo un IO para tomar los datos de la página intercambiados. Este es un pensamiento muy equivocado. First OS también debe intercambiar páginas limpias desde Buffer Pool, mientras que Innodb puede simplemente descartar páginas en caso de presión de memoria. Pero lo que es más importante, los algoritmos de Innodb se ajustan teniendo en cuenta lo que hay en la memoria y lo que hay en el disco, por ejemplo, cuando Innodb intenta evitar mantener el pestillo mientras realiza E/S mientras se está cerrando mientras accede a las páginas en el grupo de búfer. Si resulta que la página está en el intercambio en lugar de en la memoria, tendrá otro subproceso acumulándose esperando en el mismo bloqueo hasta el final de la IO, mientras que pueden tener todos los datos que necesitan para continuar en el grupo de búfer de innodb.

Creo que estas aclaraciones funcionan mejor que el 70-80 % de la recomendación y, por supuesto, no solo debe adherirse a 50 GB si tiene 64 G de memoria; los valores de 56-60 G probablemente tendrían más sentido, y de acuerdo con un grupo de otros parámetros 12G o 14G. Puede que sea una buena opción para Box de 16GB, aunque me importen valores cercanos a 14G, ya que no hay mucho espacio para otras cosas.

PD Solo he descrito la selección de Innodb Buffer Pool para el sistema Innodb dedicado. Si tiene una buena cantidad de MyISAM, Archive, PBXT, Falcon u otros motores de almacenamiento, estará entrando en un acto de equilibrio complejo además de considerar todos estos factores.

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