Kontynuując temat rejestracji dużych strumieni danych podniesiony przez , w tym artykule przyjrzymy się sposobom, w jakie możesz to zrobić zmniejszyć „fizyczny” rozmiar przechowywanych plików w PostgreSQL i ich wpływ na wydajność serwera.
Porozmawiamy o Ustawienia TOAST i wyrównanie danych. „Średnio” te metody nie pozwolą zaoszczędzić zbyt wielu zasobów, ale w ogóle nie modyfikują kodu aplikacji.
Jednak nasze doświadczenie okazało się pod tym względem bardzo produktywne, ponieważ przechowywanie prawie każdego monitoringu ze swej natury jest głównie tylko do dodawania pod względem zarejestrowanych danych. A jeśli zastanawiasz się, jak zamiast tego nauczyć bazę danych zapisywania na dysku 200MB / s połowę tego - proszę pod kat.
Małe sekrety dużych zbiorów danych
Według profilu zawodowego , regularnie przylatują do niego z legowisk pakiety tekstowe.
A ponieważ którego baza danych monitorujemy jest produktem wieloskładnikowym o skomplikowanych strukturach danych, następnie zapytania dla maksymalnej wydajności wyszło całkiem tak . Zatem objętość każdego pojedynczego wystąpienia żądania lub wynikającego z niego planu wykonania w przychodzącym do nas dzienniku okazuje się „średnio” dość duża.
Przyjrzyjmy się strukturze jednej z tabel, do której zapisujemy „surowe” dane – czyli oto oryginalny tekst z wpisu logu:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- самое главное
text
, PRIMARY KEY(pack, recno)
);Typowy znak (oczywiście już podzielony, więc jest to szablon przekroju), gdzie najważniejszy jest tekst. Czasem dość obszerne.
Przypomnijmy, że „fizyczny” rozmiar jednego rekordu w PG nie może zajmować więcej niż jednej strony danych, ale „logiczny” rozmiar to zupełnie inna sprawa. Aby zapisać wartość wolumetryczną (varchar/text/bytea) w polu, użyj :
PostgreSQL używa stałego rozmiaru strony (zwykle 8 KB) i nie pozwala, aby krotki obejmowały wiele stron. Dlatego nie jest możliwe bezpośrednie przechowywanie bardzo dużych wartości pól. Aby pokonać to ograniczenie, duże wartości pól są kompresowane i/lub dzielone na wiele linii fizycznych. Dzieje się to niezauważone przez użytkownika i ma niewielki wpływ na większość kodu serwera. Ta metoda jest znana jako TOAST...
W rzeczywistości automatycznie dla każdej tabeli z „potencjalnie dużymi” polami każdy „duży” rekord w segmentach po 2 KB:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
To znaczy, jeśli musimy napisać ciąg znaków o „dużej” wartości data, nastąpi prawdziwe nagranie nie tylko do stołu głównego i jego PK, ale także do TOAST i jego PK.
Zmniejszanie wpływu TOASTU
Ale większość naszych nagrań wciąż nie jest aż tak duża, powinien zmieścić się w 8KB - Jak mogę na tym zaoszczędzić?..
Tutaj z pomocą przychodzi nam atrybut w kolumnie tabeli:
- ROZSZERZONY umożliwia zarówno kompresję, jak i oddzielne przechowywanie. Ten opcja standardowa dla większości typów danych zgodnych z TOAST. Najpierw próbuje przeprowadzić kompresję, a następnie przechowuje ją poza tabelą, jeśli wiersz jest nadal zbyt duży.
- GŁÓWNY umożliwia kompresję, ale nie oddzielne przechowywanie. (W rzeczywistości dla takich kolumn nadal będzie wykonywane osobne przechowywanie, ale tylko jako ostateczność, gdy nie ma innego sposobu na zmniejszenie ciągu tak, aby zmieścił się na stronie.)
W rzeczywistości właśnie tego potrzebujemy w tekście - skompresuj go jak najbardziej, a jeśli w ogóle nie pasuje, włóż do TOAST. Można to zrobić bezpośrednio w locie, za pomocą jednego polecenia:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Jak ocenić efekt
Ponieważ przepływ danych zmienia się każdego dnia, nie możemy porównywać liczb bezwzględnych, ale w kategoriach względnych mniejszy udział Zapisaliśmy to w TOAST - tym lepiej. Istnieje jednak niebezpieczeństwo – im większa jest „fizyczna” objętość każdego pojedynczego rekordu, tym „szerszy” staje się indeks, ponieważ musimy objąć więcej 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łaściwie to my zacząłem pisać do TOAST 2 razy rzadziej, co rozładowało nie tylko dysk, ale także procesor:
Zaznaczę, że staliśmy się także mniejsi w „odczycie” dysku, a nie tylko „zapisie” - skoro wstawiając rekord do tabeli, musimy także „odczytać” część drzewa każdego indeksu, aby określić jego przyszłą w nich pozycję.
Kto może dobrze żyć na PostgreSQL 11
Po aktualizacji do PG11 postanowiliśmy kontynuować „strojenie” TOAST i zauważyliśmy, że począwszy od tej wersji parametr stał się dostępny do strojenia :
Kod przetwarzania TOAST jest uruchamiany tylko wtedy, gdy wartość wiersza, która ma być przechowywana w tabeli, jest większa niż bajty TOAST_TUPLE_THRESHOLD (zwykle 2 KB). Kod TOAST będzie kompresował i/lub przenosił wartości pól z tabeli do momentu, aż wartość wiersza stanie się mniejsza niż TOAST_TUPLE_TARGET bajtów (wartość zmiennej, zwykle również 2 KB) lub rozmiaru nie będzie można zmniejszyć.
Uznaliśmy, że dane, którymi zwykle dysponujemy, są albo „bardzo krótkie”, albo „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 ładowanie dysku po rekonfiguracji:
Nie jest zły! Przeciętny kolejka do dysku zmalała około 1.5 razy, a dysk jest „zajęty” w 20 procentach! Ale może to w jakiś sposób wpłynęło na procesor?
Przynajmniej nie było gorzej. Chociaż trudno ocenić, czy nawet takie woluminy nadal nie mogą zwiększyć średniego obciążenia procesora 5%.
Zmieniając miejsca wyrazów, suma... zmienia się!
Jak wiadomo, grosz oszczędza rubla, a przy naszych pojemnościach magazynowych to wszystko 10 TB/miesiąc nawet niewielka optymalizacja może dać dobry zysk. Dlatego zwróciliśmy uwagę na strukturę fizyczną naszych danych - jak dokładnie „skumulowane” pola wewnątrz rekordu każdy ze stolików.
Ponieważ z powodu to jest proste :
Wiele architektur zapewnia wyrównanie danych na granicach słów maszynowych. Na przykład w 32-bitowym systemie x86 liczby całkowite (typ całkowity, 4 bajty) zostaną wyrównane do 4-bajtowej granicy słowa, podobnie jak liczby zmiennoprzecinkowe podwójnej precyzji (zmiennoprzecinkowa podwójnej precyzji, 8 bajtów). W systemie 64-bitowym podwójne wartości zostaną wyrównane do 8-bajtowych granic słów. To kolejny powód niezgodności.
Ze względu na wyrównanie wielkość wiersza tabeli zależy od kolejności pól. Zwykle efekt ten nie jest bardzo zauważalny, ale w niektórych przypadkach może prowadzić do znacznego zwiększenia rozmiaru. Na przykład, jeśli zmieszasz pola char(1) i integer, zazwyczaj pomiędzy nimi stracone zostaną 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 байт
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байтSkąd wzięło się kilka dodatkowych bajtów w pierwszym przypadku? To proste - 2-bajtowy smallint wyrównany do 4-bajtowej granicy przed kolejnym polem, a gdy już będzie ostatnie to nie ma już nic i potrzeby wyrównywania.
Teoretycznie wszystko jest w porządku i możesz dowolnie zmieniać układ pól. Sprawdźmy to na realnych danych na przykładzie jednej z tabel, której sekcja dzienna zajmuje 10-15 GB.
Początkowa struktura:
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)Sekcja po zmianie kolejności kolumn - dokładnie te same pola, tylko inna kolejność:
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)
Całkowita objętość sekcji zależy od liczby „faktów” i zależy tylko od procesów zewnętrznych, dlatego podzielmy wielkość sterty (pg_relation_size) według liczby znajdujących się w nim rekordów - czyli otrzymujemy średni rozmiar aktualnie przechowywanego rekordu:
Minus 6% objętości, Świetnie!
Ale oczywiście wszystko nie jest takie różowe - w końcu w indeksach nie możemy zmienić kolejności pól, a zatem „w ogóle” (pg_total_relation_size) ...
...też nadal tutaj zaoszczędzono 1.5%bez zmiany ani jednej linii kodu. Tak tak!
Zaznaczam, że powyższa opcja aranżacji pól nie oznacza, że jest najbardziej optymalna. Ponieważ nie chcesz „rozrywać” niektórych bloków pól ze względów estetycznych - na przykład pary (pack, recno), czyli PK dla tej tabeli.
Ogólnie rzecz biorąc, określenie „minimalnego” rozmieszczenia pól jest dość prostym zadaniem „brutalnej siły”. Dzięki temu możesz uzyskać jeszcze lepsze wyniki ze swoich danych niż nasze - spróbuj!
Źródło: www.habr.com