MySQL 最適化 Zabbix パーティション最適化

cat > /etc/my.cnf<<EOF
[mysqld]
データディレクトリ=/data/mysql
ソケット=/var/lib/mysql/mysql.sock
デフォルトのストレージエンジン = innodb
照合サーバー = utf8_general_ci
init-connect = '名前をutf8に設定'
文字セットサーバー = utf8
シンボリックリンク=0
最大接続数=4096
innodb_buffer_pool_size=12G
最大許容パケット = 32M
結合バッファサイズ=2M
ソートバッファサイズ=2M 
クエリキャッシュサイズ = 64M  
クエリキャッシュ制限 = 4M  
スレッド同時実行性 = 8
テーブルオープンキャッシュ=1024
innodb_flush_log_at_trx_commit = 0

長いクエリ時間 = 1
ログスロークエリ=/data/mysql/mysql-slow.log 

[mysqld_safe]
ログエラー=/var/log/mariadb/mariadb.log
pidファイル=/var/run/mariadb/mariadb.pid

#[mysql]
#ソケット=/data/mysql/mysql.sock
#
# configディレクトリからすべてのファイルをインクルードする
#
!includedir /etc/my.cnf.d
終了

#cat パーティション.sql
区切り文字 $$
CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
始める
    /*
     SCHEMANAME = 変更を加えるDBスキーマ
     TABLENAME = 削除する可能性のあるパーティションを持つテーブル
     PARTITIONNAME = 作成するパーティションの名前
    */
    /*
     パーティションがまだ存在していないことを確認する
    */

    INT を宣言します。
    COUNT(1)をRETROWSに選択
    information_schema.partitions から
    ここで、table_schema = SCHEMANAME かつ table_name = TABLENAME かつ、partition_description >= CLOCK;

    RETROWS = 0の場合
        /*
          1. パーティションが作成されたことを示すメッセージを出力します。
          2. パーティションを作成するための SQL を作成します。
          3. 2 の SQL を実行します。
        */
        SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" )AS msg;
        SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADDPARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
        @sql から STMT を準備します。
        STMT を実行します。
        割り当てを解除し、STMT を準備します。
    終了の場合;
終わり$$
区切り文字 ;

区切り文字 $$
CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
始める
    /*
      SCHEMANAME = 変更を加えるDBスキーマ
     TABLENAME = 削除する可能性のあるパーティションを持つテーブル
     DELETE_BELOW_PARTITION_DATE = この日付より古い名前のパーティションを削除します (yyyy-mm-dd)
    */
    done INT DEFAULT FALSE を宣言します。
    drop_part_name VARCHAR(16) を宣言します。

    /*
     日付より古いすべてのパーティションのリストを取得します
     DELETE_BELOW_PARTITION_DATEで、すべてのパーティションにプレフィックスが付けられます。
      「p」なので、SUBSTRING TO を使用してその文字を削除します。
    */
    myCursor CURSOR FOR を宣言する
        パーティション名を選択
        information_schema.partitions から
        ここで、table_schema = SCHEMANAME、table_name = TABLENAME、ANDCAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) <DELETE_BELOW_PARTITION_DATE;
    NOT FOUND SET done = TRUE の継続ハンドラーを宣言します。

    /*
     パーティションを削除する必要があるときのための基本を作成します。また、
     @drop_partitionsは、すべてのパーティションのカンマ区切りのリストを保持します。
     削除する必要があります。
    */
    SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME,.", TABLENAME, " DROP PARTITION ");
    @drop_partitions を設定します。

    /*
     古すぎるすべてのパーティションをループし始めます。
    */
    myCursor を開きます。
    read_loop: ループ
        myCursor を drop_part_name に FETCH します。
        完了したら
            read_loop を終了します。
        終了の場合;
        SET @drop_partitions = IF(@drop_partitions = "",drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));
    ループを終了;
    IF @drop_partitions != "" の場合
        /*
          1. 必要なパーティションをすべて削除する SQL を作成します。
          2. SQL を実行してパーティションを削除します。
          3. 削除されたテーブルパーティションを出力します。
        */
        SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");
        @full_sql から STMT を準備します。
        STMT を実行します。
        割り当てを解除し、STMT を準備します。

        SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`,@drop_partitions AS `partitions_deleted`;
    それ以外
        /*
          パーティションは削除されないので、「N/A」（該当なし）と出力して示す。
          変更は行われなかった。
        */
        SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`,"N/A" AS `partitions_deleted`;
    終了の場合;
終わり$$
区切り文字 ;


区切り文字 $$
CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
始める
    OLDER_THAN_PARTITION_DATE VARCHAR(16) を宣言します。
    PARTITION_NAME VARCHAR(16) を宣言します。
    OLD_PARTITION_NAME VARCHAR(16) を宣言します。
    LESS_THAN_TIMESTAMP INT を宣言します。
    CUR_TIME INT を宣言します。

    呼び出しpartition_verify(SCHEMA_NAME,TABLE_NAME,HOURLY_INTERVAL);
    CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')) を設定します。

    @__間隔を 1 に設定します。
    create_loop: ループ
        @__interval > CREATE_NEXT_INTERVALS の場合
            create_loop を終了します。
        終了の場合;

        LESS_THAN_TIMESTAMP を CUR_TIME + (HOURLY_INTERVAL * @__interval *3600) に設定します。
        PARTITION_NAME を FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL *(@__interval - 1) * 3600, 'p%Y%m%d%H00') に設定します。
        IF(PARTITION_NAME != OLD_PARTITION_NAME)の場合
            パーティション作成を呼び出します(SCHEMA_NAME、TABLE_NAME、PARTITION_NAME、LESS_THAN_TIMESTAMP);
        終了の場合;
        @__interval=@__interval+1 を設定します。
        OLD_PARTITION_NAME = PARTITION_NAME を設定します。
    ループを終了;

    OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVALKEEP_DATA_DAYS DAY), '%Y%m%d0000') を設定します。
    パーティションドロップを呼び出します(SCHEMA_NAME、TABLE_NAME、OLDER_THAN_PARTITION_DATE);

終わり$$
区切り文字 ;

区切り文字 $$
CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
始める
    PARTITION_NAME VARCHAR(16) を宣言します。
    RETROWS INT(11) を宣言します。
    FUTURE_TIMESTAMP TIMESTAMP を宣言します。

    /*
    * 指定された SCHEMANAME.TABLENAME にパーティションが存在するかどうかを確認します。
    */
    COUNT(1)をRETROWSに選択
    information_schema.partitions から
    table_schema = SCHEMANAME かつ table_name = TABLENAME かつpartition_name が NULL の場合;

    /*
    * パーティションが存在しない場合は、テーブルをパーティション分割します。
    */
    IFRETROWS = 1 の場合
        /*
        * 現在の日付 00:00:00 を取得し、それに HOURLYINTERVAL を追加します。これが、値を保存するタイムスタンプです。
        * 1日の始まりに基づいてパーティション分割を開始します。これは、ランダムなパーティションを生成したくないためです。
        * 必ずしも希望するパーティション名と一致しない場合があります（例：時間間隔が24時間の場合、
        * 他のすべてのパーティションが「p201403280000」のようになるのに、「p201403270600」という名前のパーティションが作成されてしまいます。
        */
        FUTURE_TIMESTAMP を TIMESTAMPADD(HOUR, HOURLYINTERVAL,CONCAT(CURDATE(), " ", '00:00:00')) に設定します。
        PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00') を設定します。

        -- パーティションクエリを作成する
        SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME,".", TABLENAME, " PARTITION BY RANGE(`clock`)");
        SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ",PARTITION_NAME, " 値が (",UNIX_TIMESTAMP(FUTURE_TIMESTAMP), ") より小さい);");

        -- パーティションクエリを実行する
        @__PARTITION_SQL からステートメントを準備します。
        STMT を実行します。
        割り当てを解除し、STMT を準備します。
    終了の場合;
終わり$$
区切り文字 ;

区切り文字 $$
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
始める
        呼び出しpartition_maintenance(SCHEMA_NAME, 'history', 90, 24, 14);
        呼び出しpartition_maintenance(SCHEMA_NAME, 'history_log', 90, 24, 14);
        呼び出しpartition_maintenance(SCHEMA_NAME, 'history_str', 90, 24, 14);
        パーティションメンテナンスを呼び出します(SCHEMA_NAME、'history_text'、90、24、14)。
        パーティションメンテナンスを呼び出します(SCHEMA_NAME、'history_uint'、90、24、14)。
        呼び出しpartition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);
        呼び出しpartition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);
終わり$$
区切り文字 ;

mysql -uzabbix -pzabbix zabbix < パーティション.sql

crontab -l > crontab.txt 
cat >> crontab.txt <<EOF
#zabbix パーティションメンテナンス
01 01 * * * mysql -uzabbix -pzabbix zabbix -e"CALL partition_maintenance_all('zabbix')" &>/dev/null
終了
cat crontab.txt |crontab

nohup mysql -uzabbix -pzabbix zabbix -e "CALLpartition_maintenance_all('zabbix')" &> /root/partition.log&

MariaDB [zabbix]> テーブル作成履歴の表示
| 履歴 | テーブル `history` を作成する (
 `itemid` bigint(20) 符号なし NOT NULL,
 `clock`int(11) NOT NULL デフォルト '0',
 `value` double(16,4) NOT NULL デフォルト '0.0000',
 `ns`int(11) NOT NULL デフォルト '0',
 KEY`history_1` (`itemid`,`clock`)
) エンジン=InnoDB デフォルト文字セット=utf8
/*!50100 範囲によるパーティション分割 (`clock`)
(パーティション p201708280000 値は(1503936000)未満です) エンジン = InnoDB、
 パーティション p201708290000 値は(1504022400)未満です エンジン = InnoDB、
 パーティション p201708300000 値は(1504108800)未満です エンジン = InnoDB、
 パーティション p201708310000 値は(1504195200)未満です エンジン = InnoDB、
 パーティション p201709010000 値は(1504281600)未満です エンジン = InnoDB、
 パーティション p201709020000 値は(1504368000)未満です エンジン = InnoDB、
 パーティション p201709030000 値は(1504454400)未満です エンジン = InnoDB、
 パーティション p201709040000 値は(1504540800)未満です エンジン = InnoDB、
 パーティション p201709050000 値は(1504627200)未満です エンジン = InnoDB、
 パーティション p201709060000 値は(1504713600)未満です エンジン = InnoDB、
 パーティション p201709070000 値は(1504800000)未満です エンジン = InnoDB、
 パーティション p201709080000 値は(1504886400)未満です エンジン = InnoDB、
 パーティション p201709090000 値は(1504972800)未満です エンジン = InnoDB、
 パーティション p201709100000 値は(1505059200)未満です エンジン = InnoDB、
 パーティション p201709110000 値は (1505145600) 未満です エンジン = InnoDB) */ |