Fixing Slow Zabbix DB: Practical MySQL Partition Setup (Part 2)

— Distributing I/O Load with Disk Separation and Tablespace Configuration

Overview

This guide focuses on one of the main reasons Zabbix DB performance degrades over time — I/O concentration.
We’ll reduce disk contention in MySQL 8.x by placing Zabbix’s history and trends data on a separate disk and defining General Tablespaces to explicitly control data locations.

If you completed Part 1 (Zabbix 7.4 + MySQL installation and schema import),
this step begins the physical separation of data and the actual disk-level split of your Zabbix database.

1) Prerequisites

This procedure can be done while MySQL is running,
but the correct order must be followed:
Add data directory → Update AppArmor policy → Restart MySQL.

2) Create Directories and Set Permissions

- Create directories
# mkdir -p /data/history
# mkdir -p /data/trends

- Set ownership and permissions
# chown -R mysql:mysql /data
# chmod 700 /data/history /data/trends

✅ Checkpoint

• The MySQL service account (mysql) must have read/write access under /data.
• /data should be a separate device (e.g. /dev/sdb) to achieve real I/O distribution.

3) Modify AppArmor Policy

MySQL access paths are restricted by AppArmor.
If /data isn’t allowed, tablespace creation will fail with “access denied”.

# vi /etc/apparmor.d/usr.sbin.mysqld

Add the following two lines:

/data/ r,
/data/** rwk,

Then restart AppArmor:

# systemctl restart apparmor

✅ Checkpoint

• The new /data/ entries appear in the policy file.
• No “AppArmor parser error” messages in /var/log/syslog.

4) Register the Directory in MySQL Configuration

Starting with MySQL 8.0.21, all external tablespace paths must be pre-declared.

# vi /etc/mysql/mysql.conf.d/mysqld.cnf

Add this line at the end:

innodb_directories=/data

Restart MySQL:

# systemctl restart mysql

✅ Checkpoint

mysql> SHOW VARIABLES LIKE 'innodb_directories';

→ The value must display /data.

5) Create General Tablespaces

Now create separate tablespaces for each disk.

mysql -uroot -p
# (enter password)

mysql> USE zabbix;

-- History tablespace (/data/history)
mysql> CREATE TABLESPACE ts_history ADD DATAFILE '/data/history/ts_history.ibd' ENGINE=InnoDB;

-- Trends tablespace (/data/trends)
mysql> CREATE TABLESPACE ts_trends ADD DATAFILE '/data/trends/ts_trends.ibd' ENGINE=InnoDB;

✅ Verify results

# ls -l /data/history/ts_history.ibd
# ls -l /data/trends/ts_trends.ibd

Both files must exist.
If they’re missing, check AppArmor policy or the innodb_directories setting.

6) Move Existing Tables into Each Tablespace

By default, Zabbix stores everything under /var/lib/mysql/zabbix.
We’ll relocate the heavy I/O tables (history*, trends*) to /data disks.

-- History tables → /data/history
mysql>ALTER TABLE zabbix.history       TABLESPACE ts_history;
mysql>ALTER TABLE zabbix.history_uint  TABLESPACE ts_history;

-- Trends tables → /data/trends
mysql>ALTER TABLE zabbix.trends        TABLESPACE ts_trends;
mysql>ALTER TABLE zabbix.trends_uint   TABLESPACE ts_trends;

7) Post-Migration Checks

• Confirm .ibd files for each table exist under /data/history and /data/trends.
• Compare directory sizes: du -sh /var/lib/mysql/zabbix du -sh /data/history
• Run: SHOW ENGINE INNODB STATUS\G → Ensure no related errors.
• If AppArmor still blocks access, remove cache under /etc/apparmor.d/cache/ and re-load using: apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

8) Summary

9) Next Part Preview

In Part 3, we’ll switch MySQL to file-per-table mode
and build daily partitions for history and monthly partitions for trends.
That’s where real DB I/O optimization begins.

🛠 마지막 수정일: 2025.10.29