How to monitor MySQL / MariaDB query progress

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
• -u to specify that I was also interested in files opened in update mode
• -i 10 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.

$ sudo pmonitor -c mysqld -u -i 5 -d
/dev/null       0%
/home/mysql/aria_log.00000001   100%
/home/mysql/tc.log      100%
/home/mysql/mysql/servers.MYI   38.67%
/home/mysql/mysql/user.MYD      100%
[94 lines removed]
/home/mysql/ghtorrent/#sql-54b9_359.MYI 0.00%
/home/mysql     0%
/home/mysql/ghtorrent/#sql-54b9_359.MYD 43.12% ETA 0:27:52
/home/mysql/ghtorrent/#sql-54b9_359.MYD 43.45% ETA 0:27:59
/home/mysql/ghtorrent/#sql-54b9_359.MYD 43.80% ETA 0:28:23
/home/mysql/ghtorrent/#sql-54b9_359.MYD 44.14% ETA 0:28:02
/home/mysql/ghtorrent/#sql-54b9_359.MYD 44.47% ETA 0:27:48
/home/mysql/ghtorrent/#sql-54b9_359.MYD 44.83% ETA 0:27:17
[...]

Comments   Toot! Tweet Share