Продължаване на темата за запис на големи потоци от данни, повдигната от , в това ще разгледаме начините, по които можете намаляване на „физическия“ размер на съхраненото в PostgreSQL и тяхното въздействие върху производителността на сървъра.
Ще говорим за TOAST настройки и подравняване на данни. „Средно“ тези методи няма да спестят твърде много ресурси, но без изобщо да променят кода на приложението.
Нашият опит обаче се оказа много продуктивен в това отношение, тъй като съхранението на почти всеки мониторинг по своята същност е такова най-вече само за добавяне по отношение на записаните данни. И ако се чудите как можете да научите базата данни да пише на диск вместо това 200MB / и наполовина по-малко - моля под кат.
Малки тайни на големите данни
По профил на длъжността , те редовно летят до него от леговищата текстови пакети.
И тъй като чиято база данни наблюдаваме е многокомпонентен продукт със сложни структури от данни, след това заявки за максимална производителност се окаже доста така . Така обемът на всеки отделен екземпляр на заявка или произтичащия план за изпълнение в регистрационния файл, който идва при нас, се оказва „средно“ доста голям.
Нека да разгледаме структурата на една от таблиците, в които записваме „сурови“ данни - тоест тук е оригиналният текст от записа в дневника:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- самое главное
text
, PRIMARY KEY(pack, recno)
);Типичен знак (вече разделен, разбира се, така че това е шаблон за раздел), където най-важното е текстът. Понякога доста обемни.
Спомнете си, че „физическият“ размер на един запис в PG не може да заема повече от една страница с данни, но „логическият“ размер е съвсем различен въпрос. За да напишете обемна стойност (varchar/text/bytea) в поле, използвайте :
PostgreSQL използва фиксиран размер на страницата (обикновено 8 KB) и не позволява на кортежите да обхващат няколко страници. Следователно е невъзможно директно да се съхраняват много големи стойности на полета. За да се преодолее това ограничение, големите стойности на полето се компресират и/или разделят на множество физически линии. Това се случва незабелязано от потребителя и има малко влияние върху повечето сървърни кодове. Този метод е известен като ТОСТ...
Всъщност за всяка таблица с "потенциално големи" полета, автоматично всеки „голям“ запис в сегменти от 2KB:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Тоест, ако трябва да напишем низ с „голяма“ стойност data, тогава ще се извърши истинският запис не само към основната маса и нейния PK, но и към TOAST и нейния PK.
Намаляване на влиянието на TOAST
Но повечето от нашите записи все още не са толкова големи, трябва да се побере в 8KB - Как мога да спестя пари от това?..
Тук на помощ идва атрибутът в колоната на таблицата:
- РАЗШИРЕНИЕ позволява както компресиране, така и отделно съхранение. Това стандартен вариант за повечето типове данни, съвместими с TOAST. Първо се опитва да извърши компресиране, след което го съхранява извън таблицата, ако редът все още е твърде голям.
- ОСНОВНА позволява компресиране, но не и отделно съхранение. (Всъщност отделно съхранение все още ще се извършва за такива колони, но само в краен случай, когато няма друг начин за свиване на низа, така че да пасне на страницата.)
Всъщност точно това ни трябва за текста - компресирайте го колкото е възможно повече и ако изобщо не пасва, поставете го в TOAST. Това може да се направи директно в движение, с една команда:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Как да оценим ефекта
Тъй като потокът от данни се променя всеки ден, не можем да сравняваме абсолютни числа, а относително по-малък дял Записахме го в TOAST - толкова по-добре. Но тук има опасност - колкото по-голям е "физическият" обем на всеки отделен запис, толкова "по-широк" става индексът, защото трябва да покрием повече страници с данни.
раздел преди промените:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
раздел след промените:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Всъщност ние започна да пише на TOAST 2 пъти по-рядко, което разтовари не само диска, но и процесора:
Ще отбележа, че станахме по-малки и в „четенето“ на диска, не само в „записването“ - тъй като при вмъкване на запис в таблица трябва да „четем“ и част от дървото на всеки индекс, за да определим неговия бъдеща позиция в тях.
Кой може да живее добре с PostgreSQL 11
След актуализиране до PG11, решихме да продължим да „настройваме“ TOAST и забелязахме, че започвайки от тази версия, параметърът стана достъпен за настройка :
Кодът за обработка на TOAST се задейства само когато стойността на реда, която трябва да се съхрани в таблицата, е по-голяма от TOAST_TUPLE_THRESHOLD байта (обикновено 2 KB). Кодът TOAST ще компресира и/или ще премести стойностите на полето извън таблицата, докато стойността на реда стане по-малка от TOAST_TUPLE_TARGET байта (стойност на променлива, също обикновено 2 KB) или размерът не може да бъде намален.
Решихме, че данните, които обикновено имаме, са или „много кратки“, или „много дълги“, така че решихме да се ограничим до минималната възможна стойност:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Нека да видим как новите настройки повлияха на зареждането на диска след преконфигуриране:
Не е зле! Средно аритметично опашката към диска е намаляла приблизително 1.5 пъти, а дискът "зает" е 20 процента! Но може би това по някакъв начин е повлияло на процесора?
Поне не стана по-лошо. Въпреки това е трудно да се прецени дали дори такива обеми все още не могат да повишат средното натоварване на процесора по-високо 5%.
Сменяйки местата на членовете, сумата... се променя!
Както знаете, една стотинка спестява рубла, а с нашите обеми за съхранение това е около 10TB/месец дори малко оптимизиране може да доведе до добра печалба. Затова обърнахме внимание на физическата структура на нашите данни – как точно „подредени“ полета в записа всяка от масите.
Защото заради това е направо напред :
Много архитектури осигуряват подравняване на данни по границите на машинните думи. Например, в 32-битова x86 система, целите числа (тип цяло число, 4 байта) ще бъдат подравнени върху граница на 4-байтова дума, както и числата с плаваща запетая с двойна точност (плаваща запетая с двойна точност, 8 байта). И на 64-битова система двойните стойности ще бъдат подравнени към границите на 8-байтови думи. Това е още една причина за несъвместимост.
Поради подравняването размерът на реда на таблицата зависи от реда на полетата. Обикновено този ефект не е много забележим, но в някои случаи може да доведе до значително увеличаване на размера. Например, ако смесите char(1) и целочислени полета, между тях обикновено ще има 3 байта.
Да започнем със синтетични модели:
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 байтОткъде са дошли няколко допълнителни байта в първия случай? Просто е - 2-байтов smallint, подравнен на 4-байтова граница преди следващото поле, а когато е последното, няма нищо и не е необходимо да се подравнява.
На теория всичко е наред и можете да пренаредите полетата както искате. Нека го проверим на реални данни, като използваме примера на една от таблиците, чиято дневна секция заема 10-15 GB.
Първоначална структура:
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)Секция след промяна на реда на колоните - точно същите полета, просто различен ред:
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)
Общият обем на секцията се определя от броя на „фактите“ и зависи само от външни процеси, така че нека разделим размера на купчината (pg_relation_size) по броя на записите в него - тоест получаваме среден размер на действително съхранения запис:
Минус 6% обем, Страхотен!
Но всичко, разбира се, не е толкова розово - в края на краищата, в индексите не можем да променим реда на полетата, и следователно „като цяло“ (pg_total_relation_size) ...
...и все още тук спестен 1.5%без промяна на нито един ред код. Да да!
Отбелязвам, че горната опция за подреждане на полета не е фактът, че е най-оптималната. Защото не искате да „разкъсате“ някои блокове от полета по естетически причини - например двойка (pack, recno), което е PK за тази таблица.
Като цяло, определянето на „минималното“ подреждане на полетата е доста проста задача за „груба сила“. Следователно можете да получите дори по-добри резултати от вашите данни от нашите - опитайте!
Източник: www.habr.com