Re: We're a Big MyISAM Shop, But...
Benoit St-Jean Wrote:
-------------------------------------------------------
> This horrible bug (still open!) is one reason to
> start with:
http://bugs.mysql.com/bug.php?id=57118
>
The bug you referenced doesn't look horrible to me. We have several mixed databases right now and have not seen this behavior, so perhaps our application does not use SELECT FOR UPDATE. Or if it does, then innodb_lock_wait_timeout must be expiring fast enough that nobody notices. In any case, if it did lock, the result would be a system freeze until innodb_lock_wait_timeout expires, which does not sound like a horrible outcome.
> After that, you have to realize that to be able to
> gain anything from InnoDB if you have big
> databases/tables, you'll also need to reserve some
> serious amounts of RAM for the InnoDB specific
> buffers as well.
>
Which is precisely why I say that InnoDB is a bloated hog that should be taken out, slaughtered, and turned into bacon.
> Joining between these 2 table types does not
> prevent you from locking problems as InnoDB tables
> will have row-level locking while MyISAM tables
> will still have table locks. The locking of your
> queries will be essentially be a table-lock as
> you're as slow as the more restrictive of your 2
> table types.
In that case, we just need to convert all of the tables that are referenced in the queries. That's still only a tiny fraction of the tables in the database.
>
> MySQL 8.0 will barely support MyISAM and it was
> announced it will be deprecated. Already with
> version 8.0, some features will not be available.
>
https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/
>
Given that we just went to MySQL 5.6, and will be on it for years, I don't think we have to worry about that right now.
> Rollbacking when mixing InnoDB & MyISAM is not
> possible.
We can currently roll back to the state at the end of any previous day. We do this by flushing tables and logs with read lock, then taking an LVM snapshot and backing up the snapshot.
>
> Referential integrity not being supported by
> MyISAM, what happens if you mix both ? Well,
> could end up having partial records in your
> database (present in one table but not in the
> other).
The app doesn't use transactions anyway.