See www.zabbix.com for the official Zabbix site.

Docs/howto/mysql partition

From Zabbix.org
Jump to: navigation, search

MySQL Database Partitioning

To dispel some of the myths regarding Zabbix and MySQL partitioning - It is in fact supported in Zabbix 2.0, 2.2, 2.4, 3.0, and 3.2, just not for all the tables that could previously be partitioned in 1.8. MySQL does not support partitioning on tables that use foreign keys. The history and trend tables in Zabbix 2.0+ do not use foreign keys, therefore partitioning is allowed on those tables.

Please note that Zabbix 3.2 does not need the Index changes mentioned in the "Index changes" section. This is also noted in that section.

Step-by-Step

Connect to the Zabbix database

Connect to your Zabbix MySQL instance by using your favorite MySQL client. Most people are used to seeing the command line console, so we'll work with that for the purposes of this howto. Connect to it by using a command similar to this:

mysql -h <database_ip/fqdn> -u<user> -p

Once connected to the database, you should see something like this:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

From this point on, I'll have each MySQL command prefixed with "mysql>". Don't copy/paste that text. The output from the command is also provided to give you an idea of what to expect after running the command. Now switch to the zabbix database schema. This tutorial assumes that the schema name is "zabbix":

mysql> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>


Index changes (2.x, 3.0 only)

Now we begin to make changes to the database. There are a few things to be aware of at this point:

  1. Zabbix 3.2.x does NOT require these changes. If you are running Zabbix 3.2.x, skip the "Index changes" section entirely.
  2. If the zabbix database has been populated with data, these changes might take a while. Depending on the amount of data, the change could take seconds/minutes/hours/days.
  3. In some versions of MySQL an index change will make a read-lock on the entire table. I believe (although not 100% sure) that MySQL 5.6 does not suffer from this.
    1. A read-lock means that only SELECT statements will be able to run, any writes to the table (ie: INSERTS, UPDATES, DELETES) will have to wait.
    2. What this means is that no configuration changes, history data, trend data, etc. will be able to be inserted into the database while a read-lock is in effect.

You did read what I just wrote above, correct? Now that you understand the risks, let's begin making changes. The first step is to modify a couple of indexes to allow partitioning. Run the following SQL commands.

mysql> Alter table history_text drop primary key, add index (id), drop index history_text_2, add index history_text_2 (itemid, id);
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> Alter table history_log drop primary key, add index (id), drop index history_log_2, add index history_log_2 (itemid, id);
Query OK, 0 rows affected (2.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Keep this in mind

I was recently made aware of a link that discusses the performance implications of a table without primary keys (like the history* tables). Based on the information there, the index changes above may need to be tweaked at some point to include making a primary key on the history and history_uint tables (since they are the most heavily used history tables).

Stored Procedures

Alright, let's go ahead and add the necessary stored procedures to the Zabbix database. These procedures are documented in the Documentation section if you want more details about how they work. This section is going to look a little messy, but don't be intimidated. Just copy/paste each section of SQL code. Due to weirdness in how the output will look, I can't really show the "mysql>" prompts here. As long as you see "Query OK, 0 rows affected (0.00 sec)" after running each section AND make sure the "DELIMITER ;" SQL actually runs (sometimes you'll have to press ENTER), you'll be fine.

DELIMITER $$
CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
BEGIN
        /*
           SCHEMANAME = The DB schema in which to make changes
           TABLENAME = The table with partitions to potentially delete
           PARTITIONNAME = The name of the partition to create
        */
        /*
           Verify that the partition does not already exist
        */

        DECLARE RETROWS INT;
        SELECT COUNT(1) INTO RETROWS
        FROM information_schema.partitions
        WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_description >= CLOCK;

        IF RETROWS = 0 THEN
                /*
                   1. Print a message indicating that a partition was created.
                   2. Create the SQL to create the partition.
                   3. Execute the SQL from #2.
                */
                SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
                SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
                PREPARE STMT FROM @sql;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
BEGIN
        /*
           SCHEMANAME = The DB schema in which to make changes
           TABLENAME = The table with partitions to potentially delete
           DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)
        */
        DECLARE done INT DEFAULT FALSE;
        DECLARE drop_part_name VARCHAR(16);

        /*
           Get a list of all the partitions that are older than the date
           in DELETE_BELOW_PARTITION_DATE.  All partitions are prefixed with
           a "p", so use SUBSTRING TO get rid of that character.
        */
        DECLARE myCursor CURSOR FOR
                SELECT partition_name
                FROM information_schema.partitions
                WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        /*
           Create the basics for when we need to drop the partition.  Also, create
           @drop_partitions to hold a comma-delimited list of all partitions that
           should be deleted.
        */
        SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");
        SET @drop_partitions = "";

        /*
           Start looping through all the partitions that are too old.
        */
        OPEN myCursor;
        read_loop: LOOP
                FETCH myCursor INTO drop_part_name;
                IF done THEN
                        LEAVE read_loop;
                END IF;
                SET @drop_partitions = IF(@drop_partitions = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));
        END LOOP;
        IF @drop_partitions != "" THEN
                /*
                   1. Build the SQL to drop all the necessary partitions.
                   2. Run the SQL to drop the partitions.
                   3. Print out the table partitions that were deleted.
                */
                SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");
                PREPARE STMT FROM @full_sql;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;

                SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;
        ELSE
                /*
                   No partitions are being deleted, so print out "N/A" (Not applicable) to indicate
                   that no changes were made.
                */
                SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`;
        END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
BEGIN
        DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
        DECLARE PARTITION_NAME VARCHAR(16);
        DECLARE OLD_PARTITION_NAME VARCHAR(16);
        DECLARE LESS_THAN_TIMESTAMP INT;
        DECLARE CUR_TIME INT;

        CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);
        SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'));

        SET @__interval = 1;
        create_loop: LOOP
                IF @__interval > CREATE_NEXT_INTERVALS THEN
                        LEAVE create_loop;
                END IF;

                SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600);
                SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 'p%Y%m%d%H00');
                IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN
                        CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
                END IF;
                SET @__interval=@__interval+1;
                SET OLD_PARTITION_NAME = PARTITION_NAME;
        END LOOP;

        SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000');
        CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);

END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
BEGIN
        DECLARE PARTITION_NAME VARCHAR(16);
        DECLARE RETROWS INT(11);
        DECLARE FUTURE_TIMESTAMP TIMESTAMP;

        /*
         * Check if any partitions exist for the given SCHEMANAME.TABLENAME.
         */
        SELECT COUNT(1) INTO RETROWS
        FROM information_schema.partitions
        WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name IS NULL;

        /*
         * If partitions do not exist, go ahead and partition the table
         */
        IF RETROWS = 1 THEN
                /*
                 * Take the current date at 00:00:00 and add HOURLYINTERVAL to it.  This is the timestamp below which we will store values.
                 * We begin partitioning based on the beginning of a day.  This is because we don't want to generate a random partition
                 * that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
                 * end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000").
                 */
                SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00'));
                SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');

                -- Create the partitioning query
                SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(`clock`)");
                SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));");

                -- Run the partitioning query
                PREPARE STMT FROM @__PARTITION_SQL;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        END IF;
END$$
DELIMITER ;

Using the stored procedures

Phew!, that was quite the stack of SQL code. Don't worry, you don't need to understand what all the magic is behind those procedures (unless you want to). Now that all the code is in place, it's time for a description of how to use it. Here is the break-down of getting the procedures to do everything - you'd run SQL similar to this:

CALL partition_maintenance('<zabbix_db_name>', '<table_name>', <days_to_keep_data>, <hourly_interval>, <num_future_intervals_to_create>)

There are a few things to keep in mind:

  1. The procedures were written with the intent that the Zabbix DB could be named anything, so you'll always need to provide the name of the database as a parameter.
  2. The procedures make changes based on the idea of "intervals". An "interval" is a one hour time period.
  3. When calling the script, you can tell it the intervals at which you want partitions to be created. For instance, if you want daily partitioning, then your interval would be 24. If hourly partitioning is desired, then the interval would be 1.
  4. The number of future intervals to create is really based off of the interval for partitioning. For example, let's say you want 14 days worth of future partitions to be created to hold data. If your interval is 24 (for daily partitioning), then the number of intervals to create is 14. If your interval is 1 (for hourly partitioning) then the number of intervals to create is 336 (24*14).
  5. If the interval is ever changed, it will only take effect for newly created partitions. Partitions that already exist will not be changed to fit the new interval.
    1. If the interval is decreased, you will most likely get an error like this => "ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition". My suggestion is to first delete all future partitions (outside the scope of this article) and then try and change the interval.
  6. The procedure doesn't create multiple partitions for already existing data. All existing data will be in one partition with todays date. If you want to partition your current data you can create the partitions by hand. This can take a long time depending on the amount of data already in the database
  7. The procedures do not take Daylight savings time into account. This means that if you use the procedure on a day before the time changes to create a partition for a day after the time has changed, the name (and timestamp) of this partition will deviate from the normal naming schema. This may disrupt any monitoring scripts that check whether partitions are present. Once daylight savings time has passed, the partition naming will go back to normal.
  8. Apparently the num_future_intervals_to_create must equate to at least one day worth of partitions (24 hours). This will need to be fixed to allow for any number.

Before we continue with commands to run, please remember the same points I mentioned earlier when we first started making changes to the database. These changes could take a long time! It can negatively impact a currently running Zabbix instance while the changes are being made. If you forget this, don't blame me. You're doing this at your own risk.

OK, on to actually running the stored procedures. The commands shown are using parameters that most people would use, but you may want something different. Change the parameters as you see fit.

mysql> CALL partition_maintenance('zabbix', 'history', 28, 24, 14);
+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404160000,1397718000) |
+-----------------------------------------------------------+
1 row in set (0.39 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404170000,1397804400) |
+-----------------------------------------------------------+
1 row in set (0.51 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404180000,1397890800) |
+-----------------------------------------------------------+
1 row in set (1.06 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404190000,1397977200) |
+-----------------------------------------------------------+
1 row in set (1.10 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404200000,1398063600) |
+-----------------------------------------------------------+
1 row in set (1.13 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404210000,1398150000) |
+-----------------------------------------------------------+
1 row in set (1.43 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404220000,1398236400) |
+-----------------------------------------------------------+
1 row in set (1.67 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404230000,1398322800) |
+-----------------------------------------------------------+
1 row in set (1.73 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404240000,1398409200) |
+-----------------------------------------------------------+
1 row in set (1.83 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404250000,1398495600) |
+-----------------------------------------------------------+
1 row in set (1.88 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404260000,1398582000) |
+-----------------------------------------------------------+
1 row in set (1.93 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404270000,1398668400) |
+-----------------------------------------------------------+
1 row in set (1.98 sec)

+-----------------------------------------------------------+
| msg                                                       |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201404280000,1398754800) |
+-----------------------------------------------------------+
1 row in set (2.01 sec)

+----------------+--------------------+
| table          | partitions_deleted |
+----------------+--------------------+
| zabbix.history | N/A                |
+----------------+--------------------+
1 row in set (2.04 sec)

Query OK, 0 rows affected, 1 warning (2.04 sec)

mysql>

Improving procedure calls

As seen from the output, it created future partitions. It also found that based on the value of the number of days to keep parameter (in this case, 28), there were not any history partitions to delete. What you didn't see in this output is that before creating/deleting anything, the stored procedures made sure that partitioning even existed on the given table. In this case, partitioning did not exist, so it enabled partitioning first on the table and then proceeded to create/delete partitions as necessary.

Look at the command that was called again. Notice that the table name is a parameter passed to the procedure? This command will have to be run for every history/trend table. This could get old very quickly. In my particular environment, I took the liberty of creating one more stored procedure so that I only had to call one procedure to make all the changes. Here is what my extra stored procedure looks like, don't add it yet to your database.

DELIMITER $$
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
                CALL partition_maintenance(SCHEMA_NAME, 'history', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'history_log', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'history_str', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'history_text', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);
END$$
DELIMITER ;

All this procedure does is make calls to the "partition_maintenance" procedure for each history/trend table. Notice in the function that all the history tables have the same interval and intervals created, but the trend tables are slightly different. I keep 730 days of trend data vs. only 28 days of history data.

Go ahead and add the extra procedure to your environment by copying/pasting the code and making any desired modifications to it. Once that is done, you can simply run the new procedure (shown below) to make all modifications to all history/trend tables (I won't show all the output because there's a lot of it).

mysql> CALL partition_maintenance_all('zabbix');
+----------------+--------------------+
| table          | partitions_deleted |
+----------------+--------------------+
| zabbix.history | N/A                |
+----------------+--------------------+
1 row in set (0.01 sec)

....
....
....

+--------------------+--------------------+
| table              | partitions_deleted |
+--------------------+--------------------+
| zabbix.trends_uint | N/A                |
+--------------------+--------------------+
1 row in set (22.85 sec)

Query OK, 0 rows affected, 1 warning (22.85 sec)

mysql>


Housekeeper changes

There are a couple more steps that need to be done on the Zabbix Web UI on 2.2.x (or the zabbix_server.conf file in the case of Zabbix 2.0.x) to shut off housekeeping on the history/trend tables.

Zabbix 2.0.x

For those of you still stuck using the 2.0.x series of Zabbix there is no way to disable specific functionality of the housekeeper. You will have to disable the housekeeper entirely. This can be done by setting the "DisableHousekeeping" parameter in zabbix_server.conf to "1". You then need to restart the zabbix_server process. Keep in mind that with the housekeeper disabled, data in the "housekeeper" table in the database will never be removed. Also, old events, audit entries, and user sessions will never be deleted automatically.

Zabbix 2.2+

Zabbix 2.2 introduced more granular control over the housekeeper. All of the options are available in the Zabbix UI in the "Administration" -> "General" section. Make sure you select "Housekeeping" in the drop-down in the upper right. You should see a screen similar to the following:

Zabbix housekeeping config 2.2.x.png

  • Make sure that the checkboxes with the title "Enable internal housekeeping" are unchecked for both History and Trends.
  • Make sure that the checkboxes with the title "Override item <trend/history> period" are checked for both History and Trends.
  • Set the "Data storage period (in days)" box for history and trends to the amount of time you are retaining both. In the examples that were given, the correct values are 28 and 730 (as seen in the screenshot).

You can set the other options on this page to whatever you'd like for your own environment.

Don't forget to click Save!

All done

Awesome! Partitioning is now all set up in the database and life is all peachy-keen now. There is only one thing to keep in mind, and I stress that you must NOT forget this: Do NOT allow your database to run out of partitions. Remember how the examples provided create 14 days worth of extra partitions? This means that Zabbix can run for up to 14 days without any problems. On the 15th day, the database will not have anywhere to stick history/trend data, so data-loss WILL occur. You must remember to re-run these stored procedures (just the "partition_maintenance_all" one) every so often (either through cron or some other method). By doing this, partitions will always exist into which data can be inserted.

Documentation

There are four functions in the stored procedures:

  • partition_create - This will create a partition on the given table in the given schema.
  • partition_drop - This will drop partitions older than the given timestamp on the given table in the given schema.
  • partition_maintenance - This function is what is called by users. It is responsible for parsing the given parameters and then creating/dropping partitions as needed.
  • partition_verify - Checks if partitioning is enabled on the given table in the given schema. If it is not enable, it creates a single partition.

Parameters

partition_create

Procedure definition: partition_create(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
Example: CALL partition_create("zabbix", "history", "p20131216", 1387267200);

  • SCHEMANAME = The DB schema in which to make changes
  • TABLENAME = The table on which to create PARTITIONNAME
  • PARTITIONNAME = The name of the partition to create
  • CLOCK = PARTITIONNAME will be created to hold values with a "clock" column value less than this

partition_drop

Procedure definition: partition_drop(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE VARCHAR(64))
Example: CALL partition_drop("zabbix", "history", "20131216");

  • SCHEMANAME = The DB schema in which to make changes
  • TABLENAME = The table on which to delete PARTITIONNAME
  • DELETE_BELOW_PARTITION_DATE = The oldest partition date to allow. All partitions older than this date will be deleted. The format is yyyymmdd.

partition_maintenance

Procedure definition: partition_maintenance(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
Example: CALL partition_maintenance('zabbix', 'history', 28, 24, 14);

  • SCHEMA_NAME = The DB schema in which to make changes
  • TABLE_NAME = The table on which to make changes
  • KEEP_DATA_DAYS = The maximum number of days worth of partitions to keep. All partitions older than this number of days will be deleted.
  • HOURLY_INTERVAL = The hourly interval between partitions. For example, daily partitioning would have a value of 24, hourly partitioning would have a value of 1.
  • CREATE_NEXT_INTERVALS = The number of intervals worth of partitions to create in advance.

partition_verify

Procedure definition: partition_verify(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
Example: CALL partition_verify("zabbix", "history");

  • SCHEMANAME = The DB schema in which to make changes
  • TABLENAME = The table on which to check for partitions
  • HOURLY_INTERVAL = The hourly interval between partitions. For example, daily partitioning would have a value of 24, hourly partitioning would have a value of 1.