أكثر

لماذا يكون تنفيذ الاستعلام بطيئًا جدًا باستخدام PostGis؟

لماذا يكون تنفيذ الاستعلام بطيئًا جدًا باستخدام PostGis؟


أقوم بتطبيق هذا الاستعلام على 8257800 نقطة ولكنه يستغرق 15 ساعة ولم يُرجع أي نتائج.

حدد أ. .geom) و abs (a.value - b.value)> 400

هل هناك أي اقتراحات لتقديم هذا الاستعلام بسرعة؟


اذهب مع الخطوات: -إنشاء فهارس مكانية للجداول الخاصة بك.

-إنشاء مخازن

إنشاء أو استبدال عرض b1 كـ SELECT ST_Buffer (the_geom، 500) كـ the_geom، attributes، fid FROM Stores؛ إنشاء أو استبدال عرض b2 كـ SELECT ST_Buffer (the_geom، 2000) كـ the_geom، attributes، fid FROM Stores؛

(لست متأكدًا ، لكن ربما يجب أن تقلل هنا 8 ملايين نقطة إلى أقل مع رؤية مجسدة مثل هذا:

حدد سمات النقطة أ من b2 ب ، يشير إلى مكان st_within (a.

)

-إنشاء عرض الحلقة r2:

إنشاء أو استبدال عرض r2 كـ SELECT ff.fid، ff.attributes، ST_Difference (ff.the_geom، g.the_geom) كـ new_geom FROM b2 ff JOIN b1 g ON (g.fid = ff.fid) تجميع بواسطة ff.fid، ff .attrubets، g.the_geom، ff.the_geom؛

-اختر تلك الحلقات التي يزيد ارتفاعها عن 400 متر.

- والبت البطيء تقاطع:

حدد ff.fid، ff.attributes FROM r2 ff JOIN points_table g ON ST_Intersects (g.geom، ff.the_geom) GROUP BY ff.fid، ff.attributes، ff.the_geom؛

استعلامك يبدو غريبا نوعا ما. ما الذي تحاول تحقيقه من خلال محطتين مؤقتتين حول نفس النقطة؟

يمكنك استبدالالمسافة ST_مع المشغل ، ولكن لا يزال يتعين علينا التفكير في الشيء العازل.

هل يمكنك تحديد هدفك قليلا؟

ملاحظة: هل قمت ببناء فهرس مكاني؟

تحديث:

سأحاول

حدد * من (حدد a.gid ، b.gid ، a.geom <-> b.geom مسافة من shp1 a ، shp2 b حيث القيمة المطلقة (a.value - b.value)> 400 و ST_D داخل (a.geom، b .geom، 2000)) مربعة حيث المسافة> 500 ؛

لماذا هذا الاستعلام عن مسافة Postgis بطيء جدًا؟ مقدر استعلام Postgres & # 39 بمعامل 10000x؟

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

إليك تصور تحليل التفسير للاستعلام الثالث الذي يستغرق 9 ثوانٍ: https://explain.depesz.com/s/Xd6d

هنا هو شرح التحليل للاستعلام الرابع: https://explain.depesz.com/s/zcKa

في الأساس ، بناءً على المدخلات ، يبدو أن المسافة غير المفهرسة باستخدام & lt- & gt تكون أحيانًا أسرع ، ومن ثم بالنسبة للمدخلات الأخرى ، يكون مشغل المسافة المفهرسة (ST_DWithin) أسرع.

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

فيما يلي الفهارس ذات الصلة التي أمتلكها:

لم يؤثر استخدام الجوهر بدلاً من spgist في geog على وقت التشغيل.

جميع مناطقي الجغرافية عبارة عن نقاط وقد قمت بتشغيل VACUUM (ANALYZE ، VERBOSE) بالفعل

PostgreSQL 12.0 ، تم تجميعه بواسطة Visual C ++ build 1914 ، 64 بت

POSTGIS = "3.0.0 r17983" [ملحق] PGSQL = "120" GEOS = "3.8.0-CAPI-1.13.1" PROJ = "الإصدار 5.2.0 ، 15 سبتمبر 2018" LIBXML = "2.9.9" LIBJSON = "0.12" LIBPROTOBUF = "1.2.1" WAGYU = "0.4.3 (داخلي)" TOPOLOGY


ما أنا ضد؟

هذه مشكلة صعبة حقًا للعثور عليها واختبارها وإصلاحها.

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

2 إجابات 2

على الرغم من أن الفهرس مقترح من قبل SQL Server ، فلماذا يبطئ الأمور بفارق كبير؟

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

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

ما هي صلة Nested Loop التي تستغرق معظم الوقت وكيفية تحسين وقت تنفيذها؟

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

هل هناك شيء خاطئ أفعله أو فاتني؟

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

في الخطة باستخدام Nonclustered Index Seek ، ينتهي العمل بكامله من خلال مؤشر ترابط واحد:

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

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

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

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

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

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

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

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


فهم التخزين المؤقت في Postgres - دليل متعمق

يمكن اعتبار التخزين المؤقت جانبًا مهمًا في ضبط أداء نظام قاعدة البيانات.

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

ما هي ذاكرة التخزين المؤقت ولماذا نحتاجها

تعمل مكونات الكمبيوتر المختلفة بسرعات مختلفة. نحن البشر فقراء للغاية في فهم الأرقام بالمقياس الذي تفعله أجهزة الكمبيوتر.

بالنظر إلى الجدول أدناه (مأخوذ من هنا) ، يمكننا الحصول على فكرة.

الأرقام تقريبية على المستوى البشري.

نوع الوصول الوقت الفعلي الوقت التقريبي
1 دورة وحدة المعالجة المركزية0.3 نانوثانية1 ثانية
الوصول إلى ذاكرة التخزين المؤقت من المستوى 10.9 نانوثانية3 ق
الوصول إلى ذاكرة التخزين المؤقت من المستوى 22.8 نانو ثانية9 ق
الوصول إلى ذاكرة التخزين المؤقت من المستوى 312.9 نانوثانية43 ثانية
الوصول إلى الذاكرة الرئيسية120 نانوثانية6 دقائق
إدخال / إخراج قرص الحالة الصلبة50-150 ميكرو ثانية2-6 أيام
قرص الدوران I / O1-10 مللي ثانيةمن 1 إلى 12 شهرًا
الإنترنت: من SF إلى مدينة نيويورك40 مللي ثانية4 سنوات
الإنترنت: من SF إلى المملكة المتحدة81 مللي ثانية8 سنوات
الإنترنت: SF إلى أستراليا183 مللي ثانية19 سنة

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

تعتبر الأقراص المغناطيسية ضعيفة بالنسبة للإدخال / الإخراج العشوائي عند مقارنتها بنظيراتها الأحدث في محركات أقراص الحالة الصلبة.

معظم أحمال عمل OLTP عبارة عن إدخال / إخراج عشوائي ، وبالتالي يمكن أن يكون الجلب من القرص بطيئًا جدًا.

للتغلب على هذا ، يقوم postgres بتخزين البيانات مؤقتًا في ذاكرة الوصول العشوائي والتي يمكن أن تحسن الأداء بشكل كبير. حتى في حالة محركات أقراص الحالة الصلبة ، تكون ذاكرة الوصول العشوائي أسرع بكثير.

هذه الفكرة العامة لذاكرة التخزين المؤقت شائعة في جميع أنظمة قواعد البيانات تقريبًا.

فهم المصطلحات

قبل أن نمضي قدمًا ، من الضروري فهم بعض المصطلحات.

أقترح أن تبدأ القراءة مع postgres التخزين المادي.

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

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

بغض النظر عن المحتوى ، تحتوي postgres على تجريد تخزين يسمى صفحة (بحجم 8 كيلوبايت). الصورة أدناه تعطي فكرة تقريبية.

هذا التجريد هو ما سنتعامل معه في بقية هذا المنشور.

ما هو مخبأ؟

Postgres يخزن ما يلي.

هذا هو المحتوى الفعلي للجداول.

يتم تخزين الفهارس أيضًا في كتل 8K. يتم تخزينها في نفس مكان بيانات الجدول ، انظر مناطق الذاكرة أدناه.

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

تشرح الوثائق تلك بالتفصيل بشكل جيد. يمكننا الاستعلام عن pg_prepared_statements لمعرفة ما هو مخبأ. لاحظ أنه غير متاح عبر الجلسات وأنه مرئي فقط للجلسة الحالية.

سنستكشف كيف يتم تخزين بيانات الجدول والفهارس بالتفصيل بشكل أكبر في هذا المنشور.

مناطق الذاكرة

تحتوي Postgres على العديد من معلمات التكوين وفهم ما تعنيه أمرًا مهمًا حقًا.

بالنسبة للتخزين المؤقت ، فإن التكوين الأكثر أهمية هو Shared_buffers.

داخليًا في الكود المصدري postgres ، يُعرف هذا باسم NBuffers ، وهذا هو المكان الذي توجد فيه جميع البيانات المشتركة في الذاكرة.

تعد Shared_buffers ببساطة مصفوفة من كتل 8 كيلوبايت ، وتحتوي كل صفحة على بيانات وصفية في حد ذاتها لتمييز نفسها كما ذكر أعلاه. قبل أن يقوم postgres بفحص البيانات من القرص ، فإنه يقوم أولاً بالبحث عن الصفحات الموجودة في Shared_buffers ، إذا كانت هناك إصابة ، فإنه يقوم بإرجاع البيانات من هناك نفسه وبالتالي يتجنب إدخال / إخراج القرص.

خوارزمية مسح LRU / Clock لذاكرة التخزين المؤقت

يتم التحكم في الآليات المتضمنة في وضع البيانات في ذاكرة التخزين المؤقت والطرد منها بواسطة خوارزمية مسح على مدار الساعة.

تم تصميمه للتعامل مع أحمال عمل OLTP ، بحيث يتم التعامل مع كل حركة المرور تقريبًا في الذاكرة.

دعونا نتحدث عن كل عمل بالتفصيل.

Postgres هو نظام قائم على العمليات ، أي أن كل اتصال له عملية نظام تشغيل أصلية خاصة به والتي يتم إنتاجها من عملية جذر postgres (تسمى سابقًا postmaster).

عندما تطلب عملية ما لصفحة في ذاكرة التخزين المؤقت لـ LRU (يتم ذلك عندما يتم الوصول إلى هذه الصفحة عبر استعلام SQL نموذجي) ، فإنها تطلب تخصيص المخزن المؤقت.

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

فقط إذا لم تكن هناك مخازن مؤقتة / فتحات مجانية للصفحة ، فسيتم إخلاء المخزن المؤقت.

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

لا تعمل خوارزمية LRU (الأقل استخدامًا مؤخرًا) بشكل جيد في الواقع نظرًا لعدم وجود ذاكرة للتشغيل السابق.

يتتبع Postgres عدد استخدام الصفحة ، لذلك إذا كان عدد استخدام الصفحة صفرًا ، فسيتم طرده من الذاكرة وكتابته على القرص. تتم كتابتها أيضًا على القرص عندما تكون الصفحة متسخة (انظر أدناه).

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

الصفحات المتسخة وإبطال ذاكرة التخزين المؤقت

كنا نتحدث عن استعلامات محددة حتى الآن ، ماذا يحدث لاستعلامات DML؟

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

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

إليك المزيد من الواجبات / الدراسة التي يتعين القيام بها ، قبل المضي قدمًا ، ولا سيما حول WAL ونقاط التفتيش.

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

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

فهم ذاكرات التخزين المؤقت من شرح التحليل

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

تعطي خطة الاستعلام أدناه مثالاً ،

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

من المريح جدًا بهذه الطريقة التعرف على مقدار التخزين المؤقت من منظور الاستعلام بدلاً من العبث بالأجزاء الداخلية لنظام التشغيل / Postgres.

حالة عمليات المسح المتسلسلة

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

نظرًا لأن فحص التسلسل الفردي يمكنه مسح جميع البيانات من ذاكرة التخزين المؤقت ، يتم التعامل معه بشكل مختلف.

بدلاً من استخدام خوارزمية مسح LRU / Clock عادية ، فإنها تستخدم سلسلة من المخازن المؤقتة بإجمالي حجم 256 كيلوبايت. توضح الخطة أدناه كيفية التعامل معها.

تنفيذ الاستعلام أعلاه مرة أخرى.

يمكننا أن نرى أن 32 كتلة بالضبط قد انتقلت إلى الذاكرة ، أي 32 * 8 = 256 كيلوبايت. هذا موضح في src / backend / storage / buffer / README

تدفق الذاكرة والتخزين المؤقت لنظام التشغيل

Postgres كقاعدة بيانات عبر الأنظمة الأساسية ، تعتمد بشكل كبير على نظام التشغيل للتخزين المؤقت.

يقوم Shared_buffers في الواقع بتكرار ما يفعله نظام التشغيل. وترد أدناه صورة نموذجية لكيفية تدفق البيانات عبر postgres.

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

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

ضع في اعتبارك أن نظام التشغيل يخزن البيانات مؤقتًا لنفس السبب الذي رأيناه أعلاه ، أي لماذا نحتاج إلى ذاكرة تخزين مؤقت؟

يمكننا تصنيف الإدخال / الإخراج كنوعين ، أي القراءة والكتابة. لجعل الأمر أكثر بساطة ، تتدفق البيانات من القرص إلى الذاكرة للقراءة وتتدفق من الذاكرة إلى القرص للكتابة.

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

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

في الواقع ، لا توجد صفحات كثيرة مكدسة في منطقتي الذاكرة.

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

سنناقش المزيد حول التحسين أدناه.

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

بمجرد وضع علامة على الصفحة على أنها متسخة ، يتم مسحها في ذاكرة التخزين المؤقت لنظام التشغيل والتي تكتب بعد ذلك على القرص. هذا هو المكان الذي يتمتع فيه نظام التشغيل بحرية أكبر لجدولة I / O بناءً على حركة المرور الواردة.

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

الترتيب الأولي

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

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

بمجرد تعيين التكوين الافتراضي / بدء التشغيل. يمكننا إجراء اختبار الحمل / الأداء لمعرفة كيف يتم الصمود.

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

التحسين كما تذهب

إذا لم تتمكن من قياس شيء ما ، فلا يمكنك تحسينه

مع postgres ، هناك طريقتان يمكنك قياسهما.

بينما لا يوجد إجماع عام على النظام الأساسي الذي يعمل postgres بشكل أفضل ، أفترض أنك تستخدم شيئًا ما في عائلة أنظمة التشغيل linux. لكن الفكرة متشابهة نوعًا ما.

لتبدأ ، هناك أداة تسمى Io top يمكنها قياس إدخال / إخراج القرص. على غرار الجزء العلوي ، يمكن أن يكون هذا مفيدًا عند قياس قرص الإدخال / الإخراج. فقط قم بتشغيل الأمر iotop لقياس عمليات الكتابة / القراءة.

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

من الأفضل دائمًا مراقبة شيء ما مباشرةً من postgres ، بدلاً من المرور عبر مسار نظام التشغيل.

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

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

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

تحقق من الروابط أدناه لفهم شرح متعمق.

تعد سجلات الاستعلام طريقة أخرى لفهم ما يحدث داخل النظام.

بدلاً من تسجيل كل شيء ، يمكننا تسجيل الاستعلامات التي تتجاوز مدة معينة فقط أو تسمى سجلات الاستعلام البطيئة باستخدام المعلمة log_min_duration_statement.

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

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

هذه وحدة تم إنشاؤها داخل postgres نفسها ، ولكن يتم تعطيلها افتراضيًا.

يمكننا تمكين هذا عن طريق إنشاء ملحق pg_stat_statements

بمجرد تمكين هذا ، بعد تشغيل قدر لا بأس به من الاستعلامات ، يمكننا عندئذٍ إطلاق استعلام مثل أدناه.

يعطي الكثير من التفاصيل حول مقدار الوقت الذي استغرقته الاستعلامات ومتوسطها.

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

إذا كنت ترغب في التعمق قليلاً ، فهناك وحدتان يمكنهما الحفر مباشرة في Shared_buffers وذاكرة التخزين المؤقت لنظام التشغيل نفسه.

الشيء المهم الذي يجب ملاحظته هو أن الشرح (التحليل ، المخازن المؤقتة) يعرض البيانات من Shared_buffers فقط وليس من ذاكرة التخزين المؤقت لنظام التشغيل.

يساعدنا هذا في رؤية البيانات في المخازن المؤقتة المشتركة في الوقت الفعلي. يجمع المعلومات من Shared_buffers ويضعها داخل pg_buffercache لعرضها.

يتم عرض نموذج استعلام على النحو التالي ، والذي يسرد أفضل 100 جدول بالإضافة إلى عدد الصفحات المخزنة مؤقتًا.

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

هذه وحدة مدمجة يمكنها فعليًا تحميل البيانات في ذاكرة التخزين المؤقت Shared_buffers / OS أو كليهما. إذا كنت تعتقد أن إحماء الذاكرة هو المشكلة ، فهذا مفيد جدًا لتصحيح الأخطاء.

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

مراجع


MySQL بطيئة للغاية في استعلامات SELECT البسيطة جدًا

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

على سبيل المثال ، لدي جدول بسيط للغاية يحتوي على العناوين:

يحتوي هذا الجدول على حوالي 800 إدخال وهو في الحقيقة ليس كثيرًا. لكن تشغيل الاستعلام

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

لذلك ربما تكون مشكلة في مرحلة إرسال البيانات ، لكنني لست متأكدًا.

يحتوي جهاز VM على 2 غيغابايت من ذاكرة الوصول العشوائي ويستخدم 320 ميجابايت فقط. تعمل وحدة المعالجة المركزية أيضًا عند نسبة منخفضة جدًا من 1 إلى 2٪. لا يُظهر mytop أي استعلامات أخرى تمنع الخادم. قال مسؤول تكنولوجيا المعلومات إنهم لم يغيروا أي شيء على جانب الأجهزة.

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


أعتقد أن الإجابة لم تكن هنا لأنها بسيطة جدًا! هذا ما اكتشفته:

  1. يفتح منشئ ملفات التعريف SQL Server (في أدوات الأداء)
  2. ملف - & gt تتبع جديد.
  3. اتصل بقاعدة البيانات الخاصة بك
  4. انقر فوق علامة التبويب "تحديد الأحداث"
  5. حدد فقط الأحداث التي تتوافق مع إنهاء استعلامات SQL:
    • RPC: اكتمل
    • SQL: BatchCompleted
  6. انقر مرشحات العمود.
  7. انقر مدة في القائمة
  8. يوسع أكبر من أو يساوي وأدخل وقت الحد الذي تعتبره "بطيئًا" بالمللي ثانية
  9. انقر حسنا
  10. انقر يركض

يمكنك التصفية حسب ApplicationName و NTUserName وما إلى ذلك إذا كان لديك الكثير من التطبيقات قيد التشغيل وتريد تقليل الضوضاء. يمكنك أيضًا عرض بعض الأعمدة فقط ، على سبيل المثال فقط TextData والمدة.

يمكنك استخدام هذا للحصول على أفضل 10 استعلامات باهظة الثمن (إذا كنت تستخدم Sql server 2005 وما فوق):

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

إذا كنت تستخدم SQL Server 2005 أو إصدارًا أحدث ، فإن DMVs (طرق عرض الإدارة الديناميكية) تقدم منهجية بديلة:

أحد الأشياء التي يجب تذكرها هو أن DMVs يتم مسحها عند بدء تشغيل SQL Server ، لذلك إذا كان الخادم الخاص بك يعمل لمدة 12 دقيقة ، فقد لا يخبرك كثيرًا. كما أنها تراكمية - لذلك يمكن أن تؤدي نوافذ الصيانة (checkDB) إلى تحريف البيانات.


احصل على متوسط ​​وقت التنفيذ لجميع الاستعلامات المختارة

tldr: كيف يمكنني الحصول على متوسط ​​وقت تنفيذ الاستعلام لبيانات محددة لخادم MySQL قيد التشغيل؟

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

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

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

من الناحية المثالية ، أرغب في متوسط ​​وقت التنفيذ لجميع الاستعلامات المختارة على العبيد ، حتى أتمكن من مقارنة ما إذا كان هناك تسريع شامل أو تدهور بعد الانفصال. في mysqlreport أو mysqladmin ext ، لا شيء ينبثق كقيمة يمكنني استخدامها لهذا ، ولا يبدو أن سجل الاستعلام العام يحتوي على هذا. لقد فكرت في تعيين long_query_time لـ 0 لفترة من الوقت ، ولكن هذا من شأنه أن يبطئ الخوادم حقًا ، لذلك أنا منفتح على الخيارات الأخرى ، إذا كان هناك أي منها؟


8 إجابات 8

بالنسبة لي ، أدى تعيين خاصية ServerName في httpd.conf إلى إصلاح التأخيرات (كانت تصل إلى 10 ثوانٍ في أسوأ الأحوال):

كان لدي نفس المشكلة.

لم يساعد تعيين إعادة توجيه المضيف المحلي إلى 127.0.0.1 في ملف المضيفين. لم يساعد تحسين خادم MySQL (InnoDB -> MyISAM ، تغيير العديد من التوجيهات المتعلقة بذاكرة التخزين المؤقت في my.ini).

ثم استخدمت webgrind وحصرت المشكلة إلى مكالمة "PDO (.) الجديدة". التغيير

في dsn لـ PDO تحل المشكلة تماما! وقت تحميل الصفحة انتقل من أكثر من 3000 مللي ثانية إلى 16 مللي ثانية.

ومع ذلك ، أنا في حيرة من أمري لماذا لم يساعد سطر "127.0.0.1 localhost" في ملف المضيفين.

كانت المشكلة مع ملف إعدادات Apache الرئيسي httpd.conf.

هناك ثلاث طرق لإعداد PHP للعمل مع Apache 2.x على Windows. يمكنك تشغيل PHP كمعالج أو CGI أو تحت FastCGI. [مصدر]

وهكذا ذهبت إلى إعدادات Apache ورأيت أين كانت المشكلة: لقد قمت بإعدادها كـ CGI ، بدلاً من تحميلها كوحدة نمطية. تسبب هذا في بدء تشغيل php-cgi.exe وإغلاقه في كل مرة أقدم فيها طلبًا. كان هذا يبطئ تطوير مضيفي المحلي.

لقد غيرت الإعدادات لتحميل PHP كـ Apache وحدة والآن يعمل كل شيء على أكمل وجه. :)


الكمون والحافة والمستقبل

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

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


شاهد الفيديو: PostGIS Lesson 8 - Getting Started with PostGIS