დაზოგეთ პენი დიდ მოცულობებზე PostgreSQL-ში

აგრძელებს მიერ წამოჭრილი დიდი მონაცემთა ნაკადების ჩაწერის თემის გაგრძელებას წინა სტატია დანაყოფის შესახებ, ამაში ჩვენ განვიხილავთ გზებს, რომლითაც შეგიძლიათ შეამცირეთ შენახული "ფიზიკური" ზომა PostgreSQL-ში და მათი გავლენა სერვერის მუშაობაზე.

ჩვენ ვისაუბრებთ TOAST პარამეტრები და მონაცემთა გასწორება. "საშუალოდ", ეს მეთოდები არ დაზოგავს ძალიან ბევრ რესურსს, მაგრამ აპლიკაციის კოდის შეცვლის გარეშე.

თუმცა, ჩვენი გამოცდილება ამ მხრივ ძალიან პროდუქტიული აღმოჩნდა, რადგან მისი ბუნებით თითქმის ნებისმიერი მონიტორინგის შენახვაა ძირითადად მხოლოდ დამატებით ჩაწერილი მონაცემების თვალსაზრისით. და თუ გაინტერესებთ, როგორ შეგიძლიათ ასწავლოთ მონაცემთა ბაზას დისკზე ჩაწერა 200MB / წ ნახევარი - გთხოვთ კატის ქვეშ.

დიდი მონაცემების პატარა საიდუმლოებები

სამუშაო პროფილის მიხედვით ჩვენი სერვისი, ისინი რეგულარულად დაფრინავენ მას ბუჩქებიდან ტექსტური პაკეტები.

და მას შემდეგ VLSI კომპლექსირომლის მონაცემთა ბაზა ჩვენ მონიტორინგს ვახორციელებთ არის მრავალკომპონენტიანი პროდუქტი მონაცემთა რთული სტრუქტურით, შემდეგ მოთხოვნებით მაქსიმალური შესრულებისთვის გამოდის საკმაოდ ასე "მრავალტომიანი" რთული ალგორითმული ლოგიკით. ასე რომ, მოთხოვნის თითოეული ინდივიდუალური ინსტანციის მოცულობა ან შედეგად მიღებული შესრულების გეგმის მოცულობა ჩვენთან მოსულ ჟურნალში აღმოჩნდება "საშუალოდ" საკმაოდ დიდი.

მოდით გადავხედოთ ერთ-ერთი ცხრილის სტრუქტურას, რომელშიც ჩვენ ვწერთ "ნედლეულ" მონაცემებს - ანუ, აქ არის ორიგინალური ტექსტი ჟურნალის ჩანაწერიდან:

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) ჩასაწერად გამოიყენეთ TOAST ტექნოლოგია:

PostgreSQL იყენებს ფიქსირებული გვერდის ზომას (როგორც წესი, 8 კბ) და არ აძლევს ტუპლებს საშუალებას რამდენიმე გვერდის გავრცელება. აქედან გამომდინარე, შეუძლებელია ველის ძალიან დიდი მნიშვნელობების პირდაპირ შენახვა. ამ შეზღუდვის დასაძლევად, დიდი ველის მნიშვნელობები შეკუმშულია და/ან იყოფა მრავალ ფიზიკურ ხაზზე. ეს ხდება მომხმარებლისთვის შეუმჩნეველი და მცირე გავლენას ახდენს სერვერის კოდებზე. ეს მეთოდი ცნობილია როგორც სადღეგრძელო...

ფაქტობრივად, ყველა ცხრილისთვის "პოტენციურად დიდი" ველებით, ავტომატურად იქმნება დაწყვილებული ცხრილი „დაჭრით“. თითოეული "დიდი" ჩანაწერი 2KB სეგმენტებში:

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

ანუ, თუ ჩვენ უნდა დავწეროთ სტრიქონი „დიდი“ მნიშვნელობით data, მაშინ მოხდება რეალური ჩაწერა არა მხოლოდ მთავარ მაგიდას და მის PK-ს, არამედ TOAST-ს და მის PK-ს.

ტოასტის გავლენის შემცირება

მაგრამ ჩვენი ჩანაწერების უმეტესობა ჯერ კიდევ არც ისე დიდია, უნდა შეესაბამებოდეს 8KB-ს - როგორ დავზოგო ფული ამაზე?..

სწორედ აქ გვეხმარება ატრიბუტი STORAGE ცხრილის სვეტში:

• გაფართოვდა იძლევა როგორც შეკუმშვის, ასევე ცალკე შენახვის საშუალებას. ეს სტანდარტული ვარიანტი TOAST-ის შესაბამისი მონაცემთა უმეტესობისთვის. ის ჯერ ცდილობს შეასრულოს შეკუმშვა, შემდეგ ინახავს მას ცხრილის გარეთ, თუ მწკრივი ჯერ კიდევ ძალიან დიდია.
• მთავარი საშუალებას იძლევა შეკუმშვა, მაგრამ არა ცალკე შენახვა. (ფაქტობრივად, ცალკე შენახვა კვლავ შესრულდება ასეთი სვეტებისთვის, მაგრამ მხოლოდ როგორც ბოლო საშუალება, როდესაც სხვა გზა არ არის სტრიქონის შეკუმშვის ისე, რომ იგი მოთავსდეს გვერდზე.)

სინამდვილეში, ეს არის ზუსტად ის, რაც ჩვენ გვჭირდება ტექსტისთვის - შეკუმშეთ შეძლებისდაგვარად და თუ საერთოდ არ ჯდება ჩადეთ სადღეგრძელოში. ეს შეიძლება გაკეთდეს პირდაპირ ფრენის დროს, ერთი ბრძანებით:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

როგორ შევაფასოთ ეფექტი

ვინაიდან მონაცემთა ნაკადი ყოველდღიურად იცვლება, ჩვენ არ შეგვიძლია შევადაროთ აბსოლუტური რიცხვები, მაგრამ შედარებითი თვალსაზრისით უფრო მცირე წილი ჩვენ ეს დავწერეთ სადღეგრძელოში - მით უკეთესი. მაგრამ აქ არის საშიშროება - რაც უფრო დიდია თითოეული ცალკეული ჩანაწერის „ფიზიკური“ მოცულობა, მით უფრო „ფართო“ ხდება ინდექსი, რადგან ჩვენ უნდა დავფაროთ მონაცემების მეტი გვერდი.

სექცია ცვლილებებამდე:

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

სექცია ცვლილებების შემდეგ:

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

ფაქტობრივად, ჩვენ დაიწყო სადღეგრძელოს წერა 2-ჯერ ნაკლებად ხშირად, რომელმაც განტვირთა არა მხოლოდ დისკი, არამედ CPU:

აღვნიშნავ, რომ დისკის „კითხვისას“ ჩვენც დავპატარავდით და არა მხოლოდ „ჩაწერისას“ - რადგან ჩანაწერის ცხრილში ჩასმისას ასევე უნდა „წავიკითხოთ“ თითოეული ინდექსის ხის ნაწილი, რათა განვსაზღვროთ მისი მომავალი პოზიცია მათში.

ვისაც შეუძლია კარგად იცხოვროს PostgreSQL 11-ზე

PG11-ზე განახლების შემდეგ, გადავწყვიტეთ გაგვეგრძელებინა TOAST-ის „თუნინგი“ და შევნიშნეთ, რომ ამ ვერსიიდან დაწყებული პარამეტრი toast_tuple_target:

TOAST დამუშავების კოდი ირთვება მხოლოდ მაშინ, როდესაც ცხრილში შესანახი მწკრივის მნიშვნელობა აღემატება TOAST_TUPLE_THRESHOLD ბაიტს (ჩვეულებრივ 2 კბ). TOAST კოდი შეკუმშავს და/ან გადაიტანს ველის მნიშვნელობებს ცხრილიდან მანამ, სანამ მწკრივის მნიშვნელობა არ გახდება TOAST_TUPLE_TARGET ბაიტზე ნაკლები (ცვლადი მნიშვნელობა, ასევე ჩვეულებრივ 2 კბ) ან ზომა ვერ შემცირდება.

ჩვენ გადავწყვიტეთ, რომ მონაცემები, რომლებიც ჩვეულებრივ გვაქვს, არის ან „ძალიან მოკლე“ ან „ძალიან გრძელი“, ამიტომ გადავწყვიტეთ შეგვეზღუდა მინიმალური შესაძლო მნიშვნელობით:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

ვნახოთ, როგორ იმოქმედა ახალმა პარამეტრებმა დისკის ჩატვირთვაზე ხელახალი კონფიგურაციის შემდეგ:

Ცუდი არაა! საშუალო დისკზე რიგი შემცირდა დაახლოებით 1.5-ჯერ და დისკი „დატვირთული“ 20 პროცენტია! მაგრამ იქნებ ამან რაღაცნაირად იმოქმედა პროცესორზე?

მაინც არ გაუარესდა. თუმცა, ძნელია ვიმსჯელოთ, თუ ასეთი მოცულობები მაინც ვერ ამაღლებს საშუალო CPU დატვირთვას 5%.

ტერმინების ადგილების შეცვლით ჯამი... იცვლება!

მოგეხსენებათ, პენი ზოგავს რუბლს და ჩვენი შენახვის მოცულობით ეს დაახლოებით 10 ტბ/თვეში მცირე ოპტიმიზაციასაც კი შეუძლია კარგი მოგება მოგვცეს. ამიტომ, ყურადღება მივაქციეთ ჩვენი მონაცემების ფიზიკურ სტრუქტურას - ზუსტად როგორ ჩანაწერის შიგნით „დაწყობილი“ ველები თითოეული მაგიდა.

იმის გამო, რომ მონაცემთა გასწორება ეს არის პირდაპირ გავლენას ახდენს მიღებულ მოცულობაზე:

ბევრი არქიტექტურა უზრუნველყოფს მონაცემთა გასწორებას მანქანის სიტყვების საზღვრებზე. მაგალითად, 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-ბაიტი წვრილმანი გასწორებულია 4-ბაიტიან საზღვარზე შემდეგ ველამდე და როცა ის ბოლოა, არაფერია და არ არის საჭირო გასწორება.

თეორიულად, ყველაფერი კარგადაა და შეგიძლიათ ველების გადალაგება ისე, როგორც გსურთ. მოდით შევამოწმოთ ის რეალურ მონაცემებზე ერთ-ერთი ცხრილის მაგალითის გამოყენებით, რომლის ყოველდღიური განყოფილება იკავებს 10-15 გბ.

საწყისი სტრუქტურა:

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