🐩 🥥 🌾 In 4 Sekunden um die Welt im Columnstore (Teil 1) 👰🏽 🎲 🐠

In diesem Artikel werde ich erwägen, die Geschwindigkeit von Berichten zu erhöhen. Mit einem Bericht meine ich jede Abfrage an eine Datenbank, die Aggregatfunktionen verwendet. Außerdem werde ich auf Fragen eingehen, die sich auf die Ressourcen beziehen, die für die Erstellung und Unterstützung von Berichten sowohl von Menschen als auch von Maschinen aufgewendet werden.

In den Beispielen werde ich einen Datensatz verwenden, der 52.608.000 Datensätze enthält.

Am Beispiel nicht schwieriger analytischer Reserven werde ich zeigen, dass selbst ein schwacher Computer ohne großen Aufwand zu einem guten Werkzeug für die Analyse einer „anständigen“ Datenmenge werden kann.

Nachdem wir keine komplizierten Experimente durchgeführt haben, werden wir feststellen, dass eine reguläre Tabelle keine geeignete Quelle für analytische Abfragen ist.

Wenn der Leser die Abkürzungen OLTP und OLAP leicht entschlüsseln kann, kann es sinnvoll sein, direkt zum Abschnitt Columnstore zu wechseln

Zwei Ansätze zum Arbeiten mit Daten

Hier werde ich mich kurz fassen, weil Zu diesem Thema gibt es im Internet mehr als genug Informationen.

Auf höchster Ebene gibt es also nur zwei Ansätze für die Arbeit mit Daten: OLTP und OLAP.

OLTP - kann als sofortige Transaktionsverarbeitung übersetzt werden. In der Tat sprechen wir über die Online-Verarbeitung von kurzen Transaktionen, die mit einer kleinen Datenmenge arbeiten. Zum Beispiel das Aufzeichnen, Aktualisieren oder Löschen einer Bestellung. In den allermeisten Fällen handelt es sich bei einer Bestellung um eine äußerst kleine Datenmenge, bei deren Verarbeitung Sie keine Angst vor den langen Sperren haben müssen, die das moderne RDBMS auferlegt.

OLAP - kann als analytische Verarbeitung einer großen Anzahl von Transaktionen gleichzeitig übersetzt werden. Jeder Bericht verwendet diesen speziellen Ansatz, da der Bericht in den allermeisten Fällen zusammenfassende, aggregierte Zahlen für bestimmte Abschnitte erstellt.

Jeder Ansatz hat seine eigene Technologie. Für OLTP ist es beispielsweise PostgreSQL und für OLAP Microsoft SQL Server Analysis Services. Während PostgresSQL ein bekanntes Format zum Speichern von Daten in Tabellen verwendet, wurden für OLAP verschiedene Formate erfunden. Dies sind mehrdimensionale Tabellen, ein mit Schlüssel-Wert-Paaren gefüllter Eimer und mein bevorzugter Spaltenspeicher. Über letzteres im Detail weiter unten.

Warum sind zwei Ansätze erforderlich?

Es wurde festgestellt, dass jedes Data Warehouse früher oder später zwei Arten von Belastungen ausgesetzt ist: häufiges Lesen (natürlich auch Schreiben und Aktualisieren) extrem kleiner Datenmengen und seltenes Lesen, aber sehr große Datenmengen. In der Tat ist dies eine Aktivität, zum Beispiel der Abendkasse und des Leiters. Die Kasse, die den ganzen Tag arbeitet, füllt den Speicher mit kleinen Datenblöcken, während am Ende des Tages das angesammelte Volumen, wenn das Geschäft gut läuft, eine beeindruckende Größe erreicht. Im Gegenzug möchte der Manager am Ende des Tages wissen, wie viel Geld die Abendkasse pro Tag verdient hat.

In OLTP gibt es also Tabellen und Indizes. Diese beiden Tools eignen sich hervorragend zum Aufzeichnen von Kassenaktivitäten mit allen Details. Indizes bieten eine schnelle Suche nach einer zuvor aufgezeichneten Bestellung, sodass das Ändern einer Bestellung einfach ist. Um jedoch die Bedürfnisse des Leiters zu befriedigen, müssen wir die gesamte pro Tag gesammelte Datenmenge berücksichtigen. Darüber hinaus benötigt der Manager in der Regel nicht alle Details aller Bestellungen. Was er wirklich wissen muss, ist, wie viel Geld die Abendkasse im Allgemeinen verdient hat. Es spielt keine Rolle, wo sich das Ticketbüro befand, wann es eine Mittagspause gab, wer dafür arbeitete usw. OLAP existiert dann, so dass das System in kurzer Zeit die Frage beantworten kann, wie viel das Unternehmen insgesamt verdient hat, ohne jede Bestellung und alle Details nacheinander zu lesen. Kann OLAP dieselben Tabellen und Indizes wie OLTP verwenden? Die Antwort ist nein, zumindest sollte es nicht. Erstens, weil OLAP nicht alle in den Tabellen aufgezeichneten Details benötigt. Dieses Problem wird gelöst, indem Daten in anderen Formaten als zweidimensionalen Tabellen gespeichert werden. Zweitens werden die analysierten Informationen häufig über verschiedene Tabellen verteilt, was zu mehreren Assoziationen führt, einschließlich Assoziationen vom Typ Self-Join. Um dieses Problem zu lösen, entwickeln sie in der Regel ein spezielles Datenbankschema. Dieses Schema ist sowohl für das OLAP-Laden als auch für das normale normalisierte Schema für das OLTP-Laden optimiert.

Was passiert, wenn OLAP ein OLTP-Schema verwendet?

Tatsächlich habe ich diesen Abschnitt eingeführt, damit dieser Artikel eindeutig meine eigenen Anforderungen an das Format eines solchen Materials erfüllt, d. H. Problem, Lösung, Schlussfolgerung.

Wir listen eine Reihe von Nachteilen bei der Verwendung von OLTP-Schemata für die Datenanalyse auf.

Zu viele Indizes.

Oft müssen Sie spezielle Indizes erstellen, um Berichte zu unterstützen. Diese Indizes implementieren ein OLAP-Datenspeicherschema. Sie werden vom OLTP-Teil der Anwendung nicht verwendet, während sie belastet werden, ständige Unterstützung erfordern und Speicherplatz beanspruchen.
Die Menge der gelesenen Daten überschreitet die erforderliche Menge.
Fehlen eines klaren Datenschemas.

Tatsache ist, dass die von Berichten in einer einzigen Form übermittelten Informationen häufig in verschiedenen Tabellen verteilt sind. Solche Informationen erfordern eine ständige Transformation im laufenden Betrieb. Das einfachste Beispiel ist die Höhe der Einnahmen, die aus Bargeld und Sachgeld besteht. Ein weiteres bemerkenswertes Beispiel sind Datenhierarchien. Weil Die Anwendungsentwicklung ist fortschrittlich und es ist nicht immer bekannt, was in Zukunft benötigt wird. Dieselbe Bedeutungshierarchie kann in verschiedenen Tabellen gespeichert werden. Während die On-the-Fly-Erfassung in OLAP aktiv genutzt wird, sind dies etwas andere Dinge.
Übermäßige Komplexität der Abfragen.

Weil Ein OLTP-Schema unterscheidet sich von einem OLAP. Es wird eine stark verwandte Softwareschicht benötigt, die das OLTP-Datenschema in die richtige Form bringt.
Komplexität von Support, Debugging und Entwicklung.

Im Allgemeinen können wir sagen, dass es umso schwieriger ist, die Codebasis in einem gesunden Zustand zu halten, je komplexer sie ist. Dies ist ein Axiom.
Die Komplexität der Testabdeckung.

Viele Kopien sind aufgrund von Diskussionen darüber, wie eine Datenbank mit allen Testskripten gefüllt werden kann, fehlerhaft. Es ist jedoch besser zu sagen, dass die Aufgabe, Tests durchzuführen, durch ein einfacheres Datenschema um ein Vielfaches vereinfacht wird.
Endloses Performance-Debugging.

Es besteht eine hohe Wahrscheinlichkeit, dass der Benutzer einen Bericht bestellt, der für den Datenbankserver „schwer“ ist. Diese Wahrscheinlichkeit nimmt mit der Zeit zu. Es sollte beachtet werden, dass OLAP ebenfalls für dieses Problem anfällig ist, aber im Gegensatz zu OLTP ist die OLAP-Ressource in dieser Angelegenheit viel höher.

Columnstore

Dieser Artikel konzentriert sich auf das Columnstore-Speicherformat, jedoch ohne Details auf niedriger Ebene. Andere oben erwähnte Formate verdienen ebenfalls Aufmerksamkeit, aber dies ist ein Thema für einen anderen Artikel.

Tatsächlich ist das Columnstore-Format seit 30 Jahren bekannt. Es wurde jedoch bis vor kurzem nicht im RDBMS implementiert. Das Wesentliche beim Spaltenspeicher ist, dass Daten nicht in Zeilen, sondern in Spalten gespeichert werden. Das heißt, Auf einer Seite (alle bekannt 8 KB) zeichnet der Server nur Daten eines Feldes auf. Und so mit jedem Feld in der Tabelle der Reihe nach. Dies ist notwendig, damit Sie keine zusätzlichen Informationen lesen müssen. Stellen wir uns eine Tabelle mit 10 Feldern und einer Abfrage vor, in der nur ein Feld in der SELECT-Anweisung angegeben ist. Wenn es sich um eine reguläre Tabelle handelt, die in einem zeilenbasierten Format gespeichert ist, muss der Server alle 10 Felder lesen, gibt jedoch gleichzeitig nur eines zurück. Es stellte sich heraus, dass der Server neunmal mehr Informationen las als nötig. Columnstore löst dieses Problem vollständig, weil Im Speicherformat können Sie nur ein geordnetes Feld lesen. All dies geschieht, weil die Speichereinheit in einem RDBMS eine Seite ist. Das heißt, Der Server schreibt und liest immer mindestens eine Seite. Die Frage ist nur, wie viele Felder darauf vorhanden sind.

Wie Columnstore wirklich helfen kann

Um dies zu beantworten, muss man genaue Zahlen haben. Lass sie uns holen. Aber welche Zahlen können ein genaues Bild ergeben?

Die Menge an Speicherplatz.
Abfrageleistung.
Fehlertoleranz.
Einfache Implementierung.
Welche neuen Fähigkeiten sollte ein Entwickler haben, um mit neuen Strukturen zu arbeiten?

Speicherplatz

Lassen Sie uns eine einfache Tabelle erstellen, sie mit Daten füllen und überprüfen, wie viel Speicherplatz dafür benötigt wird.

create foreign table cstore_table ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz');

Wie Sie bemerkt haben, habe ich eine externe Tabelle erstellt. Tatsache ist, dass PostgreSQL keine integrierte Columnstore-Unterstützung bietet. PostgreSQL verfügt jedoch über ein leistungsstarkes System für Erweiterungen. Eine davon ermöglicht das Erstellen von Spaltenspeichertabellen. Links am Ende des Artikels.

pglz - teilt der Erweiterung mit, dass die Daten mithilfe des in PostgreSQL integrierten Algorithmus komprimiert werden sollen;
trd - Transaktionszeit;
op, it, wh - analytische Schnitte oder Messungen;
m1, m2, m3, m4, m5 - numerische Indikatoren oder Maße;

Fügen wir eine „anständige“ Datenmenge ein und sehen, wie viel Speicherplatz auf der Festplatte benötigt wird. Gleichzeitig überprüfen wir die Leistung des Einsatzes. Weil Ich habe meine Experimente auf einen Laptop zu Hause gestellt, ich bin ein bisschen organisch in der Datenmenge. Außerdem, was sogar gut ist, werde ich die Festplatte verwenden, auf der das Gastbetriebssystem Fedora 30 ausgeführt wird. Betriebssystemhost - Windows 10 Home Edition. Prozessor Intel Core 7. Das Gastbetriebssystem erhielt 4 GB RAM. PostgreSQL-Version - PostgreSQL 10.10 auf x86_64-pc-linux-gnu, kompiliert von gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64-Bit. Ich werde mit einem Datensatz mit der Anzahl der Datensätze 52 608 000 experimentieren.

 explain (analyze) insert into cstore_table select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d;