Partitioning a
Zabbix MySQL
database
And why you should…
oicts.com/about
Who am I?
Nathan Liefting
IT Consultant / Zabbix Trainer
@larcorba / larcorba.com
19 March 2021 oicts.com/about 1
Our presentation today
1 – Why would you partition the database
2 – How to partition the database
3 – Manage the partitions with Perl
4 – Manage the partitions with Stored Procedures
Let’s wrap things up
More content like this
19 March 2021 oicts.com/about 2
1 – Why would you partition the database
19 March 2021 oicts.com/about 3
Housekeeper can’t keep up
• Housekeeper internal process more than 75% busy all the time
• Looks up old data in the database and deletes this entry by entry
• Impossible to tune the housekeeper process at one point
19 March 2021 oicts.com/about 4
Partition before you Zabbix
• When building a good Zabbix setup, design is where it all starts
• Partitioning is part of your design
• Expect a server to grow? PARTITION
• Only a proof of concept that might go into production? PARTITION
• Partitioning a big database? It’s not fun
19 March 2021 oicts.com/about 5
2 – How to partition the database
19 March 2021 oicts.com/about 6
Let’s prepare to partition
• Partitioning can take a long time
• Use the Linux screen command!
• Partition History tables -> by day
• Partition Trends tables -> by month
19 March 2021 oicts.com/about 7
Prepare your partitioning commands - time
• Get your current timestamp
• Command output example: 2020-12-19 08:09:17
• Today’s date: 2021-02-19 09:00:00
19 March 2021 oicts.com/about 8
Prepare your partitioning commands - history
• We’ll prepare the same for the history, history_str, history_text, history_log tables
19 March 2021 oicts.com/about 9
Prepare your partitioning commands - trends
• We’ll prepare the same for the trends table
19 March 2021 oicts.com/about 10
Now it’s time to partition
• Again do not forget to open a screen
• Login to MySQL
• Execute the prepared command table by table
• Have patience!
19 March 2021 oicts.com/about 11
3 – Manage the partitions with Perl
19 March 2021 oicts.com/about 12
Getting the script
• Download the script here:
https://github.com/OpensourceICTSolutions/zabbix-mysql-partitioning-perl
• Save it to:
• Make it executable:
19 March 2021 oicts.com/about 13
Edit the script
• Make sure to open the script with your editor:
• Make sure to setup your MySQL login details:
• Then add how long you want to keep data:
19 March 2021 oicts.com/about 14
Edit the script
• Add the correct timezone:
• You’re done, unless you use MySQL 5.5 (or earlier). Comment the following:
19 March 2021 oicts.com/about 15
Edit the script
• And uncomment:
19 March 2021 oicts.com/about 16
Edit the script
• Last, but not least install the Perl script dependencies:
• Then add a Cronjob to execute the script with:
• Add the following line:
19 March 2021 oicts.com/about 17
Execute / monitor the script
• We can execute the script with:
• To check if the script ran use:
Congratulations, your database is partitioned and will remain so.
19 March 2021 oicts.com/about 18
4 – Manage the partitions with Stored
Procedures
19 March 2021 oicts.com/about 19
Stored procedures, don’t use them (for this)!
Cons Pros
• Hard to troubleshoot • Some organisations don’t allow external scripts
• Messy to setup • We included a guide in the blog post
• ‘Hidden’ inside your database
• Requires additional knowledge
19 March 2021 oicts.com/about 20
Let’s wrap things up
• Preferably partition before you put Zabbix into production
• Prepare your partition commands
• Partition the database, use screen
• Setup your partition management (Perl Script / Stored Procedures)
• Keep an eye on your partitions for a few days
• Enjoy!
For a complete text guide see:
https://blog.zabbix.com/partitioning-a-zabbix-mysql-database-with-perl-or-stored-procedures/13531/
19 March 2021 oicts.com/about 21
More content like this
19 March 2021 22
Questions?
19 March 2021 23
