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

— Converting to File-per-Table and Building History / Trends Partition Structures

Overview

This article explains how to convert the Zabbix core tables (history and trends) into daily and monthly partitioned tables, based on the tablespace layout prepared in the previous part.

Starting with MySQL 8.0, you cannot create partitions inside General Tablespaces.
Therefore, we must first convert the target tables to the file-per-table format before defining partitions.

1) Prerequisites

⚠️ Warning:
Applying partitions on a live production environment can cause heavy I/O activity.
Always validate these steps in a staging or test environment before production rollout.

2) Convert from General Tablespace to File-per-Table

Because MySQL 8.0 does not support partitioning on general tablespaces,
each table must be converted back to the innodb_file_per_table structure first.

mysql>ALTER TABLE zabbix.history       TABLESPACE = innodb_file_per_table;

mysql>ALTER TABLE zabbix.history_uint  TABLESPACE = innodb_file_per_table;

mysql>ALTER TABLE zabbix.trends        TABLESPACE = innodb_file_per_table;

mysql>ALTER TABLE zabbix.trends_uint   TABLESPACE = innodb_file_per_table;

3) Before Creating Partitions

🔹 Structural Differences between history and trends

4) Epoch Time Reference

Partition boundaries should always be defined using UTC Epoch Time.
This avoids timezone-related confusion and simplifies future cron automation.

🔹 What is Epoch Time?

Epoch Time (UNIX timestamp) represents the number of seconds elapsed
since 1970-01-01 00:00:00 UTC.

Because it’s timezone-independent, this approach ensures that
no matter where your servers are (KST, JST, PST, etc.),
partition boundaries and automation remain consistent.

🔹 Why Use UTC Epoch for Partition Boundaries?

MySQL partitions can be defined by DATETIME or TIMESTAMP columns.
Since each environment may run with different timezones,
it’s safer to fix all partition logic to UTC Epoch.
This guarantees that cron scripts and partition generation schedules remain aligned globally.

🔹 Example

If you want to set the boundary for
2025-10-25 00:00:00 UTC, its epoch value is:

2025-10-25 00:00:00 UTC = 1761350400

Check it directly in Linux:

# date -d '2025-10-25 00:00:00 UTC' +%s

5) Create Daily Partitions for history and history_uint

📌 Start by creating the first partition using today’s date as reference,
and adjust the epoch boundary to fit your environment.

mysql>ALTER TABLE zabbix.history
PARTITION BY RANGE (clock) (
  PARTITION p20251024 VALUES LESS THAN (1761350400) DATA DIRECTORY='/data/history',
  PARTITION pMAX      VALUES LESS THAN (MAXVALUE)   DATA DIRECTORY='/data/history'
);

mysql>ALTER TABLE zabbix.history_uint
PARTITION BY RANGE (clock) (
  PARTITION p20251024 VALUES LESS THAN (1761350400) DATA DIRECTORY='/data/history',
  PARTITION pMAX      VALUES LESS THAN (MAXVALUE)   DATA DIRECTORY='/data/history'
);

6) Create Monthly Partitions for trends and trends_uint

📌 Use the first day of the next month at 00:00:00 UTC as the partition boundary.

mysql>ALTER TABLE zabbix.trends
PARTITION BY RANGE (clock)(
  PARTITION p202510 VALUES LESS THAN (UNIX_TIMESTAMP('2025-11-01 00:00:00')) DATA DIRECTORY='/data/trends',
  PARTITION pMAX    VALUES LESS THAN (MAXVALUE)                               DATA DIRECTORY='/data/trends'
);

mysql>ALTER TABLE zabbix.trends_uint
PARTITION BY RANGE (clock)(
  PARTITION p202510 VALUES LESS THAN (UNIX_TIMESTAMP('2025-11-01 00:00:00')) DATA DIRECTORY='/data/trends',
  PARTITION pMAX    VALUES LESS THAN (MAXVALUE)                               DATA DIRECTORY='/data/trends'
);

7) Post-Creation Checks

• Verify that /data/history and /data/trends now contain partition files
  like p20251024.ibd, p202510.ibd, etc.
• Check MySQL logs (/var/log/mysql/error.log) for any partition errors.
• Existing data is not automatically moved into new partitions —
  only newly collected metrics will be written to them going forward.

8) Summary

9) Next Part Preview

In Part 4, we’ll build the automation layer —
two cron-managed scripts (zbx-part-history.sh and zbx-part-trends.sh)
that automatically create new partitions daily/monthly and delete expired ones
based on the defined retention policy.

🛠 마지막 수정일: 2025.10.30