PostgreSQL এ বড় ভলিউমে একটি পয়সা সংরক্ষণ করুন

দ্বারা উত্থাপিত বড় ডেটা স্ট্রীম রেকর্ডিং বিষয় অব্যাহত বিভাজন সম্পর্কে পূর্ববর্তী নিবন্ধ, এই আমরা উপায় দেখব যা আপনি করতে পারেন সঞ্চিত এর "শারীরিক" আকার হ্রাস করুন PostgreSQL-এ, এবং সার্ভার কর্মক্ষমতার উপর তাদের প্রভাব।

আমরা সম্পর্কে কথা হবে TOAST সেটিংস এবং ডেটা প্রান্তিককরণ. "গড়ে," এই পদ্ধতিগুলি খুব বেশি সংস্থান সংরক্ষণ করবে না, তবে অ্যাপ্লিকেশন কোডটি মোটেও সংশোধন না করে।

যাইহোক, আমাদের অভিজ্ঞতা এই বিষয়ে খুব ফলপ্রসূ প্রমাণিত হয়েছে, যেহেতু প্রায় কোনও পর্যবেক্ষণের স্টোরেজ এর প্রকৃতি দ্বারা বেশিরভাগই শুধুমাত্র যোগ করুন রেকর্ড করা তথ্য পরিপ্রেক্ষিতে. এবং যদি আপনি ভাবছেন যে আপনি কীভাবে ডাটাবেসকে পরিবর্তে ডিস্কে লিখতে শেখাতে পারেন 200MB / সেকেন্ড অর্ধেক হিসাবে অনেক - বিড়াল অধীনে দয়া করে.

বড় তথ্যের ছোট গোপনীয়তা

কাজের প্রোফাইল দ্বারা আমাদের সেবা, তারা নিয়মিত lairs থেকে তার কাছে উড়ে পাঠ্য প্যাকেজ.

এবং যেহেতু ভিএলএসআই কমপ্লেক্সযার ডাটাবেস আমরা নিরীক্ষণ করি জটিল ডাটা স্ট্রাকচার সহ একটি মাল্টি-কম্পোনেন্ট প্রোডাক্ট, তারপর ক্যোয়ারী সর্বোচ্চ কর্মক্ষমতা জন্য বেশ এই মত চালু জটিল অ্যালগরিদমিক যুক্তি সহ "মাল্টি-ভলিউম". সুতরাং একটি অনুরোধের প্রতিটি স্বতন্ত্র দৃষ্টান্তের ভলিউম বা আমাদের কাছে আসা লগটিতে ফলস্বরূপ কার্যকরী পরিকল্পনাটি "গড়ে" বেশ বড় বলে প্রমাণিত হয়।

আসুন আমরা "কাঁচা" ডেটা লিখি এমন একটি টেবিলের কাঠামোটি দেখি - অর্থাৎ, লগ এন্ট্রি থেকে মূল পাঠ্যটি এখানে রয়েছে:

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) লিখতে, ব্যবহার করুন টোস্ট প্রযুক্তি:

PostgreSQL একটি নির্দিষ্ট পৃষ্ঠার আকার ব্যবহার করে (সাধারণত 8 KB), এবং টিপলকে একাধিক পৃষ্ঠা স্প্যান করার অনুমতি দেয় না। অতএব, খুব বড় ক্ষেত্রের মান সরাসরি সংরক্ষণ করা অসম্ভব। এই সীমাবদ্ধতা কাটিয়ে উঠতে, বৃহৎ ক্ষেত্রের মানগুলিকে সংকুচিত করা হয় এবং/অথবা একাধিক ভৌত রেখা জুড়ে বিভক্ত করা হয়। এটি ব্যবহারকারীর অলক্ষ্যে ঘটে এবং বেশিরভাগ সার্ভার কোডের উপর সামান্য প্রভাব ফেলে। এই পদ্ধতিটি TOAST নামে পরিচিত...

প্রকৃতপক্ষে, "সম্ভাব্যভাবে বড়" ক্ষেত্র সহ প্রতিটি টেবিলের জন্য, স্বয়ংক্রিয়ভাবে "স্লাইসিং" সহ একটি জোড়া টেবিল তৈরি করা হয়েছে 2KB বিভাগে প্রতিটি "বড়" রেকর্ড:

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

অর্থাৎ, যদি আমাদের একটি "বড়" মান সহ একটি স্ট্রিং লিখতে হয় data, তারপর বাস্তব রেকর্ডিং ঘটবে শুধুমাত্র প্রধান টেবিল এবং এর পিকে নয়, টোস্ট এবং এর পিকেও.

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_TUPLE_TARGET বাইটের (ভেরিয়েবল মান, সাধারণত 2 KB) থেকে কম না হওয়া পর্যন্ত TOAST কোডটি টেবিলের বাইরে ক্ষেত্র মানগুলিকে সংকুচিত করবে এবং/অথবা স্থানান্তর করবে বা আকার কমানো যাবে না৷

আমরা সিদ্ধান্ত নিয়েছি যে আমাদের কাছে সাধারণত যে ডেটা থাকে তা হয় "খুব ছোট" বা "খুব দীর্ঘ", তাই আমরা ন্যূনতম সম্ভাব্য মানের মধ্যে নিজেদের সীমাবদ্ধ রাখার সিদ্ধান্ত নিয়েছি:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

আসুন দেখি কিভাবে নতুন সেটিংস পুনরায় কনফিগারেশনের পরে ডিস্ক লোডিংকে প্রভাবিত করে:

খারাপ না! গড় ডিস্কের সারি কমে গেছে প্রায় 1.5 বার, এবং ডিস্ক "ব্যস্ত" 20 শতাংশ! কিন্তু সম্ভবত এই একরকম CPU প্রভাবিত?

অন্তত এটা কোন খারাপ পেতে না. যদিও, এমনকি এই ধরনের ভলিউম এখনও গড় 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