Solución:
InnoDB: page_cleaner: el bucle previsto de 1000 ms tomó 4013 ms. Es posible que la configuración no sea la óptima. (vaciado = 1438 y desalojado = 0, durante el tiempo).
El problema es típico de una instancia de MySQL en la que tiene una alta tasa de cambios en la base de datos. Al ejecutar su importación de 5GB, está creando páginas sucias rápidamente. A medida que se crean páginas sucias, el hilo de limpieza de páginas es responsable de copiar las páginas sucias de la memoria al disco.
En su caso, supongo que no hace importaciones de 5GB todo el tiempo. Así que esta es una tasa de carga de datos excepcionalmente alta y es temporal. Probablemente pueda ignorar las advertencias, porque InnoDB se pondrá al día gradualmente.
Aquí hay una explicación detallada de los aspectos internos que conducen a esta advertencia.
Una vez por segundo, el limpiador de páginas escanea el grupo de búfer en busca de páginas sucias para vaciarlo del grupo de búfer al disco. La advertencia que vio muestra que tiene muchas páginas sucias que vaciar, y se necesitan más de 4 segundos para vaciar un lote de ellas en el disco, cuando debería completar ese trabajo en menos de 1 segundo. En otras palabras, muerde más de lo que puede masticar.
Ajustó esto reduciendo innodb_lru_scan_depth de 1024 a 256. Esto reduce qué tan lejos dentro del grupo de búfer el hilo del limpiador de páginas busca páginas sucias durante su ciclo de una vez por segundo. Le estás pidiendo que tome bocados más pequeños.
Tenga en cuenta que si tiene muchas instancias de grupo de búferes, el vaciado hará más trabajo. Se muerde innodb_lru_scan_depth cantidad de trabajo para cada instancia de la agrupación de almacenamientos intermedios. Por lo tanto, es posible que, sin darse cuenta, haya causado este cuello de botella al aumentar el número de grupos de búferes sin disminuir la profundidad del análisis.
La documentación para innodb_lru_scan_depth dice “Una configuración más pequeña que la predeterminada es generalmente adecuada para la mayoría de las cargas de trabajo”. Parece que le dieron a esta opción un valor que es demasiado alto por defecto.
Puede establecer un límite en las IOPS utilizadas por el vaciado en segundo plano, con el innodb_io_capacity y innodb_io_capacity_max opciones. La primera opción es un límite suave en el rendimiento de E / S que solicitará InnoDB. Pero este límite es flexible; si el vaciado se está quedando atrás de la tasa de creación de nuevas páginas sucias, InnoDB aumentará dinámicamente la tasa de vaciado más allá de este límite. La segunda opción define un límite más estricto sobre hasta qué punto InnoDB podría aumentar la tasa de descarga.
Si la tasa de descarga puede mantenerse al día con la tasa promedio de creación de nuevas páginas sucias, entonces estará bien. Pero si crea constantemente páginas sucias más rápido de lo que se pueden vaciar, eventualmente su grupo de búfer se llenará con páginas sucias, hasta que las páginas sucias excedan innodb_max_dirty_page_pct del grupo de amortiguadores. En este punto, la tasa de descarga aumentará automáticamente y nuevamente puede hacer que page_cleaner envíe advertencias.
Otra solución sería poner MySQL en un servidor con discos más rápidos. Necesita un sistema de E / S que pueda manejar el rendimiento exigido por el vaciado de su página.
Si ve esta advertencia todo el tiempo con tráfico promedio, es posible que esté intentando realizar demasiadas consultas de escritura en este servidor MySQL. Puede que sea el momento de escalar y dividir las escrituras en varias instancias de MySQL, cada una con su propio sistema de disco.
Leer más sobre el limpiador de páginas:
- Presentamos el hilo page_cleaner en InnoDB (copia archivada)
- MySQL-5.7 mejora las cargas de trabajo orientadas a DML
El cuello de botella es guardar datos en el disco duro. Cualquiera que sea el disco duro que tenga: SSD, uno normal, NVMe, etc.
Tenga en cuenta que esta solución se aplica principalmente a InnoDB
Tuve el mismo problema, he aplicado pocas soluciones.
1 °: comprobar lo que está mal
atop -d le mostrará el uso del disco. Si el disco está ‘ocupado’, intente detener todas las consultas a la base de datos (¡pero no detenga el servicio del servidor mysql!)
Para monitorear cuántas consultas tiene, use mytop, innotop o equivalente.
Si tiene 0 consultas, pero el uso del disco TODAVÍA es cercano al 100% desde unos pocos segundos / pocos minutos, entonces significa que el servidor mysql está tratando de eliminar las páginas sucias / hacer una limpieza como se mencionó anteriormente (excelente publicación de Bill Karwin) .
ENTONCES puede intentar aplicar tales soluciones:
2do: optimización de hardware
Si tu array no está en RAID 1 + 0, considere duplicar la velocidad de almacenamiento de datos utilizando este tipo de solución. Intente ampliar las posibilidades de su controlador de disco duro con la escritura de datos. Intente usar SSD o HDD más rápido. La aplicación de esta solución depende de sus posibilidades de hardware y presupuesto, y puede variar.
3 °: ajuste de software
Si el cotroller de harware funciona bien, pero desea ampliar la velocidad de almacenamiento de datos, puede configurarlo en el archivo de configuración de mysql:
3.1.
innodb_flush_log_at_trx_commit = 2 -> si está usando tablas innodb, funciona mejor desde mi experiencia con una tabla por archivo:
innodb_file_per_table = 1
3.2.
continuando con InnoDB:
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
innodb_support_xa = 0
innodb_checksums = 0
En general, las líneas anteriores reducen la cantidad de datos necesarios para guardar en el disco duro, por lo que el rendimiento es mayor.
3.3
general_log = 0
slow_query_log = 0
Las líneas anteriores deshabilitan el guardado de registros, por supuesto, es otra cantidad de datos para guardar en el disco duro
3.4 verifique nuevamente lo que está sucediendo, por ejemplo, tail -f /var/log/mysql/error.log
4to: notas generales
Notas generales: Esto fue probado bajo MySQL 5.6 Y 5.7.22 SO: Debian 9 RAID: Unidades SSD 1 + 0 Base de datos: Tablas InnoDB
innodb_buffer_pool_size = 120G
innodb_buffer_pool_instances = 8
innodb_read_io_threads = 64
innodb_write_io_threads = 64
Cantidad total de RAM en el servidor: 200 GB
Después de hacer eso, puede observar un mayor uso de la CPU; eso es normal, porque la escritura de datos es más rápida, entonces la CPU trabajará más.
Si está haciendo eso usando my.cnf, por supuesto, no olvide reiniciar el servidor MySQL.
5to: suplemento
Siendo intrigado, hice esta peculiaridad con
SET GLOBAL innodb_lru_scan_depth=256;
mencionado anteriormente.
Trabajando con tablas grandes, no he visto cambios en el rendimiento.
Después de las correcciones anteriores, no eliminé las advertencias, sin embargo, todo el sistema funciona significativamente más rápido. Todo lo anterior es solo una experimentación, pero he medido los resultados, me ayudó un poco, así que espero que pueda ser útil para otros.
Valoraciones y reseñas
Si estás contento con lo expuesto, puedes dejar un artículo acerca de qué te ha gustado de este post.