Is it possible to estimate how much time is left for a DROP statement?

Question

I made this query : ALTER TABLE dbname.tablename DROP COLUMN columnname and it's been running for a few hours and I wanted to know how much time would it still be going.

There are approximatively 750m rows.

Thanks !

If you need anymore details I'll give them, I didn't want to give ones that weren't useful

Answer 1

From the mysql client program, there is no standard way to see progress. MariaDB does have a way (see my 10 year old post Is there a progress indicator for OPTIMIZE TABLE progress?)

If you want to track it, you can do so outside of the mysql client program.

First, login to mysql and run SELECT @@global.datadir;

Let's say it give you /var/lib/mysql

In the OS as root, do the following

cd /var/lib/mysql/dbname
ls -l tablename.ibd

This will show the physical filesize of the InnoDB table.

Now run this in that same database folder

ls -lt | grep "sql\-"

or

ls -lt *sql-*

You should see a temp table of some kind start with a #

When their respective filesizes come close, it should be done soon.

If the temp file is a small and not changing, your table might be locked waiting for read or writes to finish.