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

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

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

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

💡 도움이 필요하신가요?
Zabbix, Kubernetes, 그리고 다양한 오픈소스 인프라 환경에 대한 구축, 운영, 최적화, 장애 분석, 광고 및 협업 제안이 필요하다면 언제든 편하게 연락 주세요.

📧 Contact: jikimy75@gmail.com
💼 Service: 구축 대행 | 성능 튜닝 | 장애 분석 컨설팅

📖 E-BooK [PDF] 전자책 (Gumroad): Zabbix 엔터프라이즈 최적화 핸드북
블로그에서 다룬 Zabbix 관련 글들을 기반으로 실무 중심의 지침서로 재구성했습니다. 운영 환경에서 바로 적용할 수 있는 최적화·트러블슈팅 노하우까지 모두 포함되어 있습니다.

💡 Need Professional Support?
If you need deployment, optimization, or troubleshooting support for Zabbix, Kubernetes, or any other open-source infrastructure in your production environment, or if you are interested in sponsorships, ads, or technical collaboration, feel free to contact me anytime.

📧 Email: jikimy75@gmail.com
💼 Services: Deployment Support | Performance Tuning | Incident Analysis Consulting

📖 PDF eBook (Gumroad): Zabbix Enterprise Optimization Handbook
A single, production-ready PDF that compiles my in-depth Zabbix and Kubernetes monitoring guides.