Now, considering that you have a Red Hat server, the procedure to install MySQL from the RPM repository is quite easy:
Now, you need to set up the MySQL service to start automatically when the system boots:
This will also help you to remove the test databases and anonymous user data that was created by default. This is strongly recommended for production servers.
Now, it's time to create the Zabbix database. For this, we can use the following commands:
Now that we are aware of how big our database will be, it's easy to imagine that housekeeping will be a heavy task and the time, CPU, and resource consumed by this one will grow together with the database size.
Housekeeping is in charge to remove the outdated metrics from the database and the information deleted by a user, and as we've seen the history, trends, and events tables are, after some time, huge tables. This explains why the process is so heavy to manage.
The only way we can improve performances once we have reached this volume of data is by using partitioning and disabling the housekeeper altogether.
Partitioning the history and trend tables will provide us with many major benefits:
- All history data in a table for a particular defined window time are self-contained in its own partition. This allows you to easily delete old data without impacting the database performance.
- When you use MySQL with InnoDB, and if you delete data contained in a table, the space is not released. The space freed is marked as free, but the disk space consumed will not change. When you use partition, and if you drop a partition, the space is immediately freed.
- Query performance can be improved dramatically in some situations, in particular, when there is heavy access to the table's rows in a single partition.
- When a query updates a huge amount of data or needs access to a large percentage of the partition, the sequential scan is often more efficient than the index usage with a random access or scattered reads against this index.
Unfortunately, Zabbix is not able to manage the partitions. So, we need to disable housekeeping, and use an external process to accomplish housekeeping.
What we need to have is a stored procedure that does all the work for us.
The following is the stored procedure:
Until here, we have declared the variable we need after. Now, on the next line, we will call the stored procedure responsible to check whether a partition is already present and if not, we will create them:
Now that we have calculated all the parameters needed by the create_partition
procedure, we can run it. This stored procedure will create the new partition on the defined schema:
The section that follows is responsible to remove the older partitions, using the OLDER_TAN_PARTITION_DATE
procedure, which we have calculated on the lines before:
This stored procedure will be the core of our housekeeping. It will be called with the following syntax:
The procedure works based on 1 hour intervals. Next, if you want to partition on a daily basis, the interval will be 24 hours. Instead, if you want 1 hour partitioning, the interval will be 1.
You need to specify the number of intervals that you want created in advance. For example, if you want 2 weeks interval of future partitions, use 14. If your interval is 1 (for hourly partitioning), then the number of intervals to create is 336 (24*14).
This stored procedure uses some other stores procedures:
partition_create
: This creates the partition for the specified tablepartition_verify
: This checks whether the partition is enabled on a table, if not, then create a single partitionpartition_drop
: This drops partitions older than a timestamp
For all the details about these stored procedures, see Appendix A, Partitioning the Zabbix Database.
Once you've created all the required stored procedures, you need to change two indexes to enable them in order to be ready for a partitioned table:
Once this is done, you need to schedule the partition_maintenance_all
stored procedure with a cron job. For more details about the partition_maintenance_all
procedure, please check the instructions contained in Appendix A, Partitioning the Zabbix Database. The cron job needs to execute the following command:
Once this has been set, you need to bear in mind to disable the housekeeping for history and trends. Verify that the Override item <trend/history> period Zabbix configuration is checked for both history and trends. Here, you need to set the Data storage period (in days) box for history and trends to the value you've defined in your procedure, our example in Appendix A, Partitioning the Zabbix Database is of 28 and 730.