مرحباً يا خبروفيتيس! أوجه انتباهكم إلى ترجمة المقال
"كيف أدى تغيير تكوين PostgreSQL واحد إلى تحسين أداء الاستعلام البطيء بمقدار 50 مرة" من إعداد بافان باتيباندلا. لقد ساعدني ذلك كثيرًا على تحسين أداء PostgreSQL.
في Amplitude ، هدفنا هو توفير تحليلات المنتجات التفاعلية سهلة الاستخدام حتى يتمكن الجميع من العثور على إجابات لأسئلتهم حول المنتج. لضمان سهولة الاستخدام ، يجب أن تقدم Amplitude هذه الإجابات بسرعة. لذلك ، عندما اشتكى أحد عملائنا من الوقت الذي استغرقته لتحميل القائمة المنسدلة لخصائص الحدث في واجهة مستخدم Amplitude ، بدأنا دراسة مفصلة لهذه المشكلة.
بتتبع التأخير على مستويات مختلفة ، أدركنا أن الأمر استغرق 20 ثانية لاستكمال استعلام PostgreSQL واحد. كان ذلك بمثابة مفاجأة لنا ، لأن كلا الجدولين يحتويان على فهارس في عمود الصلة.
طلب بطيء
كانت خطة تنفيذ PostgreSQL لهذا الاستعلام غير متوقعة بالنسبة لنا. على الرغم من حقيقة أن كلا الجدولين يحتويان على فهارس ، فقد قرر PostgreSQL إجراء Hash Join مع المسح المتسلسل لجدول كبير. استغرق مسح جدول كبير بالتسلسل معظم وقت الاستعلام.
خطة تنفيذ الاستعلام البطيء
في البداية كنت أظن أن هذا قد يكون بسبب التفتت. لكن بعد التحقق من البيانات ، أدركت أن البيانات تُضاف فقط إلى هذا الجدول ولا يتم حذفها عمليًا من هناك. نظرًا لأن مسح المكان باستخدام VACUUM لن يساعد كثيرًا هنا ، فقد بدأت في إجراء مزيد من الحفر. ثم جربت نفس الطلب على عميل آخر مع وقت استجابة جيد. لدهشتي ، خطة تنفيذ الاستعلام تبدو مختلفة تماما!
خطة تنفيذ لنفس الطلب على عميل آخر
ومن المثير للاهتمام ، أن التطبيق "أ" تمكن فقط من الوصول إلى 10 أضعاف البيانات أكثر من التطبيق "ب" ، لكن زمن الاستجابة كان أطول بثلاث مرات
لمشاهدة خطط استعلام PostgreSQL بديلة ، أوقفت اتصال التجزئة وأعدت تشغيل الاستعلام.
خطة التنفيذ البديلة للاستعلام البطيء
حسنا هنا! يكمل الطلب نفسه 50 مرة أسرع عند استخدام حلقة متداخلة بدلاً من صلة التجزئة. فلماذا اختار PostgreSQL أسوأ خطة للتطبيق A؟
من خلال إلقاء نظرة فاحصة على التكلفة المقدرة والمهلة الفعلية لكلتا الخطتين ، كانت النسب المقدرة للتكلفة وفترة المهلة الفعلية مختلفة تمامًا. السبب الرئيسي لهذا التناقض هو تقدير تكلفة المسح المتسلسل. تقدر PostgreSQL أن عمليات المسح المتسلسل ستكون أفضل من 4000+ من عمليات المسح ، لكن في الواقع ، كانت عمليات مسح المؤشر أسرع 50 مرة.
هذا قادني إلى
خيارات التكوين
random_page_cost و
seq_page_cost . قيم PostgreSQL الافتراضية هي
4 و
1 لـ
random_page_cost ،
seq_page_cost ، والتي تم تكوينها
لمحرك الأقراص الصلبة ، حيث يكون الوصول العشوائي إلى القرص أغلى من الوصول التسلسلي. ومع ذلك ، كانت هذه التكاليف غير دقيقة
لنشرنا باستخدام وحدة التخزين
gp2 EBS ، والتي تمثل محركات أقراص صلبة. بالنسبة لنشرنا ، يكون الوصول العشوائي والمتسلسل هو نفسه تقريبًا.
لقد غيرت قيمة
random_page_cost إلى
1 وحاولت إعادة الطلب. هذه المرة ، استخدم PostgreSQL حلقة متداخلة ، وكان الاستعلام أسرع 50 مرة. بعد التغيير ، لاحظنا أيضًا انخفاضًا كبيرًا في الحد الأقصى لوقت الاستجابة من PostgreSQL.
تحسن الأداء العام للطلب البطيء بشكل ملحوظ.
إذا كنت تستخدم SSD وتستخدم PostgreSQL مع التكوين الافتراضي ، فإنني أنصحك بتجربة
إعداد random_page_cost و
seq_page_cost . قد تفاجأ بتحسين الأداء المذهل.
لوحدي ، سأضيف أنني قمت بتعيين الحد الأدنى من المعلمات
seq_page_cost = random_page_cost = 0.1 لإعطاء الأولوية للبيانات الموجودة في الذاكرة (ذاكرة التخزين المؤقت) على عمليات المعالج ، حيث أنني خصصت كمية كبيرة من ذاكرة الوصول العشوائي لـ PostgreSQL (يتجاوز حجم ذاكرة الوصول العشوائي حجم قاعدة البيانات على القرص). ليس من الواضح تمامًا لماذا لا يزال مجتمع postgres يستخدم الإعدادات الافتراضية ذات الصلة بخادم يحتوي على كمية صغيرة من ذاكرة الوصول العشوائي والأقراص الصلبة ، وليس للخوادم الحديثة. نأمل أن يتم إصلاح هذا قريبا.