یک پنی در حجم زیاد در 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) در یک فیلد، استفاده کنید فناوری نان تست:

PostgreSQL از یک اندازه صفحه ثابت (معمولاً 8 کیلوبایت) استفاده می کند و اجازه نمی دهد تاپل ها چندین صفحه را باز کنند. بنابراین، ذخیره مستقیم مقادیر میدان بسیار بزرگ غیرممکن است. برای غلبه بر این محدودیت، مقادیر میدان بزرگ فشرده شده و/یا در چندین خط فیزیکی تقسیم می‌شوند. این مورد بدون توجه کاربر اتفاق می افتد و تأثیر کمی روی اکثر کدهای سرور دارد. این روش به نان تست معروف است...

در واقع، برای هر جدولی با فیلدهای "به طور بالقوه بزرگ"، به طور خودکار یک جدول جفت با "برش" ایجاد می شود هر رکورد "بزرگ" در بخش های 2 کیلوبایتی:

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

یعنی اگر باید رشته ای با مقدار “large” بنویسیم data، سپس ضبط واقعی رخ می دهد نه تنها به میز اصلی و PK آن، بلکه به TOAST و PK آن نیز.

کاهش نفوذ نان تست

اما بیشتر رکوردهای ما هنوز آنقدر بزرگ نیستند، باید در 8 کیلوبایت باشد - چگونه می توانم در این پول پس انداز کنم؟

اینجاست که این صفت به کمک ما می آید 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 تأثیر بگذارد؟

حداقل از این بدتر نشد. اگرچه، قضاوت در مورد اینکه حتی چنین حجم هایی هنوز نمی توانند میانگین بار 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