Comments on: Schema changes – what’s new in MySQL 5.6?

By: honeybee

honeybee — Mon, 24 Apr 2017 20:46:06 +0000

@Przemyslaw, I think your article is great and I don’t appreciate people nit-picking.

By: teo

teo — Tue, 07 Jun 2016 21:07:22 +0000

@Przemysław Yes, I read the documentation; the documentation is clear and correct, your article isn’t so much, that’s my point. In the article you state that “most of the ALTER types won’t block writes to a table”. While “most” is a vague concept and you may argue that your statement, literally taken, is true (there are probably more ALTER statements that don’t block table writes than the ones which do), I would suggest to at least add a caveat that adding a column is NOT among those. Adding a column is a very common and very basic ALTER operation (indeed it seems pathetic to me that in the 21st century MySQL is still unable to do that without blocking writes on the whole table for the whole duration of the alter operation): so, if you state that “most” ALTER statements won’t block writes, I think most readers would assume that if adding a column was an exception, you would mention it.

By: Przemysław Malkowski

Przemysław Malkowski — Tue, 07 Jun 2016 20:48:11 +0000

@teo, Have you read the official documentation about which ALTER types allow concurrent DML? Limitations for adding columns are clearly stated and if you encountered another condition under which writes are blocked, you should IMHO report a bug.

By: teo

teo — Tue, 07 Jun 2016 17:19:28 +0000

“from now on [5.6] most of the ALTER types won’t block writes to a table that is being changed!”

That is BULLSHIT, unless in “most of the ALTER types” you don’t include adding columns. An ALTER operation as simple as adding a column in MySQL 5.6 will block all writes until the alter is finished, and there’s no way to avoid that.

By: Fernando Mattera

Fernando Mattera — Wed, 17 Jul 2013 13:31:18 +0000

“dark” I mean

By: Fernando Mattera

Fernando Mattera — Wed, 17 Jul 2013 13:30:25 +0000

Paul,

I noticed about hotbackup in enterprise edition, but I didn’t found documentation. It’s so darl…

By: Przemysław Malkowski

Przemysław Malkowski — Wed, 17 Jul 2013 10:37:38 +0000

Kim: the problem is that there is no real “optimize table” feature for InnoDB, full table rebuild always happen. And it’s mapped to ALTER as you mentioned, so it’s the same as ALTER TABLE foo ENGINE=InnoDB. In the example #4 you can see this operation is locking.
Optimize works totally different for MyISAM, but still the operation locks a table.

However, I should mention, that you can do ALTER TABLE foo ENGINE=InnoDB in non-locking manner by just using pt-online-schema-change tool. And it works for all MySQL versions.

By: kim

kim — Wed, 17 Jul 2013 10:14:10 +0000

Is OPTIMIZE TABLE xxx; now online as well? OPTIMIZE used to map to an ALTER table and no writes could be done on the table while the optimize took place (to bring the optimizer stats up to date) for that table — a problem on large active tables.

thanks

By: Peter Zaitsev

Peter Zaitsev — Mon, 08 Jul 2013 14:34:48 +0000

I wonder considering TokuDB is more light on DiskIO than traditional storage engines like Innodb would LVM and other snapshot based backups be working choice in more circumstances ?

By: paul

paul — Sat, 06 Jul 2013 19:27:33 +0000

by looking at the github repo for tokudb. it seems to support hot backups in the community edition but requires an application to be created / modified in order to use it.

By: Przemysław Malkowski

Przemysław Malkowski — Sat, 06 Jul 2013 07:51:19 +0000

Alexander: yes, though TokuDB became Open Source this year. It might be a good subject for a next blog post.

Fernando: Tokutek offers Hot Backup tool for TokuDB in it’s Enterprise Edition.

By: Fernando Mattera

Fernando Mattera — Fri, 05 Jul 2013 16:09:25 +0000

Has TokuDB hot backups?

By: Alexander Zaitsev

Alexander Zaitsev — Fri, 05 Jul 2013 14:55:42 +0000

You probably know, that TokuDB supports hot schema changes and hot index creation since v5.0 (2011). I never tested how it workw, since we were off TokuDB by that time. But I suppose it should work, since that was one of the most desired features from multiple TokuDB customers by that time (TokuDB is for OLAP, OLAP means huge tables).