PostgreSQL میں بڑی مقداروں پر ایک پیسہ بچائیں۔

کی طرف سے اٹھائے گئے بڑے ڈیٹا اسٹریمز کو ریکارڈ کرنے کے موضوع کو جاری رکھنا تقسیم کے بارے میں پچھلا مضموناس میں ہم ان طریقوں کو دیکھیں گے جن میں آپ کر سکتے ہیں۔ ذخیرہ شدہ کے "جسمانی" سائز کو کم کریں۔ PostgreSQL میں، اور سرور کی کارکردگی پر ان کا اثر۔

ہم بات کریں گے۔ TOAST کی ترتیبات اور ڈیٹا کی سیدھ. "اوسط طور پر،" یہ طریقے بہت زیادہ وسائل کی بچت نہیں کریں گے، لیکن ایپلیکیشن کوڈ میں ترمیم کیے بغیر۔

تاہم، اس سلسلے میں ہمارا تجربہ بہت نتیجہ خیز نکلا، کیونکہ تقریباً کسی بھی نگرانی کا ذخیرہ اپنی نوعیت کے مطابق ہوتا ہے۔ زیادہ تر صرف شامل کریں۔ ریکارڈ شدہ ڈیٹا کے لحاظ سے۔ اور اگر آپ سوچ رہے ہیں کہ آپ ڈیٹا بیس کو ڈسک پر لکھنا کیسے سکھا سکتے ہیں۔ 200MB / s آدھا جتنا - براہ کرم بلی کے نیچے۔

بڑے ڈیٹا کے چھوٹے راز

جاب پروفائل کے ذریعے ہماری خدمت، وہ باقاعدگی سے کھوہوں سے اس کے پاس اڑتے ہیں۔ ٹیکسٹ پیکجز.

اور تب سے 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 KB) استعمال کرتا ہے، اور ٹیپلز کو متعدد صفحات تک پھیلانے کی اجازت نہیں دیتا ہے۔ لہذا، بہت بڑی فیلڈ اقدار کو براہ راست ذخیرہ کرنا ناممکن ہے۔ اس حد کو دور کرنے کے لیے، بڑی فیلڈ ویلیوز کو کمپریس کیا جاتا ہے اور/یا متعدد فزیکل لائنوں میں تقسیم کیا جاتا ہے۔ یہ صارف کی طرف سے کسی کا دھیان نہیں دیتا ہے اور زیادہ تر سرور کوڈ پر اس کا بہت کم اثر پڑتا ہے۔ یہ طریقہ TOAST کے نام سے جانا جاتا ہے...

درحقیقت، "ممکنہ طور پر بڑے" فیلڈز والی ہر میز کے لیے، خود بخود "سلائسنگ" کے ساتھ ایک جوڑی والی میز بنائی گئی ہے۔ 2KB حصوں میں ہر ایک "بڑا" ریکارڈ:

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

یعنی، اگر ہمیں "بڑی" ویلیو کے ساتھ سٹرنگ لکھنی ہے۔ data، پھر حقیقی ریکارڈنگ واقع ہوگی۔ نہ صرف مین ٹیبل اور اس کے PK پر، بلکہ TOAST اور اس کے PK کو بھی.

TOAST کے اثر کو کم کرنا

لیکن ہمارے زیادہ تر ریکارڈ اب بھی اتنے بڑے نہیں ہیں، 8KB میں فٹ ہونا چاہئے۔ - میں اس پر پیسے کیسے بچا سکتا ہوں؟

یہ وہ جگہ ہے جہاں یہ وصف ہماری مدد کے لیے آتا ہے۔ STORAGE میز کے کالم میں:

• توسیع کمپریشن اور علیحدہ اسٹوریج دونوں کی اجازت دیتا ہے۔ یہ معیاری اختیار زیادہ تر 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 بار کم لکھنا شروع کیا۔، جس نے نہ صرف ڈسک بلکہ CPU کو بھی اتارا:

میں نوٹ کروں گا کہ ہم ڈسک کو "پڑھنے" میں بھی چھوٹے ہو گئے ہیں، نہ صرف "لکھنے" - چونکہ ٹیبل میں ریکارڈ ڈالتے وقت، ہمیں ہر اشاریہ کے درخت کا حصہ "پڑھنا" پڑتا ہے تاکہ اس کا تعین کیا جا سکے۔ ان میں مستقبل کی پوزیشن.

PostgreSQL 11 پر کون اچھی طرح سے رہ سکتا ہے۔

PG11 کو اپ ڈیٹ کرنے کے بعد، ہم نے TOAST کو "ٹیوننگ" جاری رکھنے کا فیصلہ کیا اور دیکھا کہ اس ورژن سے شروع ہونے سے پیرامیٹر ٹیوننگ کے لیے دستیاب ہو گیا ہے۔ toast_tuple_target:

TOAST پروسیسنگ کوڈ صرف اس وقت فائر ہوتا ہے جب ٹیبل میں اسٹور کی جانے والی قطار کی قدر TOAST_TUPLE_THRESHOLD بائٹس (عام طور پر 2 KB) سے بڑی ہو۔ TOAST کوڈ اس وقت تک فیلڈ ویلیو کو دبائے گا اور/یا ٹیبل سے باہر لے جائے گا جب تک کہ قطار کی قدر TOAST_TUPLE_TARGET بائٹس سے کم نہ ہو جائے (متغیر قدر، عام طور پر 2 KB بھی) یا سائز کو کم نہیں کیا جا سکتا۔

ہم نے فیصلہ کیا کہ ہمارے پاس عام طور پر جو ڈیٹا ہوتا ہے وہ یا تو "بہت مختصر" یا "بہت لمبا" ہوتا ہے، لہذا ہم نے خود کو کم سے کم ممکنہ قدر تک محدود رکھنے کا فیصلہ کیا:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

آئیے دیکھتے ہیں کہ ری کنفیگریشن کے بعد نئی سیٹنگز نے ڈسک کی لوڈنگ کو کیسے متاثر کیا:

برا نہیں ہے! اوسط ڈسک کی قطار کم ہو گئی ہے۔ تقریباً 1.5 گنا، اور ڈسک "مصروف" 20 فیصد ہے! لیکن شاید اس نے کسی نہ کسی طرح سی پی یو کو متاثر کیا؟

کم از کم یہ خراب نہیں ہوا۔ اگرچہ، یہ فیصلہ کرنا مشکل ہے کہ آیا ایسی جلدیں بھی اوسط CPU لوڈ کو زیادہ نہیں بڑھا سکتیں۔ 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-بائٹ سمالنٹ 4-بائٹ باؤنڈری پر منسلک ہے۔ اگلے فیلڈ سے پہلے، اور جب یہ آخری ہے، وہاں کچھ بھی نہیں ہے اور سیدھ کرنے کی ضرورت نہیں ہے۔

نظریہ میں، سب کچھ ٹھیک ہے اور آپ اپنی مرضی کے مطابق فیلڈز کو دوبارہ ترتیب دے سکتے ہیں۔ آئیے ایک ٹیبل کی مثال کا استعمال کرتے ہوئے اسے اصلی ڈیٹا پر چیک کرتے ہیں، جس کا روزانہ سیکشن 10-15GB پر ہوتا ہے۔

ابتدائی ڈھانچہ:

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