zabbix 四张大表分区

来源：互联网发布：圈圈助手网络连接失败编辑：程序博客网时间：2024/06/09 14:08
trends_uint.ibdhistoryhistory_unittrends 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=utf8partition by RANGE (clock)  (PARTITION  p20150806 values less than (1438876800));[oracle@oadb mysql]$ date -d 20150806 +%s1438790400[oracle@oadb mysql]$ date -d 20150807 +%s1438876800 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=utf8partition 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-27mysql> 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=utf8partition 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=utf8PARTITION 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=utf8PARTITION BY RANGE (clock) (PARTITION p20161027 VALUES LESS THAN (1477584000));
0 0