التحسين الشامل لاستعلامات PostgreSQL. كيريل بوروفيكوف (تينسور)

يقدم التقرير بعض الأساليب التي تسمح مراقبة أداء استعلامات SQL عندما يكون هناك الملايين منها يوميًا، وهناك المئات من خوادم PostgreSQL الخاضعة للرقابة.

ما هي الحلول التقنية التي تسمح لنا بمعالجة هذا الحجم من المعلومات بكفاءة ، وكيف تجعل الحياة أسهل لمطور عادي.

من هو المهتم تحليل مشاكل محددة وتقنيات التحسين المختلفة استعلامات SQL وحل مهام DBA النموذجية في PostgreSQL - يمكنك ذلك أيضًا انظر سلسلة من المقالات حول هذا الموضوع.


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

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

بشكل عام ، "Tensor" لمليون من عملائنا هو VLSI - تطبيقنا: شبكة اجتماعية للشركات ، حلول لاتصالات الفيديو ، لإدارة المستندات الداخلية والخارجية ، أنظمة المحاسبة للمحاسبة والمستودعات ، ... أي ، مثل "megacombine" لإدارة الأعمال المتكاملة ، حيث يوجد أكثر من 100 مشروع داخلي مختلف .

لكي يعملوا جميعًا ويتطوروا بشكل طبيعي ، لدينا 10 مراكز تطوير في جميع أنحاء البلاد ، ولديهم المزيد 1000 مطور.

لقد عملنا مع PostgreSQL منذ عام 2008 وقمنا بتجميع قدر كبير مما نعالج - وهي بيانات العميل ، والبيانات الإحصائية والتحليلية ، والبيانات من أنظمة المعلومات الخارجية - أكثر من 400 تيرابايت. فقط "قيد الإنتاج" يوجد حوالي 250 خادمًا ، وفي المجموع هناك حوالي 1000 خادم قاعدة بيانات نراقبها.

SQL هي لغة تعريفية. أنت لا تصف "كيف" يجب أن يعمل شيء ما ، ولكن "ما" تريد الحصول عليه. يعرف نظام إدارة قواعد البيانات (DBMS) بشكل أفضل كيفية القيام بـ JOIN - كيفية توصيل الجداول الخاصة بك ، وما هي الشروط التي يجب فرضها ، وما الذي يمر عبر الفهرس ، وما الذي لن ...

تقبل بعض نظم إدارة قواعد البيانات تلميحات: "لا ، انضم إلى هذين الجدولين في قائمة الانتظار كذا وكذا" ، لكن PostgreSQL لا تعرف كيف. هذا هو الموقف الواعي للمطورين الرائدين: "من الأفضل إنهاء مُحسِّن الاستعلام بدلاً من السماح للمطورين باستخدام نوع من التلميحات".

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

بشكل عام ، ما هي المشاكل التقليدية التي عادة ما يأتي المطور [إلى DBA]؟ "هنا قمنا بتنفيذ الطلب ، و نحن بطيئون، كل شيء معلق ، شيء ما يحدث ... نوع من المتاعب!

الأسباب هي نفسها دائمًا تقريبًا:

• خوارزمية استعلام غير فعالة
  المطور: "لدي الآن 10 جداول في SQL له من خلال JOIN ..." - ويتوقع أن ظروفه ستفكك بفعالية بأعجوبة ، وسيحصل على كل شيء بسرعة. لكن المعجزات لا تحدث ، وأي نظام به مثل هذا التباين (10 جداول في واحد من FROM) يعطي دائمًا نوعًا من الخطأ. [مقالة]
• إحصاءات عفا عليها الزمن
  هذه اللحظة مناسبة جدًا بشكل خاص لـ PostgreSQL ، عندما تقوم "بسكب" مجموعة بيانات كبيرة على الخادم ، قم بتقديم طلب - ويقوم "بفحص الجنس" على اللوحة. لأنه بالأمس احتوت على 10 سجلات ، واليوم هناك 10 ملايين ، لكن PostgreSQL لم تدرك هذا الأمر بعد ، وتحتاج إلى المطالبة بذلك. [مقالة]
• "توصيل" الموارد
  تضع قاعدة بيانات كبيرة وثقيلة محملة على خادم ضعيف لا يحتوي على قرص أو ذاكرة كافية أو أداء المعالج نفسه. وهذا كل شيء ... في مكان ما يوجد سقف أداء لا يمكنك القفز فوقه.
• الحجب
  لحظة صعبة ، لكنها أكثر صلة باستعلامات التعديل المختلفة (INSERT ، UPDATE ، DELETE) - هذا موضوع كبير منفصل.

احصل على خطة

... ولكل شيء آخر ، نحن بحاجة الى خطة! نحتاج أن نرى ما يحدث داخل الخادم.

خطة تنفيذ الاستعلام لـ PostgreSQL عبارة عن شجرة خوارزمية لتنفيذ الاستعلام في التمثيل النصي. بالضبط الخوارزمية التي تم التعرف عليها على أنها الأكثر فعالية نتيجة للتحليل من قبل المخطط.

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

للحصول على خطة الاستعلام ، أسهل طريقة هي تنفيذ العبارة EXPLAIN. للحصول على جميع السمات الحقيقية ، أي تنفيذ استعلام فعلي على القاعدة - EXPLAIN (ANALYZE, BUFFERS) SELECT ....

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

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

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

يبدو أن كل شيء على ما يرام الآن ، نذهب إلى السجل ونرى هناك ... [قطعة قماشية من النص]. لكن لا يمكننا قول أي شيء عنها ، باستثناء حقيقة أن هذه خطة ممتازة ، لأنها استغرقت 11 ملي ثانية حتى تكتمل.

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

ولكن حتى لو لم يكن الأمر واضحًا ، حتى لو كان غير مريح ، فهناك المزيد من المشكلات الأساسية:

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

في مثل هذه السيناريوهات ، افهم "من هو الحلقة الأضعف؟" عمليا غير واقعي. لذلك ، حتى المطورين أنفسهم في "دليل" كتابة ذلك "فهم الخطة هو فن يجب تعلمه ، تجربة ...".

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

تصور الخطة

لذلك ، أدركنا أنه من أجل التعامل مع هذه المشاكل ، نحتاج تصور جيد للخطة. [شرط]

ذهبنا أولاً إلى "السوق" - لنلق نظرة على الإنترنت لمعرفة ما هو موجود بشكل عام.

ولكن اتضح أن هناك عددًا قليلاً جدًا من الحلول "الحية" نسبيًا التي تم تطويرها بشكل أو بآخر - حرفيًا ، هناك شيء واحد: Depesz.com بواسطة Hubert Lubaczewski. عند مدخل الحقل "تغذية" التمثيل النصي للخطة ، يُظهر لك لوحة بها بيانات تم تحليلها:

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

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

لكن هناك أيضًا مشاكل صغيرة.

أولا ، كمية ضخمة من "نسخ ولصق". تأخذ قطعة من السجل ، وتضعها هناك ، مرارًا وتكرارًا.

الثاني، لا يوجد تحليل لكمية البيانات التي تمت قراءتها - نفس المخازن المؤقتة التي تنتج EXPLAIN (ANALYZE, BUFFERS)، لا نراه هنا. إنه ببساطة لا يعرف كيفية تفكيكها وفهمها والعمل معها. عندما تقرأ الكثير من البيانات وتدرك أنك قد لا تتحلل بشكل صحيح عبر القرص وذاكرة التخزين المؤقت في الذاكرة ، فإن هذه المعلومات مهمة للغاية.

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

لكن هذه كلها "كلمات" ، يمكن للمرء أن يتعايش معها بطريقة ما ، ولكن هناك شيء واحد أبعدنا عن هذه الخدمة. هذه هي أخطاء التحليل الشائعة لتعبير الجدول (CTE) والعقد الديناميكية المختلفة مثل InitPlan / SubPlan.

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

ثم أدركنا أن الوقت قد حان لكتابة منطقتنا - الصيحة! يقول كل مطور: "الآن سنكتب ما يخصنا ، سيكون الأمر سهلاً للغاية!"

لقد أخذنا مكدسًا نموذجيًا لخدمات الويب: جوهر Node.js + Express ، وسحبنا Bootstrap و D3.js للحصول على مخططات جميلة. وكانت توقعاتنا مبررة تمامًا - تلقينا النموذج الأولي الأول في أسبوعين:

• محلل الخطة المخصصة
  أي أنه يمكننا الآن تحليل أي خطة بشكل عام من تلك التي تم إنشاؤها بواسطة PostgreSQL.
• التحليل الصحيح للعقد الديناميكية - مسح CTE ، InitPlan ، SubPlan
• تحليل توزيع المخازن المؤقتة - حيث تتم قراءة صفحات البيانات من الذاكرة ، ومن ذاكرة التخزين المؤقت المحلية ، ومن القرص
• حصلت على الرؤية
  من أجل عدم "حفر" كل شيء في السجل ، ولكن لرؤية "الحلقة الأضعف" على الفور في الصورة.

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

هذا هو التمثيل المختصر الذي نسميه نموذج الخطة.

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

نشير إلى العقدة ونرى - اتضح أن Seq Scan استغرق أقل من ربع الوقت الإجمالي ، وتم أخذ 3/4 المتبقي بواسطة CTE Scan. رعب! هذه ملاحظة صغيرة حول "معدل إطلاق النار" في CTE Scan ، إذا كنت تستخدمها بنشاط في استفساراتك. إنها ليست سريعة جدًا - فهي تخسر حتى عند إجراء مسح ضوئي منتظم للطاولة. [شرط] [شرط]

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

بطبيعة الحال ، من دون "أشعل النار" لا يمكن أن تفعل.

أول شيء "داسوا عليه" كان مشكلة التقريب. يشار إلى وقت العقدة لكل عقدة فردية في الخطة بدقة 1 ميكرو ثانية. وعندما يتجاوز عدد دورات العقدة ، على سبيل المثال ، 1000 - بعد التنفيذ ، قسمت PostgreSQL "حتى" ، ثم عند الحساب بالعكس ، نحصل على الوقت الإجمالي "بين 0.95 مللي ثانية و 1.05 مللي ثانية". عندما ينتقل العد إلى ميكروثانية ، فإنه لا يزال لا شيء ، ولكن عندما يكون بالفعل [ملي] ثانية ، عليك أن تأخذ هذه المعلومات في الاعتبار عند "فك التواء" الموارد وفقًا لعقد الخطة "من استهلك مقدار من".

النقطة الثانية ، الأكثر تعقيدًا ، هي توزيع الموارد (تلك المخازن المؤقتة) بين العقد الديناميكية. لقد كلفنا ذلك 2 أسابيع أخرى للأسبوعين الأولين للنموذج الأولي.

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

ننظر إلى الخطة ونفهم - بشكل غريب ، كان لدينا 3 مخازن مؤقتة (صفحات بيانات) "مستهلكة" في Seq Scan ، وواحدة أخرى في CTE Scan ، و 1 أخرى في CTE Scan الثاني. أي ، إذا قمنا ببساطة بتلخيص كل شيء ، فسنحصل على 2 ، لكن من الجهاز اللوحي نقرأ 6 فقط! لا يقرأ CTE Scan أي شيء من أي مكان ، ولكنه يعمل مباشرة مع ذاكرة العملية. لذا من الواضح أن هناك شيئًا خاطئًا!

في الواقع ، اتضح أنه هنا جميع هذه الصفحات الثلاث من البيانات التي تم طلبها من Seq Scan ، طلبت أولاً 3 مسح CTE الأول ، ثم تمت قراءة الثانية ، و 1 أخريين. أي ما مجموعه 1 تمت قراءة صفحات البيانات ، وليس 2.

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

أخذنا نفسا لفترة من الوقت. قال ، "الآن ، نيو ، أنت تعرف الكونغ فو! الآن تجربتنا مباشرة على شاشتك. الآن يمكنك استخدامه ". [شرط]

توحيد السجل

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

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

أولاً ، يقوم بتعيين نفس الاستعلامات ضمن مخططات مختلفة داخل نفس قاعدة البيانات استعلام مختلفة. هذا هو ، إذا فعلت ذلك أولاً SET search_path = '01'; SELECT * FROM user LIMIT 1;ثم SET search_path = '02'; ونفس الاستعلام ، ستكون هناك إدخالات مختلفة في إحصائيات هذه الوحدة ، ولن أتمكن من جمع إحصاءات عامة على وجه التحديد في سياق ملف تعريف الاستعلام هذا ، دون مراعاة المخططات.

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

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

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

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

نظرًا لأننا بدأنا بالفعل في كتابة الواجهة في Node.js ، فقد واصلنا كتابة المُجمع عليها. وقد بررت هذه التقنية نفسها ، لأنه من الملائم جدًا استخدام JavaScript للعمل مع بيانات نصية ذات تنسيق خفيف ، وهو السجل. والبنية التحتية Node.js نفسها كنظام أساسي للخلفية تجعل من السهل والملائم العمل مع اتصالات الشبكة ، وفي الواقع مع نوع من تدفقات البيانات.

وفقًا لذلك ، نقوم "بتمديد" وصلتين: الأولى ، من أجل "الاستماع" إلى السجل نفسه وأخذها لأنفسنا ، والثانية ، من أجل طرح الأسئلة بشكل دوري على القاعدة. "ولكن في السجل ظهر أنه تم حظر الجهاز اللوحي المزود بـ oid 123 ،" ولكن هذا لا يعني أي شيء للمطور ، وسيكون من الجيد أن تسأل القاعدة "ما هو OID = 123 على أي حال؟" ولذا فإننا نسأل القاعدة بشكل دوري ما لا نعرفه حتى الآن.

"شيء واحد فقط تركته ، هناك نوع من النحل يشبه الفيل! .." بدأنا في تطوير هذا النظام عندما أردنا مراقبة 10 خوادم. الأكثر أهمية في فهمنا ، حيث نشأت بعض المشاكل التي كان من الصعب التعامل معها. لكن خلال الربع الأول ، تلقينا مائة للمراقبة - لأن النظام "دخل" ، أراده الجميع ، وهو مناسب للجميع.

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

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

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

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

اللحظة الثالثة - اضطررت إلى ذلك رفض المشغلات ، على التوالي ، ومن المفاتيح الخارجية. أي ليس لدينا تكامل مرجعي على الإطلاق. لأنه إذا كان لديك جدول به زوج من FKs ، وقلت في بنية قاعدة البيانات "هنا إدخال سجل مشار إليه بواسطة FK ، على سبيل المثال ، إلى مجموعة من الإدخالات" ، فعند إدراجه ، PostgreSQL لم يتبق منه سوى كيفية التنفيذ والتنفيذ بأمانة SELECT 1 FROM master_fk1_table WHERE ... مع المعرف الذي تحاول إدراجه - فقط للتحقق من وجود هذا الإدخال هناك ، وأنك لا "تقطع" هذا المفتاح الخارجي مع الإدخال.

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

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

تخيل الآن أن لديك جدولًا تحسب فيه ببساطة عدد الطلبات التي مرت عبر مضيف معين: +1, +1, +1, ..., +1. وأنت ، من حيث المبدأ ، لا تحتاج إليها - كل هذا ممكن المجموع في الذاكرة على المجمع وإرسالها إلى القاعدة دفعة واحدة +10.

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

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

لقد كان من الأفضل لنا استخدام MD5 من نص - طلب ، خطة ، قالب ، ... نحسبه من جانب المُجمع ، و "نصب" معرّفًا جاهزًا في قاعدة البيانات. طول MD5 والتقسيم اليومي يجعلنا لا نقلق بشأن الاصطدامات المحتملة.

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

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

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

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

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

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

قبل تنفيذ هذا النهج في كتابة البيانات ، كان لدينا حوالي 4K عمليات كتابة ، وبهذه الطريقة قللنا الحمل بمقدار 4 مرات. الآن نمت 6 مرات أخرى بسبب قواعد البيانات المراقبة الجديدة - حتى 100 ميجابايت / ثانية. والآن نقوم بتخزين السجلات للأشهر الثلاثة الماضية بحجم حوالي 3-10 تيرابايت ، على أمل أن يتمكن أي مطور من حل أي مشكلة في غضون ثلاثة أشهر.

نحن نتفهم المشاكل

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

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

لقد حددنا ثلاث نقاط رئيسية لأنفسنا:

• الذي أرسل هذا الطلب
  أي ، من أي تطبيق "وصل": واجهة الويب ، الواجهة الخلفية ، نظام الدفع ، أو أي شيء آخر.
• حيث لقد حدث
  على ما الخادم المحدد. لأنه إذا كان لديك عدة خوادم ضمن تطبيق واحد ، وفجأة "ضعف" أحدها (لأن "القرص فاسد" ، و "تسرب الذاكرة" ، وبعض المشاكل الأخرى) ، فأنت بحاجة إلى معالجة الخادم على وجه التحديد.
• كيف تجلت المشكلة بطريقة أو بأخرى

لفهم "من" أرسل إلينا طلبًا ، نستخدم أداة عادية - تعيين متغير جلسة: SET application_name = '{bl-host}:{bl-method}'; - نرسل اسم مضيف منطق العمل الذي يأتي منه الطلب ، واسم الطريقة أو التطبيق الذي بدأه.

بعد أن نجتاز "سيد" الطلب ، يجب أن يتم عرضه في السجل - لهذا نقوم بتهيئة المتغير log_line_prefix = ' %m [%p:%v] [%d] %r %a'. ربما من يهتم ابحث في الدليلما يعنيه كل هذا. اتضح أننا نرى في السجل:

• وقت
• معرفات العملية والمعاملات
• الاسم الأساسي
• عنوان IP للشخص الذي أرسل هذا الطلب
• واسم الطريقة

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

لذلك ، قطع "خادم واحد - يوم واحد" وجدنا ما يكفي لأي تحليل.

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

عندما انتقلنا من النماذج الملموسة إلى النماذج ، حصلنا على ميزتين في آنٍ واحد:

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

تعتمد الطرق المتبقية على المؤشرات التي نستخرجها من الخطة: كم مرة حدث مثل هذا النمط ، والوقت الإجمالي والمتوسط ​​، وكم البيانات التي تمت قراءتها من القرص ، وكم من الذاكرة ...

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

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

ويمكنك على الفور رؤية تطبيقات مختلفة تتوافق مع نفس القالب من طلب مثل SELECT * FROM users WHERE login = 'Vasya'. الواجهة الأمامية والخلفية والمعالجة ... وتتساءل لماذا يجب قراءة المعالجة على المستخدم إذا لم يتفاعل معه.

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

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

ليس لديك فهرس مناسب على اللوحة ، فأنت تقدم طلبًا إليه ، فهو يتخطى الفهرس ، ويقع في Seq Scan ... لقد قمت بتصفية جميع السجلات باستثناء واحد. ولماذا تحتاج إلى 100 مليون سجل مصفى في اليوم ، أليس من الأفضل أن نشمر على الفهرس؟

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

ربما صادف كل من كتب الطلبات مثل هذا النمط: "أعطني آخر طلب لـ Vasya ، تاريخه." وإذا لم يكن لديك فهرس حسب التاريخ ، أو لم يكن هناك تاريخ في الفهرس الذي استخدمته ، فخطوة بالضبط على مثل هذا "أشعل النار".

لكننا نعلم أن هذا "أشعل النار" - فلماذا لا تخبر المطور على الفور بما يجب عليه فعله. وفقًا لذلك ، عند فتح الخطة الآن ، يرى مطورنا على الفور صورة جميلة مع تلميحات ، حيث يتم إخباره على الفور: "لديك مشاكل هنا وهناك ، ولكن يتم حلها بهذه الطريقة وذاك."

نتيجة لذلك ، انخفض مقدار الخبرة التي كانت مطلوبة لحل المشكلات في البداية والآن بشكل كبير. هنا لدينا مثل هذه الأداة.

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