Monitoring progress of a SQL script with many UPDATEs in MariaDB

Question

I'm running a script with several million update statements like this:

UPDATE data SET value = 0.9234 WHERE fId = 47616 AND modDate = '2018-09-24'  AND valueDate = '2007-09-01'  AND last_updated < '2018-10-01';

fId, modDate and valueDate are the 3 components of the data table's composite primary key.

I initially ran this with AUTOCOMMIT=1 but I figured it would speed up if I set AUTOCOMMIT=0 and wrapped the transactions into blocks of 25.

In autocommit mode, I used SHOW PROCESSLIST and I'd see the UPDATE statement in the output, so from the fId foreign key, I could tell how far the script had progressed.

However without autocommit, watching it running now, I haven't seen anything with SHOW PROCESSLIST, just this:

610257  schema_owner_2  201.177.12.57:53673  mydb  Sleep   0               NULL                    0.000
611020  schema_owner_1  201.177.12.57:58904  mydb  Query   0       init    show processlist        0.000

The Sleep status makes me paranoid that other users on the system are blocking the updates, but if I run SHOW OPEN TABLES I'm not sure whether there's a problem:

MariaDB [mydb]> SHOW OPEN TABLES;
+----------+----------------+--------+-------------+
| Database | Table          | In_use | Name_locked |
+----------+----------------+--------+-------------+
| mydb     | data           |      2 |           0 |
| mydb     | forecast       |      1 |           0 |
| mydb     | modification   |      0 |           0 |
| mydb     | data3          |      0 |           0 |
+----------+----------------+--------+-------------+

Is my script going to wait forever? Should I go back to using autocommit mode? Is there any way to see how far it's progressed? I guess I can inspect the data for the updates but that would be laborious to put together.

Answer 1

• Check for progress by actually checking the data.
• I assume you are doing COMMIT?
• It is reasonable to see nothing -- each UPDATE will take very few milliseconds; there will be Sleep time between UPDATEs.
• Time being 0 is your clue that it is progressing.
• There won't necessarily be any clue in the PROCESSLIST of how far it has gotten.
• You could add SELECT SLEEP(1), $fid; in the loop, where $fid (or whatever) is the last UPDATEd row id. That would slow down the progress by 1 second per 25 rows, so maybe you should do groups of 100 or 500.