Fixing Slow Zabbix DB: Practical MySQL Partition Tuning (Part 5)

— Final Performance Optimization by Disabling Housekeeper and Tuning Cache & Processes

Overview

In this final part of the series, we balance I/O between the Zabbix server processes and the MySQL database after applying the partition architecture.

We’ll completely disable the Housekeeper, then fine-tune critical parameters such as
CacheSize, HistoryCacheSize, TrendCacheSize, StartDBSyncers, and others
to maintain stability in large-scale, high-ingest environments.

1) Disable Housekeeper

The Housekeeper periodically removes outdated data from the database.
However, if partition-based auto-deletion scripts are already in place,
Housekeeper becomes redundant and only adds extra load.

In Zabbix 7.4, this must be disabled directly from the Web UI:

Path: Administration → Housekeeping

Item	Setting	Description
Events and alerts	☐ (Off)	Stops automatic event cleanup
Services	☐ (Off)	Disables SLA service history cleanup
User sessions	☑ (On)	Keep only user session retention
History	☐ (Off)	Handled by partition script
Trends	☐ (Off)	Handled by partition script

⚙️ If Housekeeper remains enabled, bulk deletions can temporarily pause MySQL I/O.
In partition-based environments, Housekeeper must be disabled.

2) Tune Zabbix Server Cache and Processes

Configuration file: /etc/zabbix/zabbix_server.conf

Uncomment the following parameters and adjust them to match your environment.
Below are recommended values for a server with 128 GB RAM.

Parameter	Description	Recommended Value
`CacheSize`	Configuration cache (hosts, templates)	256 M – 512 M
`HistoryCacheSize`	Real-time metrics cache	512 M – 1 G
`TrendCacheSize`	Trend aggregation cache	≥ 256 M
`ValueCacheSize`	Recent value cache (trigger evaluation)	≥ 512 M
`StartDBSyncers`	Parallel DB write threads	8 – 16
`StartPollers`	Poller process count ≈ (hosts ÷ 50)	Adjust by environment

Edit the file:

# vi /etc/zabbix/zabbix_server.conf

Example:

CacheSize=512M
HistoryCacheSize=1G
TrendCacheSize=512M
ValueCacheSize=512M
StartDBSyncers=12
StartPollers=20

Apply changes:

# systemctl restart zabbix-server

3) Tune MySQL InnoDB Parameters

Configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf

For a server with 128 GB RAM, the following values provide balanced stability and throughput:

innodb_buffer_pool_size = 72G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_instances = 4

Parameter	Value	Evaluation
`innodb_buffer_pool_size = 72G`	≈ 56–60 % of RAM	✅ Optimal
`innodb_log_file_size = 1G`	Stable I/O, prevents burst write spikes (4 G for very heavy loads)	⚙️
`innodb_flush_log_at_trx_commit = 2`	Performance-oriented while retaining durability	✅
`innodb_doublewrite = 1`	Essential for data integrity on power loss; `=0` may be faster on NVMe but risks torn pages	✅
`innodb_flush_method = O_DIRECT`	Prevents OS-level cache duplication	✅
`innodb_io_capacity = 2000`	Good for NVMe; 3000–4000 if I/O headroom available	✅
`innodb_read/write_io_threads = 8`	Balanced parallelism	✅
`innodb_buffer_pool_instances = 4`	Ideal for ≈ 70 GB pool	✅

Apply the settings:

# systemctl restart mysql

4) Validation and Verification

1. Check Housekeeper status
Zabbix UI → Administration → Housekeeping
→ Ensure all related items are disabled..

2. Test Zabbix UI responsiveness
Open graphs and event tabs — faster loading confirms proper optimization.

5) Results and Summary

Step	Primary Effect
Disable Housekeeper	Removes redundant cleanup tasks and reduces DB load
Expand Caches	Increases memory-based access, reduces I/O pressure
Tune InnoDB Params	Boosts transaction throughput and commit efficiency
Overall Effect	Partition-based I/O optimization + Zabbix process balance completed

💡 Your Zabbix deployment now operates as a fully automated system for data collection, retention, and cleanup.
With daily and monthly partitions, cron-based maintenance, and cache tuning combined,
Zabbix DB bottlenecks are virtually eliminated.

ⓒ 2025 엉뚱한 녀석의 블로그 [quirky guy's Blog]. 본문 및 이미지를 무단 복제·배포할 수 없습니다. 공유 시 반드시 원문 링크를 명시해 주세요.
ⓒ 2025 엉뚱한 녀석의 블로그 [quirky guy's Blog]. All rights reserved. Unauthorized copying or redistribution of the text and images is prohibited. When sharing, please include the original source link.

🛠 마지막 수정일: 2025.10.30