1.zabbix_alter_tables.sql:After importing the data tables and base data following the official documentation, you should first execute the SQL in zabbix_alter_tables.sql within the database you named. The purpose is to partition the tables history, history_log, history_str, history_text, history_uint, trends, and trends_uint based on the values of the clock field. Each partition will have a specific value range.
The value range for each partition is calculated using UNIX_TIMESTAMP(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), '%Y-%m-%d')). This means the upper limit of each partition is the Unix timestamp of a specific day one year before the current date.
Setting the date to one year before is done to avoid affecting current data and to prepare for the upcoming test of partition deletion.
2.zabbix_pratition_tables.sql:Creation of manage_partitions Table:
This table is created to keep track of how partitions should be managed for different tables. It includes fields like table name, partitioning period (daily or monthly), how long to keep the partitions (in days or months), when the partition was last updated, and additional comments.Stored Procedures:
The script defines several stored procedures for creating and dropping partitions.create_next_partitions: This procedure iterates through the manage_partitions table and creates the next set of partitions for each table listed. It decides whether to create daily or monthly partitions based on the period column of the manage_partitions table.create_partition_by_day and create_partition_by_month: These procedures are called by create_next_partitions and are responsible for creating individual partitions on a daily or monthly basis.drop_partitions: This procedure checks for old partitions that need to be dropped based on the keep_history value from the manage_partitions table.drop_old_partition: Called by drop_partitions, this procedure actually drops the specified partition from a table.Creation of a Scheduled Event (e_part_manage):
This event is scheduled to run daily at 4:00 AM. It calls the drop_partitions and create_next_partitions procedures for the zabbix_server schema, ensuring that partitions are regularly managed according to the rules defined in manage_partitions.Insert Statements for manage_partitions:
These statements insert initial configuration data into the manage_partitions table for various tables like history, history_uint, history_str, history_text, history_log, trends, and trends_uint.Each entry specifies how long to keep partitions for that table (e.g., 60 days for daily partitions, 12 months for monthly partitions).In summary, this script automates the process of partition management for a Zabbix server database. It sets up a system to regularly create and drop partitions for specified tables based on defined criteria, helping to manage data growth and optimize database performance. This approach is particularly useful in scenarios where tables grow large over time, such as in monitoring or logging systems like Zabbix.
评论