Mysql-Optimierung Zabbix-Partitionsoptimierung

Katze > /etc/my.cnf<<EOF
[mysqld]
Datenverzeichnis=/Daten/MySQL
socket=/var/lib/mysql/mysql.sock
Standard-Speicher-Engine = InnoDB
Sortierserver = utf8_general_ci
init-connect = "Namen festlegen utf8"
Zeichensatzserver = utf8
symbolische Links = 0
max_verbindungen=4096
innodb_buffer_pool_size=12G
max_allowed_packet = 32M
join_buffer_size=2M
Sortierpuffergröße = 2 M 
Abfrage-Cachegröße = 64 M  
query_cache_limit = 4 M  
Thread_Parallelität = 8
table_open_cache=1024
innodb_flush_log_at_trx_commit = 0

lange_Abfragezeit = 1
log-slow-queries=/data/mysql/mysql-slow.log 

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-Datei=/var/run/mariadb/mariadb.pid

#[mysql]
#socket=/data/mysql/mysql.sock
#
# alle Dateien aus dem Konfigurationsverzeichnis einbinden
#
!includedir /etc/my.cnf.d
Ende der Laufzeit

#cat partition.sql
TRENNUNGSZEICHEN $$
ERSTELLEN SIE DAS VERFAHREN `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
BEGINNEN
    /*
     SCHEMANAME = Das DB-Schema, in dem Änderungen vorgenommen werden sollen
     TABLENAME = Die Tabelle mit den Partitionen, die möglicherweise gelöscht werden sollen
     PARTITIONNAME = Der Name der zu erstellenden Partition
    */
    /*
     Stellen Sie sicher, dass die Partition noch nicht vorhanden ist.
    */

    DECLARE RETROWS INT;
    Wählen Sie COUNT(1) in RETROWS aus
    VON information_schema.partitions
    Wobei table_schema = SCHEMANAME UND table_name = TABLENAME UND partition_description >= CLOCK;

    WENN RETROWS = 0, DANN
        /*
          1. Drucken Sie eine Meldung, die angibt, dass eine Partition erstellt wurde.
          2. Erstellen Sie das SQL zum Erstellen der Partition.
          3. Führen Sie das SQL von Nr. 2 aus.
        */
        SELECT CONCAT( "partition_create(", SCHEMANAME, ",",TABELLENNAME, ",", PARTITIONNAME, ",", UHR, ")" )AS msg;
        SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADDPARTITION (PARTITION ', PARTITIONNAME, ' WERTE KLEINER ALS (', CLOCK, '));' );
        STMT AUS @sql VORBEREITEN;
        STMT AUSFÜHREN;
        Zuordnung freigeben, STMT vorbereiten;
    ENDE, WENN;
ENDE$$
TRENNUNGSZEICHEN ;

TRENNUNGSZEICHEN $$
PROZEDUR `partition_drop` ERSTELLEN (SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)
BEGINNEN
    /*
      SCHEMANAME = Das DB-Schema, in dem Änderungen vorgenommen werden sollen
     TABLENAME = Die Tabelle mit den Partitionen, die möglicherweise gelöscht werden sollen
     DELETE_BELOW_PARTITION_DATE = Löscht alle Partitionen mit Namen, die älter als dieses Datum sind (jjjj-mm-tt).
    */
    DECLARE fertig INT DEFAULT FALSE;
    DECLARE drop_part_name VARCHAR(16);

    /*
     Holen Sie sich eine Liste aller Partitionen, die älter sind als das Datum
     in DELETE_BELOW_PARTITION_DATE. Alle Partitionen haben das Präfix
      ein „p“, verwenden Sie also SUBSTRING, um dieses Zeichen zu entfernen.
    */
    DECLARE myCursor CURSOR FÜR
        SELECT Partitionsname
        VON information_schema.partitions
        WO Tabellenschema = SCHEMANAME UND Tabellenname = TABELLENAME UND CAST(SUBSTRING(Partitionsname VON 2) AS UNSIGNED) <DELETE_BELOW_PARTITION_DATE;
    DECLARE CONTINUE HANDLER FÜR NICHT GEFUNDEN SET done = TRUE;

    /*
     Erstellen Sie die Grundlagen für den Fall, dass wir die Partition löschen müssen. Erstellen Sie außerdem
     @drop_partitions zum Speichern einer durch Kommas getrennten Liste aller Partitionen, die
     sollten gelöscht werden.
    */
    SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME,.", TABLENAME, " DROP PARTITION ");
    SETZEN Sie @drop_partitions = "";

    /*
     Beginnen Sie mit der Schleife durch alle Partitionen, die zu alt sind.
    */
    ÖFFNE myCursor;
    read_loop: SCHLEIFE
        FETCH myCursor INTO drop_part_name;
        WENN erledigt, DANN
            LEAVE read_loop;
        ENDE, WENN;
        SET @drop_partitions = IF(@drop_partitions = "",drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name));
    ENDE DER SCHLEIFE;
    WENN @drop_partitions != "" DANN
        /*
          1. Erstellen Sie das SQL, um alle erforderlichen Partitionen zu löschen.
          2. Führen Sie SQL aus, um die Partitionen zu löschen.
          3. Drucken Sie die gelöschten Tabellenpartitionen aus.
        */
        SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";");
        STMT VON @full_sql VORBEREITEN;
        STMT AUSFÜHREN;
        Zuordnung freigeben, STMT vorbereiten;

        SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `Tabelle`,@drop_partitions AS `partitions_deleted`;
    ANDERS
        /*
          Es werden keine Partitionen gelöscht. Drucken Sie daher "N/A" (Nicht zutreffend) aus, um anzuzeigen,
          dass keine Änderungen vorgenommen wurden.
        */
        SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`,"N/A" AS `partitions_deleted`;
    ENDE, WENN;
ENDE$$
TRENNUNGSZEICHEN ;


TRENNUNGSZEICHEN $$
VERFAHREN ERSTELLEN `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
BEGINNEN
    DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
    DECLARE PARTITION_NAME VARCHAR(16);
    DECLARE OLD_PARTITION_NAME VARCHAR(16);
    DECLARE LESS_THAN_TIMESTAMP INT;
    DECLARE CUR_TIME INT;

    Rufen Sie partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL) auf;
    SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(JETZT(), '%Y-%m-%d 00:00:00'));

    SETZEN Sie @__interval = 1;
    create_loop: SCHLEIFE
        WENN @__interval > NÄCHSTE_INTERVALLE ERSTELLEN DANN
            VERLASSEN Sie create_loop;
        ENDE, WENN;

        SETZEN SIE LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval *3600);
        PARTITIONSNAME FESTLEGEN = FROM_UNIXTIME(AKTUELLE ZEIT + STUNDENINTERVAL *(@__interval - 1) * 3600, 'p%Y%m%d%H00');
        WENN(PARTITIONSNAME != ALTER PARTITIONSNAME) DANN
            Rufen Sie partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP) auf;
        ENDE, WENN;
        SETZEN Sie @__interval=@__interval+1;
        SETZEN SIE ALTEN_PARTITIONSNAME = PARTITIONSNAME;
    ENDE DER SCHLEIFE;

    SETZEN SIE OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVALKEEP_DATA_DAYS DAY), '%Y%m%d0000');
    Rufen Sie partition_drop auf (SCHEMANAME, TABELLENNAME, ÄLTER ALS PARTITIONSDATUM);

ENDE$$
TRENNUNGSZEICHEN ;

TRENNUNGSZEICHEN $$
PROZEDUR `partition_verify` ERSTELLEN (SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
BEGINNEN
    DECLARE PARTITION_NAME VARCHAR(16);
    DECLARE RETROWS INT (11);
    DECLARE FUTURE_TIMESTAMP ZEITSTEMPEL;

    /*
    * Überprüfen Sie, ob für den angegebenen SCHEMANAME.TABLENAME Partitionen vorhanden sind.
    */
    Wählen Sie COUNT(1) in RETROWS aus
    VON information_schema.partitions
    Wobei table_schema = SCHEMANAME UND table_name = TABLENAME UND partition_name NULL ist;

    /*
    * Wenn keine Partitionen vorhanden sind, partitionieren Sie die Tabelle
    */
    WENNFRETROWS = 1 DANN
        /*
        * Nehmen Sie das aktuelle Datum um 00:00:00 und fügen Sie HOURLYINTERVAL hinzu. Dies ist der Zeitstempel, unter dem wir Werte speichern werden.
        * Wir beginnen mit der Partitionierung basierend auf dem Beginn eines Tages. Dies liegt daran, dass wir keine zufällige Partition erstellen möchten
        * das nicht unbedingt mit der gewünschten Partitionsbenennung übereinstimmt (d. h.: wenn das Stundenintervall 24 Stunden beträgt, könnten wir
        * am Ende wird eine Partition mit dem Namen „p201403270600“ erstellt, während alle anderen Partitionen wie „p201403280000“ aussehen).
        */
        SETZEN SIE FUTURE_TIMESTAMP = TIMESTAMPADD(STUNDE, STUNDENINTERVALL,CONCAT(CURDATE(), " ", '00:00:00'));
        PARTITIONSNAME FESTLEGEN = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');

        -- Erstellen Sie die Partitionierungsabfrage
        SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME,".", TABLENAME, " PARTITION BY RANGE(`clock`)");
        SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ",PARTITION_NAME, " WERTE KLEINER ALS (",UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));");

        -- Führen Sie die Partitionierungsabfrage aus
        STMT AUS @__PARTITION_SQL VORBEREITEN;
        STMT AUSFÜHREN;
        Zuordnung freigeben, STMT vorbereiten;
    ENDE, WENN;
ENDE$$
TRENNUNGSZEICHEN ;

TRENNUNGSZEICHEN $$
PROZEDUR ERSTELLEN `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGINNEN
        Rufen Sie partition_maintenance(SCHEMA_NAME, 'history', 90, 24, 14) auf;
        Rufen Sie partition_maintenance(SCHEMA_NAME, 'history_log', 90, 24, 14) auf;
        Rufen Sie partition_maintenance(SCHEMA_NAME, 'history_str', 90, 24, 14) auf;
        Rufen Sie partition_maintenance(SCHEMA_NAME, 'history_text', 90, 24, 14) auf;
        Rufen Sie partition_maintenance(SCHEMA_NAME, 'history_uint', 90, 24, 14) auf;
        Rufen Sie partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14) auf;
        Rufen Sie partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14) auf;
ENDE$$
TRENNUNGSZEICHEN ;

mysql -uzabbix -pzabbix zabbix < partition.sql

crontab -l > crontab.txt 
Katze >> crontab.txt <<EOF
#zabbix partition_maintenance
01 01 * * * mysql -uzabbix -pzabbix zabbix -e"CALL partition_maintenance_all('zabbix')" &>/dev/null
Ende der Laufzeit
Katze crontab.txt |crontab

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

MariaDB [zabbix]> Tabellenverlauf anzeigen
| Geschichte | CREATE TABLE `Geschichte` (
 `itemid` bigint(20) unsigned NICHT NULL,
 `clock`int(11) NICHT NULL STANDARD '0',
 `Wert` double(16,4) NICHT NULL STANDARD '0.0000',
 `ns`int(11) NICHT NULL STANDARD '0',
 SCHLÜSSEL `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8
/*!50100 PARTITION NACH BEREICH (`Uhr`)
(PARTITION p201708280000 WERTE WENIGER ALS (1503936000) ENGINE = InnoDB,
 PARTITION p201708290000 WERTE KLEINER ALS (1504022400) ENGINE = InnoDB,
 PARTITION p201708300000 WERTE KLEINER ALS (1504108800) ENGINE = InnoDB,
 PARTITION p201708310000 WERTE KLEINER ALS (1504195200) ENGINE = InnoDB,
 PARTITION p201709010000 WERTE KLEINER ALS (1504281600) ENGINE = InnoDB,
 PARTITION p201709020000 WERTE KLEINER ALS (1504368000) ENGINE = InnoDB,
 PARTITION p201709030000 WERTE KLEINER ALS (1504454400) ENGINE = InnoDB,
 PARTITION p201709040000 WERTE KLEINER ALS (1504540800) ENGINE = InnoDB,
 PARTITION p201709050000 WERTE KLEINER ALS (1504627200) ENGINE = InnoDB,
 PARTITION p201709060000 WERTE KLEINER ALS (1504713600) ENGINE = InnoDB,
 PARTITION p201709070000 WERTE KLEINER ALS (1504800000) ENGINE = InnoDB,
 PARTITION p201709080000 WERTE KLEINER ALS (1504886400) ENGINE = InnoDB,
 PARTITION p201709090000 WERTE KLEINER ALS (1504972800) ENGINE = InnoDB,
 PARTITION p201709100000 WERTE KLEINER ALS (1505059200) ENGINE = InnoDB,
 PARTITION p201709110000 WERTE KLEINER ALS (1505145600) ENGINE = InnoDB) */ |