Table partitioning on Zabbix 3.0

[scout]

Hello,

I'm trying to implement table partitioning on Zabbix 3.0
My current setup is:
- CentOS 7
- MariaDB 10.1.12

I've used this guide for table partitioning: https://www.zabbix.org/wiki/Docs/how...l_partitioning

When I'm trying to send SQL command to alter table, I'm getting error: ERROR 1526 (HY000): Table has no partition for value 1457215200

CMD output:

MariaDB [zabbix]> SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_uint`;
+---------------------------+
| FROM_UNIXTIME(MIN(clock)) |
+---------------------------+
| 2016-03-05 18:33:22 |
+---------------------------+
1 row in set (0.96 sec)

MariaDB [zabbix]> ALTER TABLE `history_uint` PARTITION BY RANGE (`clock`) (PARTITION p2016_03_05 VALUES LESS THAN (UNIX_TIMESTAMP("2016-03-06 00:00:00")) ENGINE = InnoDB);
ERROR 1526 (HY000): Table has no partition for value 1457215200

Does anyone succeed with MariaDB table partitioning?

Thank you

[Pedro.Almeida]

Actually we partitioned trends,trends_uint,history_history_uint on a weekly basis, taking into account our items' history / trend values.

We've made some perl scripting to, automatically:
- Drop empty partitions (row count = 0)
- Create next partitions ahead of time
- Optimize (export, truncate, import) partitions where reclaimed size would be above a determined threshold (currently 1GB).

We run it daily at late hours with little impact.

Bear in mind that the last partition is always big as is contains data from all items. As soon as it "rotates" the space is not freed.
If you can handle without optimizing until the partition has no rows and you can drop it, you're fine.

We've tried optimizing partitions within mariadb but it'll usually redo or lock the entire table.

Answering your question directly, here's an example of our history_uint partition:

history_uint | CREATE TABLE `history_uint` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p2016w01 VALUES LESS THAN (1452470400) ENGINE = InnoDB,
 PARTITION p2016w02 VALUES LESS THAN (1453075200) ENGINE = InnoDB,
 PARTITION p2016w03 VALUES LESS THAN (1453680000) ENGINE = InnoDB,
 PARTITION p2016w04 VALUES LESS THAN (1454284800) ENGINE = InnoDB,
 PARTITION p2016w05 VALUES LESS THAN (1454889600) ENGINE = InnoDB,
 PARTITION p2016w06 VALUES LESS THAN (1455494400) ENGINE = InnoDB,
 PARTITION p2016w07 VALUES LESS THAN (1456099200) ENGINE = InnoDB,
 PARTITION p2016w08 VALUES LESS THAN (1456704000) ENGINE = InnoDB,
 PARTITION p2016w09 VALUES LESS THAN (1457308800) ENGINE = InnoDB,
 PARTITION p2016w10 VALUES LESS THAN (1457913600) ENGINE = InnoDB,
 PARTITION p2016w11 VALUES LESS THAN (1458518400) ENGINE = InnoDB,
 PARTITION p2016w12 VALUES LESS THAN (1459123200) ENGINE = InnoDB,
 PARTITION p2016w13 VALUES LESS THAN (1459728000) ENGINE = InnoDB,
 PARTITION p2016w14 VALUES LESS THAN (1460332800) ENGINE = InnoDB)

[scout]

Hi Pedro,

Thank you for your answer.
I'll already fixed this topic with some help from Zabbix team.

Actually they pointed me that I need to create all partitions from the beginning of time and +1 partition for existing day.

[johandavid77]

Hello My Lord, we give thanks by answer, but please help me!!!

I need you to explain me base on your example,

(ex: 2016-03-05 18:33:22 )


your date 2016-03-05 ? yeh!
+1 It means that = 2016-03-06 ????

[dfisicaro]

Hi Pedro,

Thank you for your answer.
I'll already fixed this topic with some help from Zabbix team.

Actually they pointed me that I need to create all partitions from the beginning of time and +1 partition for existing day.

Thanks Pedro, your last comment helped me.