35. ضبط أداء PostgreSQL الأساسي¶
PostgreSQL نظام قواعد بيانات متعدد الاستخدامات إلى حد كبير، قادر على العمل بكفاءة في بيئات منخفضة الموارد جدًا وفي بيئات مشتركة مع تطبيقات أخرى متنوعة. ولضمان عمله بشكل سليم في بيئات مختلفة كثيرة، فإن الإعدادات الافتراضية متحفظة جدًا وغير ملائمة إطلاقًا لقاعدة بيانات إنتاجية عالية الأداء. أضف إلى ذلك أن لقواعد البيانات الجغرافية المكانية أنماط استخدام مختلفة، وأن بياناتها تتكون عادةً من سجلات أقل عددًا وأكبر حجمًا بكثير مقارنة بقواعد البيانات غير المكانية، وسترى أن الإعدادات الافتراضية لن تكون ملائمة تمامًا لأغراضنا.
يمكن تحرير جميع معاملات الإعداد هذه في ملف إعدادات قاعدة البيانات postgresql.conf. وهو ملف نصي عادي يمكن تحريره باستخدام أي محرر نصوص. ولن تسري التغييرات إلا بعد إعادة تشغيل الخادم.
يصف هذا القسم بعض معاملات الإعداد التي يمكن ضبطها للحصول على قاعدة بيانات جغرافية مكانية أكثر جاهزية للإنتاج.
ملاحظة
هذه القيم توصيات فقط؛ فكل بيئة تختلف عن الأخرى، ويلزم الاختبار لتحديد الإعدادات المثلى. لكن هذا القسم سيمنحك بداية جيدة.
35.2. effective_cache_size¶
بالإضافة إلى الذاكرة التي يخصصها PostgreSQL لـ shared_buffers، يأخذ مخطِّط الاستعلامات في الحسبان أيضًا عدد كتل القرص التي قد يكون نظام التشغيل قد خزّنها مؤقتًا ضمن نظام ملفاته الافتراضي. وفي الأنظمة ذات الذاكرة الكبيرة قد يكون هذا كبيرًا جدًا. وقيمة effective_cache_size تساوي تقريبًا مقدار الذاكرة في الجهاز، مطروحًا منه shared_buffers، ومطروحًا منه work_mem مضروبًا في العدد المتوقع من الاتصالات، ومطروحًا منه أي ذاكرة تتطلبها العمليات الأخرى العاملة على الجهاز، ومطروحًا منه نحو 1GB لاحتياجات نظام التشغيل الأخرى المتفرقة. لن تستخدم قاعدة البيانات الذاكرة المؤقتة الإضافية مباشرة، لكنها ستحسب الخطط على افتراض أن نظام التشغيل قد خزّن بيانات نظام الملفات مؤقتًا في هذا المقدار تقريبًا من الذاكرة.
القيمة الافتراضية: عادةً 4GB
القيمة الموصى بها: أي مقدار من الذاكرة "الحرة" يُتوقع أن يكون متاحًا في ظروف التشغيل العادية
35.3. work_mem¶
يحدد مقدار الذاكرة التي يمكن أن تستهلكها عمليات الفرز الداخلية وعمليات الفهرسة وجداول التجزئة (hash tables) قبل أن تنتقل قاعدة البيانات إلى استخدام ملفات على القرص. وتحدد هذه القيمة الذاكرة المتاحة لكل عملية؛ فقد تتضمن الاستعلامات المعقدة عدة عمليات فرز أو تجزئة تعمل بالتوازي، وقد تنفّذ كل جلسة متصلة استعلامًا.
لذلك يجب أن تأخذ في الحسبان عدد الاتصالات ومدى تعقيد الاستعلامات المتوقعة قبل زيادة هذه القيمة. فائدة الزيادة أن معالجة عدد أكبر من هذه العمليات، بما فيها عبارات ORDER BY و DISTINCT، وعمليات الربط بالدمج والتجزئة (merge and hash joins)، والتجميع القائم على التجزئة، والمعالجة القائمة على التجزئة للاستعلامات الفرعية، يمكن أن تتم دون الحاجة إلى الكتابة على القرص. أما كلفة الزيادة فهي الذاكرة التي ستُستخدم لكل اتصال، والتي قد تكون مرتفعة جدًا مع أعداد الاتصالات في بيئات الإنتاج.
القيمة الافتراضية: 1MB
القيمة الموصى بها: 32MB
35.4. maintenance_work_mem¶
يحدد مقدار الذاكرة المستخدمة لعمليات الصيانة، بما فيها التنظيف (vacuuming) وإنشاء الفهارس والمفاتيح الخارجية. ولأن هذه العمليات ليست شائعة جدًا، فإن القيمة الأعلى لن تفرض إلا كلفة عرضية، وقد تسرّع أنشطة الصيانة تسريعًا كبيرًا. ويمكن بدلًا من ذلك زيادة هذا المعامل لجلسة واحدة قبل تنفيذ عدد من استدعاءات CREATE INDEX أو VACUUM كما هو موضح أدناه.
SET maintenance_work_mem TO '128MB'; VACUUM ANALYZE; SET maintenance_work_mem TO '16MB';القيمة الافتراضية: 16MB
القيمة الموصى بها: 128MB
35.5. wal_buffers¶
يحدد مقدار الذاكرة المستخدمة لبيانات سجل الكتابة المسبقة (WAL). توفّر سجلات الكتابة المسبقة آلية عالية الأداء لضمان سلامة البيانات. فخلال كل أمر تغيير تُكتب آثار التغييرات أولًا في ملفات WAL وتُفرَّغ إلى القرص. ولا تُكتب التغييرات في ملفات البيانات نفسها إلا بعد تفريغ ملفات WAL. وهذا يتيح كتابة ملفات البيانات على القرص بطريقة مثلى وغير متزامنة، مع ضمان إمكانية استعادة جميع تغييرات البيانات من WAL في حال حدوث انهيار.
يكفي أن يكون حجم هذا المخزن المؤقت كبيرًا بما يكفي لاستيعاب بيانات WAL لمعاملة نموذجية واحدة. وبينما تكون القيمة الافتراضية كافية غالبًا لمعظم البيانات، فإن البيانات الجغرافية المكانية تميل إلى أن تكون أكبر بكثير. لذا يوصى بزيادة قيمة هذا المعامل.
القيمة الافتراضية: 64kB
القيمة الموصى بها: 1MB
35.6. checkpoint_segments¶
تحدد هذه القيمة الحد الأقصى لعدد مقاطع ملفات السجل (عادةً 16MB لكل منها) التي يمكن ملؤها بين نقاط التحقق (checkpoints) التلقائية لـ WAL. ونقطة تحقق WAL هي نقطة في تسلسل معاملات WAL يُضمَن عندها أن ملفات البيانات قد حُدّثت بجميع المعلومات السابقة لنقطة التحقق. وفي هذا الوقت تُفرَّغ جميع صفحات البيانات المعدّلة (dirty) إلى القرص ويُكتب سجل نقطة تحقق في ملف السجل. وهذا يتيح لعملية الاستعادة بعد الانهيار إيجاد آخر سجل نقطة تحقق وتطبيق جميع مقاطع السجل اللاحقة لإكمال استعادة البيانات.
لأن عملية نقطة التحقق تتطلب تفريغ جميع صفحات البيانات المعدّلة إلى القرص، فإنها تُحدث حملًا كبيرًا على الإدخال/الإخراج (I/O). وتنطبق هنا الحجة نفسها المذكورة أعلاه؛ فالبيانات الجغرافية المكانية كبيرة بما يكفي للإخلال بالتحسينات المصممة للبيانات غير المكانية. وزيادة هذه القيمة ستمنع نقاط التحقق المفرطة، وإن كانت قد تجعل الخادم يعيد التشغيل ببطء أكبر في حال حدوث انهيار.
القيمة الافتراضية: 3
القيمة الموصى بها: 6
35.7. random_page_cost¶
هذه قيمة بلا وحدة تمثّل كلفة الوصول العشوائي إلى صفحة من القرص. وهذه القيمة نسبية إلى عدد من معاملات الكلفة الأخرى، بما فيها الوصول التسلسلي إلى الصفحات وكلفة عمليات المعالج. ورغم أنه لا يوجد حل سحري لهذه القيمة، فإن القيمة الافتراضية متحفظة عمومًا ومصممة لقواعد البيانات العاملة على الأقراص الدوّارة. ويجب ضبط كلفة الوصول العشوائي لأقراص SSD على قيمة أقل من ذلك.
يمكن ضبط هذه القيمة لكل جلسة على حدة باستخدام الأمر SET random_page_cost TO 2.0، وهو ما قد يفيد في اختبار تأثيرها على خطط الاستعلامات.
القيمة الافتراضية: 4.0
القيمة الموصى بها: 2.0 للأقراص الدوّارة، و 1.0 لأقراص SSD
35.8. seq_page_cost¶
هذا هو المعامل الذي يتحكم في كلفة الوصول التسلسلي إلى صفحة. ولا تحتاج هذه القيمة عمومًا إلى ضبط، لكن الفرق بينها وبين random_page_cost يؤثر تأثيرًا كبيرًا في الخيارات التي يتخذها مخطِّط الاستعلامات. ويمكن أيضًا ضبط هذه القيمة لكل جلسة على حدة.
القيمة الافتراضية: 1.0
القيمة الموصى بها: 1.0
35.9. إعادة تحميل الإعدادات¶
بعد إجراء هذه التغييرات، احفظها وأعد تحميل الإعدادات. وأسهل طريقة لذلك هي إعادة تشغيل خدمة PostgreSQL.
في pgAdmin، انقر بزر الفأرة الأيمن على الخادم PostGIS (localhost:5432) واختر Disconnect.
في خدمات Windows (
services.msc) انقر بزر الفأرة الأيمن على PostgreSQL واختر Restart.بالعودة إلى pgAdmin، انقر على الخادم مرة أخرى واختر Disconnect.