zabbixパーティションテーブルの作成

8975 ワード

<pre name="code" class="sql">      :
oadb01:/home/oracle/mysql> cat a1.sh 
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
date=`echo $1 | tr -d '-'`
date1=`echo $2 | tr -d '-'`
date2=`echo $1`
date_end=`get_date $2 +1 | tr -d '-'`
while :
    do
      date3=`get_date $date +1`
     var=`date -d "$date3" +%s`
     echo "alter table history_log add partition (partition p$date values less than ($var));"
      date2=`get_date $date +1`
      date=`echo $date2 | tr -d '-'`
      #echo $date3
if [ "$date" = "$date_end" ]
       then
        exit
fi
done

oadb01:/home/oracle/sbin> cat get_date 
export NLS_LANG="american_america.ZHS16GBK"
echo " set colsep |;
    set echo off;
    set feedback off;
    set heading off;
    set pagesize 0;
    set termout off;
    set trimout on;
    set trimspool on;
    set linesize 3000;
    spool ./date.txt1;
    select   to_char((to_date('$1','YYYYMMDD')$2),'YYYYMMDD')  from  dual  where  rownum=1;
    "  | sqlplus query/query >/dev/null
    if [ -f ./date.txt1 ]
    then
        cat ./date.txt1 | grep -v "^SQL>" | tr -d ' ' >./date.txt
        rm -f ./date.txt1
    else
        exit
    fi
    date=`cat ./date.txt`
    rm -f ./date.txt
echo $date
-------------------------------------------------------------------------------------------------------



CREATE TABLE `history_log` (  
  `id` bigint(20) unsigned NOT NULL,  
  `itemid` bigint(20) unsigned NOT NULL,  
  `clock` int(11) NOT NULL DEFAULT '0',  
  `timestamp` int(11) NOT NULL DEFAULT '0',  
  `source` varchar(64) NOT NULL DEFAULT '',  
  `severity` int(11) NOT NULL DEFAULT '0',  
  `value` text NOT NULL,  
  `logeventid` int(11) NOT NULL DEFAULT '0',  
  `ns` int(11) NOT NULL DEFAULT '0',  
  PRIMARY KEY (`id`,`clock`),  
  UNIQUE KEY `history_log_2` (`itemid`,`id`,`clock`),  
  KEY `history_log_1` (`itemid`,`clock`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
partition by RANGE (clock)  (PARTITION  p20150806 values less than (1438876800));

back_office:/root# date -d @1438876800 "+%Y%m%d"
20150807

range         


mysql> show create table trends\G;
*************************** 1. row ***************************
       Table: 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));
1 row in set (0.00 sec)

oadb01:/home/oracle/mysql> cat 1
alter table trends add partition (partition p20150807 values less than (1438963200));
alter table trends add partition (partition p20150808 values less than (1439049600));
alter table trends add partition (partition p20150809 values less than (1439136000));
alter table trends add partition (partition p20150810 values less than (1439222400));
alter table trends add partition (partition p20150811 values less than (1439308800));
alter table trends add partition (partition p20150812 values less than (1439395200));
alter table trends add partition (partition p20150813 values less than (1439481600));
alter table trends add partition (partition p20150814 values less than (1439568000));

    :

mysql> select * from trends partition(p20150806);
+--------+------------+-----+-----------+-----------+-----------+
| itemid | clock      | num | value_min | value_avg | value_max |
+--------+------------+-----+-----------+-----------+-----------+
|  23678 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23679 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23680 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23682 | 1438851600 |   4 |   99.8833 |   99.9000 |   99.9167 |
|  23683 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23684 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23685 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23686 | 1438851600 |   4 |    0.0000 |    0.0083 |    0.0167 |
|  23687 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23688 | 1438851600 |   4 |    0.0500 |    0.0709 |    0.0834 |
|  23689 | 1438851600 |   4 |    0.0167 |    0.0292 |    0.0333 |
|  23693 | 1438851600 |   4 |  100.0000 |  100.0000 |  100.0000 |
|  23703 | 1438851600 |   3 |   84.7493 |   84.7493 |   84.7493 |
|  23704 | 1438851600 |   3 |   99.9258 |   99.9258 |   99.9258 |
|  23707 | 1438851600 |   3 |   63.9416 |   63.9417 |   63.9418 |
|  23708 | 1438851600 |   3 |   84.2241 |   84.2241 |   84.2241 |
|  23731 | 1438851600 |   4 |    0.3750 |    0.4088 |    0.4400 |
|  23732 | 1438851600 |   4 |    0.0700 |    0.1788 |    0.3200 |
|  23733 | 1438851600 |   4 |    0.4900 |    0.6525 |    0.8200 |
|  23735 | 1438851600 |   4 |   89.1683 |   93.7320 |   95.8974 |
|  23736 | 1438851600 |   4 |    0.0000 |    0.0106 |    0.0172 |
|  23737 | 1438851600 |   4 |    0.5099 |    2.3167 |    5.0391 |
|  23738 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23739 | 1438851600 |   4 |    0.0585 |    0.1184 |    0.2146 |
|  23740 | 1438851600 |   4 |    0.0000 |    0.0000 |    0.0000 |
|  23741 | 1438851600 |   4 |    0.5680 |    1.2724 |    3.1757 |
|  23742 | 1438851600 |   4 |    1.8628 |    2.5440 |    2.9594 |
|  23746 | 1438851600 |   4 |   79.8505 |   80.0372 |   80.1767 |
|  23756 | 1438851600 |   4 |   85.4452 |   85.4453 |   85.4453 |
|  23757 | 1438851600 |   4 |   99.9258 |   99.9258 |   99.9258 |
|  23760 | 1438851600 |   4 |   34.0385 |   34.0396 |   34.0406 |
|  23761 | 1438851600 |   4 |   84.0508 |   84.0508 |   84.0508 |
+--------+------------+-----+-----------+-----------+-----------+
32 rows in set (0.00 sec)

mysql> select * from trends partition(p20150807);
Empty set (0.00 sec)

mysql>  select * from trends partition(p20150808);
Empty set (0.00 sec)
back_office:/root# date -d @1438851600 "+%Y%m%d"
20150806


mysql> desc history;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | double(16,4)        | NO   |     | 0.0000  |       |
| ns     | int(11)             | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table history\G;
*************************** 1. row ***************************
       Table: history
 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  p20150806 values less than (1438876800));

mysql> select * from history partition(p20150807);  ---  8    

mysql>  select * from history partition(p20150806);  ---  7    
mysql> show create table history\G;
*************************** 1. row ***************************
       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
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p20150806 VALUES LESS THAN (1438876800) ENGINE = InnoDB,
 PARTITION p20150807 VALUES LESS THAN (1438963200) ENGINE = InnoDB,
 
 [root@master ~]# date -d@1438876800 "+%Y-%m-%d"
2015-08-07
 
+--------+------------+---------+-----------+
| itemid | clock      | value   | ns        |
+--------+------------+---------+-----------+
|  23756 | 1438857116 | 85.4451 | 512820947 |
|  23757 | 1438857117 | 99.9258 | 513778618 |
|  23760 | 1438857120 | 34.0288 | 517055692 |
|  23761 | 1438857121 | 84.0508 | 518021867 |
|  23731 | 1438857151 |  0.2850 | 530365388 |
|  23732 | 1438857152 |  0.0000 | 532367654 |
|  23733 | 1438857153 |  0.1500 | 533290091 |
|  23735 | 1438857155 | 98.8063 | 535482934 |
|  23736 | 1438857156 |  0.0083 | 536551204 |
|  23737 | 1438857157 |  0.4757 | 537428882 |
|  23738 | 1438857158 |  0.0000 | 537838331 |
|  23739 | 1438857159 |  0.3003 | 538566093 |

[root@master ~]# date -d@1438857116 "+%Y-%m-%d"
2015-08-06