Spuert e Penny op grouss Bänn am PostgreSQL

Weiderféieren d'Thema vun Opname grouss Datestroum opgewuess vun virdrun Artikel iwwer Partitionéieren, an dësem wäerte mir op d'Weeër kucken wéi Dir kënnt reduzéieren der "kierperlech" Gréisst vun der gespäichert am PostgreSQL, an hiren Impakt op d'Serverleistung.

Mir schwätzen iwwer TOAST Astellungen an Datenausrichtung. "Am Duerchschnëtt", wäerten dës Methoden net ze vill Ressourcen spueren, awer ouni den Applikatiounscode iwwerhaapt z'änneren.

Wéi och ëmmer, eis Erfahrung huet sech an dëser Hisiicht ganz produktiv gewisen, well d'Lagerung vu bal all Iwwerwaachung duerch seng Natur ass meeschtens nëmmen appendéieren a punkto opgeholl Donnéeën. A wann Dir Iech frot wéi Dir d'Datebank léiere kënnt fir amplaz op Disk ze schreiwen 200MB / s Halschent esou vill - weg ënner Kaz.

Kleng Geheimnisser vu Big Data

No Aarbecht Profil eise Service, si fléien regelméisseg him aus de Lächer Text Packagen.

An zënter VLSI komplexdeem seng Datebank mir iwwerwaachen ass e Multi-Komponent Produkt mat komplexen Datestrukturen, dann Ufroen fir maximal Leeschtung ginn ganz esou aus "Multi-Volumen" mat komplexer algorithmescher Logik. Also de Volume vun all eenzelne Instanz vun enger Ufro oder dem resultéierende Ausféierungsplang am Logbuch, deen eis kënnt, ass "am Duerchschnëtt" zimlech grouss.

Loosst eis d'Struktur vun enger vun den Dëscher kucken, an deenen mir "raw" Donnéeën schreiwen - dat ass, hei ass den originalen Text vun der Logeintrag:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

En typescht Schëld (scho opgedeelt, natierlech, also ass dëst eng Rubrik Schabloun), wou dat Wichtegst ass den Text. Heiansdo zimlech voluminös.

Denkt drun datt d'"kierperlech" Gréisst vun engem Rekord an engem PG net méi wéi eng Säit vun Donnéeën ka besetzen, awer déi "logesch" Gréisst ass eng komplett aner Saach. Fir e Volumetresch Wäert ze schreiwen (varchar / Text / Bytea) zu engem Feld, benotzen TOAST Technologie:

PostgreSQL benotzt eng fix Säit Gréisst (typesch 8 KB), an erlaabt net Tuples méi Säiten ze spanen. Dofir ass et onméiglech fir ganz grouss Feldwäerter direkt ze späicheren. Fir dës Begrenzung ze iwwerwannen, gi grouss Feldwäerter kompriméiert an / oder iwwer verschidde kierperlech Linnen opgedeelt. Dëst geschitt onnotéiert vum Benotzer an huet wéineg Impakt op déi meescht Servercode. Dës Method ass bekannt als TOAST ...

An Tatsaach, fir all Dësch mat "potenziell grouss" Felder, automatesch e gepaart Dësch mat "Schnëtt" gëtt erstallt all "grousse" Rekord an 2KB Segmenter:

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

Dat ass, wa mir eng String mat engem "grousse" Wäert schreiwen muss data, da wäert déi richteg Opnahm geschéien net nëmmen un den Haaptdësch a seng PK, awer och op TOAST a seng PK.

Reduzéieren TOAST Afloss

Awer déi meescht vun eise Rekorder sinn nach ëmmer net sou grouss, soll an 8KB passen - Wéi kann ech Suen op dëser spueren?

Dëst ass wou den Attribut eis hëlleft STORAGE an der Tabell Kolonn:

• Verlängert erlaabt souwuel Kompressioun a separat Späichere. Dëst Standard Optioun fir déi meescht TOAST-kompatibel Datentypen. Et probéiert éischt d'Kompressioun ze maachen, da späichert et ausserhalb vum Dësch wann d'Zeil nach ëmmer ze grouss ass.
• HAND erlaabt Kompressioun awer net separat Späichere. (Tatsächlech gëtt separat Späichere nach ëmmer fir sou Sailen ausgefouert, awer nëmmen als leschten Auswee, wann et keen anere Wee gëtt fir d'String ze schrumpfen sou datt se op d'Säit passt.)

Tatsächlech ass dat genau wat mir fir den Text brauchen - kompriméieren et sou vill wéi méiglech, a wann et guer net passt, setzt se an TOAST. Dëst kann direkt op der Flucht gemaach ginn, mat engem Kommando:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Wéi den Effekt ze evaluéieren

Well den Datefluss all Dag ännert, kënne mir net absolut Zuelen vergläichen, awer relativ méi kleng Undeel Mir hunn et an TOAST opgeschriwwen - sou vill besser. Awer et ass eng Gefor hei - wat méi de "kierperlechen" Volume vun all eenzelne Rekord ass, dest "méi breet" gëtt den Index, well mir méi Säiten vun Daten mussen ofdecken.

Sektioun virun Ännerungen:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

Sektioun no Ännerungen:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

Tatsächlech, mir ugefaang ze schreiwen TOAST 2 Mol manner oft, déi net nëmmen d'Disk entlaascht huet, awer och d'CPU:

Ech wäert bemierken datt mir och méi kleng ginn am "Liesen" vun der Disk, net nëmmen "Schreiwen" - well wann Dir e Rekord an eng Tabell setzt, musse mir och en Deel vum Bam vun all Index "liesen" fir seng ze bestëmmen zukünfteg Positioun an hinnen.

Wien ka gutt op PostgreSQL 11 liewen

Nom Update op PG11 hu mir beschloss TOAST weider ze "tuning" a gemierkt datt vun dëser Versioun un de Parameter toast_tuple_target:

Den TOAST Veraarbechtungscode brennt nëmme wann de Zeilwäert, deen an der Tabell gespäichert gëtt, méi grouss ass wéi TOAST_TUPLE_THRESHOLD Bytes (normalerweis 2 KB). Den TOAST Code kompriméiert an / oder plënnert Feldwäerter aus der Tabell bis de Zeilwäert manner wéi TOAST_TUPLE_TARGET Bytes gëtt (variable Wäert, och normalerweis 2 KB) oder d'Gréisst kann net reduzéiert ginn.

Mir hunn decidéiert datt d'Donnéeën déi mir normalerweis hunn entweder "ganz kuerz" oder "ganz laang" sinn, also hu mir beschloss eis op de minimale méigleche Wäert ze limitéieren:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Loosst eis kucken wéi déi nei Astellungen d'Diskbelaaschtung no der Rekonfiguratioun beaflossen:

Net schlecht! Duerchschnëtt d'Schlaang op den Disk ass erofgaang ongeféier 1.5 Mol, an den Disk "beschäftegt" ass 20 Prozent! Awer vläicht huet dëst iergendwéi d'CPU beaflosst?

Op d'mannst gouf et net méi schlëmm. Och wann et schwéier ass ze beurteelen ob souguer esou Bänn nach ëmmer déi duerchschnëttlech CPU-Laascht net méi héich erhéijen 5%.

Andeems Dir d'Plaze vun de Begrëffer ännert, ännert d'Zomm ...!

Wéi Dir wësst, spuert e Penny e Rubel, a mat eise Späichervolumen geet et ëm 10 TB / Mount souguer e bëssen Optimisatioun kann e gudde Gewënn ginn. Dofir hu mir op déi kierperlech Struktur vun eisen Daten opgepasst - wéi genau "gestapelt" Felder am Rekord jiddereng vun den Dëscher.

Well wéinst daten Ausrichtung dëst ass direkt vir beaflosst de resultéierende Volumen:

Vill Architekturen bidden Datenausrichtung op Maschinn Wuert Grenzen. Zum Beispill, op engem 32-bëssen x86 System, ganz Zuelen (ganz Zuel Typ, 4 Bytes) wäert op eng 4-Byte Wuert Grenz ausgeriicht ginn, wéi duebel Präzisioun schwiewend Punkt Zuelen (duebel Präzisioun schwiewend Punkt, 8 Bytes). An op engem 64-Bit System ginn duebel Wäerter op 8-Byte Wuertgrenzen ausgeriicht. Dëst ass en anere Grond fir Inkompatibilitéit.

Wéinst Ausriichtung hänkt d'Gréisst vun enger Tabellerei vun der Uerdnung vun de Felder of. Normalerweis ass dësen Effekt net ganz bemierkbar, awer an e puer Fäll kann et zu enger wesentlecher Erhéijung vun der Gréisst féieren. Zum Beispill, wann Dir Char (1) an ganz Zuel Felder mëscht, ginn et normalerweis 3 Bytes tëscht hinnen verschwenden.

Loosst eis mat syntheteschen Modeller ufänken:

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 байт

Wou koumen e puer extra Bytes am éischte Fall? Et ass einfach - 2-Byte Smallint ausgeriicht op 4-Byte Grenz virum nächste Feld, a wann et dee leschte ass, gëtt et näischt a kee Besoin fir ze alignéieren.

An der Theorie ass alles gutt an Dir kënnt d'Felder nei arrangéieren wéi Dir wëllt. Loosst eis et op realen Daten iwwerpréiwen mat dem Beispill vun engem vun den Dëscher, déi alldeeglech Sektioun vun deenen 10-15GB besetzt.

Initial Struktur:

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)

Sektioun no änneren Kolonn Uerdnung - genee selwecht Felder, just aner Uerdnung:

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)

De Gesamtvolumen vun der Sektioun gëtt vun der Unzuel vun "Fakten" festgeluegt an hänkt nëmmen vun externe Prozesser of, also loosst eis d'Gréisst vum Koup deelen (pg_relation_size) vun der Zuel vun records an et - dat ass, mir kréien Duerchschnëtt Gréisst vun aktuellen gespäichert Rekord:

Minus 6% Volumen, Super!

Awer alles ass natierlech net sou roseg - schliisslech, an Indizes kënne mir d'Uerdnung vun de Felder net änneren, an dofir "am Allgemengen" (pg_total_relation_size) ...

... och nach ëmmer hei 1.5% spuerenouni eng eenzeg Zeil vum Code z'änneren. Jo, jo!

Ech bemierken datt déi uewe genannte Optioun fir Felder ze arrangéieren net de Fakt ass datt et déi optimal ass. Well Dir wëllt e puer Felderblocken aus ästheteschen Grënn net "zerräissen" - zum Beispill eng Koppel (pack, recno), wat de PK fir dësen Dësch ass.

Allgemeng ass d'Bestëmmung vun der "Minimum" Arrangement vu Felder eng zimlech einfach "brute Force" Aufgab. Dofir kënnt Dir nach besser Resultater vun Ären Donnéeë kréien wéi eis - probéiert et!

Source: will.com