One of the cornerstones in successful Zabbix monitoring implementations is a database that performs well even under heavy load caused by saving large number of monitored values per second. Database partitioning is a feature that can be leveraged to speed up specific operations in the database. In short, partitioning enables dropping old slices of data in the database very efficiently, instead of calling separate DELETE statements for every single piece of old data during the hourly Zabbix database housekeeper runs.
When using PostgreSQL database with Zabbix server, the TimescaleDB extension does database partitioning for you automatically as an officially supported installation. If you are using MySQL or MariaDB and want to use database partitioning, you need to configure that yourself. The “de facto” source for configuring MySQL partitioning for Zabbix is this post in the Zabbix Blog:
There are two major steps in the process described in the post:
- Creating the partitions for specific database tables
- Maintaining the partitions: creating new partitions and deleting the old ones.
For the partition maintenance the post presents two options: Perl script and stored procedures in the database. Both options are publicly available as scripts. Arguably, they are probably both readable for Perl and SQL specialists respectively, but as an avid Pythonista, I wanted to create yet another option for maintaining the partitions: a Python script. At least for me it is the most readable and understandable option.
I wrote the script already two years ago, let’s go public with it eventually:
https://github.com/markkuleinio/zabbix-partitioning-python
Bluntly said:
- Set up the database partitions as explained in the Zabbix Blog linked above
- Copy the Python script and the configuration file from the GitHub repo above
- Review the Python script and decide which MySQL connection library to use (install dependencies accordingly)
- Edit the configuration file
- Run the script, see the results
- Use cron or systemd timers to run the script daily (at least)
- Monitor the results