How to make Partition Tables on MySQL Zabbix Database

Zabbix: Partitioning MySQL / MariaDB database Tables in 5 min

In this tutorial we will learn, step by step, how to partition Zabbix database (history and trends tables) on MySQL or MariaDB using partitioning script.

Zabbix gathers data from hosts and stores them in database using history and trends tables. Zabbix history keeps raw data (each value that Zabbix has collected) and trends stores consolidated hourly data that is averaged as min,avg, and max.

Zabbix’s housekeeping process is responsible for deleting old trend and history data. Removing old data from the database using SQL delete query can negatively impact database performance. Many of us have received that annoying alarm “Zabbix housekeeper processes more than 75% busy” because of that.

That problem can be easily solved with the database partitioning. Partitioning creates tables for each hour or day and drops them when they are not needed anymore. SQL DROP is way more efficient than the DELETE statement.

You can use this tutorial for any Zabbix version after 3.0 (3.2, 3.4, 4.0, 4.2, 4.4, 5.0, 5.2, 5.4, 6.0 etc).

Before we continue please make a backup of the Zabbix database, but if the installation is new than there is no need for backup.

Step 1: Download SQL script for partitioning

Download and uncompress SQL script “zbx_db_partitiong.sql” on your database server (use wget or curl tool):

curl -O http://bestmonitoringtools.com/dl/zbx_db_partitiong.tar.gz
tar -zxvf zbx_db_partitiong.tar.gz

Script “zbx_db_partitiong.sql” is configured to keep 7 days of history data and 365 days of trend data – move to step 2 if those settings are acceptable to you.

However, if you want to change days for trends or history then open file “zbx_db_partitiong.sql“, change settings as shown in the picture below, and save the file.

Picture showing how to change days for trends and history in MySQL "create procedure" step
Picture showing how to change days for trends and history in MySQL “create procedure” step

Step 2: Create partitioning procedures with the SQL script

Syntax for running script is “mysql -u ‘<db_username>’ -p'<db_password>’ <zb_database_name> < zbx_db_partitiong.sql“.

Now, run it with your Zabbix database name, username , and password to create partitioning procedures:

mysql -u 'zabbix' -p'zabbixDBpass' zabbix < zbx_db_partitiong.sql

Script will create MySQL partitioning procedures very quickly on the new Zabbix installation, but on large databases, this may last for hours.

Step 3: Run partitioning procedures automatically

We’ve created partitioning procedures, but they don’t do anything until we run them!

This step is the most important because the partitions must be deleted and created regularly (every day) using partitioning procedures!

Don’t worry, you don’t have to do that manually. We can use two tools for such tasks: MySQL event scheduler or Crontab – choose whatever you prefer.

Be careful when configuring MySQL event scheduler or Crontab. Zabbix will stop collecting data if you misconfigure them! You will notice that by the empty graphs and the error “[Z3005] query failed: [1526] Table has no partition for value ..” in the Zabbix log file.

By default, the MySQL event scheduler is disabled. You need to enable it by setting “event_scheduler=ON” in the MySQL configuration file just after the “[mysqld]” line.

[mysqld] 
event_scheduler = ON

Don’t know where that file is located? If you used my tutorial for installing and optimizing Zabbix, then the MySQL configuration file (10_my_tweaks.cnf) should be located at “/etc/mysql/mariadb.conf.d/” or “/etc/my.cnf.d/“, otherwise try to search for it with the command:

sudo grep --include=*.cnf -irl / -e "\[mysqld\]"

Once you have made the changes restart your MySQL server for the setting to take effect!

sudo systemctl restart mysql

Nice! MySQL event scheduler should be enabled, let’s check that with the command:

[email protected]:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "SHOW VARIABLES LIKE 'event_scheduler';"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+

Now we can create an event that will run procedure “partition_maintenance_all” every 12 hours.

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all('zabbix');"

After 12 hours, check to see if the event has been executed successfully using the command below.

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "SELECT * FROM INFORMATION_SCHEMA.events\G"

EVENT_CATALOG: def
                   ...
CREATED: 2020-10-24 11:01:07
LAST_ALTERED: 2020-10-24 11:01:07
LAST_EXECUTED: 2020-10-24 11:43:07
                   ...

Option 2: Manage partitions automatically using Crontab

Crontab is a good alternative if you are unable to use the MySQL event scheduler. Open crontab file with the command “sudo crontab -e” and add a job for partitioning Zabbix MySQL database (every day at 03:30 AM) by adding this line anywhere in the file:

30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

Save and close file.

Cron will execute patitioning every day (drop old tables and create new ones) and log everything in file “/tmp/CronDBpartitiong.log“.

However, if you are impatient then run command immediately from the terminal:

[email protected]:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');"
+-----------------------------------------------------------+
| msg |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201910150000,1571180400) |
+-----------------------------------------------------------+
+-----------------------------------------------------------+
 ...etc.

and check the partitioning status afterward:

[email protected]:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "show create table history\G"
Table: history
Create Table: CREATE TABLE history (
itemid bigint(20) unsigned NOT NULL,
clock int(11) NOT NULL DEFAULT '0',
value double(16,4) NOT NULL DEFAULT '0.0000',
ns int(11) NOT NULL DEFAULT '0',
KEY history_1 (itemid,clock)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p201910140000 VALUES LESS THAN (1571094000) ENGINE = InnoDB,
PARTITION p201910150000 VALUES LESS THAN (1571180400) ENGINE = InnoDB,
PARTITION p201910160000 VALUES LESS THAN (1571266800) ENGINE = InnoDB) */

As you can see in the outputs, we have created 3 partitions for the history table.

Step 4: Configure Housekeeping on Zabbix frontend

Configure housekeeping on Zabbix frontend as shown in the picture below.

Picture showing how to configure housekeeping on Zabbix frontend
Picture showing how to configure housekeeping on Zabbix frontend

If the picture is not self-explanatory, here are the steps for configuring housekeeping on Zabbix frontend:

  • navigate to “Housekeeping” section: “Administration” → “General” → “Housekeeping“;
  • remove the checkmark from “Enable internal housekeeping” under History and Trends section;
  • put a checkmark on “Override item trend period” under History and Trends section;
  • define days for “data storage period” for trends and history under the History and Trends section (must be the same as the days configured in database partitioning – that should be 7 days for history and 365 days for trends if you haven’t changed the default settings in the script);
  • click the “Update” button.

And you are done! Keep in mind that the partitioning will delete history and trend tables according to what you have configured in the partitioning procedure. For example, if you have configured to keep 7 days of history, partitioning will start deleting history on the 8th day. After that, it will delete one history table every day so that the database always has 7 days of the history data. The same goes for the trend data, if you configured to keep 365 days of the trend data, only after 365 days it will start deleting old trend tables.

CONGRATULATIONS!
You have successfully partitioned MySQL tables on the Zabbix database!
No need to change anything else as other steps are optional.

CONTINUE TO LEARN MORE:
How to change partitioning settings
Read more about partitioning procedures that are used in the script.

Sometimes it may happen that you initially set too many days for history and trends for the Zabbix database, so the disk space fills up too quickly. Or the opposite happens, you didn’t configure enough days for history or trends. What to do then?

You don’t need to run the script again, just create a new procedure that you will run instead of the old one.

a) Create a new partitioning procedure

Connect to the MySQL/MariaDB server:

mysql -u 'zabbix' -p'zabbixDBpass' zabbix

Create a new procedure but change the number of days for trends and history according to your needs, I will set 30 days for history and 400 days for trends:

DELIMITER $$
CREATE PROCEDURE partition_maintenance_all_30and400(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 30, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 400, 24, 3);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 400, 24, 3);
END$$
DELIMITER ;

b) Update MySQL event scheduler or Crontab

We have created the partitioning procedure in the previous step, but it is not active yet! Now we must replace the old procedure with the new one that will delete and add partitions regularly. Choose one of the two options below depending on what you have configured on your Zabbix instance.

Option 1: Update MySQL event scheduler

If you created the event scheduler following this tutorial, then use this command to replace the old procedure with the new one.

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "ALTER EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all_30and400('zabbix');"

Option 2: Update Crontab

For those using Crontab, open crontab file with the command “sudo crontab -e“, comment out the the old procedure job, and add a new one

# old procedure, still exists in the database so it can be used if needed
# 30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all_30and400('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

Save your changes and exit the Crontab.

Step 6: Info about Zabbix partitioning script

Zabbix partitioning SQL script that is used in this guide contains these partitioning procedures:

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 @[email protected]__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 ;


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

Need more information ? Watch this video about MySQL database partitioning for Zabbix.


Thank you for reading.

57 thoughts on “Zabbix: Partitioning MySQL / MariaDB database Tables in 5 min”

  1. thanks for the great post!
    This is exactly what I was looking for, and I’m already looking forward to improvement.

    Best Regards,

  2. Hi!

    Thanks for your tutorial!
    But I have a problem.
    When the partitioning arrives to my history_uint and trends_uint tables, I have the problem “Table has no partition for value XXXXXXXX”.

    I am on MariaDB 10.14.2 and Zabbix 5.6.

    Thank you!

    1. Aldin Osmanagic

      Hi,
      I have explained that error in the section “Step 3: Run partitioning procedures automatically”
      Regards

  3. Sébastien RIVIERE

    Hi,
    I’ve tried your solution with store proc. Currently, I’m facing an issue when I want to launch the procedure. There is a message :
    ERROR 1503 (HY000) at line 1: A PRIMARY KEY must include all columns in the table’s partitioning function
    Indeed, all tables have a column ID. Maybe you knew how to proceed with this and modify the script if needed.
    Thanks for your help.
    Sébastien

  4. The servers stops collecting data after the MySQL event scheduler are runned. After restarting the server it works again.

    Zabbix 5.0.9
    MariaDB 10.5.9
    DB size ~50GB

    When importing the SQL script it it was done in a second. You mentioned that it could take hours on an old installation, do you think there was a problem there?

      1. Thanks,

        when running “CALL partition_maintenance_all(‘zabbix’);”, all shows “partitions deleted” as N/A, ie:
        +—————-+——————–+
        | table | partitions_deleted |
        +—————-+——————–+
        | zabbix.history | N/A |
        +—————-+——————–+

        “show create table history\G”:

        Table: history
        Create Table: CREATE TABLE `history` (
        `itemid` bigint(20) unsigned NOT NULL,
        `clock` int(11) NOT NULL DEFAULT 0,
        `value` double(16,4) NOT NULL DEFAULT 0.0000,
        `ns` int(11) NOT NULL DEFAULT 0,
        KEY `history_1` (`itemid`,`clock`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        PARTITION BY RANGE (`clock`)
        (PARTITION `p202103180000` VALUES LESS THAN (1616108400) ENGINE = InnoDB,
        PARTITION `p202103190000` VALUES LESS THAN (1616194800) ENGINE = InnoDB,
        PARTITION `p202103200000` VALUES LESS THAN (1616281200) ENGINE = InnoDB,
        PARTITION `p202103210000` VALUES LESS THAN (1616367600) ENGINE = InnoDB,
        PARTITION `p202103220000` VALUES LESS THAN (1616454000) ENGINE = InnoDB)

        1. Aldin Osmanagic

          Is your Zabbix working now? What files do you have in DIR /var/lib/mysql/zabbix ? Did you wait until the 8th day has passed because that is the time when the first partition drop will occur if you configure 7 days for history?

          1. It seems it was a one time glitch that Zabbix stopped collecting data. Now it has been run via the event scheduler a couple of times and all seems good.

            Thanks for your help!

  5. So I followed the instructions exact and the verification commands show everything worked, except when I tried to manually run:
    mysql -u ‘zabbix’ -p’mypassword’ zabbix -e “CALL partition_maintenance_all”
    ERROR 1305 (42000) at line 1: PROCEDURE zabbix.partition_maintenance_all does not exist

    So i tried to redo everything and I got:
    ERROR 1304 (42000) at line 2: PROCEDURE partition_create already exists

    Please help.

    1. Aldin Osmanagic

      Did you use full comand with database like this:
      mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix -e “CALL partition_maintenance_all(‘zabbix’);” ?

  6. David Cooper

    Hi there,

    thanks for the great article.
    my question if i have a mysql cluster (2 masters and 1 slave), when and where will/should the scheduled partitioning SQL script run? one master node or all three nodes?

    Thanks for your attention.
    David

    1. Aldin Osmanagic

      On the master-master cluster, you only need to configure partitioning on one MySQL instance. I m not sure if the same applies to the master-slave cluster.

      1. David Cooper

        Thanks. I wonder if a conditional event can be scheduled on both master nodes so that the event will be executed only if the node is associated with a virtual IP? Is that possible? This way a failover will to the other master node would still keep the scheduled event.

        1. Aldin Osmanagic

          The event that is created on the master is disabled by default on the slave. Not sure how that works in the master-master replication environment. In my master-master scenario, I m using crontab on both master nodes and they are running partitioning, one is running on Mon, Wed, Fri, Sun, and the second on is running on Tue, Thu, and Sat.

    1. Aldin Osmanagic

      It should work, keep in mind that you need to wait until the configured threshold for dropping the tables is breached.

      For example, if you defined 7 days for history and 365 days for trends partitioning will drop the history table for the first time on the 8th day. After that, it will drop one history table every day so that the database always has historical data for the last 7 days. The same goes for the trend data, only after 365 days partitioning will start to drop trend tables and will always keep 365 days of trend data in the database.

      1. Andres Bukid

        Hi Aldin,

        I am seeing a lot of *.ibd file in my server, as you are saying, this files will be drop at the 8th day?

        [[email protected] ~]# find . -xdev -type f -size +100M -print | xargs ls -lh | sort -k5,5 -h -r
        -rw-r—–. 1 polkitd ssh_keys 2.4G Oct 28 08:01 ./zabbix-docker/zbx_env/var/lib/mysql/zabbix/history_uint#p#p202010250000.ibd
        -rw-r—–. 1 polkitd ssh_keys 2.0G Oct 28 08:45 ./zabbix-docker/zbx_env/var/lib/mysql/zabbix/history_uint#p#p202010270000.ibd
        -rw-r—–. 1 polkitd ssh_keys 1.8G Oct 28 03:52 ./zabbix-docker/zbx_env/var/lib/mysql/zabbix/history_uint#p#p202010260000.ibd
        -rw-r—–. 1 polkitd ssh_keys 1.2G Oct 28 21:32 ./zabbix-docker/zbx_env/var/lib/mysql/zabbix/history_uint#p#p202010280000.ibd
        -rw-r—–. 1 polkitd ssh_keys 1.1G Oct 27 23:35 ./zabbix-docker/zbx_env/var/lib/mysql/binlog.000037
        -rw-r—–. 1 polkitd ssh_keys 856M Oct 26 15:38 ./zabbix-docker/zbx_env/var/lib/mysql/binlog.000030
        -rw-r—–. 1 polkitd ssh_keys 833M Oct 28 21:32 ./zabbix-docker/zbx_env/var/lib/mysql/binlog.000038
        -rw-r—–. 1 polkitd ssh_keys 340M Oct 23 14:41 ./zabbix-docker/zbx_env/var/lib/mysql/binlog.000006
        -rw-r—–. 1 polkitd ssh_keys 252M Oct 25 17:38 ./zabbix-docker/zbx_env/var/lib/mysql/binlog.000020
        -rw-r—–. 1 polkitd ssh_keys 215M Oct 26 20:55 ./zabbix-docker/zbx_env/var/lib/mysql/binlog.000036
        -rw-r—–. 1 polkitd ssh_keys 212M Oct 23 01:08 ./zabbix-docker/zbx_env/var/lib/mysql/binlog.000005
        -rw-r—–. 1 polkitd ssh_keys 152M Oct 28 08:01 ./zabbix-docker/zbx_env/var/lib/mysql/zabbix/history#p#p202010250000.ibd
        -rw-r—–. 1 polkitd ssh_keys 128M Oct 28 08:45 ./zabbix-docker/zbx_env/var/lib/mysql/zabbix/history#p#p202010270000.ibd
        -rw-r—–. 1 polkitd ssh_keys 128M Oct 23 20:09 ./zabbix-docker/zbx_env/var/lib/mysql/binlog.000017
        -rw-r—–. 1 polkitd ssh_keys 116M Oct 28 03:23 ./zabbix-docker/zbx_env/var/lib/mysql/zabbix/history#p#p202010260000.ibd

        1. Aldin Osmanagic

          Yes. Just wait and check on the 8th day. Check your graph for disk usage for the Zabbix DB server.

  7. Hi!
    If i list files in /var/lib/mysql/zabbix i see that files created yesterday and today ends with 2300.ibd
    Example:
    history_uint#P#p202010230000.ibd
    history_uint#P#p202010240000.ibd
    history_uint#P#p202010250000.ibd
    history_uint#P#p202010252300.ibd
    history_uint#P#p202010262300.ibd
    history_uint#P#p202010272300.ibd

    Will this be a problem tomorrow?

    1. Aldin Osmanagic

      I’m not sure why it sometimes sets to 23h instead of 00h but that’s normal – I checked on 3 different Zabbix instances.

  8. Hello.
    I’ve done partitioning with your script for 7 d(history) & 365 d(trends) and it works fine.
    Could you please answer: what steps should I do if I want to change amount of days for history, for example, 30 days?
    Should I just rerun the schema import with history option changed in it?

  9. @ Aldin Osmanagic

    Hello, can you plse advise what command i need to run to check if my mysql db is running fine and call partitioning is well implemented.

    Rgds
    Wizemindz

    1. Aldin Osmanagic

      Use the command from the tutorial:
      mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix -e “show create table history\G”

      Everything is working as intended if you receive “PARTITION p0000″ in the output of that command..

Leave a Comment

Your email address will not be published.