How to Speed Up Zabbix Latest Data Display by Adding Dedicated Tables and Indexes

Problem Background

Zabbix stores monitoring data in history tables (e.g., history, history_uint) that lack primary keys or indexes, prioritizing fast inserts over query speed. In massive environments this design leads to slow retrieval of the most recent data, high lock contention, and even server crashes due to memory pressure.

Optimization Goals

Make the latest‑data query as fast as possible.

Ensure that the optimization does not degrade the ingestion speed of historical tables.

Preserve the existing data schema.

Proposed Solution

Create a set of tables that mirror the structure of the original history tables (e.g., last, last_uint, last_str) and store only the most recent record for each itemid. Add a primary‑key index on itemid (and on related items.itemid and hosts.hostid if missing) so that queries can use indexed look‑ups.

When Zabbix server writes history data, insert or update the corresponding row in the new "last" table using an ON DUPLICATE KEY UPDATE clause, which replaces the previous value without creating additional rows.

Design & Implementation

1. Table Creation – Define tables with the same columns as the original history tables:

2. Add Primary‑Key Indexes – Create a primary key on itemid for each new table and ensure items.itemid and hosts.hostid are indexed.

3. Modify Zabbix Server Source – Locate the code that flushes in‑memory history to the database and insert the following logic (illustrated in the original images):

dc_add_last_dbl(phis->itemid, &(phis->ts), phis->vf, 0);

This call inserts the latest value into the new table with an

ON DUPLICATE KEY UPDATE value=VALUES(value), clock=VALUES(clock)

clause, guaranteeing only one row per itemid.

4. Update Front‑End Queries – Replace references to the original history tables in the UI SQL with the new tables (e.g., change history to last, history_uint to last_uint). The query structure remains identical because the schemas match.

Effect and Summary

After recompiling and redeploying the server, the latest‑data dashboard loads instantly, confirming that the dedicated tables and indexes eliminate the costly GROUP BY / ORDER BY on massive history tables. Moreover, historical data ingestion remains unaffected because the heavy queries no longer lock the history tables.

Key Takeaway: In high‑throughput monitoring systems, isolating the most recent data into a small, indexed table dramatically improves read latency while preserving write performance for the bulk historical store.