The progress indicator of MySQL or MariaDB long-running commands and queries is extremely extremely and frustratingly coarse. In an index update I'm running now it was stuck in the same state for more than three hours. Thankfully, the pmonitor tool allows us to precisely monitor the progress of many commands. Here's an example of its application on MariaDB.

Adding an index on a GHTorrent table with 6 billion records seemed to take forever, stuck in the "Enabling keys" state.

MariaDB [ghtorrent]> alter table project_commits > add unique index(commit_id, project_id); Stage: 2 of 2 'Enabling keys' 0% of stage done

Looking at the MariaDB process list wasn't much more helpful.

MariaDB [ghtorrent]> show processlist\G *************************** 1. row *************************** Id: 857 User: ghtorrent Host: localhost db: ghtorrent Command: Query Time: 15383 State: Repair by sorting Info: alter table project_commits add unique index(commit_id, project_id) Progress: 50.000 *************************** 2. row *************************** Id: 859 User: ghtorrent Host: localhost db: ghtorrent Command: Query Time: 0 State: Init Info: show processlist Progress: 0.000 2 rows in set (0.000 sec)

To get a better picture I simply run pmonitor with the following arguments:

-c and the name of the MariaDB server

and the name of the MariaDB server -u to specify that I was also interested in files opened in update mode

to specify that I was also interested in files opened in update mode -i 10 to iterate the reporting every ten seconds

to iterate the reporting every ten seconds -d to see files whose ETA (estimated time of arrival) differed from one iteration to the next

This gave me a very accurate running picture of the command's progress.