إشعار
يتطلب الوصول إلى هذه الصفحة تخويلاً. يمكنك محاولة تسجيل الدخول أو تغيير الدلائل.
يتطلب الوصول إلى هذه الصفحة تخويلاً. يمكنك محاولة تغيير الدلائل.
ضبط الفهرس هو ميزة في قاعدة بيانات Azure لمثيل الخادم المرن PostgreSQL التي تعمل تلقائيا على تحسين أداء حمل العمل الخاص بك من خلال تحليل الاستعلامات المتعقبة وتقديم توصيات الفهرس.
إنه عرض مضمن في قاعدة بيانات Azure لمثيل خادم PostgreSQL المرن، والذي يعتمد على أداء المراقبة مع وظائف مخزن الاستعلام . يقوم ضبط الفهرس بتحليل حمل العمل الذي يتم تعقبه بواسطة مخزن الاستعلام، وينتج توصيات الفهرس لتحسين أداء حمل العمل الذي تم تحليله أو لإسقاط الفهارس المكررة أو غير المستخدمة.
- تحديد الفهارس المفيدة لإنشاء لأنها يمكن أن تحسن بشكل كبير الاستعلامات التي تم تحليلها أثناء جلسة ضبط الفهرس.
- تحديد الفهارس التي هي تكرارات دقيقة ويمكن إزالتها لتقليل تأثير الأداء على وجودها وصيانتها على الأداء العام للنظام.
- تحديد الفهارس غير المستخدمة في فترة قابلة للتكوين يمكن أن تكون مرشحين للتخلص منها.
وصف عام لخوارزمية ضبط الفهرس
عند تكوين معلمة index_tuning.mode الخادم إلى report، يتم بدء جلسات الضبط تلقائيا مع التردد الذي تم تكوينه في معلمة index_tuning.analysis_intervalالخادم ، ويتم التعبير عنها بالدقائق.
وفي المرحلة الأولى، تبحث جلسة الضبط عن قائمة قواعد البيانات التي ترى فيها أن أي توصيات قد تنتجها قد تؤثر تأثيرا كبيرا على الأداء العام للنظام. للقيام بذلك، فإنه يجمع جميع الاستعلامات المسجلة بواسطة مخزن الاستعلامات التي تم التقاط عمليات التنفيذ الخاصة بها ضمن الفاصل الزمني للبحث الذي تركز عليه جلسة الضبط هذه. يمتد الفاصل الزمني للبحث حاليا إلى الدقائق الماضية index_tuning.analysis_interval ، من وقت بدء جلسة الضبط.
بالنسبة لجميع الاستعلامات التي بدأها المستخدم مع عمليات التنفيذ المسجلة في مخزن الاستعلامات والتي لم تتم إعادة تعيين إحصائيات وقت التشغيل الخاصة بها، يقوم النظام بترتيبها استنادا إلى إجمالي وقت التنفيذ المجمع. وهو يركز اهتمامه على أبرز الاستعلامات، بناء على مدتها.
يتم استبعاد الاستعلامات التالية من تلك القائمة:
- الاستعلامات التي بدأها النظام. (أي الاستعلامات التي يتم تنفيذها حسب
azuresuالدور) - الاستعلامات المنفذة في سياق أي قاعدة بيانات نظام (
azure_sysوtemplate0template1و وazure_maintenance).
تتكرر الخوارزمية عبر قواعد البيانات الهدف، والبحث عن الفهارس المحتملة التي يمكن أن تحسن أداء أحمال العمل التي تم تحليلها. كما يبحث عن الفهارس التي يمكن إزالتها لأنه يتم تعريفها على أنها مكررة أو غير مستخدمة لفترة زمنية قابلة للتكوين.
إنشاء توصيات INDEX
لكل قاعدة بيانات تم تحديدها كمرشح لتحليلها لإنتاج توصيات الفهرس، يتم أخذ جميع استعلامات SELECT و UPDATE و INSERT و DELETE التي يتم تنفيذها أثناء الفاصل الزمني للبحث وفي سياق قاعدة البيانات المحددة هذه في الاعتبار.
يتم تصنيف المجموعة الناتجة من الاستعلامات استنادا إلى إجمالي وقت التنفيذ المجمع الخاص بها، ويتم تحليل الجزء العلوي index_tuning.max_queries_per_database للحصول على توصيات الفهرس المحتملة.
تهدف التوصيات المحتملة إلى تحسين أداء هذه الأنواع من الاستعلامات:
- الاستعلامات ذات عوامل التصفية (أي الاستعلامات ذات دالات التقييم في عبارة WHERE)،
- الاستعلامات التي تنضم إلى علاقات متعددة، سواء كانت تتبع بناء الجملة الذي يتم التعبير عن الصلات فيه باستخدام عبارة JOIN أو ما إذا كان يتم التعبير عن دالات التقييم الخاصة بالصلة في عبارة WHERE.
- استعلامات تجمع بين عوامل التصفية والانضمام إلى دالات التقييم.
- الاستعلامات ذات التجميع (الاستعلامات التي تحتوي على عبارة GROUP BY).
- الاستعلامات التي تجمع بين عوامل التصفية والتجموع.
- الاستعلامات ذات الفرز (الاستعلامات التي تحتوي على عبارة ORDER BY).
- استعلامات تجمع بين عوامل التصفية والفرز.
إشعار
النوع الوحيد من الفهارس التي يوصي بها النظام حاليا هي تلك من النوع B-Tree.
إذا كان الاستعلام يشير إلى عمود واحد من الجدول ولا يحتوي هذا الجدول على إحصائيات، فإنه يتخطى الاستعلام بأكمله ولا ينتج أي توصيات فهرس لتحسين تنفيذه.
يمكن تشغيل التحليل المطلوب لجمع الإحصائيات يدويا باستخدام الأمر ANALYZE أو تلقائيا بواسطة البرنامج الخفي للإخلاء التلقائي.
index_tuning.max_indexes_per_table يحدد عدد الفهارس التي يمكن التوصية بها، باستثناء أي فهارس قد تكون موجودة بالفعل في الجدول لأي جدول واحد المشار إليه بواسطة أي عدد من الاستعلامات أثناء جلسة عمل الضبط.
index_tuning.max_index_count يحدد عدد توصيات الفهرس التي تم إنتاجها لجميع جداول أي قاعدة بيانات تم تحليلها أثناء جلسة ضبط.
لكي يتم إصدار توصية فهرس، يجب أن يقدر محرك الضبط أنه يحسن استعلاما واحدا على الأقل في حمل العمل الذي تم تحليله بواسطة عامل محدد مع index_tuning.min_improvement_factor.
وبالمثل، يتم التحقق من جميع توصيات الفهرس للتأكد من أنها لا تقدم تراجعا على أي استعلام واحد في حمل العمل هذا لعامل محدد مع index_tuning.max_regression_factor.
إشعار
index_tuning.min_improvement_factor وكلاهما index_tuning.max_regression_factor يشير إلى تكلفة خطط الاستعلام، وليس إلى مدتها أو الموارد التي يستهلكونها أثناء التنفيذ.
يتم وصف جميع المعلمات المذكورة في الفقرات السابقة وقيمها الافتراضية والنطاقات الصالحة في خيارات التكوين.
يتبع البرنامج النصي الذي تم إنتاجه مع التوصية بإنشاء فهرس، هذا النمط:
create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])
يتضمن عبارة concurrently. لمزيد من المعلومات حول تأثيرات هذه العبارة، تفضل بزيارة وثائق PostgreSQL الرسمية ل CREATE INDEX.
يقوم ضبط الفهرس تلقائيا بإنشاء أسماء الفهارس الموصى بها، والتي تتكون عادة من أسماء أعمدة المفاتيح المختلفة مفصولة ب "_" (تسطير سفلي) ومع لاحقة "_idx" ثابتة. إذا تجاوز الطول الإجمالي للاسم حدود PostgreSQL أو إذا كان يصادم مع أي علاقات موجودة، يكون الاسم مختلفا قليلا. يمكن اقتطاعه، ويمكن إلحاق رقم بنهاية الاسم.
حساب تأثير توصية CREATE INDEX
يتم قياس تأثير إنشاء توصية فهرس على IndexSize (ميغابايت) و QueryCostImprovement (النسبة المئوية).
IndexSize هي قيمة واحدة تمثل الحجم المقدر للفهرس، مع مراعاة العلاقة الأساسية الحالية للجدول وحجم الأعمدة المشار إليها بواسطة الفهرس الموصى به.
يتكون QueryCostImprovement من صفيف من القيم، حيث يمثل كل عنصر التحسن في تكلفة الخطة لكل استعلام تقدر تكلفة خطته لتحسينه إذا كان هذا الفهرس موجودا. يعرض كل عنصر معرف الاستعلام (استعلم) والنسبة المئوية التي ستتحسن بها تكلفة الخطة إذا تم تنفيذ التوصية (الأبعاد).
DROP INDEX وتوصيات REINDEX
لكل قاعدة بيانات يتم تحديد وظيفة ضبط الفهرس لها، يجب أن تبدأ جلسة عمل جديدة، وبعد اكتمال مرحلة توصيات CREATE INDEX، توصي بإسقاط الفهارس الموجودة أو إعادة فهرستها، استنادا إلى المعايير التالية:
- قم بالإفلات إذا كان يعتبر مكرر للآخرين.
- إسقاط إذا لم يتم استخدامه لفترة زمنية قابلة للتكوين.
- إعادة فهرسة التي تم وضع علامة عليها على أنها غير صالحة.
إسقاط الفهارس المكررة
توصيات لإسقاط الفهارس المكررة: أولا، تحديد الفهارس التي لها تكرارات.
يتم تصنيف التكرارات استنادا إلى دوال مختلفة يمكن عزوها إلى الفهرس واستنادا إلى أحجامها المقدرة.
وأخيرا، يوصي بإسقاط جميع التكرارات ذات الترتيب الأقل من قائد المرجع الخاص به ويصف سبب تصنيف كل تكرار بالطريقة التي كانت عليه.
لكي يعتبر الفهرسان مكررين، يجب أن يكونا:
- يتم إنشاؤها عبر نفس الجدول.
- كن فهرسا من نفس النوع بالضبط.
- تطابق أعمدة المفاتيح الخاصة بها، وبالنسبة لمفاتيح الفهرس متعددة الأعمدة، تتطابق مع الترتيب الذي تتم الإشارة إليها به.
- مطابقة شجرة التعبير لمسندها. ينطبق فقط على الفهارس الجزئية.
- مطابقة شجرة التعبير لكافة مراجع الأعمدة غير المتماثلة. ينطبق فقط على الفهارس التي تم إنشاؤها على التعبيرات.
- مطابقة ترتيب كل عمود مشار إليه في المفتاح.
إسقاط الفهارس غير المستخدمة
تحدد توصيات إسقاط الفهارس غير المستخدمة تلك الفهارس التي:
- لا تستخدم لأيام على الأقل
index_tuning.unused_min_period. - إظهار الحد الأدنى (المتوسط اليومي) لعدد DMLs
index_tuning.unused_dml_per_tableعلى الجدول حيث يتم إنشاء الفهرس. - إظهار الحد الأدنى (المتوسط اليومي) لعدد
index_tuning.unused_reads_per_tableالقراءات على الجدول حيث يتم إنشاء الفهرس.
إعادة فهرسة غير صالحة
تحدد توصيات إعادة فهرسة الفهارس الموجودة تلك الفهارس التي تم وضع علامة عليها على أنها غير صالحة. لمعرفة المزيد حول سبب ووقت وضع علامة على الفهارس على أنها غير صالحة، راجع الدالة REINDEX في الوثائق الرسمية ل PostgreSQL.
حساب تأثير توصية DROP INDEX
يتم قياس تأثير توصية مؤشر الإفلات على بعدين: الميزة (النسبة المئوية) و IndexSize (ميغابايت).
الميزة هي قيمة واحدة يمكن تجاهلها في الوقت الحالي.
IndexSize هي قيمة واحدة تمثل الحجم المقدر للفهرس، مع مراعاة العلاقة الأساسية الحالية للجدول وحجم الأعمدة المشار إليها بواسطة الفهرس الموصى به.
تكوين ضبط الفهرس
يمكن تمكين ضبط الفهرس وتعطيله وتكوينه من خلال مجموعة من المعلمات التي تتحكم في سلوكه.
عند تمكين ضبط الفهرس، يتم تنبيهه بتردد تم تكوينه في index_tuning.analysis_interval معلمة الخادم (افتراضيا إلى 720 دقيقة أو 12 ساعة) ويبدأ في تحليل حمل العمل المسجل بواسطة مخزن الاستعلام خلال تلك الفترة.
لاحظ أنه إذا قمت بتغيير قيمة index_tuning.analysis_interval، تتم ملاحظتها فقط بعد اكتمال التنفيذ المجدول التالي. لذلك، على سبيل المثال، إذا قمت بتمكين ضبط الفهرس يوم واحد في الساعة 10:00 صباحا، لأن القيمة الافتراضية لمدة index_tuning.analysis_interval 720 دقيقة، فمن المقرر أن يبدأ التنفيذ الأول في الساعة 10:00 مساء في نفس اليوم. لن تؤثر أي تغييرات تجريها على القيمة index_tuning.analysis_interval بين 10:00 صباحا و10:00 مساء على هذا الجدول الأولي. فقط عند اكتمال التشغيل المجدول، سيقرأ القيمة الحالية المعينة ل index_tuning.analysis_interval وسيجدول التنفيذ التالي وفقا لتلك القيمة.
تتوفر الخيارات التالية لتكوين معلمات ضبط الفهرس:
| البارامتر | وصف | افتراضي | نطاق | الوحدات |
|---|---|---|---|---|
index_tuning.analysis_interval |
تعيين التردد الذي يتم فيه تشغيل كل جلسة عمل لتحسين الفهرس عند تعيين index_tuning.mode إلى REPORT. |
720 |
60 - 10080 |
محضر |
index_tuning.max_columns_per_index |
الحد الأقصى لعدد الأعمدة التي يمكن أن تكون جزءا من مفتاح الفهرس لأي فهرس موصى به. | 2 |
1 - 10 |
|
index_tuning.max_index_count |
الحد الأقصى للفهرس الموصى به لكل قاعدة بيانات أثناء جلسة تحسين واحدة. | 10 |
1 - 25 |
|
index_tuning.max_indexes_per_table |
الحد الأقصى لعدد الفهارس التي يمكن التوصية بها لكل جدول. | 10 |
1 - 25 |
|
index_tuning.max_queries_per_database |
عدد أبطأ الاستعلامات لكل قاعدة بيانات يمكن التوصية بفهارسها. | 25 |
5 - 100 |
|
index_tuning.max_regression_factor |
التراجع المقبول الذي يقدمه فهرس موصى به على أي من الاستعلامات التي تم تحليلها أثناء جلسة تحسين واحدة. | 0.1 |
0.05 - 0.2 |
النسبه المئويه |
index_tuning.max_total_size_factor |
الحد الأقصى للحجم الإجمالي، في النسبة المئوية لإجمالي مساحة القرص، التي يمكن لجميع الفهارس الموصى بها لأي قاعدة بيانات معينة استخدامها. | 0.1 |
0 - 1 |
النسبه المئويه |
index_tuning.min_improvement_factor |
تحسين التكلفة الذي يجب أن يوفره الفهرس الموصى به لواحد على الأقل من الاستعلامات التي تم تحليلها أثناء جلسة تحسين واحدة. | 0.2 |
0 - 20 |
النسبه المئويه |
index_tuning.mode |
تكوين تحسين الفهرس على أنه معطل (OFF) أو ممكن لإصدار توصية فقط. يتطلب تمكين مخزن الاستعلام عن طريق تعيين pg_qs.query_capture_mode إلى TOP أو ALL. |
OFF |
OFF, REPORT |
|
index_tuning.unused_dml_per_table |
الحد الأدنى لعدد عمليات DML المتوسطة اليومية التي تؤثر على الجدول، لذلك يتم اعتبار الفهارس غير المستخدمة للانخفاض. | 1000 |
0 - 9999999 |
|
index_tuning.unused_min_period |
الحد الأدنى لعدد الأيام التي لم يتم فيها استخدام الفهرس، استنادا إلى إحصائيات النظام، لذلك يتم اعتباره للانخفاض. | 35 |
30 - 70 |
|
index_tuning.unused_reads_per_table |
الحد الأدنى لعدد عمليات القراءة اليومية المتوسطة التي تؤثر على الجدول بحيث يتم اعتبار الفهارس غير المستخدمة للانخفاض. | 1000 |
0 - 9999999 |
إذا كنت تستخدم أوامر az postgres flexible-server index-tuning show-settingsaz postgres flexible-server index-tuning set-settings CLI ولعرض أو تعديل أي من إعدادات ضبط الفهرس، فإن القيم المقبولة كوسيطات للمعلمة --name هي تلك المعروضة في عمود المعلمة للجدول السابق، ولكن دون تضمين البادئة index_tuning..
المعلومات التي ينتجها ضبط الفهرس
تصف كيفية قراءة التوصيات التي ينتجها ضبط الفهرس وتفسيرها واستخدامها بالتفصيل الكامل كيفية الحصول على التوصيات التي ينتجها ضبط الفهرس واستخدامها.
القيود وإمكانية الدعم
فيما يلي قائمة القيود ونطاق إمكانية الدعم لضبط الفهرس.
الحذف التلقائي للتوصيات
يتم حذف التوصيات تلقائيا بعد 35 يوما من آخر مرة يتم إنتاجها فيها. لكي تعمل آلية الحذف التلقائي هذه، يجب تمكين ضبط الفهرس.
التبعية على ملحق نقص الأداء
لضبط الفهرس لإنتاج توصيات CREATE INDEX، فإنه يستخدم ملحق hypopg .
إذا كان الملحق موجودا بالفعل عند بدء جلسة ضبط، يتم استخدامه على المخطط الذي تم إنشاؤه فيه. وعند انتهاء جلسة الضبط، لا يتم إسقاط الملحق. استثناء لهذا هو إذا تم إنشاء الملحق في pg_catalog المخطط. إذا كان الأمر كذلك، فإن ضبط الفهرس يسقط الملحق.
إذا لم يكن الملحق موجودا في المقام الأول أو قمنا بإسقاطه لأنه تم إنشاؤه في pg_catalog المخطط، فسينشئه ضبط الفهرس ضمن مخطط يسمى ms_temp_recommendations709253 ، وعندما تنتهي جلسة الضبط بنجاح، فإنه يسقط الملحق ويزيل المخطط.
يمكن للمستخدمين الأعضاء في azure_pg_admin الدور إسقاط ملحق نقص الأداء في أي وقت، حتى عندما تم إنشاؤه بواسطة ميزة ضبط الفهرس. ومع ذلك، قد يؤدي إسقاطه أثناء تشغيل جلسة ضبط الفهرس إلى فشل جلسة العمل هذه ولا ينتج عنها أي توصيات.
مستويات الحوسبة المدعومة ووحدات SKU
يتم دعم ضبط الفهرس على جميع المستويات المتوفرة حاليا: قابل للاندفاع والأغراض العامة والذاكرة المحسنة، وعلى أي وحدة SKU للحوسبة مدعومة حاليا مع 4 vCores على الأقل.
الإصدارات المدعومة من PostgreSQL
يتم دعم ضبط الفهرس على الإصدارات الرئيسية12 أو أعلى من قاعدة بيانات Azure لمثيلات الخادم المرنة PostgreSQL.
استخدام search_path
يستهلك ضبط الفهرس القيمة المستمرة في عمود search_pathquery_store.qs_view، بحيث عند تحليل كل استعلام، تم تعيين نفس القيمة search_path عند تنفيذ الاستعلام في الأصل هو الذي تم تعيينه لتحليل التوصيات المحتملة.
الاستعلامات ذات المعلمات
يتم تحليل الاستعلامات ذات المعلمات التي تم إنشاؤها باستخدام PREPARE أو باستخدام بروتوكول الاستعلام الموسع وتحليلها لإنتاج توصيات الفهرس عليها.
لتحليل الاستعلامات ذات المعلمات، يتطلب ضبط الفهرس تعيين pg_qs.parameters_capture_mode إلى capture_first_sample عندما يلتقط مخزن الاستعلام تنفيذ الاستعلام. يتطلب أيضا التقاط المعلمات بشكل صحيح بواسطة مخزن الاستعلام عند تنفيذ الاستعلام. بمعنى آخر، للاستعلام الذي يتم تحليله، إلى parameters_capture_status.
وضع القراءة فقط وقراءة النسخ المتماثلة
نظرا لأن ضبط الفهرس يعتمد على مخزن الاستعلام، وهو غير معتمد في النسخ المتماثلة للقراءة أو عندما يكون المثيل في وضع القراءة فقط، فإننا لا ندعمه على النسخ المتماثلة للقراءة أو على المثيلات الموجودة في وضع القراءة فقط.
تم إنتاج أي توصيات تمت رؤيتها على نسخة متماثلة للقراءة على النسخة المتماثلة الأساسية بعد تحليل حمل العمل الذي تم تنفيذه على النسخة المتماثلة الأساسية حصريا.
تقليص الحوسبة
إذا تم تمكين ضبط الفهرس على خادم، وقمت بتقليص حساب هذا الخادم إلى أقل من الحد الأدنى لعدد vCores المطلوب، تظل الميزة ممكنة. نظرا لأن الميزة غير مدعومة على الخوادم التي بها أقل من 4 vCores، فإنها لا تعمل لتحليل حمل العمل وتقديم التوصيات، حتى لو index_tuning.mode تم تعيينها إلى ON عند تقليص الحساب. بينما لا يفي الخادم بالحد الأدنى من المتطلبات، لا يمكن الوصول إلى جميع index_tuning.* معلمات الخادم. كلما قمت بتحجيم الخادم الخاص بك احتياطيا إلى حساب يلبي الحد الأدنى من المتطلبات، index_tuning.mode يتم تكوينه بأي قيمة تم تعيينها قبل تقليصه إلى حساب لا يفي بالمتطلبات.
قابلية وصول عالية وقراءة النسخ المتماثلة
إذا كان لديك قابلية وصول عالية أو قراءة نسخ متماثلة تم تكوينها على الخادم الخاص بك، فكن على دراية بالآثار المرتبطة بإنتاج أحمال عمل كثيفة الكتابة على الخادم الأساسي عند تنفيذ الفهارس الموصى بها. كن حذرا بشكل خاص عند إنشاء فهارس يقدر حجمها كبير.
الأسباب التي قد تجعل ضبط الفهرس لا ينتج عنه توصيات إنشاء فهرس لاستعلامات معينة
فيما يلي قائمة بأنواع الاستعلام التي لن يؤدي ضبط الفهرس إلى إنشاء توصيات إنشاء فهرس. تلك التي:
- واجه خطأ عندما يحاول محرك ضبط الفهرس الحصول على إخراج EXPLAIN الخاص به أثناء مرحلة التحليل.
- جداول مرجعية لا تحتوي على إحصائيات عن محتواها في كتالوج نظام pg_statistic. شغل ANALYSIS على تلك الجداول حتى يتمكن محرك الضبط من أخذ هذه الاستفسارات في الاعتبار مستقبلا.
- اقتطاع نص الاستعلام في مخزن الاستعلام. هذا هو الحال عندما يتجاوز طول نص الاستعلام القيمة التي تم تكوينها في pg_qs.max_query_text_length.
- الكائنات المرجعية التي تم إسقاطها أو إعادة تسميتها قبل إجراء التحليل. يمكن أن تظل هذه الاستعلامات صالحة من الناحية النحوية ، ولكنها ليست صالحة دلاليا.
- الوصول إلى الجداول أو الفهارس المؤقتة في الجداول المؤقتة.
- الوصول إلى طرق العرض أو طرق العرض الملموسة.
- الوصول إلى الجداول المقسمة.
- يتم تحديدها على أنها بيانات المنفعة. عبارات الأداة المساعدة أو أوامر الأداة المساعدة هي، في الأساس، أي عبارة لا تعتبر تحديدا أو إدراج أو تحديث أو حذف أو دمج، وأوامر معينة تحتوي على أحد هذه الأوامر.
- ليست من بين أبطأ index_tuning.max_queries_per_database ، لقاعدة البيانات والفترة التي تم تحليلها.
- تم تشغيلها في سياق قاعدة بيانات واحدة محددة، عندما لم يتم تحديد أي من هذه الاستعلامات على أنها الأبطأ على مستوى الخادم.