Anwendungsszenarien und Lösungen für die MySQL-Komprimierung

Einführung

Beschreibt die Anwendungsfälle und Lösungen für die MySQL-Komprimierung, einschließlich Komprimierungstransportprotokollen, Lösungen für komprimierte Spalten und Lösungen für komprimierte Tabellen.

Wenn es um MySQL-Komprimierung geht, können wir an die folgenden komprimierungsbezogenen Szenarien denken:

1. Die zwischen Client und Server übertragene Datenmenge ist zu groß und muss komprimiert werden, um Bandbreite zu sparen

2. Die Datenmenge in einer bestimmten Spalte von MySQL ist groß und nur die Daten einer bestimmten Spalte werden komprimiert

3. Eine oder mehrere MySQL-Tabellen enthalten zu viele Daten. Sie müssen die Tabellendaten komprimieren, um den Speicherplatzbedarf zu reduzieren.

Für diese Probleme gibt es gute Lösungen auf MySQL-Seite. Für das erste Problem können Sie das MySQL-Komprimierungsprotokoll verwenden, um es zu lösen; für das zweite Problem können Sie die MySQL-Komprimierungs- und Dekomprimierungsfunktionen verwenden, um es perfekt zu lösen; und für das komplexeste dritte Problem kann es auf Engine-Ebene gelöst werden. Derzeit unterstützen Engines wie myisam, innodb, tokudb und MyRocks alle die Tabellenkomprimierung. In diesem Artikel werden solche Probleme im Zusammenhang mit dem MySQL-Komprimierungsmechanismus ausführlich erörtert. Im Folgenden sind die wichtigsten Inhalte aufgeführt:

1. Einführung in das MySQL-Komprimierungsprotokoll

1. Anwendbare Szenarien

Das MySQL-Komprimierungsprotokoll eignet sich für Szenarien, in denen die zwischen MySQL-Server und -Client übertragene Datenmenge groß oder die verfügbare Bandbreite gering ist. Typische Szenarien sind wie folgt:

a. Unzureichende Bandbreite bei der Abfrage großer Datenmengen (zum Beispiel beim Exportieren von Daten);

b. Beim Kopieren ist die Menge des Binärprotokolls zu groß. Aktivieren Sie den Parameter „slave_compressed_protocol“, um eine Protokollkomprimierungsreplikation durchzuführen.

2. Einführung in das Komprimierungsprotokoll

Das Komprimierungsprotokoll ist Teil des MySQL-Kommunikationsprotokolls. Um das Komprimierungsprotokoll für die Datenübertragung zu aktivieren, müssen sowohl der MySQL-Server als auch der MySQL-Client den zlib-Algorithmus unterstützen. Das Aktivieren des Komprimierungsprotokolls führt zu einer leichten Erhöhung der CPU-Auslastung. Komprimierungsprotokoll aktivieren. Verwenden Sie den Parameter -C oder --compress=true, um die Client-Komprimierungsfunktion zu aktivieren. Wenn die Option -C oder compress=true aktiviert ist, wird beim Verbinden mit dem Serversegment das Serverfähigkeitsflag 0x0020 (CLIENT_COMPRESS) gesendet und nach der Aushandlung mit dem Server (nach 3 Handshakes) wird das Komprimierungsprotokoll unterstützt. Durch die Komprimierung ändert sich das Format des Datenpakets. Die spezifischen Änderungen sind wie folgt:

Unkomprimiertes Paketformat:

Das komprimierte Datenpaketformat ist:

Möglicherweise ist Ihnen aufgefallen, dass das komprimierte Datagrammformat in komprimierte und unkomprimierte Formate unterteilt ist. Dies ist eine von MySQL vorgenommene Optimierung, um den CPU-Overhead zu reduzieren. Wenn der Inhalt weniger als 50 Byte umfasst, wird er nicht komprimiert, wenn er jedoch mehr als 50 Byte umfasst, wird die Komprimierung aktiviert. Die spezifischen Regeln lauten wie folgt:

Wenn der Wert des dritten Felds gleich 0x00 ist, bedeutet dies, dass das aktuelle Paket nicht komprimiert ist, sodass der Inhalt von n * Byte 1 * Byte, n * Byte ist, dh der Anforderungstyp und der Anforderungsinhalt.

Wenn der Wert des dritten Felds größer als 0x00 ist, bedeutet dies, dass das aktuelle Paket mit zlib komprimiert wurde. Daher müssen bei Verwendung n * Bytes dekomprimiert werden. Der dekomprimierte Inhalt ist 1 * Byte, n * Byte, d. h. der Anforderungstyp und der Anforderungsinhalt.

3. Lösungspraxis

Fügen Sie den Parameter -C oder --compress=true hinzu, wenn der Client eine Verbindung herstellt. Wenn Sie Unterstützung für Komprimierungsprotokolle zur Synchronisierung hinzufügen möchten, müssen Sie slave_compressed_protocol=1 konfigurieren. Nachfolgend sehen Sie ein Beispiel für die Verbindung mit einem MySQL-Server unter Verwendung des komprimierten Protokolls:

MySQL -h hostip -uroot -p password --compress

MySQLdump -h hostip -uroot -p password -default-character-set=utf8 --compress --single-transaction dbname tabellenname > tabellenname.sql

Wenn Sie die komprimierte Übertragung bei der Master-Slave-Replikation aktivieren müssen, schalten Sie einfach den Parameter slave_compressed_protocol=1 auf dem Slave ein.

4. Kompressionseffekt

Sie können die Wirkung der komprimierten Übertragung beobachten, indem Sie die Option --compress in MySQLdump oder den Parameter slave_compressed_protocol bei der Master-Slave-Replikation verwenden. Die Wirkung ist leicht zu erkennen, daher werde ich hier keine Screenshots zeigen.

MySQL-Spaltenkomprimierungslösung

Derzeit gibt es keine direkte Lösung zum Komprimieren von MySQL-Spalten. TMySQL von Tencent kann Spalten direkt komprimieren. Hier stellen wir hauptsächlich einen Umweg zur Rettung des Landes vor, der darin besteht, die von MySQL auf Geschäftsebene bereitgestellten Komprimierungs- und Dekomprimierungsfunktionen zum Komprimieren und Dekomprimieren von Spalten zu verwenden. Das heißt, wenn Sie eine Spalte komprimieren möchten, müssen Sie beim Schreiben die Funktion COMPRESS aufrufen, um den Inhalt dieser Spalte zu komprimieren und ihn dann in der entsprechenden Spalte zu speichern. Verwenden Sie beim Lesen die Funktion UNCOMPRESSED, um den komprimierten Inhalt zu dekomprimieren.

1. Anwendbare Szenarien

Besonders groß ist die Datenmenge in einer oder mehreren Spalten in MySQL, meist handelt es sich dabei um Datentypen wie varchar, text und char.

2. Einführung in die Komprimierungsfunktion

Die MySQL-Komprimierungsfunktion COMPRESS komprimiert einen String und gibt einen Binärstring zurück. Die Verwendung dieser Funktion erfordert, dass der MySQL-Server Komprimierung unterstützt, andernfalls wird NULL zurückgegeben. Das komprimierte Feld wird am besten mit dem Feldtyp varbinary oder blob gespeichert. Verwenden Sie die Funktion UNCOMPRESSED, um komprimierte Daten zu dekomprimieren. Beachten Sie, dass dieser Ansatz geringfügige Änderungen auf der Geschäftsseite erfordert. Der komprimierte Inhalt wird wie folgt gespeichert:

a. Leere Strings werden als leere Strings gespeichert

b. Nicht leere Zeichenfolgen werden wie folgt gespeichert: Die ersten 4 Bytes speichern die unkomprimierte Zeichenfolge, gefolgt von der komprimierten Zeichenfolge.

3. Lösungspraxis

Im Folgenden sind einige verwandte Funktionen des Feldkomprimierungsschemas aufgeführt:

Komprimierungsfunktion

COMPRESS()

Dekomprimierungsfunktion

UNCOMPRESS()

Zeichenfolgenlängenfunktion

LENGTH()

Unkomprimierte Zeichenfolgenlängenfunktion

UNCOMPRESSED_LENGTH()

Praktische Schritte:

a. Erstellen Sie eine Testtabelle

Tabelle erstellen, wenn nicht vorhanden `test`.`test_compress` (

`id` int unsigned NICHT NULL AUTO_INCREMENT KOMMENTAR 'ID',

`content` blob NOT NULL COMMENT 'content column',

PRIMÄRSCHLÜSSEL (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Testtabelle komprimieren';

b. Komprimierte Daten in die Netzliste einfügen

in „Test“ einfügen. „test_compress“ (Inhalt) Werte (KOMPRIMIERT (WIEDERHOLE („a“, 1000)));

c. Komprimierte Daten lesen

Wählen Sie UNCOMPRESS(Inhalt) aus „Test“. „test_compress“;

d. Abfrage der entsprechenden Länge und des entsprechenden Inhalts

4. Kompressionseffekt

Aus dem obigen Screenshot können wir erkennen, dass der Komprimierungseffekt relativ gut ist. Bei Text, Char, Varchr, Blob usw. ist der Komprimierungseffekt umso besser, je mehr wiederholte Daten vorhanden sind.

3. InnoDB-Tabellenkomprimierungslösung

1. Anwendbare Szenarien

Komprimierte Tabellen werden im Allgemeinen in Szenarien verwendet, in denen die Datenmenge zu groß ist, der Speicherplatz nicht ausreicht, die Last hauptsächlich in der E/A reflektiert wird und die CPU des Servers über mehr freie Kapazität verfügt.

2. Einführung in die Tabellenkomprimierung a. Warum ist eine Komprimierung erforderlich?

Derzeit unterstützen viele Tabellen die Komprimierung, beispielsweise Myisam, InnoDB, TokuDB und MyRocks. Da die Verwendung von InnoDB keine Änderungen erfordert, für die Online-Umgebung vollständig transparent ist und über eine sehr ausgereifte Komprimierungslösung verfügt, wird hier nur InnoDB ausführlich beschrieben. Die Komprimierungsschemata für TokuDB und MyRocks werden im MySQL-Komprimierungsschema (Teil 2) beschrieben.

Als SSDs noch nicht weit verbreitet waren, waren die Datenbanken fast vollständig IO-lastig. Als die CPU über viel freie Kapazität verfügte, war der Engpass bei der Festplatten-IO bereits deutlich spürbar. Durch die Speicherung großer Datenmengen, insbesondere Protokolldaten und Überwachungsdaten, wächst der Speicherplatz schnell an. Auch unzureichender Festplattenspeicher ist in vielen Unternehmen ein Problem. Eine bessere Methode wurde entwickelt: Durch Komprimierung wird der Speicherplatzbedarf verringert und E/A sowie Bandbreite optimiert, indem eine kleine Menge an CPU-Ressourcen geopfert wird. Insbesondere für das Geschäft des Mehr- und Wenigerlesens.

Nach der Einführung der SSD wurde die E/A-Last der Datenbank reduziert, das Problem des Speicherplatzes war jedoch immer noch nicht vollständig gelöst. Aus diesem Grund werden komprimierte Tabellen noch immer häufig verwendet. Aus diesem Grund unterstützen so viele Engines die Komprimierung. InnoDB unterstützt Komprimierung seit MySQL 5.5, aber die Komprimierungsrate ist relativ niedrig und liegt normalerweise bei etwa 50 %. Die Komprimierungsrate von tokuDB kann etwa 80 % erreichen, die von MyRocks etwa 70 %.

Hinweis: Die Komprimierungsrate hängt stark von der Zusammensetzung der gespeicherten Daten ab. Nicht alle Daten können die oben genannte Komprimierungsrate erreichen. Wenn die meisten Daten aus Zeichenfolgen bestehen und viele Daten wiederholt werden, ist die Komprimierungsrate sehr gut.

b. Einführung in die InnoDB-Komprimierung

Voraussetzung für die Verwendung der InnoDB-Komprimierung ist, dass der Parameter innodb_file_per_table aktiviert und der Parameter innodb_file_format auf Barracuda eingestellt ist.

Sie können ROW_FORMAT=COMPRESSED verwenden, um eine Tabelle zu erstellen oder zu ändern und die Komprimierung für InnoDB zu aktivieren. Wenn KEY_BLOCK_SIZE nicht angegeben ist, beträgt der Standardwert die Hälfte von innodb_page_size. Sie können die Komprimierung für InnoDB auch aktivieren, indem Sie KEY_BLOCK_SIZE=n angeben. n kann 1, 2, 4, 8 oder 16 in K sein. Je kleiner der Wert von n, desto höher die Komprimierungsrate und desto mehr CPU-Ressourcen werden verbraucht. Beachten Sie, dass die Komprimierung für 32K- oder 64K-Seiten nicht unterstützt wird. Wenn die Komprimierung aktiviert ist, werden auch die Indexdaten komprimiert.

Sie können die Komprimierungsstufe auch festlegen, indem Sie innodb_compression_level von 1 bis 9 anpassen, wobei 6 die Standardeinstellung ist. Je niedriger der Pegel, desto höher die Komprimierungsrate, es werden jedoch auch mehr CPU-Ressourcen benötigt.

c. Komprimierungsalgorithmus

Die InnoDB-Komprimierung basiert auf der bekannten zlib-Bibliothek und verwendet den L777-Komprimierungsalgorithmus, der ausgereift und effizient bei der Reduzierung der Datengröße und CPU-Auslastung ist. Gleichzeitig ist dieser Algorithmus verlustfrei, sodass die ursprünglichen unkomprimierten Daten immer aus der komprimierten Datei rekonstruiert werden können. Das Implementierungsprinzip von LZ777 besteht darin, die Seriennummer wiederholter Daten zu finden und sie dann zu komprimieren, sodass das Datenmuster die Komprimierungseffizienz bestimmt. Im Allgemeinen können Benutzerdaten um mehr als 50 % komprimiert werden.

d. Umgang mit komprimierten Tabellen im Pufferpool

Im Pufferpool buffer_pool werden komprimierte Daten auf Seiten der Größe KEY_BLOCK_SIZE gespeichert. Wenn Sie komprimierte Daten extrahieren oder die den komprimierten Daten entsprechende Spalte aktualisieren möchten, wird eine unkomprimierte Seite erstellt, um die Daten zu dekomprimieren. Nachdem die Datenaktualisierung abgeschlossen ist, werden die Daten der komprimierten Seite auf die komprimierte Seite neu geschrieben. Wenn nicht genügend Speicher vorhanden ist, wirft MySQL die entsprechenden unkomprimierten Seiten aus. Wenn Sie also die Komprimierung aktivieren, kann Ihr Pufferpool komprimierte und unkomprimierte Seiten oder nur komprimierte Seiten enthalten. Möglicherweise müssen Sie Ihren Pufferpool buffer_pool jedoch dennoch vergrößern, damit er sowohl komprimierte als auch unkomprimierte Seiten speichern kann.

MySQL verwendet einen LRU-Algorithmus (Least-Recently-Used), um zu bestimmen, welche Seiten im Speicher behalten und welche entfernt werden sollen. So verbleiben „Hot Data“ häufiger im Speicher. Beim Zugriff auf komprimierte Tabellen verwendet MySQL einen adaptiven LRU-Algorithmus, um ein Gleichgewicht zwischen komprimierten und unkomprimierten Seiten im Speicher aufrechtzuerhalten. Wenn die E/A-Last des Systems hoch ist, neigt dieser Algorithmus dazu, unkomprimierte Seiten zu entfernen, um mehr Platz für komprimiertere Seiten zu schaffen. Wenn die CPU-Auslastung des Systems hoch ist, neigt MySQL dazu, sowohl komprimierte als auch unkomprimierte Seiten zu entfernen. Zu diesem Zeitpunkt wird mehr Speicher zum Beibehalten heißer Daten verwendet, wodurch Dekomprimierungsvorgänge reduziert werden.

e. So beurteilen Sie, ob KEY_BLOCK_SIZE angemessen ist

Um die Auswirkungen komprimierter Tabellen auf die Performance besser zu verstehen, gibt es in der Information Schema-Bibliothek entsprechende Tabellen, mit denen sich Kennzahlen wie Speicherverbrauch und Komprimierungsrate auswerten lassen. INNODB_CMP sammelt Informationen über den Gesamtstatus eines bestimmten Typs von KEY_BLOCK_SIZE-komprimierten Tabellen und fasst Statistiken für alle KEY_BLOCK_SIZE-komprimierten Tabellen zusammen. Die Tabelle INNODB_CMP_PER_INDEX sammelt Komprimierungsinformationen für jede Tabelle und jeden Index. Diese Informationen sind hilfreich, um die Komprimierungseffizienz einer Tabelle zu einem bestimmten Zeitpunkt zu bewerten oder Leistungsprobleme zu diagnostizieren. Die Sammlung der Tabelle INNODB_CMP_PER_INDEX wirkt sich auf die Systemleistung aus. Sie muss mit der Option innodb_cmp_per_index_enabled aufgezeichnet werden. In einer Produktionsumgebung sollte sie am besten nicht aktiviert werden.

Wir können die Komprimierungsfehler der INNODB_CMP-Tabelle beobachten. Wenn es viele Fehler gibt, müssen wir KEY_BLOCK_SIZE erhöhen. Es wird generell empfohlen, KEY_BLOCK_SIZE auf 8 zu setzen.

3. Lösungspraxis

a. Legen Sie die Parameter innodb_file_per_table und innodb_file_format fest

SETZEN SIE GLOBAL innodb_file_per_table=1;SETZEN SIE GLOBAL innodb_file_format=Barracuda;

b. Erstellen Sie die entsprechende Komprimierungstabelle

Wenn die Tabelle bereits existiert, ändern Sie sie mit „ALTER“. Der SQL-Befehl lautet wie folgt:

ALTER TABLE compress_test ROW_FORMAT=KOMPRESSIERT KEY_BLOCK_SIZE=8;

4. Kompressionseffekt

Der Komprimierungseffekt wird durch die Anpassung einer Online-Überwachungstabelle an die komprimierte Dateigröße veranschaulicht. Der Vergleich vor und nach der Komprimierung sieht wie folgt aus: