zabbix history分區 or TokuDB利用event為zabbix資料表定期添加和删除分區

實施過程

<a href="http://lihuipeng.blog.51cto.com/3064864/1561221" target="_blank">http://lihuipeng.blog.51cto.com/3064864/1561221</a>

邏輯

一天建立一個分區，分區比目前時間少7天的删除掉，等于說保留一個禮拜的資料

<code>zabbix 分區</code>

<code>zabbix_db_name：庫名</code>

<code>table_name：表名</code>

<code>days_to_keep_data：儲存多少天的資料</code>

<code>hourly_interval：每隔多久生成一個分區</code>

<code>num_future_intervals_to_create：本次一共生成多少個分區</code>

<code>這個例子就是history表最多儲存</code><code>7</code><code>天的資料，每隔</code><code>24</code><code>小時生成一個分區，這次一共生成</code><code>7</code><code>個分區</code>

<code>history</code>

<code>history_1   itemid, clock  Normal  </code><code>0</code>   <code>A   </code><code>0</code>   <code>15085</code>   <code>0</code>       <code>0</code>          

<code>Alter table history_text drop primary key, add index (</code><code>id</code><code>), drop index history_text_2, add index history_text_2 (itemid, </code><code>id</code><code>)</code>

<code>Alter table history_log drop primary key, add index (</code><code>id</code><code>), drop index history_log_2, add index history_log_2 (itemid, </code><code>id</code><code>);</code>

<code>ERROR </code><code>1503</code> <code>(HY000) at line </code><code>1</code><code>: A PRIMARY KEY must include </code><code>all</code> <code>columns </code><code>in</code> <code>the table's partitioning function</code>

<code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history'</code><code>, </code><code>7</code><code>, </code><code>24</code><code>, </code><code>7</code><code>);</code>

<code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history_log'</code><code>, </code><code>30</code><code>, </code><code>24</code><code>, </code><code>7</code><code>);</code>

<code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history_str'</code><code>, </code><code>30</code><code>, </code><code>24</code><code>, </code><code>7</code><code>);</code>

<code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history_text'</code><code>, </code><code>30</code><code>, </code><code>24</code><code>, </code><code>7</code><code>);</code>

<code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history_uint'</code><code>, </code><code>15</code><code>, </code><code>24</code><code>, </code><code>7</code><code>);</code>

<code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'trends'</code><code>, </code><code>60</code><code>, </code><code>24</code><code>, </code><code>7</code><code>);</code>

<code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'trends_uint'</code><code>, </code><code>60</code><code>, </code><code>24</code><code>, </code><code>7</code><code>);</code>

<code>DELIMITER $$</code>

<code>CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(</code><code>32</code><code>))</code>

<code>BEGIN</code>

<code>                </code><code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history'</code><code>, </code><code>28</code><code>, </code><code>24</code><code>, </code><code>14</code><code>);</code>

<code>                </code><code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history_log'</code><code>, </code><code>28</code><code>, </code><code>24</code><code>, </code><code>14</code><code>);</code>

<code>                </code><code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history_str'</code><code>, </code><code>28</code><code>, </code><code>24</code><code>, </code><code>14</code><code>);</code>

<code>                </code><code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history_text'</code><code>, </code><code>28</code><code>, </code><code>24</code><code>, </code><code>14</code><code>);</code>

<code>                </code><code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history_uint'</code><code>, </code><code>28</code><code>, </code><code>24</code><code>, </code><code>14</code><code>);</code>

<code>                </code><code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'trends'</code><code>, </code><code>730</code><code>, </code><code>24</code><code>, </code><code>14</code><code>);</code>

<code>                </code><code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'trends_uint'</code><code>, </code><code>730</code><code>, </code><code>24</code><code>, </code><code>14</code><code>);</code>

<code>END$$</code>

<code>DELIMITER ;</code>

partition_create

<code>        </code> 

<code> </code> 

<code>        </code><code>DECLARE RETROWS </code><code>INT</code><code>;</code>

<code>        </code><code>SELECT COUNT(</code><code>1</code><code>) INTO RETROWS</code>

<code>        </code><code>FROM information_schema.partitions</code>

<code>        </code><code>WHERE table_schema </code><code>=</code> <code>SCHEMANAME AND TABLE_NAME </code><code>=</code> <code>TABLENAME AND partition_name </code><code>=</code> <code>PARTITIONNAME;</code>

<code>        </code><code>IF RETROWS </code><code>=</code> <code>0</code> <code>THEN</code>

<code>                </code> 

<code>                </code><code>SELECT CONCAT( </code><code>"partition_create("</code><code>, SCHEMANAME, </code><code>","</code><code>, TABLENAME, </code><code>","</code><code>, PARTITIONNAME, </code><code>","</code><code>, CLOCK, </code><code>")"</code> <code>) AS msg;</code>

<code>                </code><code>SET</code> <code>@SQL </code><code>=</code> <code>CONCAT( </code><code>'ALTER TABLE '</code><code>, SCHEMANAME, </code><code>'.'</code><code>, TABLENAME, </code><code>' ADD PARTITION (PARTITION '</code><code>, PARTITIONNAME, </code><code>' VALUES LESS THAN ('</code><code>, CLOCK, </code><code>'));'</code> <code>);</code>

<code>                </code><code>PREPARE STMT FROM @SQL;</code>

<code>                </code><code>EXECUTE STMT;</code>

<code>                </code><code>DEALLOCATE PREPARE STMT;</code>

<code>        </code><code>END IF;</code>

<code>END</code>

partition_drop

<code>        </code><code>DECLARE done </code><code>INT</code> <code>DEFAULT FALSE;</code>

<code>        </code><code>DECLARE drop_part_name VARCHAR(</code><code>16</code><code>);</code>

<code>        </code><code>DECLARE myCursor CURSOR FOR</code>

<code>                </code><code>SELECT partition_name</code>

<code>                </code><code>FROM information_schema.partitions</code>

<code>                </code><code>WHERE table_schema </code><code>=</code> <code>SCHEMANAME AND TABLE_NAME </code><code>=</code> <code>TABLENAME AND CAST(SUBSTRING(partition_name FROM </code><code>2</code><code>) AS UNSIGNED) &lt; DELETE_BELOW_PARTITION_DATE;</code>

<code>        </code><code>DECLARE CONTINUE HANDLER FOR NOT FOUND </code><code>SET</code> <code>done </code><code>=</code> <code>TRUE;</code>

<code>        </code><code>SET</code> <code>@alter_header </code><code>=</code> <code>CONCAT(</code><code>"ALTER TABLE "</code><code>, SCHEMANAME, </code><code>"."</code><code>, TABLENAME, </code><code>" DROP PARTITION "</code><code>);</code>

<code>        </code><code>SET</code> <code>@drop_partitions </code><code>=</code> <code>"";</code>

<code>        </code><code>OPEN</code> <code>myCursor;</code>

<code>        </code><code>read_loop: LOOP</code>

<code>                </code><code>FETCH myCursor INTO drop_part_name;</code>

<code>                </code><code>IF done THEN</code>

<code>                        </code><code>LEAVE read_loop;</code>

<code>                </code><code>END IF;</code>

<code>                </code><code>SET</code> <code>@drop_partitions </code><code>=</code> <code>IF(@drop_partitions </code><code>=</code> <code>"</code><code>", drop_part_name, CONCAT(@drop_partitions, "</code><code>,", drop_part_name));</code>

<code>        </code><code>END LOOP;</code>

<code>        </code><code>IF @drop_partitions !</code><code>=</code> <code>"" THEN</code>

<code>                </code><code>SET</code> <code>@full_sql </code><code>=</code> <code>CONCAT(@alter_header, @drop_partitions, </code><code>";"</code><code>);</code>

<code>                </code><code>PREPARE STMT FROM @full_sql;</code>

<code>                </code><code>SELECT CONCAT(SCHEMANAME, </code><code>"."</code><code>, TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`;</code>

<code>        </code><code>ELSE</code>

<code>                </code><code>SELECT CONCAT(SCHEMANAME, </code><code>"."</code><code>, TABLENAME) AS `table`, </code><code>"N/A"</code> <code>AS `partitions_deleted`;</code>

partition_maintenance

<code>        </code><code>DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(</code><code>16</code><code>);</code>

<code>        </code><code>DECLARE PARTITION_NAME VARCHAR(</code><code>16</code><code>);</code>

<code>        </code><code>DECLARE LESS_THAN_TIMESTAMP </code><code>INT</code><code>;</code>

<code>        </code><code>DECLARE CUR_TIME </code><code>INT</code><code>;</code>

<code>        </code><code>CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);</code>

<code>        </code><code>SET</code> <code>CUR_TIME </code><code>=</code> <code>UNIX_TIMESTAMP(DATE_FORMAT(NOW(), </code><code>'%Y-%m-%d 00:00:00'</code><code>));</code>

<code>        </code><code>IF DATE(NOW()) </code><code>=</code> <code>'2014-04-01'</code> <code>THEN</code>

<code>                </code><code>SET</code> <code>CUR_TIME </code><code>=</code> <code>UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL </code><code>1</code> <code>DAY), </code><code>'%Y-%m-%d 00:00:00'</code><code>));</code>

<code>        </code><code>SET</code> <code>@__interval </code><code>=</code> <code>1</code><code>;</code>

<code>        </code><code>create_loop: LOOP</code>

<code>                </code><code>IF @__interval &gt; CREATE_NEXT_INTERVALS THEN</code>

<code>                        </code><code>LEAVE create_loop;</code>

<code>                </code><code>SET</code> <code>LESS_THAN_TIMESTAMP </code><code>=</code> <code>CUR_TIME </code><code>+</code> <code>(HOURLY_INTERVAL </code><code>*</code> <code>@__interval </code><code>*</code> <code>3600</code><code>);</code>

<code>                </code><code>SET</code> <code>PARTITION_NAME </code><code>=</code> <code>FROM_UNIXTIME(CUR_TIME </code><code>+</code> <code>HOURLY_INTERVAL </code><code>*</code> <code>(@__interval </code><code>-</code> <code>1</code><code>) </code><code>*</code> <code>3600</code><code>, </code><code>'p%Y%m%d%H00'</code><code>);</code>

<code>                </code><code>CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);</code>

<code>                </code><code>SET</code> <code>@__interval</code><code>=</code><code>@__interval</code><code>+</code><code>1</code><code>;</code>

<code>        </code><code>SET</code> <code>OLDER_THAN_PARTITION_DATE</code><code>=</code><code>DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), </code><code>'%Y%m%d0000'</code><code>);</code>

<code>        </code><code>CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);</code>

partition_maintenance_all

<code>       </code><code>CALL partition_maintenance(SCHEMA_NAME, </code><code>'history'</code><code>, </code><code>7</code><code>, </code><code>24</code><code>, </code><code>7</code><code>);</code>

partition_verify

<code>        </code><code>DECLARE RETROWS </code><code>INT</code><code>(</code><code>11</code><code>);</code>

<code>        </code><code>DECLARE FUTURE_TIMESTAMP TIMESTAMP;</code>

<code>        </code><code>WHERE table_schema </code><code>=</code> <code>SCHEMANAME AND TABLE_NAME </code><code>=</code> <code>TABLENAME AND partition_name IS NULL;</code>

<code>        </code><code>IF RETROWS </code><code>=</code> <code>1</code> <code>THEN</code>

<code>                </code><code>SET</code> <code>FUTURE_TIMESTAMP </code><code>=</code> <code>TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), </code><code>" "</code><code>, </code><code>'00:00:00'</code><code>));</code>

<code>                </code><code>SET</code> <code>PARTITION_NAME </code><code>=</code> <code>DATE_FORMAT(CURDATE(), </code><code>'p%Y%m%d%H00'</code><code>);</code>

<code>                </code><code>SET</code> <code>@__PARTITION_SQL </code><code>=</code> <code>CONCAT(</code><code>"ALTER TABLE "</code><code>, SCHEMANAME, </code><code>"."</code><code>, TABLENAME, </code><code>" PARTITION BY RANGE(`clock`)"</code><code>);</code>

<code>                </code><code>SET</code> <code>@__PARTITION_SQL </code><code>=</code> <code>CONCAT(@__PARTITION_SQL, </code><code>"(PARTITION "</code><code>, PARTITION_NAME, </code><code>" VALUES LESS THAN ("</code><code>, UNIX_TIMESTAMP(FUTURE_TIMESTAMP), </code><code>"));"</code><code>);</code>

<code>                </code><code>PREPARE STMT FROM @__PARTITION_SQL;</code>

cat /opt/zabbix_mysql_partition.sh

<code>#!/bin/bash</code>

<code>if</code> <code>[ </code><code>-</code><code>f </code><code>/</code><code>root</code><code>/</code><code>.bash_profile ];then</code>

<code>source </code><code>/</code><code>root</code><code>/</code><code>.bash_profile</code>

<code>fi </code>

<code>mysql </code><code>-</code><code>uroot </code><code>-</code><code>p</code><code>'xx'</code> <code>zabbix </code><code>-</code><code>e </code><code>"CALL partition_maintenance_all('zabbix');"</code>

or

假如遷移Zabbix資料庫到TokuDB

http://imysql.com/2014/06/24/migrate-zabbix-db-to-tokudb.shtml

http://imysql.cn/2015/11/23/mysql-faq-use-event-in-zabbix-table-partitions.shtml

<code>CREATE TABLE history (</code>

<code> </code><code>itemid bigint(</code><code>20</code><code>) unsigned NOT NULL,</code>

<code> </code><code>clock </code><code>int</code><code>(</code><code>11</code><code>) NOT NULL DEFAULT </code><code>'0'</code><code>,</code>

<code> </code><code>value double(</code><code>16</code><code>,</code><code>4</code><code>) NOT NULL DEFAULT </code><code>'0.0000'</code><code>,</code>

<code> </code><code>ns </code><code>int</code><code>(</code><code>11</code><code>) NOT NULL DEFAULT </code><code>'0'</code><code>,</code>

<code> </code><code>KEY history_1 (itemid,clock)</code>

<code> </code><code>) ENGINE</code><code>=</code><code>TokuDB DEFAULT CHARSET</code><code>=</code><code>utf8 ROW_FORMAT</code><code>=</code><code>TOKUDB_QUICKLZ</code>

<code> </code><code>PARTITION BY </code><code>RANGE</code> <code>(clock)</code>

<code> </code><code>(PARTITION p20150531 VALUES LESS THAN (</code><code>1433088000</code><code>) ENGINE </code><code>=</code> <code>TokuDB,</code>

<code> </code><code>...</code>

<code> </code><code>PARTITION p20160411 VALUES LESS THAN (</code><code>1460390400</code><code>) ENGINE </code><code>=</code> <code>TokuDB);</code>

<code>delimiter $$$</code>

<code> </code><code>drop event </code><code>if</code> <code>exists zabbix_alter_partition_daily;</code>

<code> </code><code>CREATE EVENT zabbix_alter_partition_daily</code>

<code> </code><code>ON SCHEDULE EVERY </code><code>1</code> <code>DAY </code><code>-</code><code>-</code> <code>每天執行</code>

<code> </code><code>DO</code>

<code> </code><code>begin</code>

<code> </code><code>-</code><code>-</code> <code>記日志</code>

<code> </code><code>insert into zlogs select </code><code>0</code><code>, now(), date_format(date_sub(now(), INTERVAL </code><code>180</code> <code>DAY),</code>

<code> </code><code>" ALTER TABLE history DROP PARTITION p%Y%m%d"</code><code>);</code>

<code> </code><code>-</code><code>-</code> <code>删除history表</code><code>180</code><code>天前的舊分區</code>

<code> </code><code>-</code><code>-</code> <code>用PREPARE &amp; EXECUTE 準備和執行删除的SQL</code>

<code> </code><code>SET</code> <code>@drop_p_stmt </code><code>=</code> <code>date_format(date_sub(now(), INTERVAL </code><code>180</code> <code>DAY),</code><code>" ALTER TABLE history DROP PARTITION p%Y%m%d"</code><code>);</code>

<code> </code><code>PREPARE drop_p_stmt FROM @drop_p_stmt;</code>

<code> </code><code>EXECUTE drop_p_stmt;</code>

<code> </code><code>-</code><code>-</code> <code>建立history表</code><code>30</code><code>天後的新分區</code>

<code> </code><code>insert into zlogs select </code><code>0</code><code>, now(), concat(</code>

<code> </code><code>date_format(date_add(now(), INTERVAL </code><code>180</code> <code>DAY),</code><code>" ALTER TABLE history ADD PARTITION ( PARTITION p%Y%m%d VALUES LESS THAN "</code><code>),</code>

<code> </code><code>"("</code><code>, </code>

<code> </code><code>unix_timestamp( date_add(date_format(now(), </code><code>"%Y%m%d"</code><code>), INTERVAL </code><code>31</code> <code>DAY) ),</code>

<code> </code><code>"))"</code><code>);</code>

<code> </code><code>SET</code> <code>@add_p_stmt </code><code>=</code> <code>concat(</code>

<code> </code><code>date_format(date_add(now(), INTERVAL </code><code>30</code> <code>DAY),</code><code>" ALTER TABLE history ADD PARTITION ( PARTITION p%Y%m%d VALUES LESS THAN "</code><code>),</code>

<code> </code><code>"("</code><code>,</code>

<code> </code><code>PREPARE add_p_stmt FROM @add_p_stmt;</code>

<code> </code><code>EXECUTE add_p_stmt;</code>

<code> </code><code>end $$$</code>

<code> </code><code>delimiter ;</code>

本文轉自 liqius 51CTO部落格，原文連結：http://blog.51cto.com/szgb17/1977751，如需轉載請自行聯系原作者