Using Cgroups for MySQL Resource Isolation: CPU, Memory, IO, and Network Limits

1. Background

Resource contention among MySQL instances can cause performance degradation and instability; Cgroups provide a proven solution for isolating resources such as CPU, memory, I/O, and network.

2. Cgroups Implementation

2.1 CPU Limiting

Cgroups support three common CPU limiting modes: priority, time quota, and core binding.

<code>cgcreate -g cpu:/mysql23761

echo 100000 > /sys/fs/cgroup/cpu/mysql23761/cpu.cfs_period_us

echo 400000 > /sys/fs/cgroup/cpu/mysql23761/cpu.cfs_quota_us

cgclassify -g cpu:mysql23761 3614</code>

2.2 Memory Limiting

Set a memory limit higher than the current usage; enable OOM control if desired.

<code>cgcreate -g memory:/mysql23761

echo 2G > /sys/fs/cgroup/memory/mysql23761/memory.limit_in_bytes

echo 0 > /sys/fs/cgroup/memory/mysql23761/memory.oom_control

cgclassify -g memory:mysql23761 36310</code>

2.3 I/O Limiting

Both bandwidth and IOPS can be limited; O_DIRECT must be used for effective enforcement.

<code>cgcreate -g blkio:/mysql23761

echo "8:16 10485760" >> /sys/fs/cgroup/blkio/mysql23761/blkio.throttle.write_bps_device

echo "8:16 500" >> /sys/fs/cgroup/blkio/mysql23761/blkio.throttle.write_iops_device

cgclassify -g blkio:mysql23761 47719</code>

2.4 Network Limiting

Outbound bandwidth is limited via tc with a net_cls cgroup; inbound bandwidth requires a virtual interface and traffic redirection.

<code># Outbound limit (100 Mbit/s)
cgcreate -g net_cls:/mysql23761

echo 0x100001 > /sys/fs/cgroup/net_cls/mysql23761/net_cls.classid

tc qdisc add dev bond0 root handle 10: htb default 1

tc class add dev bond0 parent 10: classid 10:1 htb rate 100mbit ceil 100mbit

tc filter add dev bond0 parent 10: protocol ip prio 10 handle 1: cgroup

cgclassify -g net_cls:mysql23761 47719</code>

3. Testing with Sysbench

A CentOS 7 environment with MySQL 8.0.35 and sysbench is used. Tests compare unrestricted runs with each type of limit applied, showing significant drops in TPS/QPS when limits are active.

<code># Example sysbench command
/usr/local/sysbench/bin/sysbench /usr/local/sysbench/src/lua/oltp_read_write.lua \
  --mysql-host=192.168.168.1 --mysql-port=23761 --mysql-db=sbtest \
  --mysql-user=sysbench --mysql-password=sysbench \
  --table_size=1000000 --tables=5 --threads=64 \
  --report-interval=10 --time=120 run</code>

4. Summary

Cgroups effectively isolate MySQL resources, but operational safeguards (monitoring, alerting, emergency procedures) and management of large numbers of cgroup rules are essential for production deployment.