Im Zusammenhang mit dem Thema der Aufzeichnung großer Datenmengen, das , betrachten wir hier Möglichkeiten, wie man die „physikalische“ Größe des gespeicherten in PostgreSQL verringern kann und ihren Einfluss auf die Serverleistung.
Es geht um TOAST-Einstellungen und Datenausrichtung.Im Durchschnitt ermöglichen diese Methoden nicht allzu viele Ressourcen zu sparen, jedoch — ganz ohne Modifizierung des Anwendungscodes.
Unserer Erfahrung nach war dies in dieser Hinsicht jedoch sehr produktiv, da das Speichersystem nahezu jeder Überwachung aufgrund seiner Natur größtenteils append-only ist, was die geschriebenen Daten betrifft. Und falls Sie sich fragen, wie man die Datenbank anweisen kann, auf die Festplatte zu schreiben, anstatt 200MB/s um die Hälfte weniger — bitte weiterlesen.
Kleine Geheimnisse großer Daten
Im Rahmen unserer , erhält er regelmäßig aus den Protokollen Textpakete..
Und da , dessen Datenbanken wir überwachen, ein vielschichtiges Produkt mit komplexen Datenstrukturen ist, ergeben sich auch die Anfragen , um eine maximale Leistung zu erzielen, als durchaus solche So ist auch das Volumen jeder einzelnen Instanz von Anfragen oder des resultierenden Ausführungsplans in den Protokollen, die zu uns gelangen, „im Durchschnitt“ ausreichend groß.
Schauen wir uns die Struktur einer der Tabellen an, in die wir „rohe“ Daten schreiben — das heißt, direkt den ursprünglichen Text aus dem Protokoll:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- Abschnittsschlüssel
date
, data -- das Wichtigste
text
, PRIMARY KEY(pack, recno)
);Eine typische solche Tabelle (bereits partitioniert, selbstverständlich, daher ist dies ein Abschnitts-Template), in der das Wichtigste der Text ist. Manchmal recht umfangreich.
Erinnern wir uns daran, dass die „physikalische“ Größe eines Eintrags in PG nicht mehr als eine Datenseite einnehmen kann, aber die „logische“ Größe — völlig anders. Um einen umfangreichen Wert (varchar/text/bytea) in einem Feld zu speichern, wird :
PostgreSQL verwendet eine feste Seitengröße (in der Regel 8 KB) und erlaubt es nicht, dass Tupel mehrere Seiten einnehmen. Daher ist es nicht möglich, sehr große Feldwerte direkt zu speichern. Um diese Einschränkung zu überwinden, werden große Feldwerte komprimiert und/oder auf mehrere physische Zeilen aufgeteilt. Dies geschieht für den Benutzer unmerklich und hat nur geringe Auswirkungen auf den Großteil des Servercodes. Diese Methode ist bekannt als TOAST …
Tatsächlich wird für jede Tabelle mit "potenziell großen" Feldern automatisch jeder "großen" Aufzeichnung in Segmente von je 2KB 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 die tatsächliche Speicherung nicht nur in der Haupttabelle und ihrem PK erfolgen, sondern auch in TOAST und ihrem PK..
TOAST-Einfluss verringern
Aber die meisten Aufzeichnungen sind dennoch nicht allzu groß, sollten in 8KB passen — wie können wir dabei sparen?..
Hier kommt uns das Attribut der Spalte der Tabelle zugute:
- EXTENDED erlaubt sowohl Kompression als auch getrennte Speicherung. Dies ist die Standardoption für die meisten Datentypen, die mit TOAST kompatibel sind. Zuerst wird versucht, eine Kompression durchzuführen, danach— wenn die Zeile immer noch zu groß ist — erfolgt die Speicherung außerhalb der Tabelle.
- MAIN erlaubt Kompression, jedoch keine separate Speicherung. (Tatsächlich wird dennoch eine separate Speicherung für solche Spalten durchgeführt, aber nur als letzte Maßnahme, wenn es keine andere Möglichkeit gibt, die Zeile so zu reduzieren, dass sie auf die Seite passt.)
Tatsächlich ist dies genau das, was wir für Texte benötigen — maximal komprimieren, und falls es nicht passt — nach TOAST auslagern.Das kann direkt "on the fly" mit einem Befehl gemacht werden:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Wie man den Effekt bewertet
Da der Datenfluss täglich variiert, können wir keine absoluten Zahlen vergleichen, aber relativ gilt: je kleiner der Anteil, den wir in TOAST geschrieben haben — desto besser. Aber hier besteht die Gefahr — je größer unser "physikalisches" Volumen jeder einzelnen Aufzeichnung ist, desto "breiter" wird der Index, da mehr Seitenabdeckung erforderlich ist.
Abschnitt vor den Änderungen:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Abschnitt nach den Änderungen:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Tatsächlich schreiben wir jetzt dreimal seltener in TOAST., was nicht nur die Festplatte, sondern auch die CPU entlastete:
Ich bemerke, dass wir auch weniger «lesen» von der Festplatte, nicht nur «schreiben» — da beim Einfügen eines Eintrags in eine Tabelle auch Teile des Baums jedes der Indizes «ausgelesen» werden müssen, um die zukünftige Position darin zu bestimmen.
Wer gut mit PostgreSQL 11 leben kann
Nach dem Upgrade auf PG11 haben wir entschieden, das TOAST-«Tuning» fortzusetzen und festgestellt, dass ab dieser Version ein konfigurierbarer Parameter verfügbar ist, :
Der TOAST-Verarbeitungs-Code wird nur aktiv, wenn die Zeilenwertgröße, die in der Tabelle gespeichert werden soll, größer ist als TOAST_TUPLE_THRESHOLD Bytes (in der Regel 2 KB). Der TOAST-Code wird die Feldwerte komprimieren und/oder außerhalb der Tabelle lagern, solange der Zeilenwert nicht kleiner als TOAST_TUPLE_TARGET Bytes (variable Größe, normalerweise ebenfalls 2 KB) wird oder eine Reduzierung nicht mehr möglich ist.
Wir haben beschlossen, dass unsere Daten in der Regel entweder «sehr kurz» oder «sehr lang» sind, daher haben wir uns entschieden, mit dem minimal möglichen Wert zu arbeiten:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Lassen Sie uns sehen, wie sich die neuen Einstellungen auf die Festplattenbelastung nach der Umstellung ausgewirkt haben:
Nicht schlecht! Die durchschnittliche Wartezeit auf die Festplatte ist ungefähr um das 1,5-Fache gesunken, und die «Auslastung» der Festplatte um etwa 20%! Aber vielleicht hat sich das auch auf die CPU ausgewirkt?
Zumindest ist es definitiv nicht schlechter geworden. Es ist jedoch schwer zu sagen, wenn selbst solche Volumina die durchschnittliche CPU-Auslastung nicht über 5%.
Die Summe ändert sich durch den Tausch der Summanden!
Wie bekannt ist, spart der Pfennig den Rubel, und bei unseren Speichervolumina von etwa 10 TB/Monat kann selbst eine kleine Optimierung einen guten Gewinn bringen. Daher haben wir auf die physische Struktur unserer Daten geachtet — wie konkret die Felder innerhalb eines Eintrags in jeder der Tabellen «angeordnet» sind.
Denn aufgrund der wirkt sich dies direkt :
Viele Architekturen sehen eine Ausrichtung der Daten nach den Grenzen maschinenlesbarer Wörter vor. Zum Beispiel werden in einem 32-Bit-x86-System Ganzzahlen (Datentyp integer, der 4 Bytes benötigt) nach einer 4-Byte-Grenze ausgerichtet, ebenso wie Gleitkommazahlen (Datentyp double precision, 8 Bytes). In einem 64-Bit-System werden double-Werte nach der Grenze von 8-Byte-Worten ausgerichtet. Dies ist ein weiterer Grund für die Inkompatibilität.
Aufgrund der Ausrichtung hängt die Größe der Tabellenspalte von der Reihenfolge der Felder ab. In der Regel ist dieser Effekt nicht sehr auffällig, aber in einigen Fällen kann er zu einer erheblichen Größensteigerung führen. Zum Beispiel, wenn man die Felder der Typen char(1) und integer vermischt, gehen in der Regel 3 Bytes verloren.
Lassen Sie uns mit synthetischen Modellen beginnen:
SELECT pg_column_size(ROW(
'0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 Bytes
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 BytesWoher kommen die zusätzlichen Bytes im ersten Fall? Ganz einfach — Ein 2-Byte-smallint wird auf eine 4-Byte-Grenze ausgerichtet vor dem nächsten Feld, und wenn es das letzte ist, gibt es nichts mehr auszurichten und keinen Grund dafür.
In der Theorie ist alles gut und man kann die Felder beliebig verschieben. Lassen Sie uns dies mit echten Daten überprüfen, anhand eines Beispiels einer Tabelle, deren täglicher Abschnitt 10-15 GB umfasst.
Ursprüngliche Struktur:
CREATE TABLE public.plan_20190220
(
-- Abgeleitet von Tabelle plan: pack uuid NOT NULL,
-- Abgeleitet von Tabelle plan: recno smallint NOT NULL,
-- Abgeleitet von Tabelle plan: host uuid,
-- Abgeleitet von Tabelle plan: ts timestamp with time zone,
-- Abgeleitet von Tabelle plan: exectime numeric(32,3),
-- Abgeleitet von Tabelle plan: duration numeric(32,3),
-- Abgeleitet von Tabelle plan: bufint bigint,
-- Abgeleitet von Tabelle plan: bufmem bigint,
-- Abgeleitet von Tabelle plan: bufdsk bigint,
-- Abgeleitet von Tabelle plan: apn uuid,
-- Abgeleitet von Tabelle plan: ptr uuid,
-- Abgeleitet von Tabelle 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 der Änderung der Spaltenreihenfolge – genau die gleichen Felder, nur in anderer Reihenfolge:
CREATE TABLE public.plan_20190221
(
-- Abgeleitet von Tabelle plan: dt date NOT NULL,
-- Abgeleitet von Tabelle plan: ts timestamp with time zone,
-- Abgeleitet von Tabelle plan: pack uuid NOT NULL,
-- Abgeleitet von Tabelle plan: recno smallint NOT NULL,
-- Abgeleitet von Tabelle plan: host uuid,
-- Abgeleitet von Tabelle plan: apn uuid,
-- Abgeleitet von Tabelle plan: ptr uuid,
-- Abgeleitet von Tabelle plan: bufint bigint,
-- Abgeleitet von Tabelle plan: bufmem bigint,
-- Abgeleitet von Tabelle plan: bufdsk bigint,
-- Abgeleitet von Tabelle plan: exectime numeric(32,3),
-- Abgeleitet von Tabelle 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, deshalb teilen wir die Größe des Heaps (pg_relation_size) zur Anzahl der Einträge darin – also erhalten wir die durchschnittliche Größe des tatsächlich gespeicherten Eintrags:
Minus 6 % des Volumens, hervorragend!
Aber natürlich ist nicht alles so rosig – denn können wir die Reihenfolge der Felder in den Indizes nicht ändern, und deshalb "insgesamt" (pg_total_relation_size)…
… letztendlich aber auch hier 1,5 % gespart, ohne eine Zeile Code zu ändern. Tatsächlich!
Ich möchte anmerken, dass die oben angegebene Anordnung der Felder nicht garantiert die optimalste ist. Denn aus ästhetischen Gründen möchte man manche Feldblöcke nicht schon „zerreißen“ – zum Beispiel das Paar (pack, recno), das der PK für diese Tabelle entspricht.
Insgesamt ist die Bestimmung der „minimalen“ Anordnung der Felder eine relativ einfache „Ermusterungs“-Aufgabe. Daher können Sie mit Ihren Daten sogar bessere Ergebnisse erzielen als wir – probieren Sie es aus!
Quelle: habr.com
