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

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
lyeaf
  • 177
  • 1
  • 9
  • If you expect to DROP frequently, we can discuss better ways to do it. But first, what version of MySQL is it? – Rick James Mar 30 '23 at 17:25

1 Answers1

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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 2
    This will only work if innodb_file_per_table is ON though. – jcaron Mar 11 '23 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. – RolandoMySQLDBA Mar 11 '23 at 17:01