🥇 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)
);

هڪ عام نشاني (اڳ ۾ ئي سيڪشن ٿيل آهي، يقينا، تنهنڪري هي هڪ سيڪشن ٽيمپليٽ آهي)، جتي سڀ کان اهم شيء متن آهي. ڪڏهن ڪڏهن ڪافي مقدار ۾.

ياد رهي ته هڪ پي جي ۾ هڪ رڪارڊ جي "جسماني" سائيز ڊيٽا جي هڪ کان وڌيڪ صفحي تي قبضو نٿو ڪري سگهي، پر "منطقي" سائيز هڪ مڪمل طور تي مختلف معاملو آهي. ھڪڙي حجم جي قيمت (varchar/text/bytea) ھڪڙي فيلڊ ۾ لکڻ لاء، استعمال ڪريو TOAST ٽيڪنالاجي:

PostgreSQL هڪ مقرر ٿيل صفحي جي سائيز (عام طور تي 8 KB) استعمال ڪري ٿو، ۽ ٽوپلز کي ڪيترن ئي صفحن کي ڦهلائڻ جي اجازت نٿو ڏئي. تنهن ڪري، اهو ناممڪن آهي سڌو سنئون تمام وڏي فيلڊ جي قيمتن کي ذخيرو ڪرڻ. ھن حد کي ختم ڪرڻ لاءِ، وڏي فيلڊ ويلز کي دٻايو ويو آھي ۽/يا ڪيترن ئي فزيڪل لائينن ۾ ورهايو ويو آھي. اهو صارف طرفان اڻڄاتل ٿئي ٿو ۽ اڪثر سرور ڪوڊ تي ٿورو اثر آهي. اهو طريقو TOAST طور سڃاتو وڃي ٿو ...

حقيقت ۾، هر ٽيبل لاء "امڪاني طور تي وڏي" شعبن سان، خودڪار طور تي "slicing" سان ٺهيل ٽيبل ٺاهي وئي آهي هر هڪ "وڏي" رڪارڊ 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 مطابق ڊيٽا جي قسمن لاءِ. اهو پهريون ڪمپريشن انجام ڏيڻ جي ڪوشش ڪري ٿو، پوء ان کي ٽيبل کان ٻاهر اسٽور ڪري ٿو جيڪڏهن قطار اڃا تمام وڏي آهي.

MAIN ڪمپريشن جي اجازت ڏئي ٿو پر الڳ اسٽوريج نه. (حقيقت ۾، الڳ اسٽوريج اڃا تائين اهڙي ڪالمن لاء انجام ڏنو ويندو، پر صرف آخري حل جي طور تي، جڏهن تار کي ڇڪڻ جو ڪو ٻيو طريقو ناهي ته جيئن اهو صفحي تي اچي.)

حقيقت ۾، اهو ئي آهي جيڪو اسان کي متن جي ضرورت آهي - جيترو ٿي سگھي ان کي دٻايو، ۽ جيڪڏھن اھو بلڪل مناسب نه آھي، ان کي 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_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 سيڪڙو آهي! پر شايد اهو ڪنهن به طرح سي پي يو کي متاثر ڪيو؟

گهٽ ۾ گهٽ اهو وڌيڪ خراب نه ٿيو. جيتوڻيڪ، اهو فيصلو ڪرڻ ڏکيو آهي ته اڃا به اهڙي مقدار اڃا تائين سراسري سي پي يو لوڊ بلند نه ڪري سگهي 5%.

اصطلاحن جي جڳهن کي تبديل ڪرڻ سان، مجموعو ... تبديل ٿئي ٿو!

جئين توهان کي خبر آهي، هڪ پئسو هڪ روبل بچائيندو آهي، ۽ اسان جي اسٽوريج جي مقدار سان اهو آهي 10 ٽي بي / مهينو ٿورڙي اصلاح به سٺو نفعو ڏئي سگهي ٿي. تنهن ڪري، اسان اسان جي ڊيٽا جي جسماني جوڙجڪ تي ڌيان ڏنو - ڪيئن بلڪل رڪارڊ اندر "اسٽيڪ ٿيل" فيلڊ هر هڪ ٽيبل.

ڇاڪاڻ ته ڊيٽا جي ترتيب هي سڌو اڳتي آهي نتيجي جي مقدار کي متاثر ڪري ٿو:

ڪيتريون ئي فن تعمير مشين لفظ جي حدن تي ڊيٽا جي ترتيب فراهم ڪن ٿيون. مثال طور، هڪ 32-bit x86 سسٽم تي، انٽيجرز (انٽيجر جو قسم، 4 بائيٽ) 4-بائيٽ لفظ جي چوديواري تي ترتيب ڏنل هوندا، جيئن ڊبل پريسيجن فلوٽنگ پوائنٽ نمبرز (ڊبل پريسيسن فلوٽنگ پوائنٽ، 8 بائيٽ) هوندا. ۽ 64-bit سسٽم تي، ٻٽي قدر 8-بائيٽ لفظ جي حدن سان ترتيب ڏني ويندي. هي غير مطابقت جو هڪ ٻيو سبب آهي.

ترتيب ڏيڻ جي ڪري، ٽيبل جي قطار جي سائيز فيلڊ جي ترتيب تي منحصر آهي. عام طور تي هي اثر تمام قابل ذڪر نه آهي، پر ڪجهه حالتن ۾ اهو سائيز ۾ هڪ اهم اضافو ٿي سگهي ٿو. مثال طور، جيڪڏھن توھان ملايو چار(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

PostgreSQL ۾ وڏي مقدار تي هڪ پئسو بچايو

وڏي ڊيٽا جا ننڍا راز

TOAST اثر کي گھٽائڻ

اثر جو اندازو ڪيئن ڪجي

PostgreSQL 11 تي ڪير سٺو رهي سگهي ٿو

اصطلاحن جي جڳهن کي تبديل ڪرڻ سان، مجموعو ... تبديل ٿئي ٿو!

تبصرو شامل ڪريو جواب منسوخ