قصة قصيرة عن الطلب "الثقيل" والحل الأنيق للمشكلة
في الآونة الأخيرة ، في الليل ، بدأت التنبيهات بإيقاظنا: لم يكن هناك مساحة كافية على القرص. اكتشفنا بسرعة أن المشكلة تكمن في مهام ETL.
تم تنفيذ مهمة ETL في جدول حيث يتم تخزين السجلات الثنائية ومقالب النفايات. كل ليلة ، كانت هذه المهمة لإزالة مقالب مكررة وإخلاء مساحة.
للبحث عن مقالب مكررة ، استخدمنا هذا الاستعلام:
id, MIN(id) OVER (PARTITION BY blob ORDER BY id) FROM dumps
يجمع الاستعلام بين نفس المقالب بواسطة حقل BLOB. باستخدام وظيفة النافذة ، نحصل على معرف أول ظهور لكل تفريغ. ثم مع هذا الطلب نحذف جميع مقالب مكررة.
تم تنفيذ الطلب لبعض الوقت ، وكما يتبين من السجلات ، فقد أكل الكثير من الذاكرة. يوضح الرسم البياني كيف سجل مساحة خالية على القرص كل ليلة:
مع مرور الوقت ، طلب المزيد من الذاكرة ، عمقت الفشل. وبالنظر إلى خطة التنفيذ ، رأينا على الفور أين يذهب كل شيء:
Buffers: shared hit=3916, temp read=3807 written=3816 -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160) Sort Key: blob, id Sort Method: external merge Disk: 30456kB Buffers: shared hit=3916, temp read=3807 written=3816 -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160) Buffers: shared hit=3916 Execution time: 159.960 ms
الفرز يستغرق الكثير من الذاكرة. من حيث التنفيذ ، يتطلب الفرز حوالي 30 ميغابايت من الذاكرة من مجموعة بيانات الاختبار.
لماذا هذا
PostgreSQL يخصص الذاكرة للتجزئة والفرز. يتم التحكم في مقدار الذاكرة بواسطة المعلمة work_mem . الحجم الافتراضي لـ work_mem هو 4 ميغابايت. إذا كانت هناك حاجة إلى أكثر من 4 ميغابايت للتجزئة أو التصنيف ، فإن PostgreSQL يستهلك مساحة القرص مؤقتًا.
يستهلك استعلامنا بوضوح أكثر من 4 ميغابايت ، لذلك تستخدم قاعدة البيانات الكثير من الذاكرة. قررنا: لن نتسرع ، ولم نزيد المعلمة ونوسع مساحة التخزين. من الأفضل البحث عن طريقة أخرى لتقليص الذاكرة للفرز .
الفرز الاقتصادي
"يعتمد مقدار الفرز الذي ستأكله على حجم مجموعة البيانات ومفتاح الفرز. لا يمكنك تقليل مجموعة البيانات ، ولكن حجم المفتاح ممكن .
بالنسبة للنقطة المرجعية ، نأخذ متوسط حجم مفتاح الفرز:
avg ---------- 780
يزن كل مفتاح 780. لتقليل المفتاح الثنائي ، يمكن تجزئته. في PostgreSQL ، يوجد md5 لهذا (نعم ، ليس الأمان ، لكن لغرضنا سوف يفعل). دعونا نرى كم يزن BLOB مع md5:
avg ----------- 36
حجم تجزئة المفتاح خلال md5 هو 36 بايت. يزن مفتاح التجزئة 4٪ فقط من الخيار الأصلي .
بعد ذلك ، أطلقنا الطلب الأصلي باستخدام مفتاح التجزئة:
id, MIN(id) OVER ( PARTITION BY md5(array_to_string(blob, '') ) ORDER BY id) FROM dumps;
وخطة التنفيذ:
Buffers: shared hit=3916 -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160) Sort Key: (md5(array_to_string(blob, ''::text))), id Sort Method: quicksort Memory: 4005kB Buffers: shared hit=3916 -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160) Buffers: shared hit=3916 Execution time: 374.125 ms
باستخدام مفتاح تجزئة ، يستهلك الطلب 4 ميغابايت إضافية فقط ، أي أكثر بقليل من 10٪ من 30 ميغابايت السابقة. لذا فإن حجم مفتاح الفرز يؤثر بشكل كبير على مقدار الذاكرة التي يأكلها النوع .
مزيد من أكثر
في هذا المثال ، قمنا بتجزئة BLOB باستخدام md5 . يجب أن تزن التجزئة التي تم إنشاؤها باستخدام MD5 16 بايت. و حصلنا على المزيد:
md5_size ------------- 32
كان حجم التجزئة الخاص بنا أكبر مرتين ، لأن md5 ينتج تجزئة في شكل نص سداسي عشري.
في PostgreSQL ، يمكنك استخدام MD5 للتجزئة باستخدام ملحق pgcrypto . pgcrypto يخلق MD5 من نوع bytea (في ثنائي) :
select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size crypto_md5_size --------------- 20
التجزئة لا يزال 4 بايت أكثر مما ينبغي. إنه فقط يستخدم نوع البايت هذه البايتات الأربعة لتخزين طول القيمة ، لكننا لن bytea .
اتضح أن نوع uuid في PostgreSQL يزن 16 بايت بالضبط ويدعم أي قيمة تعسفية ، لذلك نتخلص من البايتات الأربعة المتبقية:
uuid_size --------------- 16
هذا كل شيء. 32 بايت مع md5 تتحول إلى 16 مع uuid .
راجعت تأثيرات التغيير من خلال أخذ مجموعة بيانات أكبر. لا يمكن عرض البيانات نفسها ، لكنني سأشارك النتائج:
كما ترون من الجدول ، كان الطلب الأصلي المشكل يبلغ 300 ميجابايت (واستيقظنا في منتصف الليل). باستخدام مفتاح uuid ، استغرق الفرز 7 ميغابايت فقط.
متابعة الاعتبارات
يستهلك الطلب ذو مفتاح فرز الذاكرة المجزأة أقل ، لكنه يعمل بشكل أبطأ بكثير:
يستخدم هاش المزيد من وحدة المعالجة المركزية ، وبالتالي فإن الطلب مع تجزئة أبطأ. لكننا حاولنا حل المشكلة مع مساحة القرص ، علاوة على ذلك ، يتم تنفيذ المهمة في الليل ، لذلك الوقت ليس مشكلة. نحن للخطر لانقاذ الذاكرة.
هذا مثال رائع على حقيقة أنك لا تحتاج دائمًا إلى محاولة تسريع استعلامات قاعدة البيانات . من الأفضل استخدام ما هو متوازن والضغط على الحد الأقصى من الحد الأدنى من الموارد.