Fortsetzung des Themas der Aufzeichnung großer Datenströme, angesprochen von , hier werden wir uns mit den Möglichkeiten befassen, die Ihnen möglich sind Reduzieren Sie die „physische“ Größe der gespeicherten Daten in PostgreSQL und ihre Auswirkungen auf die Serverleistung.
Wir reden darüber TOAST-Einstellungen und Datenausrichtung. „Im Durchschnitt“ sparen diese Methoden nicht allzu viele Ressourcen, ohne jedoch den Anwendungscode überhaupt zu ändern.
Allerdings erwiesen sich unsere Erfahrungen in dieser Hinsicht als sehr ergiebig, da die Speicherung fast aller Überwachungen naturgemäß so ist meist nur anhängen in Bezug auf die aufgezeichneten Daten. Und wenn Sie sich fragen, wie Sie der Datenbank beibringen können, stattdessen auf die Festplatte zu schreiben 200MB / s halb so viel - bitte unter Kat.
Kleine Geheimnisse von Big Data
Nach Jobprofil , sie fliegen regelmäßig von den Verstecken zu ihm Textpakete.
Und da deren Datenbank wir überwachen, ist ein mehrkomponentiges Produkt mit komplexen Datenstrukturen, dann Abfragen für maximale Leistung kommt ganz so raus . Das Volumen jeder einzelnen Instanz einer Anfrage bzw. des daraus resultierenden Ausführungsplans im Protokoll, das zu uns kommt, fällt also „im Durchschnitt“ recht groß aus.
Schauen wir uns die Struktur einer der Tabellen an, in die wir „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)
);Ein typisches Schild (natürlich bereits unterteilt, es handelt sich also um eine Abschnittsvorlage), bei dem der Text das Wichtigste ist. Teilweise recht voluminös.
Denken Sie daran, dass die „physische“ Größe eines Datensatzes in einem PG nicht mehr als eine Datenseite belegen kann, die „logische“ Größe jedoch eine völlig andere Sache ist. Um einen volumetrischen Wert (varchar/text/bytea) in ein Feld zu schreiben, verwenden Sie :
PostgreSQL verwendet eine feste Seitengröße (normalerweise 8 KB) und lässt nicht zu, dass sich Tupel über mehrere Seiten erstrecken. 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 auf mehrere physische Leitungen aufgeteilt. Dies geschieht unbemerkt für den Benutzer und hat kaum Auswirkungen auf den meisten Servercode. Diese Methode ist als TOAST bekannt...
Tatsächlich automatisch für jede Tabelle mit „potenziell großen“ Feldern jeder „große“ Datensatz in 2-KB-Segmenten:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Das heißt, wenn wir einen String mit einem „großen“ Wert schreiben müssen data, dann erfolgt die eigentliche Aufnahme nicht nur für die Haupttabelle und ihren PK, sondern auch für TOAST und ihren PK.
Reduzierung des TOAST-Einflusses
Aber die meisten unserer Platten sind immer noch nicht so groß, sollte in 8 KB passen - Wie kann ich dabei Geld sparen?
Hier kommt uns das Attribut zu Hilfe in der Tabellenspalte:
- VERLÄNGERT ermöglicht sowohl Komprimierung als auch separate Speicherung. Das Standardoption für die meisten TOAST-kompatiblen Datentypen. Es versucht zunächst eine Komprimierung durchzuführen und speichert sie dann außerhalb der Tabelle, wenn die Zeile noch zu groß ist.
- MAIN Ermöglicht Komprimierung, jedoch keine separate Speicherung. (Tatsächlich wird für solche Spalten weiterhin eine separate Speicherung durchgeführt, aber nur als letztes, wenn es keine andere Möglichkeit gibt, die Zeichenfolge so zu verkleinern, dass sie auf die Seite passt.)
Genau das brauchen wir für den Text – Komprimieren Sie es so weit wie möglich, und wenn es überhaupt nicht passt, legen Sie es in TOAST. Dies kann direkt im laufenden Betrieb mit einem Befehl erfolgen:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;So bewerten Sie die Wirkung
Da sich der Datenfluss täglich ändert, können wir keine absoluten Zahlen vergleichen, sondern relativ kleinerer Anteil Wir haben es in TOAST aufgeschrieben – umso besser. Hier besteht jedoch eine Gefahr: Je größer das „physische“ Volumen jedes einzelnen Datensatzes, desto „breiter“ wird der Index, da wir mehr Datenseiten abdecken müssen.
Abschnitt vor Veränderungen:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Abschnitt nach Änderungen:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Tatsächlich wir fing an, zweimal seltener an TOAST zu schreiben, wodurch nicht nur die Festplatte, sondern auch die CPU entladen wurde:
Ich möchte anmerken, dass wir auch beim „Lesen“ der Festplatte kleiner geworden sind, nicht nur beim „Schreiben“ – denn beim Einfügen eines Datensatzes in eine Tabelle müssen wir auch einen Teil des Baums jedes Index „lesen“, um ihn zu bestimmen zukünftige Position in ihnen.
Wer kann gut mit PostgreSQL 11 leben?
Nach dem Update auf PG11 beschlossen wir, TOAST weiter zu „tunen“ und stellten fest, dass der Parameter ab dieser Version für die Optimierung verfügbar war :
Der TOAST-Verarbeitungscode wird nur ausgelöst, wenn der in der Tabelle zu speichernde Zeilenwert größer als TOAST_TUPLE_THRESHOLD Bytes (normalerweise 2 KB) ist. Der TOAST-Code komprimiert und/oder verschiebt Feldwerte aus der Tabelle, bis der Zeilenwert kleiner als TOAST_TUPLE_TARGET Bytes (Variablenwert, ebenfalls normalerweise 2 KB) wird oder die Größe nicht reduziert werden kann.
Wir haben entschieden, dass die Daten, die wir normalerweise haben, entweder „sehr kurz“ oder „sehr lang“ sind, also haben wir beschlossen, uns auf den minimal möglichen Wert zu beschränken:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Sehen wir uns an, wie sich die neuen Einstellungen nach der Neukonfiguration auf das Laden der Festplatte ausgewirkt haben:
Nicht schlecht! Durchschnitt Die Warteschlange zur Festplatte hat sich verringert ungefähr das 1.5-fache und die Festplatte ist zu 20 Prozent ausgelastet! Aber vielleicht hat das irgendwie Auswirkungen auf die CPU?
Zumindest wurde es nicht schlimmer. Allerdings ist es schwierig zu beurteilen, ob selbst solche Volumes die durchschnittliche CPU-Auslastung noch steigern können 5%.
Durch die Änderung der Stellen der Begriffe ändert sich die Summe...!
Wie Sie wissen, spart ein Cent einen Rubel, und bei unseren Speichervolumina ist das ungefähr so 10 TB/Monat Schon eine kleine Optimierung kann einen guten Gewinn bringen. Deshalb haben wir auf die physische Struktur unserer Daten geachtet – wie genau „gestapelte“ Felder innerhalb des Datensatzes jede der Tabellen.
Weil wegen Das ist einfach :
Viele Architekturen bieten eine Datenausrichtung an Maschinenwortgrenzen. Beispielsweise werden auf einem 32-Bit-x86-System Ganzzahlen (Ganzzahltyp, 4 Bytes) an einer 4-Byte-Wortgrenze ausgerichtet, ebenso wie Gleitkommazahlen mit doppelter Genauigkeit (Gleitkommazahlen mit doppelter Genauigkeit, 8 Bytes). Und auf einem 64-Bit-System werden doppelte Werte an 8-Byte-Wortgrenzen ausgerichtet. Dies ist ein weiterer Grund für die Inkompatibilität.
Aufgrund der Ausrichtung hängt die Größe einer Tabellenzeile von der Reihenfolge der Felder ab. Normalerweise ist dieser Effekt nicht sehr auffällig, kann aber in manchen Fällen zu einer deutlichen Größenzunahme führen. Wenn Sie beispielsweise char(1)- und Integer-Felder mischen, werden normalerweise 3 Bytes dazwischen 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 kamen im ersten Fall ein paar zusätzliche Bytes? Es ist einfach - 2-Byte-Smallint, ausgerichtet an der 4-Byte-Grenze vor dem nächsten Feld und wenn es das letzte ist, gibt es nichts und keine Notwendigkeit für eine Ausrichtung.
Theoretisch ist alles in Ordnung und Sie können die Felder nach Belieben neu anordnen. Lassen Sie es uns anhand realer Daten am Beispiel einer der Tabellen überprüfen, deren Tagesabschnitt 10-15 GB einnimmt.
Ausgangsstruktur:
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)Abschnitt nach Änderung der Spaltenreihenfolge - genau Gleiche Felder, nur andere Reihenfolge:
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. Teilen wir also die Größe des Heaps (pg_relation_size) nach der Anzahl der darin enthaltenen Datensätze - das heißt, wir erhalten durchschnittliche Größe des tatsächlich gespeicherten Datensatzes:
Minus 6 % Volumen, Großartig!
Aber natürlich ist nicht alles so rosig – schließlich In Indizes können wir die Reihenfolge der Felder nicht ändernund daher „im Allgemeinen“ (pg_total_relation_size) ...
...auch noch hier 1.5 % gespartohne eine einzige Codezeile zu ändern. Ja ja!
Ich stelle fest, dass die obige Option zum Anordnen von Feldern nicht die optimalste ist. Weil man aus ästhetischen Gründen einige Feldblöcke – zum Beispiel ein paar – nicht „zerreißen“ möchte (pack, recno), das ist der PK für diese Tabelle.
Im Allgemeinen ist die Bestimmung der „minimalen“ Anordnung von Feldern eine ziemlich einfache „Brute-Force“-Aufgabe. Daher können Sie mit Ihren Daten noch bessere Ergebnisse erzielen als mit unseren – probieren Sie es aus!
Source: habr.com