6

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

1
  • If you expect to DROP frequently, we can discuss better ways to do it. But first, what version of MySQL is it?
    – Rick James
    Commented Mar 30, 2023 at 17:25

1 Answer 1

7

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.

2
  • 2
    This will only work if innodb_file_per_table is ON though.
    – jcaron
    Commented Mar 11, 2023 at 14:02
  • @jcaron This is very true. If innodb_file_per_table is off, the .frm will still be present. This indicates the presence of the temp table. All you can judge is how long it's been running with its timestamp. You won't know the size. Once the #sql-XXXX.frm file disappears, the ALTER TABLE is done. Commented Mar 11, 2023 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.