🥇DBA: تنظيم المزامنة والواردات بكفاءة

للمعالجة المعقدة لمجموعات البيانات الكبيرة (مختلفة عمليات ETL: الواردات والتحويلات والمزامنة مع مصدر خارجي) غالبًا ما تكون هناك حاجة "تذكر" مؤقتًا وقم بالمعالجة بسرعة على الفور شيء ضخم.

عادة ما تبدو المهمة النموذجية من هذا النوع كما يلي: "هنا قسم المحاسبة تفريغها من بنك العميل آخر المدفوعات المستلمة، تحتاج إلى تحميلها بسرعة على الموقع وربطها بحساباتك.

ولكن عندما يبدأ حجم هذا "الشيء" بالقياس بمئات الميجابايت، ويجب أن تستمر الخدمة في العمل مع قاعدة البيانات على مدار 24 ساعة طوال أيام الأسبوع، تنشأ العديد من الآثار الجانبية التي ستدمر حياتك.

للتعامل معها في PostgreSQL (وليس فيها فقط)، يمكنك استخدام بعض التحسينات التي ستسمح لك بمعالجة كل شيء بشكل أسرع وباستهلاك أقل للموارد.

1. أين يتم الشحن؟

أولاً، دعونا نقرر أين يمكننا تحميل البيانات التي نريد "معالجتها".

1.1. الجداول المؤقتة (الجدول المؤقت)

من حيث المبدأ، بالنسبة لجداول PostgreSQL المؤقتة هي نفسها مثل أي جداول أخرى. لذلك، الخرافات مثل "كل شيء هناك يتم تخزينه فقط في الذاكرة، ويمكن أن ينتهي". ولكن هناك أيضًا العديد من الاختلافات المهمة.

"مساحة الاسم" الخاصة بك لكل اتصال بقاعدة البيانات

إذا حاول اتصالان الاتصال في نفس الوقت CREATE TABLE x، فسيحصل شخص ما بالتأكيد خطأ غير التفرد كائنات قاعدة البيانات.

ولكن إذا حاول كلاهما التنفيذ CREATE TEMPORARY TABLE x، فسيقوم كلاهما بذلك بشكل طبيعي، وسيحصل عليه الجميع نسختك الجداول. ولن يكون هناك شيء مشترك بينهما.

"التدمير الذاتي" عند قطع الاتصال

عند إغلاق الاتصال، يتم حذف كافة الجداول المؤقتة تلقائيًا، وذلك يدويًا DROP TABLE x ليس هناك فائدة سوى...

إذا كنت تعمل من خلال pgbouncer في وضع المعاملة، فإن قاعدة البيانات تستمر في الاعتقاد بأن هذا الاتصال لا يزال نشطًا، ولا يزال هذا الجدول المؤقت موجودًا فيه.

ولذلك، فإن محاولة إنشائه مرة أخرى، من اتصال مختلف بـ pgbouncer، ستؤدي إلى حدوث خطأ. ولكن يمكن التحايل على ذلك باستخدام CREATE TEMPORARY TABLE IF NOT EXISTS x.

صحيح، من الأفضل عدم القيام بذلك على أي حال، لأنه بعد ذلك يمكنك "فجأة" العثور على البيانات المتبقية من "المالك السابق". بدلاً من ذلك، من الأفضل قراءة الدليل ورؤية أنه عند إنشاء جدول، من الممكن إضافته ON COMMIT DROP - أي أنه عند اكتمال المعاملة، سيتم حذف الجدول تلقائيًا.

عدم التكرار

نظرًا لأنها تنتمي إلى اتصال محدد فقط، لا يتم نسخ الجداول المؤقتة. لكن وهذا يلغي الحاجة للتسجيل المزدوج للبيانات في الكومة + WAL، لذا فإن INSERT/UPDATE/DELETE فيه أسرع بشكل ملحوظ.

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

1.2. جدول غير مسجل

ولكن ماذا يجب أن تفعل، على سبيل المثال، إذا كان لديك نوع من عمليات ETL المرهقة التي لا يمكن تنفيذها في معاملة واحدة، ولكن لا يزال لديك pgbouncer في وضع المعاملة؟ ..

أو أن تدفق البيانات كبير جدًا لا يوجد عرض نطاق ترددي كافٍ على اتصال واحد من قاعدة بيانات (اقرأ، عملية واحدة لكل وحدة المعالجة المركزية)؟..

أو بعض العمليات مستمرة بشكل غير متزامن في اتصالات مختلفة؟..

لا يوجد سوى خيار واحد هنا - إنشاء جدول غير مؤقت مؤقتًا. التورية، نعم. إنه:

قمت بإنشاء جداول "خاصة بي" بأسماء عشوائية إلى أقصى حد حتى لا تتقاطع مع أي شخص
مقتطف: ملأتهم بالبيانات من مصدر خارجي
تحول: تم تحويله، وملء حقول الارتباط الرئيسية
حمل: سكب البيانات الجاهزة في الجداول المستهدفة
حذف الجداول "الخاصة بي".

والآن - ذبابة في المرهم. في الحقيقة، جميع عمليات الكتابة في PostgreSQL تحدث مرتين - الأول في وال، ثم إلى أجسام الجدول/الفهرس. يتم كل هذا لدعم ACID وتصحيح رؤية البيانات بينهما COMMIT"جوزي و." ROLLBACK"المعاملات الفارغة."

لكننا لسنا بحاجة إلى هذا! لدينا العملية برمتها إما أنها كانت ناجحة تماما أو لم تكن كذلك.. لا يهم عدد المعاملات الوسيطة التي ستكون هناك - نحن لسنا مهتمين بـ "مواصلة العملية من المنتصف"، خاصة عندما لا يكون من الواضح مكانها.

للقيام بذلك، قدم مطورو PostgreSQL، في الإصدار 9.1، شيئًا مثل الجداول غير المسجلة:

مع هذه الإشارة، يتم إنشاء الجدول على أنه غير مسجل. البيانات المكتوبة في الجداول غير المسجلة لا تمر عبر سجل الكتابة المسبقة (انظر الفصل 29)، مما يتسبب في حدوث مثل هذه الجداول العمل بشكل أسرع بكثير من المعتاد. ومع ذلك، فهم ليسوا محصنين ضد الفشل؛ في حالة فشل الخادم أو إيقاف التشغيل في حالات الطوارئ، جدول غير مسجل اقتطاعها تلقائيا. بالإضافة إلى ذلك، محتويات الجدول غير المسجل لا تتكرر إلى خوادم الرقيق. أي فهارس تم إنشاؤها على جدول غير مسجل تصبح غير مسجلة تلقائيًا.

باختصار، سيكون أسرع بكثير، ولكن إذا "سقط" خادم قاعدة البيانات، فسيكون الأمر مزعجًا. ولكن كم مرة يحدث هذا، وهل تعرف عملية ETL الخاصة بك كيفية تصحيح ذلك بشكل صحيح "من المنتصف" بعد "تنشيط" قاعدة البيانات؟..

إذا لم يكن الأمر كذلك، والحالة أعلاه مشابهة لحالتك، فاستخدم UNLOGGEDلكن ابدا لا تقم بتمكين هذه السمة على الجداول الحقيقيةالبيانات التي عزيزة عليك.

1.3. عند الالتزام {حذف الصفوف | يسقط}

يتيح لك هذا البناء تحديد السلوك التلقائي عند اكتمال المعاملة عند إنشاء جدول.

حول ON COMMIT DROP لقد كتبت بالفعل أعلاه، فإنه يولد DROP TABLEولكن مع ON COMMIT DELETE ROWS الوضع أكثر إثارة للاهتمام - لقد تم إنشاؤه هنا TRUNCATE TABLE.

نظرًا لأن البنية التحتية الكاملة لتخزين الوصف التعريفي للجدول المؤقت هي نفسها تمامًا مثل الجدول العادي، إذن يؤدي الإنشاء المستمر وحذف الجداول المؤقتة إلى "تضخم" شديد في جداول النظام pg_class، pg_attribute، pg_attrdef، pg_depend،...

تخيل الآن أن لديك عاملًا على اتصال مباشر بقاعدة البيانات، والذي يفتح معاملة جديدة كل ثانية، ويقوم بإنشاء جدول مؤقت وتعبئته ومعالجته وحذفه... سيكون هناك فائض من القمامة المتراكمة في جداول النظام، و سيؤدي ذلك إلى فرامل إضافية لكل عملية.

على العموم لا تفعل هذا! في هذه الحالة هو أكثر فعالية بكثير CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS أخرجها من دورة المعاملات - ثم بحلول بداية كل معاملة جديدة تكون الجداول موجودة بالفعل سوف تكون موجودة (حفظ المكالمة CREATE)، لكن سوف تكون فارغة، благодаря TRUNCATE (كما حفظنا مكالمتها) عند إتمام المعاملة السابقة.

1.4. مثل...بما في ذلك...

ذكرت في البداية أن إحدى حالات الاستخدام النموذجية للجداول المؤقتة هي أنواع مختلفة من الواردات - ويقوم المطور بنسخ قائمة حقول الجدول المستهدف بتعب في إعلان جدوله المؤقت...

لكن الكسل هو محرك التقدم! لهذا إنشاء جدول جديد "بناءً على العينة" يمكن أن يكون أبسط من ذلك بكثير:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

وبما أنه يمكنك بعد ذلك إنشاء الكثير من البيانات في هذا الجدول، فلن يكون البحث فيه سريعًا أبدًا. ولكن هناك حل تقليدي لهذا - الفهارس! ونعم، يمكن أن يحتوي الجدول المؤقت أيضًا على فهارس.

نظرًا لأن الفهارس المطلوبة غالبًا ما تتزامن مع فهارس الجدول الهدف، فيمكنك الكتابة ببساطة LIKE target_table INCLUDING INDEXES.

إذا كنت بحاجة أيضا DEFAULT-القيم (على سبيل المثال، لملء قيم المفتاح الأساسي)، يمكنك استخدامها LIKE target_table INCLUDING DEFAULTS. أو ببساطة - LIKE target_table INCLUDING ALL - نسخ الإعدادات الافتراضية، والفهارس، والقيود، ...

ولكن هنا عليك أن تفهم أنه إذا قمت بإنشائها قم باستيراد الجدول فورًا باستخدام الفهارس، فسيستغرق تحميل البيانات وقتًا أطولمما لو قمت بملء كل شيء أولاً، وبعد ذلك فقط قمت بلف الفهارس - انظر إلى كيفية القيام بذلك كمثال pg_dump.

بشكل عام، RTFM!

2. كيف تكتب؟

اسمحوا لي فقط أن أقول - استخدمه COPY-flow بدلاً من "pack" INSERT, التسارع في بعض الأحيان. يمكنك حتى مباشرة من ملف تم إنشاؤه مسبقًا.

3. كيفية المعالجة؟

لذا، دعونا نجعل مقدمتنا تبدو كالتالي:

لديك جدول يحتوي على بيانات العميل المخزنة في قاعدة البيانات الخاصة بك 1 مليون سجل
كل يوم يرسل لك عميل جديد "الصورة" كاملة
من التجربة تعلم ذلك من وقت لآخر لا يتم تغيير أكثر من 10 آلاف سجل

والمثال الكلاسيكي لمثل هذا الموقف هو قاعدة كلادر - هناك الكثير من العناوين في المجمل، ولكن في كل تحميل أسبوعي هناك تغييرات قليلة جدًا (إعادة تسمية المستوطنات، الجمع بين الشوارع، ظهور منازل جديدة) حتى على المستوى الوطني.

3.1. خوارزمية المزامنة الكاملة

من أجل التبسيط، لنفترض أنك لا تحتاج حتى إلى إعادة هيكلة البيانات - ما عليك سوى إحضار الجدول إلى النموذج المطلوب، أي:

نزع كل ما لم يعد موجودا
تحديث كل ما هو موجود بالفعل ويحتاج إلى تحديث
لإدراج كل ما لم يحدث بعد

لماذا يجب أن تتم العمليات بهذا الترتيب؟ لأن هذه هي الطريقة التي سينمو بها حجم الجدول إلى الحد الأدنى (تذكر MVCC!).

حذف من التوقيت الصيفي

لا، بالطبع يمكنك القيام بعمليتين فقط:

نزع (DELETE) كل شيء بشكل عام
لإدراج كل ذلك من الصورة الجديدة

لكن في نفس الوقت، بفضل MVCC، سيزيد حجم الجدول مرتين بالضبط! يعد الحصول على +1M من صور السجلات في الجدول بسبب تحديث 10K بمثابة تكرار كبير...

اقتطاع التوقيت الصيفي

يعرف المطور الأكثر خبرة أنه يمكن تنظيف الجهاز اللوحي بأكمله بتكلفة زهيدة:

نظيف (TRUNCATE) الجدول بأكمله
لإدراج كل ذلك من الصورة الجديدة

الطريقة فعالة , في بعض الأحيان ينطبق تماما، ولكن هناك مشكلة... سنضيف مليون سجل لفترة طويلة، لذلك لا يمكننا ترك الجدول فارغًا طوال هذا الوقت (كما سيحدث بدون تغليفه في معاملة واحدة).

مما يعني:

لقد بدأنا معاملة طويلة الأمد
TRUNCATE يفرض وصول حصري-حجب
نحن نفعل الإدراج لفترة طويلة، والجميع في هذا الوقت لا أستطيع حتى SELECT

هناك شيء لا يسير على ما يرام...

تغيير الجدول... إعادة تسمية... / إسقاط الجدول...

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

لا يزال أيضا وصول حصري، على الرغم من وقت أقل بكثير
تتم إعادة تعيين جميع خطط / إحصائيات الاستعلام لهذا الجدول، تحتاج إلى تشغيل ANALYZE
جميع المفاتيح الخارجية مكسورة (FK) إلى الطاولة

كان هناك تصحيح WIP من Simon Riggs الذي اقترح صنعه ALTER-عملية استبدال نص الجدول على مستوى الملف، دون لمس الإحصائيات وFK، ولكن لم يتم جمع النصاب القانوني.

حذف، تحديث، إدراج

لذلك، نستقر على خيار عدم الحظر المتمثل في ثلاث عمليات. ما يقرب من ثلاثة... كيف نفعل هذا بشكل أكثر فعالية؟

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие

-- вставляем отсутствующие
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. استيراد ما بعد المعالجة

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

إذا كانت العملية الخاصة بك فقط تتمتع بحق الوصول للكتابة في وقت المزامنة، فيمكنك استخدام المشغل الذي سيجمع كل التغييرات لنا:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

يمكننا الآن تطبيق المشغلات قبل بدء المزامنة (أو تمكينها عبر ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

ثم نستخرج بهدوء جميع التغييرات التي نحتاجها من جداول السجل ونقوم بتشغيلها من خلال معالجات إضافية.

3.3. استيراد المجموعات المرتبطة

لقد نظرنا أعلاه في الحالات التي تكون فيها هياكل البيانات للمصدر والوجهة متماثلة. ولكن ماذا لو كان التحميل من نظام خارجي بتنسيق مختلف عن بنية التخزين في قاعدة البيانات الخاصة بنا؟

لنأخذ كمثال تخزين العملاء وحساباتهم، الخيار الكلاسيكي "متعدد إلى واحد":

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

لكن التنزيل من مصدر خارجي يأتي إلينا على شكل "الكل في واحد":

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

من الواضح أنه يمكن تكرار بيانات العميل في هذا الإصدار، والسجل الرئيسي هو "الحساب":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

بالنسبة للنموذج، سنقوم ببساطة بإدخال بيانات الاختبار الخاصة بنا، ولكن تذكر - COPY أكثر فعالية!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

أولاً، دعونا نسلط الضوء على تلك "التخفيضات" التي تشير إليها "حقائقنا". في حالتنا، تشير الفواتير إلى العملاء:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

من أجل ربط الحسابات بمعرفات العملاء بشكل صحيح، نحتاج أولاً إلى اكتشاف هذه المعرفات أو إنشائها. دعونا نضيف الحقول تحتها:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

دعونا نستخدم طريقة مزامنة الجدول الموضحة أعلاه مع تعديل بسيط - لن نقوم بتحديث أو حذف أي شيء في الجدول المستهدف، لأننا نستورد العملاء "للإلحاق فقط":

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

في الواقع، كل شيء وارد invoice_import الآن قمنا بملء حقل الاتصال client_id، والتي سوف نقوم بإدخال الفاتورة بها.

المصدر: www.habr.com

DBA: تنظيم المزامنة والواردات بكفاءة