Estimating query completion time in MySQL
Many factors influence the query execution times, so there’s no accurate way to tell how long a given query will run. However, if you roughly know the number of records your query touches, we can ballpark the progress and the potential execution time. Here’s how.
show processlist provides a list of running processes/queries. Use that to find out the status of your query. It also shows you the current query, elapsed time and its state.
+-------+--------+-----------------+-----------+-------+----+------------+--------------------------+
|Id |User |Host |db |Command|Time|State |Info |
+-------+--------+-----------------+-----------+-------+----+------------+--------------------------+
|4276356|user1 |10.61.1.171:50035|test_db |Query |44 |Sending data|select count(*) |
| | | | | | | |from users |
|4276397|user1 |10.61.1.171:50069|null |Query |0 |starting |show processlist |
+-------+--------+-----------------+-----------+-------+----+------------+--------------------------+Once you know if your query is running, you can use show engine innodb status to get a detailed status of the storage engine.
=====================================
2022-10-27 19:07:57 0x1461fbd78700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds
...snipped...
------------
TRANSACTIONS
------------
Trx id counter 11881679757
Purge done for trx's n:o < 11881678629 undo n:o < 0 state: running but idle
History list length 564
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 303886057346344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 303886057348136, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 303886057347240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 303886057344552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 303886057345448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 303886057343656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 303886057342760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 11881678627, ACTIVE 378 sec fetching rows
mysql tables in use 1, locked 1
274193 lock struct(s), heap size 30318800, 17482055 row lock(s), undo log entries 17207863
MySQL thread id 18, OS thread handle 22411069859584, query id 4682 10.61.1.173 appuser updating
UPDATE folders SET parent_node_id = substring_index(ancestry, '/', -1)
...snipped...
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=20303, Main thread ID=22389133928192 , state=sleeping
Number of rows inserted 27, updated 17207863, deleted 0, read 58254156
0.00 inserts/s, 44733.46 updates/s, 0.00 deletes/s, 44733.45 reads/s
Number of system rows inserted 0, updated 2661, deleted 0, read 9520
0.00 inserts/s, 2.48 updates/s, 0.00 deletes/s, 0.59 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================TRANSACTIONS and ROW OPERATIONS are the sections of interest here. The row operations section gives you a breakdown of DML operations per second. The Transactions section provides information about the current query, the number of row locks acquired and the undo log entries. Undo log entries signify the number of updates done.
If you are only selecting the rows, the estimated query completion time is the potential rows divided by the reads per second.
To find the time to completion for update/delete operations, you need to divide the count of remaining records by updates(or deletes) per second. In the above example, the total number of estimated records is 39M. With an update rate of 44733.6 records/second, the query needs at least an additional (39000000 - 17207863)/(44733.46) = ~487.15 seconds.
If you do not have a rough count on the number of records your query updates, depending on the query, the number of row locks can give you an indicative number.
Keep in mind that this is an estimate of the average time remaining. Real values will differ.