وفر فلسًا واحدًا على الكميات الكبيرة في 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)
);

أي إذا كان علينا كتابة سلسلة ذات قيمة "كبيرة". data، ثم سيتم التسجيل الحقيقي ليس فقط للطاولة الرئيسية وPK الخاصة بها، ولكن أيضًا لـ TOAST وPK الخاص بها.

تقليل تأثير الخبز المحمص

لكن معظم سجلاتنا لا تزال ليست بهذا الحجم، يجب أن تتناسب مع 8 كيلو بايت - كيف يمكنني توفير المال على هذا؟..

هذا هو المكان الذي تأتي فيه السمة لمساعدتنا 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 مرتين أقل، الذي لم يفرغ القرص فحسب، بل وحدة المعالجة المركزية أيضًا:

سأشير إلى أننا أصبحنا أصغر أيضًا في "قراءة" القرص، وليس فقط "الكتابة" - حيث أنه عند إدراج سجل في جدول، يتعين علينا أيضًا "قراءة" جزء من شجرة كل فهرس لتحديد مكانه الموقف المستقبلي فيهم.

من يمكنه العيش بشكل جيد على 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 بالمائة! ولكن ربما أثر هذا بطريقة أو بأخرى على وحدة المعالجة المركزية؟

على الأقل لم يصبح الأمر أسوأ. على الرغم من أنه من الصعب الحكم على ما إذا كانت هذه الأحجام لا تزال غير قادرة على رفع متوسط ​​حمل وحدة المعالجة المركزية بشكل أعلى 5%.

وبتغيير مواضع المصطلحات يتغير المجموع...!

كما تعلمون، فإن قرشًا واحدًا يوفر روبلًا، وهذا الأمر يتعلق بأحجام التخزين لدينا 10 تيرابايت/شهر حتى القليل من التحسين يمكن أن يحقق ربحًا جيدًا. لذلك، أولينا اهتمامًا للبنية المادية لبياناتنا - كيف بالضبط الحقول "المكدسة" داخل السجل كل من الجداول.

لأنه بسبب محاذاة البيانات هذا هو على التوالي إلى الأمام يؤثر على الحجم الناتج:

توفر العديد من البنيات محاذاة البيانات على حدود الكلمات الآلية. على سبيل المثال، في نظام 32 بت x86، سيتم محاذاة الأعداد الصحيحة (نوع عدد صحيح، 4 بايت) على حد كلمة مكون من 4 بايت، كما هو الحال مع أرقام الفاصلة العائمة ذات الدقة المزدوجة (النقطة العائمة مزدوجة الدقة، 8 بايت). وعلى نظام 64 بت، سيتم محاذاة القيم المزدوجة لحدود الكلمات المكونة من 8 بايت. وهذا سبب آخر لعدم التوافق.

بسبب المحاذاة، يعتمد حجم صف الجدول على ترتيب الحقول. عادةً لا يكون هذا التأثير ملحوظًا جدًا، ولكن في بعض الحالات يمكن أن يؤدي إلى زيادة كبيرة في الحجم. على سبيل المثال، إذا قمت بخلط حقول char(1) وinteger، فسيكون هناك عادةً 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