Get best of the week

Sparen Sie viel Geld bei großen Mengen in PostgreSQL

Wenn Sie das Thema der Aufzeichnung großer Datenströme fortsetzen, das im vorherigen Artikel zur Partitionierung angesprochen wurde , betrachten wir hier die Möglichkeiten, wie Sie die in PostgreSQL gespeicherte "physische" Größe reduzieren können , und deren Auswirkungen auf die Serverleistung.

Es geht um TOAST-Einstellungen und Datenausrichtung . "Im Durchschnitt" sparen diese Methoden nicht zu viele Ressourcen, ohne jedoch den Anwendungscode zu ändern.


Unsere Erfahrung hat sich in dieser Hinsicht jedoch als sehr produktiv erwiesen, da das Repository für fast jede Überwachung naturgemäß nur in Bezug auf aufgezeichnete Daten nur angehängt wird . Und wenn Sie daran interessiert sind, wie Sie einer Datenbank beibringen können, auf eine Festplatte zu schreiben, anstatt 200 MB / s halb so viel - ich bitte um einen Schnitt.

Kleine Geheimnisse von Big Data


Entsprechend dem Profil unseres Dienstes erhält er regelmäßig Textpakete aus den Protokollen .

Und da der VLSI-Komplex , dessen Datenbanken wir überwachen, ein Mehrkomponentenprodukt mit komplexen Datenstrukturen ist, werden die Abfragen zur Erzielung maximaler Leistung von solchen „Multi-Volumes“ mit komplexer algorithmischer Logik erhalten . Das Volumen jeder einzelnen Instanz der Anforderung oder des daraus resultierenden Ausführungsplans im Protokoll, das zu uns kommt, ist also ziemlich "durchschnittlich".

Schauen wir uns die Struktur einer der Tabellen an, in die wir die "Rohdaten" schreiben - das heißt, hier ist der Originaltext aus dem Protokolleintrag:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt --  
    date
, data --  
    text
, PRIMARY KEY(pack, recno)
);

Solch eine typische Platte (natürlich bereits unterteilt, daher handelt es sich um eine Abschnittsvorlage), bei der der Text am wichtigsten ist. Manchmal ziemlich voluminös.

Denken Sie daran, dass die „physische“ Größe eines Datensatzes in PG nicht mehr als eine Datenseite belegen kann, die „logische“ Größe jedoch eine ganz andere Sache ist. Um einen Volumenwert (varchar / text / bytea) in das Feld zu schreiben, wird die TOAST-Technologie verwendet :
PostgreSQL verwendet eine feste Seitengröße (normalerweise 8 KB) und erlaubt nicht, dass Tupel mehrere Seiten umfassen. Daher ist es unmöglich, sehr große Feldwerte direkt zu speichern. Um diese Einschränkung zu überwinden, werden große Feldwerte komprimiert und / oder in mehrere physikalische Zeilen aufgeteilt. Dies geschieht vom Benutzer unbemerkt und wirkt sich geringfügig auf den größten Teil des Servercodes aus. Diese Methode ist als TOAST bekannt ...

Tatsächlich wird für jede Tabelle mit "potenziell großen" Feldern automatisch eine gepaarte Tabelle mit dem "Slicing" jedes "großen" Datensatzes in 2-KB-Segmenten erstellt:

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Das heißt, wenn wir eine Zeile mit einem "großen" Wert schreiben müssen data, wird der reale Datensatz nicht nur in der Haupttabelle und ihrer PK, sondern auch in TOAST und seiner PK auftreten .

Reduzieren Sie den TOAST-Effekt


Aber die meisten Datensätze hier sind immer noch nicht so groß, sie sollten in 8 KB passen - wie würden Sie das sparen? ..

Hier hilft uns das Attribut STORAGEin der Tabellenspalte:
  • EXTENDED ermöglicht sowohl Komprimierung als auch separate Speicherung. Dies ist die Standardoption für die meisten TOAST-kompatiblen Datentypen. Zuerst wird versucht, eine Komprimierung durchzuführen, dann wird sie außerhalb der Tabelle gespeichert, wenn die Zeile noch zu groß ist.
  • MAIN ermöglicht die Komprimierung, jedoch keine separate Speicherung. (Tatsächlich wird für solche Spalten jedoch eine separate Speicherung durchgeführt, jedoch nur als letztes Mittel , wenn es keine andere Möglichkeit gibt, die Zeile so zu verkleinern, dass sie auf die Seite passt.)
Genau das brauchen wir für den Text - drücken Sie ihn so weit wie möglich zusammen und setzen Sie ihn in TOAST, auch wenn er überhaupt nicht passt . Sie können dies direkt "on the fly" mit einem Befehl tun:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Wie man den Effekt bewertet


Da sich der Datenfluss jeden Tag ändert, können wir keine absoluten Zahlen vergleichen. Relativ gesehen ist es jedoch umso besser , je kleiner der Anteil ist, den wir in TOAST aufgezeichnet haben. Es besteht jedoch die Gefahr, dass der Index umso „breiter“ wird, je mehr wir das „physische“ Volumen jedes einzelnen Datensatzes haben, da wir mehr Datenseiten abdecken müssen.

Abschnitt vor Änderungen :
heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

Abschnitt nach Änderungen :
heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

Tatsächlich haben wir 2 Mal seltener mit dem Schreiben in TOAST begonnen , wodurch nicht nur die Festplatte, sondern auch die CPU entladen wurde:



Ich stelle fest, dass wir die Festplatte auch weniger "lesen", nicht nur "schreiben" - denn wenn Sie einen Datensatz in eine Tabelle einfügen, müssen Sie auch einen Teil des Baums jedes der Indizes "subtrahieren", um seine zukünftige Position in ihnen zu bestimmen.

Wer auf PostgreSQL 11 lebt gut


Nach dem Upgrade auf PG11 haben wir beschlossen, TOAST weiter zu optimieren, und festgestellt, dass ab dieser Version der Parameter für die Konfiguration verfügbar wurde toast_tuple_target:
Der TOAST-Verarbeitungscode wird nur ausgelöst, wenn der in der Tabelle zu speichernde Zeilenwert größer als die Bytes TOAST_TUPLE_THRESHOLD ist (normalerweise 2 KB). TOAST-Code komprimiert und / oder verschiebt Feldwerte aus der Tabelle, bis der Zeilenwert kleiner als TOAST_TUPLE_TARGET-Bytes ist (variabel, normalerweise auch 2 KB) oder es unmöglich wird, die Größe zu reduzieren.
Wir haben entschieden, dass die Daten, die wir normalerweise haben, entweder "sehr kurz" oder sofort "sehr lang" sind, und haben uns daher entschlossen, uns auf den niedrigstmöglichen Wert zu beschränken:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Mal sehen, wie sich die neuen Einstellungen auf das Laden der Festplatte nach der Migration auswirken:


Nicht schlecht! Die durchschnittliche Warteschlange für eine Festplatte wurde um das 1,5-fache und die Festplattenbelegung um 20 Prozent reduziert ! Aber vielleicht hat das irgendwie die CPU beeinflusst?


Zumindest wurde es definitiv nicht schlimmer. Es ist jedoch schwierig zu beurteilen, ob selbst solche Volumes die durchschnittliche CPU-Auslastung nicht über 5% erhöhen können .

Bei einem Positionswechsel ändert sich die Summe ...!


Wie Sie wissen, spart ein Penny einen Rubel, und bei einem Speichervolumen von etwa 10 TB / Monat kann bereits eine kleine Optimierung einen guten Gewinn bringen. Aus diesem Grund haben wir auf die physikalische Struktur unserer Daten hingewiesen - wie genau die „Felder“ im Datensatz jeder Tabelle angeordnet sind.

Aufgrund der Datenausrichtung wirkt sich dies direkt auf das resultierende Volume aus :
Viele Architekturen bieten Datenausrichtung über Maschinenwortgrenzen hinweg. Beispielsweise werden auf einem x86-32-Bit-System Ganzzahlen (Integer-Typ, belegt 4 Byte) an der Grenze von 4-Byte-Wörtern sowie Gleitkommazahlen mit doppelter Genauigkeit (Typ mit doppelter Genauigkeit, 8 Byte) ausgerichtet. Bei einem 64-Bit-System werden Doppelwerte am Rand von 8-Byte-Wörtern ausgerichtet. Dies ist ein weiterer Grund für die Inkompatibilität.

Aufgrund der Ausrichtung hängt die Größe der Tabellenzeile von der Reihenfolge der Felder ab. Normalerweise ist dieser Effekt nicht sehr auffällig, kann aber in einigen Fällen zu einer signifikanten Vergrößerung führen. Wenn Sie beispielsweise Felder vom Typ char (1) und Integer gemischt platzieren, werden in der Regel 3 Bytes umsonst verschwendet.

Beginnen wir mit synthetischen Modellen:

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46

Woher kam das zusätzliche Bytepaar im ersten Fall? Alles ist einfach - ein 2-Byte-Smallint wird an einer 4-Byte-Grenze vor dem nächsten Feld ausgerichtet, und wenn es das letzte ist, gibt es nichts und es besteht keine Notwendigkeit, es auszurichten.

Theoretisch ist alles in Ordnung und Sie können die Felder nach Belieben neu anordnen. Lassen Sie uns am Beispiel einer der Tabellen, deren täglicher Abschnitt 10-15 GB benötigt, die realen Daten überprüfen.

Quellstruktur:

CREATE TABLE public.plan_20190220
(
--  from table plan:  pack uuid NOT NULL,
--  from table plan:  recno smallint NOT NULL,
--  from table plan:  host uuid,
--  from table plan:  ts timestamp with time zone,
--  from table plan:  exectime numeric(32,3),
--  from table plan:  duration numeric(32,3),
--  from table plan:  bufint bigint,
--  from table plan:  bufmem bigint,
--  from table plan:  bufdsk bigint,
--  from table plan:  apn uuid,
--  from table plan:  ptr uuid,
--  from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

Der Abschnitt nach dem Ändern der Reihenfolge der Spalten ist genau das gleiche Feld, nur die Reihenfolge ist unterschiedlich :

CREATE TABLE public.plan_20190221
(
--  from table plan:  dt date NOT NULL,
--  from table plan:  ts timestamp with time zone,
--  from table plan:  pack uuid NOT NULL,
--  from table plan:  recno smallint NOT NULL,
--  from table plan:  host uuid,
--  from table plan:  apn uuid,
--  from table plan:  ptr uuid,
--  from table plan:  bufint bigint,
--  from table plan:  bufmem bigint,
--  from table plan:  bufdsk bigint,
--  from table plan:  exectime numeric(32,3),
--  from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

Das Gesamtvolumen des Abschnitts wird durch die Anzahl der „Fakten“ bestimmt und hängt nur von externen Prozessen ab. Daher teilen wir die Größe von heap ( pg_relation_size) durch die Anzahl der darin enthaltenen Datensätze - das heißt, wir erhalten die durchschnittliche Größe des tatsächlich gespeicherten Datensatzes :


Minus 6% des Volumens , ausgezeichnet!

Aber natürlich ist nicht alles so rosig - weil wir in den Indizes die Reihenfolge der Felder nicht ändern können und daher "im Allgemeinen" ( pg_total_relation_size) ...


... schließlich haben sie hier 1,5% gespart , ohne eine einzige Codezeile zu ändern. Ja ja!



Ich stelle fest, dass die obige Anordnung der Felder nicht die optimale ist. Weil einige Feldblöcke aus ästhetischen Gründen nicht schon „auseinandergerissen“ werden möchten - zum Beispiel ein Paar (pack, recno), das für diese Tabelle PK ist.

Im Allgemeinen ist die Definition der "minimalen" Feldanordnung eine ziemlich einfache "erschöpfende" Aufgabe. Daher können Sie mit Ihren Daten noch bessere Ergebnisse erzielen als mit unseren - probieren Sie es aus!

More articles:


All Articles