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

Ready to streamline your complex Zabbix operations?

I’ve distilled the most valuable insights from this blog into one essential guide. Take full control of your environment with the Zabbix 7 Enterprise Optimization Handbook [Mastering Hybrid Infrastructure Monitoring with a Kubernetes First Approach].

👉 Get your PDF copy here: https://jikimy.gumroad.com/l/zabbixmaster

— 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.12.12