MySQL Long Query Progress Monitoring

Solo para comenzar mi pregunta, entiendo que no hay apoyo directo para algo como esto. Lo que estoy buscando es algún tipo de solución alternativa, o derivación intrincada que me permita get un resultado medio respetable.

Estoy trabajando con un clúster MySQL bastante grande (tablas> 400 millones de filas) usando el motor de clúster.

¿Alguien conoce una forma de recuperar directamente o de alguna otra manera get una indicación precisa (o mejor) del progreso a través de una consulta larga en mysql? Tengo algunas consultas que pueden tomar hasta 45 minutos, y debo determinar si estamos en un 10% o un 90% durante el process.

EDITAR:

Como solicité en los comentarios, aquí hay una versión destilada y generizada de una de las consultas que está conduciendo a mi pregunta original …

SELECT `userId` FROM `openEndedResponses` AS `oe` WHERE `oe`.`questionId` = 3 -- zip code AND (REPLACE( REPLACE( `oe`.`value`, ' ', '' ), '-', '' ) IN ( '30071', '30106', '30122', '30134', '30135', '30168', '30180', '30185', '30187', '30317', '30004' )); 

Esta consulta se ejecuta contra una sola tabla con ~ 95 millones de filas. Lleva 8 segundos ejecutar la consulta y otros 13 para transferir los datos (21 segundos en total). Teniendo en count el tamaño de la table y el hecho de que se estén utilizando funciones de manipulación de cadenas, diría que se está ejecutando bastante rápido. Sin embargo, para el usuario, siguen apareciendo 21 segundos atascados o inactivos. Alguna indicación de progreso sería ideal.

Pude estimar algo así al consultar el número de filas para procesar y luego dividir el procesamiento en un bucle, trabajando solo en un subset del total de filas a la vez.

El ciclo completo fue bastante complicado, pero la lógica básica fue la siguiente:

 SELECT @minID = Min(keyColumn) FROM table WHERE condition SELECT @maxID = Max(keyColumn) FROM table WHERE condition SELECT @potentialRows = (@maxID - @minID) / @iterations WHILE @minID < @maxID BEGIN SET @breakID = @minID + @potentialRows SELECT columns FROM table WITH (NOLOCK, ...) WHERE condition AND keyColumn BETWEEN @minID AND @breakID SET @minID = @breakID + 1 END 

Tenga en count que esto funciona mejor si los ID están distribuidos uniformemente.

Si está intentando una consulta compleja, el command EXPLAIN SQL o el MySQL Query Analyzer pueden ayudar a comprender qué está sucediendo. Si es simplemente una consulta grande, puede intentar crear una tabla temporal con SELECT INTO y / o usando cláusulas LIMIT / OFFSET en las consultas SELECT. Si usa LIMIT / OFFSET en las tablas originales, es posible que necesite establecer el nivel de transacción en serializable, IIRC, para que obtenga lecturas uniformes mientras itera sobre los datos. Si primero crea una tabla temporal, esa tabla debe mantenerse constante independientemente.

No creo que sea compatible con MySQL Estoy seguro de que MySQL no admite ninguna indicación sobre el progreso de las consultas en ejecución. La única solución es optimizar / dividir consultas. Select se puede dividir por id como lo sugirió Dour High Arch. Aquí hay una consulta de 33 milion row table:

 mysql> SELECT SQL_NO_CACHE min(id), max(id) FROM `urls`; +---------+----------+ | min(id) | max(id) | +---------+----------+ | 5000 | 35469678 | +---------+----------+ 1 row in set (0.00 sec) 

Es mejor usar un número integer o al less un campo de date para dividir. Debe ser índice primario o único y no debe permitir valores nulos.

Por ahora, para mi situación específica, parece que no hay una solución real para esto. Como no puedo dividir mi consulta en varias más pequeñas y resulta contraproducente select count(*) primero y luego ejecutar la consulta "real" (duplica el time de ejecución de una consulta que ya es dolorosamente lenta), ninguna de las soluciones parece viable ya sea. Tal vez pronto, MySQL apoyará algo como esto

Esto es lo que deberá hacer para mejorar la siguiente consulta:

 SELECT `userId` FROM `openEndedResponses` AS `oe` WHERE `oe`.`questionId` = 3 -- zip code AND (REPLACE( REPLACE( `oe`.`value`, ' ', '' ), '-', '' ) IN ( '30071', '30106', '30122', '30134', '30135', '30168', '30180', '30185', '30187', '30317', '30004' )); 

Deberá asegurarse de que oe.questionId esté indexado; Deberá asegurarse de que oe.value no tenga espacio en toda la tabla cuando oe.questionId sea 3; suponiendo que 4 o 5 pueden ser, digamos, nombres de ciudades, donde aún desea permitir espacios.

Al hacer esto, podrá eliminar todos los REPLACES, lo que permitirá a MySQL usar un índice en oe.value.

MySQL fusionará ambos índices y le dará el resultado mucho más rápido, en términos de procesamiento.

En el caso de que tenga muchos userId repetidos; querrás agruparlos; de tal manera que las inputs del índice se descartan inmediatamente. Aún necesita escanear todo el índice fusionado; pero el tamaño del set de resultados tomará less time para ser transferido; mucho less de 13 segundos!

Pruébalo y mantente al tanto del resultado

¡Mejor!

¿Qué hay de search en la partición de su tabla mysql para que pueda distribuir la carga de lectura / escritura? Trate de limitar cada partición a 50 millones de filas (obviamente depende de su hardware)

Sé que esta es una vieja pregunta, pero estaba buscando una respuesta similar, al tratar de averiguar cuánto más tardaría mi actualización en una consulta de 250m filas.

Si tu corres:

 SHOW ENGINE INNODB STATUS \G 

Luego, en TRANSACCIONES, encuentre la transacción en cuestión, examine esta sección:

 ---TRANSACTION 34282360, ACTIVE 71195 sec starting index read mysql tables in use 2, locked 2 1985355 lock struct(s), heap size 203333840, 255691088 row lock(s), undo log entries 21355084 

El bit importante es "deshacer inputs de logging". Para cada fila actualizada, en mi caso parecía agregar una input de logging de deshacer (tratando de ejecutarla de nuevo después de unos segundos y ver cuántos se han agregado).

Si saltas hasta el final del informe de estado, verás esto:

 Number of rows inserted 606188224, updated 251615579, deleted 1667, read 54873415652 0.00 inserts/s, 1595.44 updates/s, 0.00 deletes/s, 3190.88 reads/s 

Aquí podemos ver que las actualizaciones de velocidad que se están aplicando son 1595.44 filas por segundo (aunque si está ejecutando otras consultas de actualización en tándem, esta velocidad podría estar separada entre sus consultas).

Así que de esto, sé que 21 millones se han actualizado con (250m-21m) 229m filas restantes.

229,000,000 / 1600 = 143,125 segundos (143,125 / 60) / 60 = 39.76 horas para llevar

Entonces parece que puedo mover los pulgares por otros días. ¡A less que esta respuesta sea incorrecta, en cuyo caso la actualizaré en algún momento antes!