Kontynuując temat zapisu dużych strumieni danych, poruszony w , w tym przyjrzymy się sposobom, dzięki którym można zmniejszyć „fizyczny” rozmiar przechowywanych w PostgreSQL oraz ich wpływ na wydajność serwera.
Będzie mowa o ustawieniach TOAST i wyrównaniu danych. „Średnio” te metody pozwolą zaoszczędzić niewiele zasobów, ale — całkowicie bez modyfikacji kodu aplikacji.
Jednak nasze doświadczenia okazały się dość owocne w tym zakresie, ponieważ przechowalnia prawie każdego monitoringu z natury jest w dużej części append-only z punktu widzenia zapisywanych danych. A jeśli chcesz wiedzieć, jak można nauczyć bazę pisać na dysk z prędkością 200MB/s znacznie mniejszą — zapraszam pod tekst.
Małe sekrety dużych danych
W profilu działania , regularnie wpływają do nas z logów pakiety tekstowe.
A ponieważ , których bazy danych monitorujemy, to produkt wielokomponentowy o złożonych strukturach danych, to i zapytania w celu osiągnięcia maksymalnej wydajności okazują się całkiem . Dlatego objętość każdego pojedynczego wystąpienia zapytania lub planu wykonania w napływających do nas logach okazuje się „średnio” wystarczająco duża.
Przyjrzyjmy się strukturze jednej z tabel, do której zapisujemy „surowe” dane — czyli oryginalny tekst z zapisu logu:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- klucz sekcji
date
, data -- najważniejsze
text
, PRIMARY KEY(pack, recno)
);Typowa taka tabelka (już partycjonowana, oczywiście, dlatego to jest wzór sekcji), gdzie najważniejszy jest tekst. Czasami dość obszerna.
Przypomnijmy, że „fizyczny” rozmiar jednego rekordu w PG nie może zajmować więcej niż jedna strona danych, ale „logiczny” rozmiar — to zupełnie inna sprawa. Aby zapisać w polu obszerne wartości (varchar/text/bytea) używana jest :
PostgreSQL używa stałego rozmiaru strony (zwykle 8 KB) i nie pozwala na zajmowanie wielu stron przez krotki. Dlatego nie można bezpośrednio przechowywać bardzo dużych wartości pól. Aby przezwyciężyć to ograniczenie, duże wartości pól są kompresowane i/lub dzielone na kilka fizycznych rzędów. Dzieje się to niewidocznie dla użytkownika i na większość kodu serwera wpływa nieznacznie. Ta metoda znana jest jako TOAST …
W rzeczywistości dla każdej tabeli z "potencjalnie dużymi" polami automatycznie każdego "dużego" rekordu w segmentach po 2KB:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
); Oznacza to, że jeśli musimy zapisać wiersz z "dużą" wartością data, to rzeczywisty zapis nastąpi nie tylko w głównej tabeli i jej kluczu głównym, ale również w TOAST i jej kluczu głównym.
Zmniejszamy wpływ TOAST
Ale większość naszych zapisów jest wciąż nie tak wielka, powinny się zmieścić w 8KB — jak tu zaoszczędzić?..
Tutaj z pomocą przychodzi atrybut w kolumnie tabeli:
- EXTENDED dopuszcza zarówno kompresję, jak i oddzielne przechowywanie. To standardowa opcja dla większości typów danych zgodnych z TOAST. Najpierw podejmowana jest próba wykonania kompresji, a następnie — zapis w zewnętrznej tabeli, jeśli wiersz wciąż jest zbyt duży.
- MAIN dopuszcza kompresję, ale nie oddzielne przechowywanie. (W rzeczywistości oddzielne przechowywanie i tak zostanie wykonane dla tych kolumn, ale tylko jako ostateczność, gdy nie ma innego sposobu, aby zmniejszyć wiersz tak, aby zmieścił się na stronie.)
W rzeczywistości to dokładnie to, czego potrzebujemy dla tekstu — maksymalnie skompresować, a jeśli już naprawdę się nie zmieści — przenieść do TOAST. Można to zrobić od razu „w locie”, jednym poleceniem:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Jak ocenić efekt
Ponieważ każdego dnia strumień danych się zmienia, nie możemy porównywać absolutnych cyfr, ale w relatywnych im mniejszy udział zapisaliśmy w TOAST — tym lepiej. Ale tu jest niebezpieczeństwo — im większa jest nasza „fizyczna” objętość każdego pojedynczego rekordu, tym „szerszy” staje się indeks, ponieważ trzeba pokryć większą ilość stron danych.
Sekcja przed zmianami:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Sekcja po zmianach:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)W rzeczywistości zaczęliśmy zapisywać w TOAST 2 razy rzadziej, co odciążyło nie tylko dysk, ale i CPU:
Zauważam, że zaczęliśmy również "czytać" dysk mniej, nie tylko "zapisywać" — ponieważ przy dodawaniu wpisu do jakiejś tabeli musimy "przeczytać" także część drzewa każdego z indeksów, aby określić jego przyszłą pozycję w nich.
Komu żyje się dobrze na PostgreSQL 11
Po aktualizacji do PG11 postanowiliśmy kontynuować "tuning" TOAST i zwróciliśmy uwagę, że od tej wersji dostępny stał się parametr do konfiguracji :
Kod obsługi TOAST działa tylko wtedy, gdy wartość ciągu, która ma być przechowywana w tabeli, jest większa niż TOAST_TUPLE_THRESHOLD bajtów (zwykle to 2 KB). Kod TOAST będzie kompresować i/lub przenosić wartości pola poza tabelę, dopóki wartość ciągu nie spadnie poniżej TOAST_TUPLE_TARGET bajtów (zmienna wielkość, również zwykle 2 KB) lub zmniejszenie objętości nie będzie możliwe.
Postanowiliśmy, że nasze dane zazwyczaj są albo "zupełnie krótkie", albo od razu "bardzo długie", dlatego postanowiliśmy ograniczyć się do minimalnej możliwej wartości:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Zobaczmy, jak nowe ustawienia wpłynęły na obciążenie dysku po przeregulowaniu:
Niezłe! Średnia kolejka do dysku zmniejszyła się około 1.5 razy, a "zajętość" dysku — o 20%! Ale może to miało jakiś wpływ na CPU?
Przynajmniej na pewno nie było gorzej. Choć trudno to ocenić, gdy nawet takie obciążenia wciąż nie są w stanie podnieść średniego obciążenia CPU wyżej 5%.
Z zamianą miejsc składników suma... zmienia się!
Jak wiadomo, grosz oszczędza rubla, a przy naszych objętościach przechowywania wynoszących 10TB/miesiąc nawet niewielka optymalizacja może przynieść niezły zysk. Dlatego zwróciliśmy uwagę na fizyczną strukturę swoich danych — jak konkretnie "ulegają" pola wewnątrz zapisu każdej z tabel.
Ponieważ z powodu to bezpośrednio :
Wiele architektur przewiduje wyrównanie danych do granic słów maszynowych. Na przykład w systemie 32-bitowym x86 liczby całkowite (typ integer, zajmujące 4 bajty) będą wyrównane do granicy 4-bajtowych słów, podobnie jak liczby zmiennoprzecinkowe podwójnej precyzji (typ double precision, 8 bajtów). A w systemie 64-bitowym wartości double będą wyrównane do granicy 8-bajtowych słów. To kolejny powód niekompatybilności.
Ze względu na wyrównanie rozmiar wiersza tabeli zależy od kolejności rozmieszczania pól. Zwykle ten efekt nie jest bardzo widoczny, ale w niektórych przypadkach może prowadzić do znacznego zwiększenia rozmiaru. Na przykład, jeśli wymieszać pola typów char(1) i integer, między nimi zazwyczaj będą tracić 3 bajty.
Zacznijmy od modeli syntetycznych:
SELECT pg_column_size(ROW(
'0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 bajtów
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 bajtówSkąd wzięły się te dodatkowe bajty w pierwszym przypadku? Wszystko jest proste — 2-bajtowy smallint jest wyrównywany do granicy 4-bajtowej przed następnym polem, a gdy stoi na końcu — nie ma czego wyrównywać i nie ma potrzeby.
Teoretycznie — wszystko jest w porządku i można przestawiać pola według uznania. Sprawdźmy to na rzeczywistych danych na przykładzie jednej z tabel, której sekcja dzienna zajmuje 10-15GB.
Początkowa struktura:
CREATE TABLE public.plan_20190220
(
-- Dziedziczone z tabeli plan: pack uuid NOT NULL,
-- Dziedziczone z tabeli plan: recno smallint NOT NULL,
-- Dziedziczone z tabeli plan: host uuid,
-- Dziedziczone z tabeli plan: ts timestamp with time zone,
-- Dziedziczone z tabeli plan: exectime numeric(32,3),
-- Dziedziczone z tabeli plan: duration numeric(32,3),
-- Dziedziczone z tabeli plan: bufint bigint,
-- Dziedziczone z tabeli plan: bufmem bigint,
-- Dziedziczone z tabeli plan: bufdsk bigint,
-- Dziedziczone z tabeli plan: apn uuid,
-- Dziedziczone z tabeli plan: ptr uuid,
-- Dziedziczone z tabeli 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)Sekcja po zmianie kolejności kolumn — dokładnie te same pola, tylko inna kolejność:
CREATE TABLE public.plan_20190221
(
-- Dziedziczone z tabeli plan: dt date NOT NULL,
-- Dziedziczone z tabeli plan: ts timestamp with time zone,
-- Dziedziczone z tabeli plan: pack uuid NOT NULL,
-- Dziedziczone z tabeli plan: recno smallint NOT NULL,
-- Dziedziczone z tabeli plan: host uuid,
-- Dziedziczone z tabeli plan: apn uuid,
-- Dziedziczone z tabeli plan: ptr uuid,
-- Dziedziczone z tabeli plan: bufint bigint,
-- Dziedziczone z tabeli plan: bufmem bigint,
-- Dziedziczone z tabeli plan: bufdsk bigint,
-- Dziedziczone z tabeli plan: exectime numeric(32,3),
-- Dziedziczone z tabeli 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) Całkowity rozmiar sekcji określany jest przez liczbę „faktów” i zależy tylko od procesów zewnętrznych, dlatego podzielmy rozmiar heap (pg_relation_size) na liczbę zapisów w niej — więc otrzymamy średni rozmiar rzeczywistego zapisanego rekordu:
Minus 6% objętości, świetnie!
Ale to wszystko, oczywiście, nie jest tak różowe — ponieważ w indeksach nie możemy zmienić kolejności pól, a więc „ogólnie” (pg_total_relation_size)…
… mimo wszystko i tutaj zaoszczędziliśmy 1,5%, nie zmieniając ani jednej linijki kodu. No tak!
Zauważam, że powyższa wersja rozmieszczenia pól — nie jest pewne, że jest najbardziej optymalna. Bo niektóre bloki pól nie chcemy „przerywać” już ze względów estetycznych — na przykład para (pack, recno), która jest PK dla tej tabeli.
Ogólnie jednak, określenie „minimalnego” rozmieszczenia pól — to dość proste zadanie „przeglądowe”. Dlatego możesz na swoich danych uzyskać wyniki jeszcze lepsze niż nasze — spróbuj!
Źródło: habr.com
