Log in

No account? Create an account
entries friends calendar profile My Website Previous Previous Next Next
Mark Atwood
A working "progress bar" for a huge ALTER TABLE
My friend Gabriel came up with a working "progress bar" for ALTER TABLE. Until MySQL / Drizzle can do this "natively", this is a pretty neat trick:



3 comments or Leave a comment
mauser From: mauser Date: September 20th, 2009 10:47 pm (UTC) (Link)
I bet you could do my php project in your sleep. But then I wouldn't learn anything, right? :-)
fallenpegasus From: fallenpegasus Date: September 21st, 2009 01:19 am (UTC) (Link)
Probably not, I don't actually know any PHP. :)
From: (Anonymous) Date: September 22nd, 2009 09:29 pm (UTC) (Link)

TokuDB reports alter progress in show proceslist

The TokuDB storage engine from Tokutek reports progress on alter table operations in show processlist. For example, in one client session I started an alter table on a 50M row table:

mysql> select count(*) from shrink;
| count(*) |
| 50000000 |
1 row in set (19.45 sec)

mysql> alter table shrink engine=tokudb;

In a separate client session, show processlist reports progress:

mysql> show processlist\G
*************************** 1. row ***************************
Id: 85
User: mysql
Host: localhost
db: test
Command: Query
Time: 274
State: Inserted about 4281000 rows
Info: alter table shrink engine=tokudb
*************************** 2. row ***************************
Id: 87
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
2 rows in set (0.00 sec)

Since both the number of rows processed and the total seconds are reported, it's easy to compute the average rows/sec so far. Since the rate may drop over time, you can run show processlist multiple times and use deltas to get an estimate of the current rate.
3 comments or Leave a comment