trends_uint.ibd
history
history_unit
trends
CREATE TABLE `trends` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`num` int(11) NOT NULL DEFAULT '0',
`value_min` double(16,4) NOT NULL DEFAULT '0.0000',
`value_avg` double(16,4) NOT NULL DEFAULT '0.0000',
`value_max` double(16,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by RANGE (clock) (PARTITION p20150806 values less than (1438876800));
[oracle@oadb mysql]$ date -d 20150806 +%s
1438790400
[oracle@oadb mysql]$ date -d 20150807 +%s
1438876800
CREATE TABLE `trends` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`num` int(11) NOT NULL DEFAULT '0',
`value_min` double(16,4) NOT NULL DEFAULT '0.0000',
`value_avg` double(16,4) NOT NULL DEFAULT '0.0000',
`value_max` double(16,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by RANGE (clock) (PARTITION p20161027 values less than (1477584000));
mysql> select * from zabbix.trends;
+--------+------------+-----+-----------+-----------+-----------+
| itemid | clock | num | value_min | value_avg | value_max |
+--------+------------+-----+-----------+-----------+-----------+
| 23678 | 1477537200 | 26 | 0.0000 | 0.0005 | 0.0025 |
| 23679 | 1477537200 | 26 | 0.0000 | 0.0084 | 0.0325 |
| 23680 | 1477537200 | 26 | 0.0000 | 0.0036 | 0.0075 |
| 23682 | 1477537200 | 26 | 82.6072 | 97.7308 | 99.9917 |
| 23683 | 1477537200 | 26 | 0.0000 | 0.0023 | 0.0330 |
[oracle@oadb mysql]$ date -d@1477540800 "+%Y-%m-%d"
2016-10-27
mysql> select * from trends partition(p20161027);
+--------+------------+-----+-----------+-----------+-----------+
| itemid | clock | num | value_min | value_avg | value_max |
+--------+------------+-----+-----------+-----------+-----------+
| 23719 | 1477544400 | 17 | 0.0000 | 0.0000 | 0.0000 |
| 23720 | 1477544400 | 17 | 0.0000 | 0.0000 | 0.0000 |
| 23721 | 1477544400 | 17 | 0.0000 | 0.0000 | 0.0000 |
| 23723 | 1477544400 | 17 | 99.9667 | 99.9735 | 99.9792 |
| 23724 | 1477544400 | 17 | 0.0000 | 0.0000 | 0.0000 |
| 23725 | 1477544400 | 17 | 0.0000 | 0.0007 | 0.0042 |
| 23726 | 1477544400 | 17 | 0.0000 | 0.0000 | 0.0000 |
| 23727 | 1477544400 | 17 | 0.0000 | 0.0000 | 0.0000 |
| 23728 | 1477544400 | 17 | 0.0000 | 0.0000 | 0.0000 |
| 23729 | 1477544400 | 17 | 0.0167 | 0.0223 | 0.0250 |
| 23730 | 1477544400 | 17 | 0.0000 | 0.0034 | 0.0042 |
| 23734 | 1477544400 | 17 | 100.0000 | 100.0000 | 100.0000 |
| 23777 | 1477544400 | 17 | 0.0000 | 0.0000 | 0.0000 |
+--------+------------+-----+-----------+-----------+-----------+
13 rows in set (0.00 sec)
mysql> select * from trends partition(p20161028);
Empty set (0.00 sec)
CREATE TABLE `trends_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`num` int(11) NOT NULL DEFAULT '0',
`value_min` bigint(20) unsigned NOT NULL DEFAULT '0',
`value_avg` bigint(20) unsigned NOT NULL DEFAULT '0',
`value_max` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by RANGE (clock) (PARTITION p20161027 values less than (1477584000));
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 p20161027 VALUES LESS THAN (1477584000));
CREATE TABLE `history_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` bigint(20) DEFAULT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (clock) (PARTITION p20161027 VALUES LESS THAN (1477584000));