MySQL长查询进度监控 [英] MySQL Long Query Progress Monitoring

问题描述

仅提出我的问题，我了解没有直接支持这样的事情.我要寻找的是任何一种变通方法，或者令人费解的推导方法，可以使我得到一半可预见的结果.

Just to preface my question, I understand that there is no direct support for something like this. What I am looking for is any sort of work-around, or convoluted derivation that would get me a half-respectable result.

我正在使用群集引擎处理一个相当大的MySQL群集(表> 4亿行).

I am working with a rather large MySQL cluster (tables > 400 million rows) using the cluster engine.

有人知道通过直接在mysql中进行长查询来直接检索或以其他方式(或更好)得出准确的进度指示的方法吗?我有一些查询可能最多需要45分钟的时间，我需要确定在处理过程中我们是10％还是90％.

Is anyone aware of a way to either directly retrieve or otherwise derive a somewhat (or better) accurate indication of progress through a long query in mysql? I have some queries that can take up to 45 minutes, and I need to determine if we're 10% or 90% through the processing.

这里的评论中是一个的提炼和泛化版本，它导致了我的原始问题...

As requested in the comments here is a distilled and generified version of one of the queries that is leading to my original question...

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' ));

此查询针对具有约9500万行的单个表运行.运行查询需要8秒钟，传输数据又需要13秒钟(总计21秒钟).考虑到表的大小以及正在使用的字符串操作函数，我想说它运行得非常快.但是，对于用户而言，卡住或闲置仍然是21秒.一些进展的指示将是理想的.

This query is run against a single table with ~95 million rows. It takes 8 seconds to run the query and another 13 to transfer the data (21 sec total). Considering the size of the table, and the fact that there are string manipulation functions being used, I'd say it's running pretty damn fast. However, to the user, it's still 21 seconds appearing either stuck or idle. Some indication of progress would be ideal.

推荐答案

就目前而言-对于我的具体情况-似乎还没有真正的解决方案.由于我无法将查询拆分为几个较小的查询，并且事实证明，它首先对select count(*)起反作用，然后运行真实"查询(使本来就很慢的查询的执行时间增加了一倍)，因此，任何变通方法似乎都不可行.也许很快，MySQL将支持这样的东西

For now -- for my very specific situation -- there seems to be no real solution for this. Since I can't split my query into several smaller ones and it's proving counterproductive to select count(*) first, and then running the "real" query (doubles execution time of an already painfully slow query), none of the workarounds seem viable either. Maybe soon, MySQL will support something like this

这篇关于MySQL长查询进度监控的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！