Categories
Database Development

Methods for tracking processing time for long running ADD INDEX call in MySQL

I’ve set off index creation on a very large table in MySQL and while I expected it to take a long time, I’m 5 days in and wondering if there’s any way to debug potential issues or simply let it run. I don’t have a precise row count but to estimate, it’s in the 100s of billions of rows and the table is ~400GB on disk. Neither memory or CPU usage appears to be overly taxed (mem ~8GB (out of 16GB total)).

The call I made from within MySQL is as follows:

alter table prices add index(dataDate, ticker, expDate, type), add index(s
ymbol), algorithm=inplace, lock=none;

Running show processlist from within a different MySQL instance shows the call with State ‘altering table’ so the call doesn’t appear blocked. Anything else I can check to gauge progress?

For reference I’m working with MySQL 8 and within Ubuntu 18.04.

Edit: Index eventually completed, took a little over 9 days.

Leave a Reply

Your email address will not be published. Required fields are marked *