Ease My Pain: InnoDB in a Memory-Constrained Environment

[Authorized User]

We have a physical server with 48 cores, 1TB RAM, and 14TB of usable NVME storage. Here is the challenge: we need to run 125 separate instances of MySQL on it. Each instance will handle a separate database consisting of about 5,000 tables. The size of each database will range from 10-400 GB, but the largest ones are relatively few. Most databases are in the 30 GB range. The application is probably 85% read, 15% write.

We can do this fine with MyISAM. In our experience, we can run 50 instances of MySQL with satisfactory performance on a 12-core server with 192 GB RAM and 2TB of 15K spinning disk, so putting 100 instances on a 48-core, 1TB RAM, NVME-based server would not be a problem. But, to paraphrase Leonidas, "InnoDB, that's gonna be a problem." InnoDB is a bloated, fragile, resource-gulping freakshow, consuming up to 300% more storage and keeping the disk hopping with all of its log writing. Conventional wisdom states that we should allocate 70-90% of available RAM to the buffer pool. That cannot happen in this case.

So here's the question. Given that we can only give each instance of MySQL a tiny fraction of what InnoDB wants for its buffer pool, what ELSE can we do to make InnoDB run as fast as possible and minimize the pain?