[MDEV-9013] alter table engine=blackhole takes lot of time Created: 2015-10-26  Updated: 2018-01-17  Resolved: 2018-01-17

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.0.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alex Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

CentOS 6.7



 Description   

I decided to alter big myisam tables to blackhole and it took very long, like if it was converting to any other type

Example:
Query OK, 201147303 rows affected (8 min 9.60 sec)
Records: 201147303 Duplicates: 0 Warnings: 0

Actually I could make it faster by either dropping/recreating or truncating/alter

Maybe a small fix can be introduced? To make alter-to-blackhole happen faster regardless the original table size/engine?

Thanks!



 Comments   
Comment by Elena Stepanova [ 2015-11-24 ]

Sorry for the delay.
Considering how ALTER TABLE works, it appears to be logical that ALTER TABLE ... ENGINE=BLACKHOLE on a big table works longer than DROP / CREATE.
Effectively, you create an empty BLACKHOLE table, select all rows from the source MyISAM table, insert them into the blackhole table, then drop the MyISAM table, and rename the BLACKHOLE table. Surely it will work slower than if you had only done CREATE and DROP, skipping selecting and pseudo-inserting 200M rows.

Now, I don't know if it's possible to optimize this process somehow for BLACKHOLE tables, but the question is – is it really worth the trouble? I don't expect it's a frequently used operation, and when one does need it, why can't one really just drop the table and re-create it using BLACKHOLE engine?

Comment by Alex [ 2015-11-24 ]

Hi Elena,
Of course it's possible to use drop/create.

But if someone deals with it without prior investigation/testing (or thinking about nature of alter command) - it would end bad like in my case and active system may get freeze for a while until alter finishes. I just thought it could be a good thing to improve it a bit. And yes, you are right - it's not something frequently used. Maybe it worth at least few words in documentation to warn about alter engine=blackhole possible time consumption / effect it might have on a live system ?

Comment by Elena Stepanova [ 2015-11-24 ]

I'll assign it to serg to decide whether anything is needed to be done here.

Generated at Thu Feb 08 07:31:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.