Inhaltsverzeichnis
partitionierte Tabellen mit MySQL
Der Spaltentyp "DATETIME" darf in Partitionsausdrücken nicht verwendet werden, statt dessen kann man den Spaltentyp "TIMESTAMP" verwenden.
MySQL 5.7: welche Tabellen sind in dieser DB partitioniert?
SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,SUBPARTITION_ORDINAL_POSITION,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE PARTITION_DESCRIPTION IS NOT NULL;
wievile Einträge sind in einer bestimmten Partition?
SELECT count(*) AS 'meinedb.meinetab p201810' FROM meinedb.meinetab PARTITION (p201810);
Partitionstypen in MySQL
Diese Partitionstypen gibt es bei MySQL:
RANGE(der TIMESTAMP kann z.B. die Spanne pro Partition beschreibt – hier muss fortlaufend immer eine neue Tabelle hinzugefügt werden)LIST(zum Beispiel „alphabetisch“)HASH(um eine gleichmäßige Verteilung der Daten auf eine im Voraus festgelegte Anzahl von Partitionen zu erzielen)KEY(wie HASH, nur das man hier den „Ausdruck“ nicht selber definieren kann, der ist „MD5“ oder „PASSWORD“)
eine vorhandene Tabelle partitionieren
PARTITION BY RANGE
Ggf. ist es nötig, bevor man eine vorhandene Tabelle partitionieren kann, den Typ der einiger Spalten anzupassen und oft muss auch der PRIMARY KEY angepassw werden.
zum Beispiel einen AUTO_INCREMENT auf eine Spalte legen oder ein NULL in ein NOT NULL umzuändern:
ALTER TABLE `datenbank`.`tabelle` CHANGE COLUMN `id` `id` bigint(20) NOT NULL AUTO_INCREMENT, CHANGE COLUMN `time` `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Zeitpunkt, zu dem das Ereignis eintrat' ;
ALTER TABLE `datenbank`.`tabelle` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`time`) USING BTREE ;
ALTER TABLE `datenbank`.`tabelle`
PARTITION BY RANGE (UNIX_TIMESTAMP(time)) (
PARTITION p201904 VALUES LESS THAN (UNIX_TIMESTAMP('2019-05-01 00:00:00')),
PARTITION p201905 VALUES LESS THAN (UNIX_TIMESTAMP('2019-06-01 00:00:00')),
PARTITION p201906 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-01 00:00:00')),
PARTITION p201907 VALUES LESS THAN (UNIX_TIMESTAMP('2019-08-01 00:00:00')),
PARTITION p201908 VALUES LESS THAN (UNIX_TIMESTAMP('2019-09-01 00:00:00')),
PARTITION p201909 VALUES LESS THAN (UNIX_TIMESTAMP('2019-10-01 00:00:00')),
PARTITION p201910 VALUES LESS THAN (UNIX_TIMESTAMP('2019-11-01 00:00:00')),
PARTITION p0 VALUES LESS THAN MAXVALUE
)
;
#!/bin/bash SICHERE_TAGE="92" # Die Anzahl der Tage, die aufgehoben werden sollen DATENBANK="log_db" TABELLE="log_tab" MYSQL="mysql --ssl-cipher=ECDHE-RSA-AES256-GCM-SHA384 -h192.168.33.06 -P3306" # für jeden Tag eine Partition anlegen echo " ALTER TABLE ${DATENBANK}.${TABELLE} PARTITION BY RANGE (UNIX_TIMESTAMP(datetime)) ( $(for i in $(seq -7 ${SICHERE_TAGE}) do echo " PARTITION p$(date -d"$((${i}+1)) day ago" +'%Y%m%d') VALUES LESS THAN (UNIX_TIMESTAMP('$(date -d"${i} day ago" +'%F') 00:00:00'))," done | tac) PARTITION p0 VALUES LESS THAN MAXVALUE ) ; " | ${MYSQL}
PARTITION BY HASH
Um eine Tabelle nach HASH zu partitionieren, muß die HASH-Spalte von einem INT-Type sein!
Modulus-HASH soll die Daten über die Partitionen gleichmäßiger verteilen.
Man sollte die Anzahl der Partitionen als einen Wert von 2^x (2, 4, 8, 16, …) wählen, weil der HASH-Algorythmus (Binärbaum) sich dann am besten ausbalancieren kann.
- Tabelle_partitionieren_-_HASH.sql
ALTER TABLE `promotion_tracking_backend`.`CrossSiteTrackingData` PARTITION BY HASH (id) PARTITIONS 16;
SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE PARTITION_DESCRIPTION IS NOT NULL AND TABLE_SCHEMA='promotion_tracking_backend' AND TABLE_NAME='CrossSiteTrackingData';
PARTITION BY LINEAR HASH
LINEAR HASH Partitioning: MySQL unterstützt auch lineares Hashing, das sich von regulärem Hashing darin unterscheidet, dass lineares Hashing einen linearen Potenzen-von-Zwei-Algorithmus verwendet, während reguläres Hashing den Modulus des Werts der Hash-Funktion verwendet.
LINEAR HASH soll beim INSERT und DELETE sowie dem verändern der Partitionsanzahl etwas schneller sein als der Modulus-HASH.
ALTER TABLE `promotion_tracking_backend`.`CrossSiteTrackingData` PARTITION BY LINEAR HASH (id) PARTITIONS 16;
partitionierte Tabelle neu anlegen
Es ist wichtig, dass alle Spalten, die in der PARTITION-Funktion verwendet werden, im PRIMARY KEY sind.
- partitionierte_Tabelle.sql
DROP TABLE log_db.log; CREATE TABLE log_db.log ( id INT(11) NOT NULL AUTO_INCREMENT, category VARCHAR(255) DEFAULT NULL, level INT(11) DEFAULT NULL, SERVER_PROTOCOL VARCHAR(250) DEFAULT NULL, REQUEST_METHOD VARCHAR(250) DEFAULT NULL, REQUEST_TIME DOUBLE DEFAULT NULL, QUERY_STRING VARCHAR(250) DEFAULT NULL, HTTP_ACCEPT_ENCODING VARCHAR(250) DEFAULT NULL, HTTP_CONNECTION VARCHAR(250) DEFAULT NULL, HTTP_HOST VARCHAR(250) DEFAULT NULL, HTTP_REFERER VARCHAR(250) DEFAULT NULL, HTTP_USER_AGENT VARCHAR(250) DEFAULT NULL, HTTPS VARCHAR(250) DEFAULT NULL, REMOTE_ADDR VARCHAR(32) DEFAULT NULL, REMOTE_PORT SMALLINT(6) DEFAULT NULL, SCRIPT_FILENAME VARCHAR(250) DEFAULT NULL, REQUEST_URI VARCHAR(250) DEFAULT NULL, sessionid VARCHAR(32) DEFAULT NULL, session_data text, post_data text, USER VARCHAR(64) DEFAULT NULL, message text, zeitpunkt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id,zeitpunkt), KEY USER (USER) ) PARTITION BY RANGE (UNIX_TIMESTAMP(zeitpunkt)) ( PARTITION p201806 VALUES LESS THAN (UNIX_TIMESTAMP('2018-07-01 00:00:00')), PARTITION p201807 VALUES LESS THAN (UNIX_TIMESTAMP('2018-08-01 00:00:00')), PARTITION p201808 VALUES LESS THAN (UNIX_TIMESTAMP('2018-09-01 00:00:00')) ) ;
> echo "SELECT * FROM log;" | mysql -t log_db
Jeder Monat wird in eine eigene Partition geschrieben.
> ls -lha /var/lib/mysql/log_db/log/ total 268K drwxr-x--- 2 mysql mysql 4,0K Mai 23 16:10 . drwxr-xr-x 9 mysql mysql 4,0K Mai 23 11:44 .. -rw-r----- 1 mysql mysql 65 Mai 16 17:51 db.opt -rw-r----- 1 mysql mysql 14K Mai 23 16:29 log.frm -rw-r----- 1 mysql mysql 112K Mai 23 16:29 log#P#p201806.ibd -rw-r----- 1 mysql mysql 112K Mai 23 16:29 log#P#p201807.ibd -rw-r----- 1 mysql mysql 112K Mai 23 16:29 log#P#p201808.ibd
Partitionen zu einer Tabelle hinzufügen bzw. entfernen
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html → ADD- oder DROP-Operationen für RANGE- oder LIST-Partitionen sind sofortige Operationen oder fast sofortige. ADD- oder COALESCE-Operationen für HASH- oder KEY-Partitionen kopieren Daten zwischen allen Partitionen, es sei denn, LINEAR HASH oder LINEAR KEY wurde verwendet; Dies entspricht im Grunde genommen dem Erstellen einer neuen Tabelle, obwohl die Operation ADD oder COALESCE Partition für Partition ausgeführt wird. REORGANIZE-Operationen kopieren nur geänderte Partitionen und berühren keine unveränderten Partitionen.
- neue_partition_zur_Tabelle_dazu.sql
# # Neue Partition anlegen. # ALTER TABLE log_db.log ADD PARTITION ( PARTITION p201811 VALUES LESS THAN (UNIX_TIMESTAMP('2018-12-01 00:00:00')) ) ;
- alte_partition_von_Tabelle_weg.sql
# # Eine Partition löschen. # ALTER TABLE log_db.log DROP PARTITION p201811 ;
- alte_partition_von_Tabelle_kick.sql
# # Eine Partition löschen. # ALTER TABLE log_db.log TRUNCATE PARTITION p201811 ;
Partitionierung einer Tabelle rotieren
- Partitionen_der_Tabelle.sql
# # IN diesem Block müssen alle benötigten Partitionen definiert sein, # sollte hier eine Partition nicht aufgeführt sein, dann wird sie gelöscht! # Neue hinzugekommene Partitionen werden angelegt. # ALTER TABLE log_db.log PARTITION BY RANGE (UNIX_TIMESTAMP(zeitpunkt)) ( PARTITION p201806 VALUES LESS THAN (UNIX_TIMESTAMP('2018-07-01 00:00:00')), PARTITION p201807 VALUES LESS THAN (UNIX_TIMESTAMP('2018-08-01 00:00:00')), PARTITION p201808 VALUES LESS THAN (UNIX_TIMESTAMP('2018-09-01 00:00:00')), PARTITION p201809 VALUES LESS THAN (UNIX_TIMESTAMP('2018-10-01 00:00:00')), PARTITION p201810 VALUES LESS THAN (UNIX_TIMESTAMP('2018-11-01 00:00:00')), PARTITION p0 VALUES LESS THAN MAXVALUE ) ;
Leider kann man das rotieren von Partitionen nicht automatisch innerhalb der MySQL-DB mit einem EVENT einrichten, weil MySQL in der PARTITION-Sektion keine Variablen akzeptiert.
Es werden in der PARTITION-Sektion auch keine Funktionen akzeptiert, die sich auf eine Zeitzone beziehen wie z.B. NOW() oder CURDATE() mit dessen Hilfe es möglich wäre einen Zeitpunkt zu definieren, der in Abhängigkeit vom "jetzt" steht.
Deshalb ist die einzige Lösung, den SQL-Befehl "extern" zu generieren. Im folgenden stelle ich eine solche Lösung vor.
- /root/bin/Tabelle_Partitionen_Vorlage.sh
#!/bin/bash #set -x #==============================================================================# # # Dieses Skript gibt das SQL-Querry aus, mit dem eine Tabelle partitioniert # werden kann. # Sollte hier eine Partition nicht mit angegeben worden sein, # die aber vorhanden ist, dann wird sie gelöscht! # Neu hinzugekommene Partitionen werden angelegt. # #==============================================================================# VERSION="v2018061400" ANZAHL="6" # Anzahl der Monate/Partitionen, die bleiben sollen DB_NAME="${1}" # Name der Datenbank DB_TABELLE="${2}" # Name der Tabelle PRI_SPALTE="${3}" # Name der Spalte, in der der Zeitpunkt liegt (diese Spalte MUSS der "PRIMARY KEY" sein oder im "PRIMARY KEY" enthalten sein) #------------------------------------------------------------------------------# if [ -z "${3}" -o "${1}" = "h" -o "${1}" = "help" ] ; then echo echo "${0} [Datenbank] [Tabelle] [Spalte]" echo "${0} buchung protokoll zeitpunkt" exit 0 fi #------------------------------------------------------------------------------# partitionen() { for i in $(seq 0 $((${ANZAHL}+1)) | tac) do #echo "i=${i}" P_JAHR="$(date -d"$((${i}-1)) month ago" +'%Y')" P_MONAT="$(date -d"$((${i}-1)) month ago" +'%m')" D_JAHR="$(date -d"$((${i}-2)) month ago" +'%Y')" D_MONAT="$(date -d"$((${i}-2)) month ago" +'%m')" echo " PARTITION p${P_JAHR}${P_MONAT} VALUES LESS THAN (UNIX_TIMESTAMP('${D_JAHR}-${D_MONAT}-01 00:00:00'))," done } #------------------------------------------------------------------------------# echo "# ${DB_NAME} / ${DB_TABELLE} / ${PRI_SPALTE}" echo " ALTER TABLE \`${DB_NAME}\`.\`${DB_TABELLE}\` PARTITION BY RANGE (UNIX_TIMESTAMP(${PRI_SPALTE})) ( $(partitionen) PARTITION p0 VALUES LESS THAN MAXVALUE ) ; "
Das obere Skript zeigt den SQL-Aufruf, um die Partitionierung einzurichten bzw. um sie regelmäßig zu aktualisieren.
Das folgende Skript führt dieses SQL-Kommando auch gleich auf die entsprechende Tabelle aus.
Bei jedem Aufruf wird die Tabellen-Partitionen-Konfiguration überprüft, wobei jede Abweichung zwischen vorhandener Konfiguration und generierter Konfiguration direkt auf die Tabelle angewendet wird. Das heißt, wenn eine Partition in der Tabelle vorhanden ist, die in der generierten Konfiguration nicht vorhanden ist, dann wird sie gelöscht. Wenn eine Partition in der Tabelle nicht vorhanden ist, die aber in der generierten Konfiguration vorhanden ist, dann wird sie neu angelegt.
Sinnvollerweise könnte man dieses Skript regelmäßig (z.B. täglich oder wöchentlich) von einem CRON-Job starten lassen.
- /root/bin/Partitionen_der_Tabelle.sh
#!/bin/bash #==============================================================================# # # Dieses Skript partitioniert die "log"-Tabelle für einen bestimmten Zeitraum. # # Sollte hier eine Partition nicht mit angegeben werden, dann wird sie gelöscht! # Neu hinzugekommene Partitionen werden angelegt. # #==============================================================================# # # echo "SELECT * FROM log;" | mysql -tS /var/run/mysqld/mysqld_3306.sock log_db # echo "SELECT * FROM log;" | mysql -tP3306 -hdb3306master.lan log_db # #==============================================================================# VERSION="v2018052400" ANZAHL="3" # es sollen die letzten 3 Monate liegen bleiben, was älter ist wird gelöscht DB_PORT="3306" DB_HOST="db${DB_PORT}master.lan" DB_NAME="log_db" DB_TABELLE="log" PRI_SPALTE="zeitpunkt" #------------------------------------------------------------------------------# ### dieses Skript darf nur auf dem Master laufen if [ "x$(ip a | fgrep " $(host ${DB_HOST} | awk '/has address/{print $NF}')/")" = "x" ] ; then #echo "Diese Maschine ist nicht der Master. ABBRUCH" exit 0 fi #------------------------------------------------------------------------------# partitionen() { for i in $(seq 0 $((${ANZAHL}+1)) | tac) do #echo "# i=${i}" P_JAHR="$(date -d"$((${i}-1)) month ago" +'%Y')" P_MONAT="$(date -d"$((${i}-1)) month ago" +'%m')" D_JAHR="$(date -d"$((${i}-2)) month ago" +'%Y')" D_MONAT="$(date -d"$((${i}-2)) month ago" +'%m')" echo " PARTITION p${P_JAHR}${P_MONAT} VALUES LESS THAN (UNIX_TIMESTAMP('${D_JAHR}-${D_MONAT}-01 00:00:00'))," done } #------------------------------------------------------------------------------# echo " ALTER TABLE \`${DB_NAME}\`.\`${DB_TABELLE}\` PARTITION BY RANGE (UNIX_TIMESTAMP(${PRI_SPALTE})) ( $(partitionen) PARTITION p0 VALUES LESS THAN MAXVALUE ) ; " | mysql -P${DB_PORT} -h${DB_HOST} ${DB_NAME}
Dieses Skript muss regelmäßig aufgerufen werden, midestens einmal pro Monat; in diesem Beispiel wird es täglich um ein Uhr aufgerufen:
> cat /etc/cron.d/Partitionen_der_Tabelle 0 1 * * * root /root/bin/Partitionen_der_Tabelle.sh
Wenn alles ordnungsgemäß läuft, dann sollten niemals Daten in der Partition "p0" landen. (https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html):
> echo "SELECT COUNT(*) FROM log PARTITION (p0);" | mysql -NP3306 -hdb3306master.lan log_db 0
Partitionen einer Tabelle mit einem MySQL-EVENT pflegen
Die automatische Rotation der Tabellen-Partitionen kann durch einen externen CRON-Job erfolgen (siehe oben).
Theoretisch sollte das aber komplett durch die MySQL mit Hilfe von EVENTS selber bewerkstelligt werden können - ich habe diverse Tests dazu durchgeführt und Doku gelesen; leider scheint die MySQL im PATITION-Block weder Variablen zu unterstützen, noch Zeitzonenbezogene Funktionen (also Funktionen die eine aktuelle Zeit ausgeben wie z.B. NOW() oder CURDATE()) zu unterstützen.
Somit ist eine Automatisierung der Partitionsrotation durch EVENTS nicht möglich; evtl. könnte es mit Hilfe von anderen internen Prozeduren klappen, dazu fehlen mir aber aktuell die Kenntnisse.
# echo "SHOW VARIABLES LIKE '%event_scheduler%';" | mysql -tS /var/run/mysqld/mysqld_3368.sock +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+
Vorschlag für ein EVENT zur Partitionsrotation 
USE `log_db`;
DROP PROCEDURE IF EXISTS prozedur_partitionen_der_tabelle_log;
DELIMITER |
CREATE PROCEDURE prozedur_partitionen_der_tabelle_log ()
BEGIN
SET @yp1 = (SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y'));
SET @mp1 = (SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%m'));
SET @dp1 = (UNIX_TIMESTAMP(CONCAT(@yp1, '-', @mp1, '-01 00:00:00') ));
SET @pp1 = (CONCAT('PARTITION ', 'p', @yp1, @mp1, ' VALUES LESS THAN ',@dp1) );
SET @y00 = (SELECT DATE_FORMAT(CURDATE(), '%Y'));
SET @m00 = (SELECT DATE_FORMAT(CURDATE(), '%m'));
SET @d00 = (UNIX_TIMESTAMP(CONCAT(@y00, '-', @m00, '-01 00:00:00') ));
SET @p00 = (CONCAT('PARTITION ', 'p', @y00, @m00, ' VALUES LESS THAN ',@d00) );
SET @ym1 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y'));
SET @mm1 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%m'));
SET @dm1 = (UNIX_TIMESTAMP(CONCAT(@ym1, '-', @mm1, '-01 00:00:00') ));
SET @pm1 = (CONCAT('PARTITION ', 'p', @ym1, @mm1, ' VALUES LESS THAN ',@dm1) );
SET @ym2 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y'));
SET @mm2 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%m'));
SET @dm2 = (UNIX_TIMESTAMP(CONCAT(@ym2, '-', @mm2, '-01 00:00:00') ));
SET @pm2 = (CONCAT('PARTITION ', 'p', @ym2, @mm2, ' VALUES LESS THAN ',@dm2) );
SET @ym3 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y'));
SET @mm3 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%m'));
SET @dm3 = (UNIX_TIMESTAMP(CONCAT(@ym3, '-', @mm3, '-01 00:00:00') ));
SET @pm3 = (CONCAT('PARTITION ', 'p', @ym3, @mm3, ' VALUES LESS THAN ',@dm3) );
SET @ym4 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y'));
SET @mm4 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%m'));
SET @dm4 = (UNIX_TIMESTAMP(CONCAT(@ym4, '-', @mm4, '-01 00:00:00') ));
SET @pm4 = (CONCAT('PARTITION ', 'p', @ym4, @mm4, ' VALUES LESS THAN ',@dm4) );
SET @ym5 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y'));
SET @mm5 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%m'));
SET @dm5 = (UNIX_TIMESTAMP(CONCAT(@ym5, '-', @mm5, '-01 00:00:00') ));
SET @pm5 = (CONCAT('PARTITION ', 'p', @ym5, @mm5, ' VALUES LESS THAN ',@dm5) );
SET @ym6 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y'));
SET @mm6 = (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%m'));
SET @dm6 = (UNIX_TIMESTAMP(CONCAT(@ym6, '-', @mm6, '-01 00:00:00') ));
SET @pm6 = (CONCAT('PARTITION ', 'p', @ym6, @mm6, ' VALUES LESS THAN ',@dm6) );
ALTER TABLE log_00
PARTITION BY RANGE (UNIX_TIMESTAMP(c_time)) (
@pp1,
@p00,
@pm1,
@pm2,
@pm3,
@pm4,
@pm5,
@pm6,
PARTITION p0 VALUES LESS THAN MAXVALUE
);
END |
DELIMITER ;
DROP EVENT IF EXISTS event_partitionen_der_tabelle_log;
CREATE EVENT event_partitionen_der_tabelle_log
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
CALL prozedur_partitionen_der_tabelle_log();