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

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

---

— 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

Component	Requirement
OS	Ubuntu 22.04
Zabbix	7.4
MySQL	8.x
Separated disks	/data/history, /data/trends already configured
Previous steps	Schema import and tablespace migration (Parts 1–2) completed

⚠️ 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

Table	Data Volume	Purpose	Typical Partition Interval
zabbix.history*	Millions of rows per second	Real-time, short-term metrics	Daily (1–3 days)
zabbix.trends*	Aggregated average / max / min data	Mid- to long-term storage	Monthly (30 days)

---

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.

🔥 HISTORY / HISTORY_UINT — Initial Partition Rule (Daily)

History table partitions are defined from “today 00:00:00 (UTC) to tomorrow 00:00:00 (UTC)” in 24-hour units.
In other words, each partition covers one full day.

---

① Check today’s date (UTC)

date -u +%Y-%m-%d

Example:

2025-12-07

This date is used directly as the partition name (pYYYYMMDD).

---

② Calculate the Epoch boundary for tomorrow (today +1 day) at 00:00:00 UTC

The value used in VALUES LESS THAN() must be
the Epoch timestamp of 00:00:00 UTC on the next day.

date -u -d '2025-12-08 00:00:00' +%s

Example output:

1765152000

This number is the partition boundary.

---

③ Partition naming rule (aligned with the automation script)

• Partition name = today’s date
• Boundary value = Epoch for tomorrow 00:00 UTC

Therefore:

• Partition name: p20251207
• VALUES LESS THAN: 1765152000 (2025-12-08 00:00 UTC)

---

④ Meaning of the partition

p20251207 = clock < 2025-12-08 00:00:00 (UTC)

That means:

The partition p20251207 contains all data for the entire day of 2025-12-07.

---

🔥 TRENDS / TRENDS_UINT — Initial Partition Rule (Monthly)

Trends tables use the boundary “next month’s 1st day at 00:00:00 UTC”.

When creating the initial partition manually, UNIX_TIMESTAMP() must be used.

① Partition name (pYYYYMM)

Use the current month at the time the script is first applied.

Example:
Initial deployment in December 2025 →

p202512

② VALUES LESS THAN boundary

Use:

UNIX_TIMESTAMP('next month 1st day 00:00:00 UTC')

Base month: 2025-12
Next month: 2026-01-01 00:00:00 UTC

VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01 00:00:00'))

This defines the full data range for December 2025.

You must never copy and paste the initial partition examples for history or trends as they are.
They must be adjusted and applied according to your own environment.

---

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

Step	Action	Result
Tablespace → File-per-table	ALTER TABLE ... TABLESPACE = innodb_file_per_table	Partition creation enabled
Create history partitions	Daily (UTC epoch)	Daily partitions under /data/history
Create trends partitions	Monthly (UNIX_TIMESTAMP)	Monthly partitions under /data/trends
Boundary reference	UTC	Prevents timezone mismatch in automation

---

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