347475 - [MySQL] Use InnoDB for most tables

Status: RESOLVED FIXED

(Bugzilla :: Database, enhancement, P1)

Description

[Max Kanat-Alexander]

Now that we have referential integrity, I'd like to see MySQL actually support it.

That means that every table besides the "bugs" table and the "longdescs" table should use InnoDB.

As far as I can see, this is as simple as doing an "ALTER TABLE $table ENGINE = InnoDB", but I do have to check out the MySQL documentation and make sure that there's nothing strange with InnoDB in the version of MySQL that we require.

Comment 1

[Max Kanat-Alexander]

Attachment: v1

Okay, here it is. Pretty simple code, overall.

Comment 2

[Max Kanat-Alexander]

There are important things that we should mention in the relnotes:

1) InnoDB tables take up more disk space than MyISAM tables. This should be
   unimportant unless you have a large attach_data table.

2) If you have replication, you'll probably have to re-set it up after
   running checksetup. I suppose that's normal, though--I wouldn't expect 
   most of checksetup's changes to replicate properly. You can also see:

   http://dev.mysql.com/doc/refman/4.1/en/innodb-and-mysql-replication.html

3) They don't support REPAIR TABLE. Thankfully, most of the times that you'd
   have to use REPAIR TABLE on a MyISAM table, InnoDB tables automatically
   recover without any user interaction.

   However, if the tables become corrupted because of an operating system
   error, you can fix them by doing a mysqldump and restoring the DB.
   If that doesn't work, you can restore them from the MySQL binary log
   using the mysqlbinlog tool. I myself have recovered whole Bugzilla
   installations with mysqlbinlog, so I know it works. And finally, if THAT
   doesn't work for some reason, you'll have to restore the tables from
   a backup. So people should *always* have a current backup of their
   Bugzilla database.

Comment 3

[Max Kanat-Alexander]

Another thing to note is that when using InnoDB tables on a very large installation, shutting down MySQL may take some time. This is entirely normal, and users should NOT kill -9 the process, because it could corrupt their DB.

Comment 4

[Max Kanat-Alexander]

More notes:

"InnoDB doesn't handle symlinked tables during an ALTER TABLE like MyISAM does. The new table will end up in the default location, not where the original table lived."

"You cannot move .ibd files from one instance to another. The .ibd files contain transaction ids and log sequence numbers. You also cannot move tables around on the same machine. Instead, use RENAME TABLE (and maybe a symlinked directly) to accomplish it."

"SHOW INNODB STATUS will give some insight into your disk I/O performance..."

MySQL doesn't perform well with hyperthreading. If you can disable it, disable it. That's probably true even for MyISAM tables.

Also, supposedly InnoDB takes less disk space in 5.0.

Comment 5

[Rémi Zara]

So if I understand well, you make all tables InnoDb except for bugs and longdescs ? From my experiment with MySql 4.1, it will refuse to make foreign keys if not both the referencing table and the referenced table are of the same type (and will only enforce them for InnoDB), and table bugs is referenced by table longdescs, so they should both be innodb for foreign keys to be enforced...
But maybe I'm missing something here...

Comment 6

[Max Kanat-Alexander]

(In reply to comment #5)
> table bugs is referenced by
> table longdescs, so they should both be innodb for foreign keys to be
> enforced...

  No, you're right. But longdescs can't be an InnoDB table, so that particular foreign key will be unenforced by MySQL. (We'll do clever things inside Bugzilla::DB::Schema::Mysql to make this happen.) Eventually the bugs table will be InnoDB, we just have to decide what to do about short_desc.

  These are mostly bridges that we'll cross when we get to them. They're all crossable.

Comment 7

[Max Kanat-Alexander]

Attachment: v2 (fix bitrot)

Granting myself review, as module owner. We discussed moving to InnoDB in one of our early meetings, and decided that it would be best to wait until the beginning of the 3.2 development cycle.

It's now the beginning of the 3.2 development cycle, so here we go!

Comment 8

[Max Kanat-Alexander]

I'm also granting myself approval, as justdave said module owners can do (when we originally discussed myself and LpSolit getting approval privileges).

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.50; previous revision: 1.49
done
Checking in Bugzilla/DB/Schema/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Schema/Mysql.pm,v  <--  Mysql.pm
new revision: 1.15; previous revision: 1.14
done

Comment 9

[Max Kanat-Alexander]

Added to the release notes for Bugzilla 3.2 in a patch on bug 432331.

Comment 10

[Denis Roy]

> 2) If you have replication, you'll probably have to re-set it up after
>    running checksetup. I suppose that's normal, though--I wouldn't expect 
>    most of checksetup's changes to replicate properly. You can also see:

Max, can you explain this?  What is it about checksetup that cannot be replicated?  Re-setting up replication is painful.

Comment 11

[Max Kanat-Alexander]

(In reply to comment #10)
> > 2) If you have replication, you'll probably have to re-set it up after
> >    running checksetup. I suppose that's normal, though--I wouldn't expect 
> >    most of checksetup's changes to replicate properly. You can also see:
> 
> Max, can you explain this?  What is it about checksetup that cannot be
> replicated?  Re-setting up replication is painful.

  I don't know, ask justdave. I actually think you may not have to re-set up replication at all--the ALTER TABLE commands probably replicate just fine.