18 cosas que puede hacer para deshacerse de los cuellos de botella de MySQL causados ​​por el alto tráfico (segunda parte)

Esta es una serie de blogs de tres partes que se enfoca en lidiar con un evento inesperado de alto tráfico a medida que sucede. La primera parte está aquí y la tercera parte está aquí.

7. Obtenga más memoria

Complejidad: Baja
Impacto potencial: Alto

Si sus datos no están bien almacenados en la memoria, su rendimiento de MySQL probablemente sea limitado. Si sus datos se ajustan bien, agregar aún más memoria no proporcionará mejoras en el rendimiento.

Incluso cuando se encuentra en un almacenamiento muy rápido, como Intel Optane o almacenamiento NVMe conectado directamente, acceder a los datos en la memoria sigue siendo mucho más rápido que un orden de magnitud.

¿Cómo sabes si tienes suficiente memoria? Comprueba el uso de la memoria y la actividad de E/S.

La actividad de E / S es en realidad el primer artículo que miré. Si, como en este caso, no ha leído IO para hablar, todos sus datos están en el caché, ya sea caché de datos MySQL o caché de archivos del sistema operativo. Sin embargo, la actividad de escritura no se eliminará por completo incluso si todos los datos se almacenan en caché, ya que los cambios en la base de datos se registrarán en el disco.

En general, no intente eliminar Read IO Completely; solo necesita demasiada memoria en la mayoría de los casos, y no es necesario. Sin embargo, desea asegurarse de que Read IO no tenga un impacto sustancial en su rendimiento. Puede hacer esto asegurándose de que la carga del disco sea manejable o, si tiene instalado Percona Monitoring and Management (PMM), puede controlar la cantidad de lecturas de disco que afectan su rendimiento de consultas específicas en Query Analytics.

Nota: Si bien es posible que algunos valores solo agreguen más memoria porque el sistema operativo la usará como caché, para obtener la mayor parte de la nueva memoria disponible, deberá configurar MySQL para usarla. Innodb_buffer_pool_size es la variable más importante a considerar. El 80% de la memoria se usa a menudo como regla general pero hay más.

Una cosa que debe tener cuidado, ya que está configurando MySQL para aprovechar toda su memoria, es asegurarse de no comprometer demasiado la memoria y de que MySQL no se quede sin memoria virtual (porque puede fallar o ser eliminado por Out de la memoria). OOM) asesino).

También desea asegurarse de que no haya una actividad de intercambio significativa (1 MB/seg o más), pero el uso del espacio de intercambio está bien. Verificar «Defendiendo el intercambio: conceptos erróneos comunes» para más detalles.

8. Pasar a un almacenamiento más rápido

Complejidad: Media
Impacto potencial: Alto

Cuando el tamaño de sus datos es pequeño, almacenarlos en la memoria es la mejor manera de escalar y leer. Si su base de datos es grande, puede ser poco práctico y una unidad más rápida puede ser una mejor opción. Además, obtenga pasajes de las Escrituras que deben leerse incluso si tiene mucha memoria. Este artículo antiguo, pero aún válido, entra en detalles sobre este tema.

Con la CPU, necesita saber cuándo necesita más núcleo o más rápido, la situación con el almacenamiento es aún más complicada. Debe comprender la diferencia entre rendimiento (IOPS) y latencia (echa un vistazo a este fantástico artículo sobre el tema) y también la diferencia entre los rendimientos de lectura y escritura.

Una forma de realizar un seguimiento del rendimiento de IO es observar la cantidad de almacenamiento de IOPS que sirve o el ancho de banda de la actividad de IO.

Es útil si conoce los límites de su almacenamiento y si está cerca o se encuentra con ellos. Es posible que no sepa el almacenamiento de rendimiento exacto que puede proporcionar. En este caso, es útil echar un vistazo a Disk IO Load, que le muestra cuántas operaciones de IO están activas en este momento.

Si ve este número en decenas o cientos, es probable que su disco esté sobrecargado. El problema con el almacenamiento, a diferencia de la CPU, es que no tenemos forma de saber cuál es el «nivel natural de competencia», cuándo las solicitudes pueden proceder en paralelo o cuándo debe ocurrir la cola.

Mire la latencia de la demanda para leer y escribir y ver si son diferentes del tiempo antes del pico de tráfico. Además, las latencias de lectura y escritura pueden verse afectadas de manera independiente y deben monitorearse por separado.

¿Cuánto más rápido puede afectar un disco al rendimiento de sus aplicaciones? Desde el punto de vista de la lectura, puede consultar PMM Query Analytics como expliqué en el 7. Obtenga más memoria sección, pero escribir, es más complicado.

Escribir en InnoDB Redo Log, o más específicamente, persistir en el disco a través de fsync () es un cuello de botella muy común. Puede ver si esto está sucediendo en su sistema observando la cantidad de fsyncs pendientes (panel de detalles de MySQL Innodb, sección Innodb Disk IO).

Si está cerca de 1 todo el tiempo, probablemente tenga un cuello de botella con el disco duro. Para mejorar la situación, necesita almacenamiento con una mejor latencia de escritura (fsync ()). Puede ajustar su configuración de MySQL para reducir la garantía de durabilidad o ajustar su carga de trabajo para agrupar consultas en un número menor de transacciones.

¿Cuáles son las opciones de almacenamiento más rápidas disponibles? El almacenamiento Intel Optane SSD o NVMe tiende a ofrecer el mejor rendimiento y la latencia más rápida y predecible. Sin embargo, si usa esas soluciones, especialmente en la nube, asegúrese de usar alguna forma de replicación para la redundancia de datos.

Si necesita usar almacenamiento en red, busque opciones de rendimiento optimizado como AWS Tipo de volumen EBS io1. Los volúmenes gp2 tradicionales de «propósito general» pueden ser mucho más costosos, pero tienen un rendimiento máximo más bajo.

9. Revisa tu red

Complejidad: Baja
Impacto potencial: Alto

Al verificar si una red es un cuello de botella en su evento de pico de tráfico, debe observar el ancho de banda, la latencia y los errores.

Las redes tienden a ser más complicadas que otros recursos porque todos estos deben medirse para diferentes clientes por separado. Por ejemplo, los clientes que se ejecutan en «localhost» tienden a no tener problemas, sin embargo, los clientes que se ejecutan en otras partes del mundo y se comunican con su base de datos tendrán problemas.

El ancho de banda de la red, al menos cuando se trata del nodo local, rara vez es un problema.

En raras ocasiones, las aplicaciones recuperan grandes conjuntos de resultados y saturan la red. Las copias de seguridad de la red y otras grandes transferencias de datos pueden saturar la red y hacer que se ralenticen las transacciones de otros usuarios.

La latencia entre su cliente y el servidor de la base de datos se puede medir aproximadamente con la herramienta «ping» o «mtr». Si tiene una red de 10 Gb, puede esperar 0,2 ms en el mismo centro de datos. Por lo general, es un poco más alto en los proveedores de la nube en la misma zona de disponibilidad. Varias zonas de alta disponibilidad vienen con una latencia más alta y una latencia entre regiones distantes puede ser de 100 ms y puede tener una variación significativamente mayor que la red local.

En este caso, vemos que la ruta entre el cliente y el servidor pasa por un solo enrutador (y quizás algunos conmutadores) con una latencia promedio de 1,5 ms y ningún paquete perdido.

Debe mantener su servidor de aplicaciones y su base de datos lo más cerca posible, en la misma zona de disponibilidad si es posible, pero ciertamente en la misma región para las aplicaciones sensibles a la latencia.

Cuando se trata de errores, la retransmisión de TCP es su peor enemigo porque puede agregar mucha latencia significativa.

Si ve un aumento en las tasas de retransmisión durante su evento de pico de tráfico, es probable que haya problemas a nivel de red que deban abordarse.

10. Ubique y optimice las consultas que están causando la carga

Complejidad: Media
Impacto potencial: Alto

Localizar y optimizar las preguntas incorrectas es una de las actividades más valiosas que puede realizar porque proporciona beneficios a largo plazo. A diferencia de hacer una copia de seguridad de su hardware, no necesita ninguna inversión adicional (con el tiempo).

Si te gusta el monitoreo y la gestión de Percona, deberías echar un vistazo a la herramienta Query Analytics, que por defecto ordena las consultas por la carga que generan.

Examinar y optimizar sus preguntas en este orden es una excelente manera de hacer que su sistema funcione más rápido. En algunos casos, como la solicitud de comisión, realmente no puede optimizar la solicitud en sí, pero puede acelerarla mediante cambios de configuración de hardware o MySQL.

Verifique los detalles de ejecución de la solicitud:

Explique Plan para ver si esta aplicación se puede optimizar y cómo:

MySQL Query Optimization es un tema demasiado complejo para cubrirlo en una publicación de blog. consideraré aprender a leer Explique y asistir a un seminario web.

11. Agregar índices faltantes

Complejidad: Baja
Impacto potencial: Alto

La optimización completa de las consultas puede requerir cambios en la forma en que se escribe una consulta, lo que requiere tiempo de desarrollo y prueba que puede ser difícil de obtener. Es por eso que, como primer paso, es posible que desee concentrarse en agregar solo los índices que faltan. Esto no requiere cambios en la aplicación y es bastante seguro (con raras excepciones), y no debería cambiar los resultados de la aplicación.

Consulte este seminario web para obtener más detalles.

12. Índice de caída innecesario

Complejidad: Media
Impacto potencial: Medio

Con el tiempo, es común que el esquema de la base de datos acumule índices duplicados, redundantes o sin usar. Algunos se agregaron por error o malentendido, otros fueron valiosos en el pasado, pero ya no son como la aplicación modificada.

Puede leer más sobre los índices redundantes y duplicados en esta publicación de blog. El pt-duplicate-key-checker de Percona Toolkit también es una gran herramienta para encontrar.

Un índice no utilizado es un poco más complicado y arriesgado: el hecho de que no haya una demanda que necesite este índice en la última semana no significa que no haya un informe mensual o trimestral que lo necesite.

La publicación del blog, Limpieza básica para índices MySQL, proporciona una receta sobre cómo encontrar dichos índices. Si está en MySQL 8, es posible que desee considerar esto hace que dicho índice sea invisible un rato antes de cazar.

Esta es una serie de blogs de tres partes. La primera parte está aquí, y la tercera parte está aquí.

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