Comments on: mk-schema-change? Check out ideas from oak-online-alter-table

By: shlomi

shlomi — Sun, 20 Jun 2010 03:00:41 +0000

@Vamsi, Shall we continue this discussion by mail? You can find my email address here.

By: Nagavamsi Ponnekanti (Vamsi)

Nagavamsi Ponnekanti (Vamsi) — Sat, 19 Jun 2010 20:47:52 +0000

Hi Shlomi,
Regarding your comment “I’ve seen it work, and I’ve seen it crash.”, do u remember which step(s) it was crashing at?
Thanks.

By: shlomi

shlomi — Sat, 19 Jun 2010 18:32:09 +0000

@Vamsi,
That’s because Google changed their linking method. Please follow this link with your browser to get to the real download link.
You have downloaded an HTML page.

Shlomi

By: Nagavamsi Ponnekanti (Vamsi)

Nagavamsi Ponnekanti (Vamsi) — Sat, 19 Jun 2010 18:24:27 +0000

Shlomi,
wget is able to get http://code.google.com/p/openarkkit/downloads/detail?name=openark-kit-111.tar.gz,
but tar complains.

tar -zxvf online.tar.gz

gzip: stdin: not in gzip format
tar: Child returned status 1
tar: Error exit delayed from previous errors

By: Mark Callaghan

Mark Callaghan — Sat, 19 Jun 2010 13:54:54 +0000

@Vamsi – InnoDB gets S locks on rows in the SELECT portion of INSERT/UPDATE/DELETE … SELECT * from FOO. This is done so that the statement produces the same result on a slave. If this were not done, then another connection could start running after the long running statement above, update the selected table (FOO above), commit and write to the binlog before the INSERT/UPDATE/DELETE … SELECT * from FOO above.

In that case the long-running statement above doesn’t see the fast update on the master. But on the slave the fast update is applied first from the relay log and the long-running INSERT/UPDATE/DELETE reads it.

Unfortunately, InnoDB almost always gets the S lock even when it shouldn’t — binlog is disabled was the obvious case. It might also get the S lock when sql_log_bin=0. Of course, we can fix things like that.

By: shlomi

shlomi — Sat, 19 Jun 2010 07:07:24 +0000

@Vamsi,

1. Yes. Originally I tried tuple comparison, e.g. (a,b,c) >= (3,4,5). That didn’t turn out well, and I’ve documented it on: MySQL not being able to utilize a compound index?.
With multiple column the breaking down of the range comparison is ugly indeed, but it’s code-generated, so who cares…? Plus, the optimizer uses the key well.

2. With InnoDB locks are never relased during transaction but only when it commits or rolls back.

3. Likewise

Shlomi

By: shlomi

shlomi — Sat, 19 Jun 2010 03:58:25 +0000

Hi Mark, Yes, code is published for over a year: openark kit Look for oak-online-alter-table.

By: Nagavamsi Ponnekanti (Vamsi)

Nagavamsi Ponnekanti (Vamsi) — Sat, 19 Jun 2010 02:26:55 +0000

Shlomi,
Interesting approach. Some questions I have are
1. For multi-column unique key, chunking would make where clause complicated, right? Eg., for a 2-column key, if 1st select reads till [x, y] the where clause of 2nd select will look like ((col1 = x and col2 > y) OR (col1 > x)). Seems to get even more messy with 3 column keys. If we use such complex where clauses, is mysql optimizer smart enough to make use of index on unique key?

2. Mysql seems to get read locks on rows of table S in statements like “insert into T(…) select … from S”. Are those row locks held till commit, or released as soon as scan moves to next row?

3. Likewise does “delete from T where key not in (select … from S)” also get READ row locks on S, and fo so, are those read locks held till commit?

By: Mark Callaghan

Mark Callaghan — Sat, 19 Jun 2010 00:39:28 +0000

We finally got around to working on a tool for this. It uses triggers. Did you publish code for your version?

By: shlomi

shlomi — Wed, 10 Mar 2010 19:39:15 +0000

I think your quote on triggers took some 30% of our entire conversation 😀

By all means, this is not a discussion on triggers. There’s an algorithm beside the triggers here.