Luego de indagar en varios repositorios y páginas webs de internet al terminar encontramos la respuesta que te enseñaremos pronto.
Solución:
¡Fue el intercambio! Terminé replicando la base de datos en el mismo hardware y escribí algunos scripts para emular el tráfico en vivo en la base de datos. También ejecuté algunas consultas importantes para ayudar a llenar el grupo de búfer y luego me aseguré de que mi base de datos réplica coincidiera aproximadamente con las métricas de mi base de datos de producción. Luego intenté ejecutar consultas grandes contra él y se bloqueó, incluso con los índices aplicados. Podría reproducir el problema sin desactivar el servicio de producción, por lo que ahora puedo romper las cosas tanto como quiera.
Me di cuenta de que ejecutar las mismas consultas grandes anteriormente en la vida de la base de datos de réplica no había causado ningún problema y rastreé el punto donde comienzan los bloqueos. Ocurre casi inmediatamente después de que el grupo de búfer se vuelve lo suficientemente grande como para enviar algunos datos (SO, o de otro modo) para intercambiar en la instancia t2.micro. Aquí hay una imagen de Cloudwatch del intercambio que crece después de que la memoria liberable cae por debajo de ~ 50 MB más o menos:
Cualquier consulta grande (con o sin índice) comienza a bloquear la base de datos después del círculo rojo. Puede ver el uso total de CPU de 5 minutos cuando bloqueé la base de datos durante casi un minuto realizando una ELIMINACIÓN.
Con esta teoría en mente, probé dos soluciones:
1) Cambié el valor de innodb_buffer_pool_size a 375M (en lugar de su valor predeterminado de AWS de 3/4 del tamaño de RAM de la instancia). Esto reduce el tamaño máximo del grupo de búfer y garantiza que la huella de memoria de la base de datos no crezca lo suficiente como para empujar el sistema operativo/etc. al intercambio. ¡Esto funcionó!
2) Intenté ejecutar la base de datos en una instancia más grande (2GiB de RAM). ¡Esto también funcionó!
Ambas soluciones funcionan, y la bonificación con (1) es que no tengo que gastar dinero extra. Estoy trabajando para ajustar el valor de innodb_buffer_pool_size para que sea lo más grande posible sin causar intercambio. Ejecuté la misma consulta DELETE en 1.2s y la base de datos siguió respondiendo. La siguiente captura de pantalla ni siquiera era posible con la base de datos de producción, ya que la base de datos dejaba de responder durante estas largas consultas, por lo que el tablero nunca se actualizaba y finalmente perdía la conexión.
Para empezar, tener mejores índices. Para el SELECT:
INDEX(ItemID, ItemStatus, -- in either order
`Time`) -- then the range
Para el DELETE:
INDEX(ItemStatus, `Time`) -- in this order
(No, no existe un único índice que sea óptimo para ambas consultas).
Esto debería ayudar con el rendimiento (CPU, IOPS, “bloqueo”) y puede (o no) ayudar con otros problemas, como el page_cleaners.
¿Aproximadamente cuántas filas hay en la tabla? (¿140K ahora?) En el conjunto de resultados de ese SELECT?
Más…
innodb_file_per_table es un problema de distribución del disco; Irrelevante. El sintonizable principal es el caché en RAM: innodb_buffer_pool_size. Pero, con solo 1 GB de RAM, ese caché debe ser bastante pequeño. Esto explicaría por qué una tabla de 190 000 filas necesitaría IOP.
Este es el escenario… Las consultas solo se realizan en buffer_pool. Es decir, para leer (o escribir o eliminar) una fila, el bloque de 16 KB en el que vive (o vivirá) primero se lleva al buffer_pool. La tabla mencionada podría tener unos 50 MB. Supongo (sin saberlo) que buffer_pool_size está configurado para aproximadamente 100 MB. Él SELECT y DELETE en discusión necesitaba un escaneo completo de la tabla, por lo tanto, 50 MB se extraerían en buffer_pool, expulsando bloques de otras tablas si fuera necesario. Y viceversa cuando pase algo grande con esas mesas.
Plan A: Agregar los índices recomendados. Puede haber otras formas de reducir la huella del disco para mejorar la capacidad de almacenamiento en caché. Por ejemplo, BIGINT es de 8 bytes; INT tiene solo 4 bytes, pero tiene un límite de unos pocos miles de millones. Pero incluso MEDIUMINT (3 bytes, unos pocos millones), etc., puede ser suficiente.
Plan B: Obtener una configuración de RDS más grande.
Si crees que te ha sido de provecho nuestro artículo, sería de mucha ayuda si lo compartieras con otros programadores así contrubuyes a extender esta información.