Re: Ease My Pain: InnoDB in a Memory-Constrained Environment

[Authorized User]

Forgive me for leaking a bit of frustration. We currently have maybe 10,000 InnoDB tables. They are 300% the size of their MyISAM counterparts. Issuing a SELECT COUNT(*) takes bloody ages. And every time MySQL crashes for any reason, InnoDB whines that table X cannot be opened because the tablespace is already in use by table Y, or some such thing. Oh yeah, and it eats memory like a ghoul from the Walking Dead. Overall, InnoDB has been 500% more trouble to host, support, troubleshoot, backup, and recover, than our MyISAM databases, which have been far more reliable. So yeah, I'm annoyed, but I'm trying to get over it.

That said, the writing is on the wall. MyISAM is pretty much history with MySQL 8.0, so we have to make the move.

While your comments are wise, the situation is what it is. We have a server. We need to jam a number of MyISAM instances on it. We can't give InnoDB the memory it wants, therefore many queries will go to disk. What can we do to make InnoDB work as comfortably as possible?