Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 37 ยท Scaling and Proxies

Database Tuning, Housekeeping and Partitioning

Keep a Zabbix database healthy: history and trend retention, how the housekeeper deletes data, partitioning and TimescaleDB, and the Nagios retention settings.

The Zabbix database grows without pause. Every value of every item becomes a row, and rows must be deleted again when they are old enough. How you store and delete them decides whether your monitoring system is fast in year three or crawling. This lesson covers retention settings, the built-in housekeeper, partitioning and TimescaleDB, and the equivalent topics for Nagios. Zabbix and its database are not available on this site; commands are shown as read-only, and the stand-ins model the arithmetic.

What is stored where

Main tables in the schema:

Table Content
history, history_uint Raw numeric values (float, unsigned integer)
history_str, history_text, history_log Text, long text and log values
trends, trends_uint Hourly min, avg, max, count per item
events, problem, alerts Problem history and sent notifications
items, hosts, triggers, ... Configuration (small)

History and trends dominate. A 1,000 NVPS installation writes 86.4 million history rows per day.

Retention: item level and global

Each item has history storage period and trend storage period (for example 14 days and 365 days). The defaults are often too generous. Questions to ask per item:

  • Do I ever look at raw values older than a week? If not, 7 to 14 days of history is enough.
  • Do I need trends for a year of capacity planning? Probably yes for CPU, disk and traffic, no for a status flag.
  • Is an item text or log? Those rows are large; keep them short or disable storage.

In Administration > Housekeeping you can enable "Override item history period" and "Override item trend period" to enforce maximums globally. Setting an item's history to 0 means "do not store raw history" while still evaluating triggers on the current value (triggers need history, so setting zero breaks functions such as avg()).

Preprocessing helps reduce storage too: "Discard unchanged" and "Discard unchanged with heartbeat" avoid storing the same value every minute for status-like items.

The housekeeper

The housekeeper process runs periodically (default every hour; HousekeepingFrequency in the server config) and deletes expired data in chunks of MaxHousekeeperDelete rows (default 5,000 per item or run). Its limits:

  • It works with DELETE statements, which are expensive on huge tables and create gaps and fragmentation.
  • If the database cannot keep up, expired rows pile up and the table keeps growing.
  • It cleans events, sessions, audit entries and old problems too, each with its own period under Administration.

You can see its activity in the server log (housekeeper [deleted 4,500,000 hist/trends ...]) and run it on demand with zabbix_server -R housekeeper_execute.

Partitioning

For databases beyond a few hundred million rows, replace row deletion with partition dropping. History and trend tables are partitioned by time (daily for history, monthly for trends). Dropping an old partition is nearly instant and frees space immediately. Steps in outline:

  1. Partition history* and trends* tables by clock (MySQL range partitioning or PostgreSQL native partitions).
  2. A scheduled job (stored procedure, cron script) creates future partitions and drops expired ones.
  3. In the frontend, disable "Enable internal housekeeping" for history and trends so it stops competing.

Partition maintenance must not stop: if new partitions are not created, inserts fail once the last one is full. Monitor it as carefully as the database itself.

TimescaleDB

With PostgreSQL, the TimescaleDB extension turns history and trends into hypertables with automatic chunking, optional compression (often 80 to 95 percent smaller), and chunk dropping for retention. Zabbix detects it and offers settings under Administration > Housekeeping ("Enable compression" and the "Compress records older than" period). Compression makes old data read-only in effect for edits and slower to modify, which fits monitoring data well.

Database tuning in brief

  • MySQL/MariaDB: innodb_buffer_pool_size large (50 to 75 percent of RAM on a dedicated host), innodb_flush_log_at_trx_commit=2 for throughput if some loss on a crash is acceptable, innodb_file_per_table=1.
  • PostgreSQL: appropriate shared_buffers, checkpoint settings, and autovacuum tuned for the append-heavy tables.
  • Use the same character set and collation Zabbix documents (utf8mb4 with utf8mb4_bin for MySQL).
  • Keep the frontend's DB user separate if you can, and back up configuration tables separately from history (see backup and upgrade).
  • Add the "Zabbix server health" items for zabbix[wcache,history,pfree] and DB sync process busy percentage.

Stand-in: retention planner

Python Retention versus disk (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The numbers show that halving history retention saves much more than shortening trends: history dominates storage by an order of magnitude at this scale.

Stand-in: housekeeper catch-up

If the housekeeper deletes fewer rows per hour than arrive, the table never shrinks. This model shows the steady-state test.

Python Does the housekeeper keep up? (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When the loop shows extra days of data, switch to partition dropping instead of raising limits.

Nagios retention

Nagios Core has no history database. Retention applies to log files and state: log_rotation_method (daily or weekly), archived logs in log_archive_path, retain_state_information and state_retention_file (so state survives a restart), and max_check_result_file_age. Performance-data stores such as RRD or InfluxDB have their own retention (RRD files are fixed size by design). Because plugins return only a status and text, there is nothing like a trends table to tune.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which tables typically grow the largest?

    Every collected value becomes a history row.
  2. 2. What is the main advantage of partition dropping over the housekeeper?

    Dropping a partition frees data at once; DELETE of millions of rows is slow.
  3. 3. What do trends store?

    Trends keep long-range statistics at small size.
  4. 4. What must you do after enabling partition-based retention?

    Otherwise the housekeeper and partition jobs compete for the same job.

Summary

  • History and trends dominate the database; retention per item sets the size.
  • The housekeeper deletes with DELETE statements and may fall behind on large systems.
  • Partition history and trends or use TimescaleDB compression for big installations, and monitor the partition job.
  • Nagios retains logs and state files, not value history.

Next: Nagios Distributed Monitoring