Mysql: Separate memory for databases

Question

Is there a way to separate memory allocation for databases in mysql on the same server?
Say for database_1, we would like to allocate 20GB RAM,
and for database_2, 10GB RAM.
Is there any way this is possible.

Answer 1

• No.
• Don't do it.

I say not to do it because it usually leads to folly. If one database grows faster than the other, then it is starved while the other has spare space. This may hobble the overall system more than the original benefit you envisioned.

Another example: Having /tmp on a separate filesystem. I hate to think how many times DBAs find queries crapping out, only to discover that their tiny /tmp fs was starved while the main disk was swimming in space.

On the other hand, there are valid cases for splitting up things -- one disk is an old, slow, spinning drive, another is a fast, low latency, SSD. Or you add an extra drive, but it is not convenient to get the OS to see all the drives as one larger virtual drive. These involve spelling out what databases / tables / logs to put where -- and lead to the potential problem that I mentioned above.

If you are referring to RAM, then the general rule is to give most of RAM to innodb_buffer_pool_size, then forget about RAM allocation. Only a few percent of RAM needs to be allocated for table cache, etc, etc. The buffer pool is used by all tables 'equally' -- that is on a roughly LRU basis.