🍅 ⬛️ 💖 PostgreSQL Antipatterns: تحديث جدول كبير تحت التحميل 🛌🏻 ✅ ✋🏻

ماذا يجب أن أفعل (وبالتأكيد لا) إذا كنت بحاجة إلى تحديث عدد كبير من السجلات في جدول PostgreSQL "متعدد الملايين" المستخدم بفاعلية - تهيئة قيمة الحقل الجديد أو تصحيح الأخطاء في السجلات الموجودة؟ وفي الوقت نفسه توفير وقتك وعدم خسارة أموال الشركة بسبب التوقف.

قم بإعداد بيانات الاختبار:

CREATE TABLE tbl(k text, v integer); INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k , (random() * 100)::integer v FROM generate_series(1, 1000000) i; --  ,   ! CREATE INDEX ON tbl(k, v);

افترض أننا نريد فقط زيادة قيمة v بمقدار 1 لجميع السجلات التي تحتوي على k في النطاق 'q' .. 'z'.

ولكن قبل بدء التجارب ، سنحفظ مجموعة البيانات الأصلية من أجل الحصول على نتائج "نظيفة" في كل مرة:

 CREATE TABLE _tbl AS TABLE tbl;

استكمال: واحد للجميع ، وكل واحد

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

 UPDATE tbl SET v = v + 1 WHERE k BETWEEN 'q' AND 'z';

[انظروا شرح.tensor.ru]

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

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

© wumo.com/wumo

والأسوأ من ذلك كله هو أن أنظمة الويب يجب أن تفعل ذلك ، حيث يتم إنشاء الاتصالات بقاعدة البيانات حسب الحاجة - بعد كل شيء ، تتراكم مثل هذه الاتصالات "المتدلية" وستستهلك موارد كل من قاعدة البيانات والعميل إذا لم تقم بإنشاء آلية دفاع منفصلة عن ذلك.

انقسام المعاملات

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

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

الاتصال وإدارة المعاملات

بدءًا من PostgreSQL 11 ، من الممكن إدارة المعاملات مباشرةً داخل الكود الإجرائي:

في الإجراءات التي يطلق عليها الأمر CALL ، وكذلك في الكتل المجهولة من الكود (في أمر DO) ، يمكنك إكمال المعاملات عن طريق تنفيذ COMMIT و ROLLBACK. بعد اكتمال المعاملة بواسطة هذه الأوامر ، سيتم بدء واحدة جديدة تلقائيًا.

لكن هذا الإصدار بعيد عن الجميع ، والعمل مع CALL له حدوده. لذلك ، سنحاول حل مشكلتنا دون وسائل خارجية ، وحتى تعمل على جميع الإصدارات الحالية ، وحتى مع الحد الأدنى من التغييرات على الخادم نفسه - حتى لا يكون من الضروري تجميع وإعادة تشغيل أي شيء.

للسبب نفسه ، لن نفكر في خيار تنظيم المعاملات المستقلة من خلال pg_background .

إدارة الاتصالات "داخل" القاعدة

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

 CREATE EXTENSION dblink;

"... والكثير من الأطفال المثير للاشمئزاز أحضروا"

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

الحد الساذج ... OFFSET

الفكرة الأولى هي إجراء بحث "ترقيم الصفحات": "دعنا نختار آلاف السجلات في كل مرة" بزيادة OFFSET في كل طلب جديد:

 UPDATE tbl T SET v = Tv + 1 FROM ( SELECT k , v FROM tbl WHERE k BETWEEN 'q' AND 'z' ORDER BY --       k, v --     ! LIMIT $1 OFFSET $2 * $1 ) S WHERE (Tk, Tv) = (Sk, Sv);

قبل اختبار أداء هذا الحل ، سنقوم باستعادة مجموعة البيانات:

 TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tbl;

كما رأينا في الخطة أعلاه ، سنحتاج إلى تحديث سجلات 384 كيلو بايت تقريبًا. لذلك ، دعونا نرى على الفور كيف سيتم تنفيذ التحديثات أقرب إلى النهاية - في منطقة التكرار 300 من 1000 إدخال :

[انظروا شرح.tensor.ru]

أوه ... تحديث العينة في نهاية سجلات 1K بأكملها سيكلفنا ما يقرب من الوقت الذي يكلفه الإصدار الأصلي بأكمله !

هذا ليس خيارنا. لا يزال من الممكن استخدامها بطريقة ما إذا حصلت على عدد قليل من التكرارات وقيم OFFSET الصغيرة. لأن LIMIT X OFFSET Y لقاعدة البيانات يكافئ " طرح / select / form أول سجلات X + Y ، ثم رمي Y الأولى في سلة المهملات " ، والتي تبدو كبيرة بالنسبة لقيم Y الكبيرة.

في الواقع ، لا يمكن تطبيق هذه الطريقة على الإطلاق ! لا نعتمد فقط على القيم المحدّثة للاختيار ، بل نخاطر أيضًا بتخطي جزء من السجلات وتحديث الجزء الآخر مرتين في حالة وصول الكتل ذات المفاتيح نفسها إلى حد الصفحة:

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

ترتيب حزين ... الحد

لنعدِّل المهمة قليلاً - أضف حقلًا جديدًا سنكتب فيه القيمة v + 1:

 ALTER TABLE tbl ADD COLUMN x integer;

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

تم تدريسها بالفعل من خلال تجربة مريرة ، فلنقم على الفور بإنشاء فهرس سيبقى فيه فقط الإدخالات غير المهيأة:

 CREATE INDEX CONCURRENTLY ON tbl(k, v) WHERE x IS NULL;

لا يحظر فهرس CONCURRENTLY العمل للقراءة والكتابة مع الجدول ، في حين يتم لفه ببطء حتى على مجموعة بيانات ضخمة.

الآن الفكرة هي "دعونا نختار من هذا الفهرس في كل مرة فقط أول ألف سجل " :

 UPDATE tbl T SET x = Tv + 1 FROM ( SELECT k, v FROM tbl WHERE k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1000 --   OFFSET! ) S WHERE (Tk, Tv) = (Sk, Sv) AND Tx IS NULL;

[انظروا شرح.tensor.ru]

بالفعل أفضل بكثير - مدة كل معاملة فردية الآن أقصر بنحو 6 مرات.

ولكن لنرى مرة أخرى ما ستتحول إليه خطة التكرار رقم 200:

 Update on tbl t (actual time=530.591..530.591 rows=0 loops=1) Buffers: shared hit=789337 read=1 dirtied=1

ساء الوقت مرة أخرى (25٪ فقط) ، وزادت المخازن المؤقتة - لكن لماذا؟
والحقيقة هي أن MVCC في PostgreSQL يترك "النفوس الميتة" في الفهرس - إصدارات من السجلات التي تم تحديثها بالفعل ، والآن لم تعد مناسبة للمؤشر. بمعنى أنه مع أخذ أول 1000 سجل فقط في التكرار 200 ، ما زلنا نتفحص ، على الرغم من أننا في وقت لاحق ، نتجاهل الإصدارات 199K السابقة من tuples التي تم تغييرها بالفعل.

إذا كانت التكرارات المطلوبة إلينا ليست عدة مئات ، ولكن عدة مئات الآلاف ، فإن التدهور سيكون أكثر وضوحًا مع كل تنفيذ استعلام لاحق.

تحديث حسب القطاع

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

الزوج المفهرسة (k، v) ممتاز لمهمتنا. لنبني استعلامًا حتى يتمكن من البناء على آخر زوج معالج:

 WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1;

في التكرار الأول ، يكفي أن نقوم بتعيين معلمات الاستعلام على القيمة "صفر" ('' ، 0) ، ولكل تكرار لاحق ، نأخذ نتيجة الاستعلام السابق .

[انظروا شرح.tensor.ru]

وقت المعاملة / القفل أقل من ميلي ثانية واحدة ، ولا يوجد أي انخفاض في عدد التكرارات ، ولا يلزم إجراء مسح أولي كامل لجميع البيانات في الجدول. ! ممتاز

وضع النسخة النهائية مع dblink

 DO $$ DECLARE k text = ''; v integer = 0; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port')); --  PREPARED STATEMENT,      PERFORM dblink($q$ PREPARE _q(text, integer) AS WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1; $q$); -- ,    LOOP SELECT * INTO k, v FROM dblink($p$EXECUTE _q('$p$ || k || $p$',$p$ || v || $p$)$p$) T(k text, v integer); RAISE NOTICE '(k,v) = (''%'',%)', k, v; --   ,     EXIT WHEN (k, v) IS NULL; END LOOP; PERFORM dblink_disconnect(); END; $$ LANGUAGE plpgsql;

ميزة إضافية لهذه الطريقة هي القدرة على مقاطعة تنفيذ هذا البرنامج النصي في أي وقت ، ثم استئناف من النقطة المطلوبة.

حسابات معقدة في التحديث

سوف أذكر الموقف بشكل منفصل مع الحساب الصعب للقيمة المعينة - عندما تحتاج إلى حساب شيء ما من الجداول المرتبطة.

يزيد الوقت المستغرق في الحوسبة أيضًا من مدة المعاملة. لذلك ، سيكون الخيار الأفضل هو أخذ عملية حساب هذه القيم إلى ما بعد التحديث.

على سبيل المثال ، نود أن نملأ حقلنا الجديد x بعدد السجلات التي لها نفس القيمة (k ، v). لنقم بإنشاء جدول "مؤقت" ، لا يفرض إنشاءه أقفال إضافية:

 CREATE TABLE tmp AS SELECT k, v, count(*) x FROM tbl GROUP BY 1, 2; CREATE INDEX ON tmp(k, v);

يمكننا الآن التكرار وفقًا للطراز الموضح أعلاه وفقًا لهذا الجدول ، مع تحديث الهدف:

 UPDATE tbl T SET x = Sx FROM tmp S WHERE (Tk, Tv) = (Sk, Sv) AND (Sk, Sv) = ($1, $2);

كما ترون ، لا يلزم إجراء حسابات معقدة.

فقط تذكر حذف الجدول المساعد لاحقًا.

PostgreSQL Antipatterns: تحديث جدول كبير تحت التحميل