MySQL同一服务器下能否为不同数据库分配独立内存？如database_1分20GB、database_2分10GB

Great question! Let’s break this down clearly—the short answer is yes, but you can’t directly assign a fixed GB limit to a database out of the box with standard MySQL Community Edition. Instead, you’ll need to use indirect approaches or enhanced MySQL distributions to achieve this kind of memory isolation. Here are the most practical solutions:

1. Use Percona Server/MariaDB’s Buffer Pool Instance Isolation #

Percona Server and MariaDB extend MySQL with features that let you split the innodb_buffer_pool into multiple independent instances. You can then bind specific database tables to a dedicated buffer pool, effectively reserving memory for that database.

• First, configure multiple buffer pool instances in your my.cnf/my.ini:

  innodb_buffer_pool_size = 30G  # Total combined memory for all instances
  innodb_buffer_pool_instances = 3  # Split into 3 instances (e.g., 10G each)
  

• Then, when creating or altering tables in database_1, assign them to a specific buffer pool tablespace:

  CREATE TABLE database_1.my_table (...) TABLESPACE = innodb_buffer_pool_1;
  ALTER TABLE database_2.my_table TABLESPACE = innodb_buffer_pool_2;
  

  This ensures tables from database_1 primarily use the memory allocated to innodb_buffer_pool_1, and database_2 uses the second instance. Note: You’ll need to apply this to all tables in the target databases for consistent isolation.

2. Run Multiple MySQL Instances with OS-Level Cgroup Limits #

If you need strict, hard memory limits, you can run separate mysqld processes (one per database) on the same server, then use cgroups (Linux) or similar OS-level tools to cap memory usage for each instance.

• Set up separate configuration files for each instance:
  • For database_1: my_db1.cnf with innodb_buffer_pool_size=18G (leave some headroom for other MySQL overhead) and port=3307
  • For database_2: my_db2.cnf with innodb_buffer_pool_size=8G and port=3308
• Use cgroups to limit each instance’s total memory (including buffer pool, query cache, etc.):

  # Create cgroup for db1
  sudo cgcreate -g memory:/db1_group
  sudo cgset -r memory.limit_in_bytes=20G db1_group
  # Start db1 instance in the cgroup
  sudo cgexec -g memory:db1_group mysqld --defaults-file=/etc/my_db1.cnf
  # Repeat for db2 with 10G limit
  

  This gives you full isolation, but requires managing multiple instances (ports, logs, backups) which adds operational overhead.

3. MySQL 8.0+ Resource Groups (Connection-Based Isolation) #

MySQL 8.0 introduced Resource Groups, which let you restrict CPU and memory usage for specific users or connections. While it doesn’t bind memory directly to a database, you can map users who only access database_1 to a resource group with a 20GB limit, and database_2 users to a 10GB group.

• Create resource groups:

  CREATE RESOURCE GROUP db1_group
    TYPE USER
    WITH MEMORY_LIMIT = 20G;
  
  CREATE RESOURCE GROUP db2_group
    TYPE USER
    WITH MEMORY_LIMIT = 10G;
  

• Assign users to the groups:

  ALTER USER 'db1_user'@'%' RESOURCE GROUP db1_group;
  ALTER USER 'db2_user'@'%' RESOURCE GROUP db2_group;
  

  This ensures any connections from db1_user (who only accesses database_1) can’t exceed the 20GB memory limit. Just make sure your user permissions are locked down to only their target database.

Key Caveats #

• Standard MySQL Community Edition has no native "per-database memory limit" feature—all the above workarounds either use third-party distributions, OS tools, or connection-level rules.
• Buffer pool isolation works best for read-heavy workloads where caching is the main memory consumer.
• Multiple instances add management overhead but offer the strictest isolation.

内容的提问来源于stack exchange，提问作者Haluk