Enhance Zabbix's backend database with OceanBase

Introduce

This article will discuss the challenges encountered by ClassIn, an online education platform by EEO (Empower Education Online), during its use of Zabbix. It will compare the advantages of OceanBase over MySQL and demonstrate how ClassIn leverages the OceanBase database to enhance the scalability of the Zabbix backend database and significantly reduce storage costs.

Background

Zabbix, as an enterprise-level monitoring solution, can monitor a variety of components such as servers, network devices, databases, applications, and more. In enterprise-level monitoring, the number of items to be monitored is usually quite large, and each item may require monitoring hundreds of metrics. This results in a significant amount of monitoring data being generated. For instance, for a MySQL server, it is necessary to monitor metrics such as hardware status and service status, leading to nearly 300 monitoring items per node.

When EEO initially started using Zabbix, they chose MySQL to store the monitoring data. As the business grew, the number of devices and metrics to be monitored increased, resulting in substantial data growth.

Zabbix has two large tables in its backend database, history_uint and history, used to store monitoring data. Currently, the daily data increment for the history_uint table in the EEO monitoring system is approximately 33GB, and for the history table, it is about 50GB.

During off-peak periods, the daily incremental data volume of several large tables approaches 100GB. However, with limited disk capacity, disk alarms occur frequently during peak business periods. Additionally, MySQL suffers from a severe single-point data write bottleneck.

As is well known, MySQL is a traditional single-node database and cannot handle continuous data growth on a single node. Therefore, in Zabbix, several large tables need to be transformed into partitioned tables using range partitioning based on time, and the data must be periodically cleaned. The periodic cleaning is achieved by maintaining a set of scripts to remove expired data.

Regular cleaning of partitioned data requires additional maintenance work. If the scripts do not execute in a timely manner, it can easily affect the availability of Zabbix. The challenges of maintaining partitioned tables have also become increasingly apparent as the business volume has grown.

Pain Points

Based on the above, the main pain points that ClassIn encountered while using Zabbix are as follows:

1. Large Volume of Monitoring Data and High Storage Costs: The expense of storing six months' worth of monitoring data using MySQL had become unbearable.

2. Single-Point Write Bottleneck: As business volume grows, the number of monitored devices in the system increases, leading to a growth in monitoring data. MySQL quickly encounters single-point write bottlenecks.

3. Difficulty in Maintaining Partitioned Tables: Zabbix uses several large tables to store monitoring data and defines them as time-based partitioned tables to facilitate data cleaning. However, this makes them difficult to maintain.

Given these pain points, ClassIn began searching for a more suitable database to store Zabbix monitoring system data, ultimately turning their attention to OceanBase.

What is OceanBase?

OceanBase Database is a standalone distributed and integrated database that is fully compatible with MySQL. It leverages a native distributed architecture and incorporates enterprise-level features such as high availability suitable for financial-grade applications, transparent horizontal scaling, distributed transactions, multitenancy, and syntax compatibility. OceanBase Database Community Edition is an open-source database management system that not only supports connectivity with virtualization and big data applications and products, but also supports a comprehensive range of GUI-based development tools, O&M and monitoring tools, and data migration tools. Furthermore, it offers open APIs and various eco-capabilities to meet the diverse business requirements of enterprises or individuals.

For more information, Please visit the official website of OceanBase.

What Does OceanBase Bring to Zabbix?

Reduced storage costs

To address the pain point of large monitoring data volumes and high storage costs, ClassIn's DBA conducted tests in the production environment to compare storage costs before and after migrating business data from MySQL to OceanBase. The following table clearly shows that for the 12 most commonly used large tables in ClassIn's business, due to differences in table structures, each table's compression rate varies slightly. Overall, OceanBase can reduce the storage space for business data to between 1/4 to 1/5 of what MySQL requires.

Therefore, on machines with the same specifications, OceanBase's high data compression rate allows Zabbix users to store monitoring data over a longer time span.

Additionally, OceanBase has made numerous optimizations to minimize the overhead caused by encoding and decoding during data read and write processes, significantly reducing the impact on read and write performance. For more details on encoding and decoding, please refer to the OceanBase documentation.

Eliminated the scalability bottleneck

Since OceanBase is a native distributed database based on LSM-Tree, it has a natural advantage in addressing the single-point read and write bottleneck.

Firstly, OceanBase supports dynamic scaling. As the business volume continues to grow, more OBServer nodes can be added to the cluster dynamically. After new nodes are added, the data will automatically undergo load balancing operations. Load balancing not only ensures that the data volume across nodes remains balanced, but also distributes the leader replicas (responsible for writing data for each partition) to different nodes. Consequently, each node is capable of handling a portion of the incoming write traffic, ensuring efficient distribution of write operations.

Secondly, OceanBase's storage layer architecture is based on LSM-Tree. Data is first written to the MemTable in memory and then gradually flushed and merged to disk. As a result, the write performance is comparable to that of an in-memory database.

According to the results of sysbench write performance tests conducted in the production environment of ClassIn, OceanBase demonstrates a comprehensive write performance that is over three times that of MySQL.

Simplified the maintenance approach for partitioned tables

Addressing the pain point of difficult maintenance of MySQL partitioned tables, ClassIn also put OceanBase through its paces. OceanBase supports a rich variety of Online DDL operations, making the upkeep of partitioned tables exceedingly straightforward. DDL commands akin to truncate partition can be executed immediately without necessitating the use of dedicated maintenance windows to halt monitoring systems for partition table maintenance tasks.

Furthermore, OceanBase is a native distributed database, which can easily resolve MySQL's single-point storage bottleneck by adding new nodes. Combined with its automatic data load balancing capabilities, it can further extend the retention period of partitioned data.

In contrast, when MySQL clears partitioned data, it not only locks tables but also generates significant disk I/O. Therefore, MySQL makes it difficult to provide normal monitoring services during the time window for cleaning up expired Zabbix data and also requires DBAs to perform additional script maintenance work.

In addition to these advantages, OceanBase also provides users with the tool ODC (OceanBase Developer Center). Users can directly use this tool to set partition management strategies, automatically completing operations such as creating and deleting partitions, without needing to manually maintain scripts.

For example, you can use ODC to retain the most recent 200 partitions, which means periodically deleting any partitions beyond the latest 200.

Detailed usage of ODC can be found in the OceanBase documentation.

What Else?

After the DBAs at ClassIn chose to replace MySQL with OceanBase for storing monitoring data in their Zabbix monitoring system, they stated: "Based on our current usage, OceanBase has been a pleasant surprise. The use of the Paxos protocol in OceanBase ensures strong consistency between the primary and replica copies, solving the long-standing issues of high availability and master-slave latency that we faced with MySQL. The LSM-Tree storage architecture prevents disk I/O alerts during peak periods of frequent data writes and updates. Additionally, the advanced compression features have significantly reduced storage costs, and partitioned tables are now easier to manage."

Since then, the core businesses of ClassIn have also gradually migrated to the OceanBase database. The following diagram was summarized by ClassIn's DBAs and shows the reasons why they chose OceanBase as the database for their core businesses:

Zabbix and OceanBase have completed product compatibility authentication

In February 2024, after a series of compatibility tests, Zabbix and OceanBase successfully completed their product compatibility authentication.

The compatibility authorization between Zabbix and OceanBase shows that Zabbix works well with OceanBase as the backend database. OceanBase is not only fully compatible with Zabbix but also ensures stable and efficient operation within the Zabbix system, outperforming MySQL in all aspects.

In Conclusion

As the data volume in Zabbix's backend database continues to grow, the combination of OceanBase database and Zabbix can effectively address common challenges faced by users, such as high scalability, high availability, and storage costs. Therefore, we hope that Zabbix users can achieve a better experience through OceanBase.

Furthermore, we look forward to more opportunities for collaboration with Zabbix in the future to jointly drive technological advancements.