ضبط إعدادات PostgreSQL لتحسين الأداء

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

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

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

خيارات PostgreSQL المخصصة

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

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

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

من الملاحظ أن الأداء الجيد في بيئات الإنتاج يولي بالفعل أهمية كبيرة لـ Shared_buffer ، على الرغم من أنه ينبغي إجراء الاختبارات دائمًا لتحقيق التوازن الصحيح.

^{التحقق من قيمة Shared_buffer}

testdb=# SHOW shared_buffers; shared_buffers ---------------- 128MB (1 row) 

_{ملاحظة : كن حذرًا ، نظرًا لأن بعض النواة لا تدعم قيمة أكبر ، خاصة على Windows.}

wal_buffers

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

effective_cache_size

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

work_mem

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

 work_mem * total sort operations 

لجميع المستخدمين. يمكن أن يؤدي تعيين هذه المعلمة على مستوى العالم إلى استخدام ذاكرة عالية جدًا. لذلك ، يوصى بشدة بتغييره على مستوى الجلسة.

^{work_mem = 2 ميغابايت}

 testdb=# SET work_mem TO "2MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN ----------------------------------------------------------------------------------- Gather Merge (cost=509181.84..1706542.14 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=508181.79..514431.86 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) (5 rows) 

يتم تقييم عقدة فرز الطلب الأولي في 514431.86. التكلفة هي وحدة حسابية تعسفية. للطلب أعلاه ، لدينا work_mem فقط 2 ميغابايت. لأغراض الاختبار ، دعنا نرفع هذه القيمة إلى 256 ميجابايت ونرى ما إذا كانت تؤثر على التكلفة.

^{work_mem = 256 ميجابايت}

 testdb=# SET work_mem TO "256MB"; testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; QUERY PLAN ----------------------------------------------------------------------------------- Gather Merge (cost=355367.34..1552727.64 rows=10000116 width=24) Workers Planned: 4 -> Sort (cost=354367.29..360617.36 rows=2500029 width=24) Sort Key: b -> Parallel Seq Scan on bar (cost=0.00..88695.29 rows=2500029 width=24) 

خفضت تكلفة الطلب من 514431.86 إلى 360617.36 ، أي انخفضت بنسبة 30 ٪.

maintenance_work_mem

maintenance_work_mem هي معلمة ذاكرة تستخدم لمهام الصيانة. القيمة الافتراضية هي 64 ميجابايت. يساعد تعيين قيمة كبيرة في مهام مثل VACUUM و RESTORE و CREATE INDEX و ADD FOREIGN KEY و ALTER TABLE.

^{maintenance_work_mem = 10 ميغابايت}

 postgres=# CHECKPOINT; postgres=# SET maintenance_work_mem to '10MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 170091.371 ms (02:50.091) 

^{maintenance_work_mem = 256 ميجابايت}

 postgres=# CHECKPOINT; postgres=# set maintenance_work_mem to '256MB'; postgres=# CREATE INDEX foo_idx ON foo (c); CREATE INDEX Time: 111274.903 ms (01:51.275) 

وقت إنشاء الفهرس هو 170091.371 مللي ثانية إذا تم تعيين المعلمة maintenance_work_mem على 10 ميغابايت فقط ، لكنه ينخفض ​​إلى 111274.903 مللي ثانية عندما نزيد المعلمة maintenance_work_mem إلى 256 ميغابايت.

synchronous_commit

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

checkpoint_timeout ، checkpoint_completion_target

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

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

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

استنتاج

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