MySQL Forums
Forum List  »  InnoDB

Ease My Pain: InnoDB in a Memory-Constrained Environment
Posted by: Authorized User
Date: September 09, 2021 12:54PM

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?

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.