مشاركة عبر

أفضل الممارسات لتحميل البيانات بشكل مجمع إلى قاعدة بيانات Azure ل PostgreSQL

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

أساليب التحميل

يتم ترتيب أساليب تحميل البيانات التالية بالترتيب من الأكثر استهلاكا للوقت إلى أقل استهلاك للوقت:

  • تشغيل أمر سجل INSERT واحد.
  • دفعة إلى 100 إلى 1000 صف لكل تثبيت. يمكنك استخدام كتلة معاملة لتضمين سجلات متعددة لكل تثبيت.
  • تشغيل INSERT بقيم صف متعددة.
  • تشغيل الأمر COPY.

الطريقة المفضلة لتحميل البيانات في قاعدة بيانات هي COPY الأمر . COPY إذا لم يكن الأمر مستحيلا، فإن الدفعة INSERT هي أفضل طريقة تالية. تعد مؤشرات الترابط المتعددة باستخدام COPY أمر مثالية لتحميل البيانات بشكل مجمع.

خطوات تحميل البيانات المجمعة

فيما يلي خطوات التحميل المجمع للبيانات إلى قاعدة بيانات Azure لمثيل خادم PostgreSQL المرن.

الخطوة 1: إعداد بياناتك

تأكد من أن بياناتك نظيفة ومنسقة بشكل صحيح لقاعدة البيانات.

الخطوة 2: اختر أسلوب التحميل

حدد طريقة التحميل المناسبة استنادا إلى حجم بياناتك وتعقيدها.

الخطوة 3: تنفيذ أسلوب التحميل

قم بتشغيل أسلوب التحميل المختار لتحميل بياناتك إلى قاعدة البيانات.

الخطوة 4: التحقق من البيانات

بعد التحميل، تحقق من تحميل البيانات بشكل صحيح في قاعدة البيانات.

أفضل الممارسات لتحميل البيانات الأولية

فيما يلي أفضل الممارسات لتحميل البيانات الأولية.

إسقاط الفهارس

قبل إجراء تحميل أولي للبيانات، نوصي بإسقاط جميع الفهارس في الجداول. دائما ما يكون إنشاء الفهارس بعد تحميل البيانات أكثر كفاءة.

إسقاط القيود

يتم وصف قيود الإفلات الرئيسية هنا:

  • قيود المفتاح الفريدة

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

  • قيود المفتاح الخارجي

نوصي بإسقاط قيود المفتاح الخارجي قبل تحميل البيانات الأولية وإعادة إنشائها بعد اكتمال تحميل البيانات.

يؤدي تغيير المعلمة session_replication_role إلى replica تعطيل جميع عمليات التحقق من المفتاح الخارجي أيضا. ومع ذلك، إذا لم يتم استخدام التغيير بشكل صحيح، يمكن أن يترك البيانات غير متناسقة.

جداول غير مسجلة

ضع في اعتبارك إيجابيات وسلبيات الجداول غير المسجلة قبل استخدامها في أحمال البيانات الأولية.

يؤدي استخدام الجداول غير المسجلة إلى تسريع تحميل البيانات. لا تتم كتابة البيانات المكتوبة إلى جداول غير مسجلة في سجل الكتابة المسبقة.

عيوب استخدام الجداول غير المسجلة هي:

  • إنها ليست آمنة من الأعطال. يتم اقتطاع جدول غير مسجل تلقائيا بعد حدوث عطل أو إيقاف تشغيل غير نظيف.
  • لا يمكن نسخ البيانات من الجداول غير المسجلة نسخا متماثلا إلى خوادم الاستعداد.

لإنشاء جدول غير مسجل أو تغيير جدول موجود إلى جدول غير مسجل، استخدم الخيارات التالية:

  • إنشاء جدول جديد غير مسجل باستخدام بناء الجملة التالي:

    CREATE UNLOGGED TABLE <tablename>;

  • تحويل جدول مسجل موجود إلى جدول غير مسجل باستخدام بناء الجملة التالي:

    ALTER TABLE <tablename> SET UNLOGGED;

ضبط معلمة الخادم

  • auto vacuum': It's best to turn off التفريغ التلقائي' أثناء تحميل البيانات الأولية. بعد اكتمال التحميل الأولي، نوصي بتشغيل دليل VACUUM ANALYZE على جميع الجداول في قاعدة البيانات ثم تشغيل auto vacuum.

إشعار

اتبع التوصيات هنا فقط إذا كانت هناك مساحة كافية من الذاكرة والقرص.

  • maintenance_work_mem: يمكن تعيينه إلى 2 غيغابايت كحد أقصى (GB) على قاعدة بيانات Azure لمثيل خادم PostgreSQL المرن. maintenance_work_mem يساعد في تسريع الفراغ التلقائي والفهرس وإنشاء المفتاح الخارجي.

  • checkpoint_timeout: في قاعدة بيانات Azure لمثيل خادم PostgreSQL المرن، checkpoint_timeout يمكن زيادة القيمة إلى 24 ساعة كحد أقصى من الإعداد الافتراضي وهو 5 دقائق. نوصي بزيادة القيمة إلى ساعة واحدة قبل تحميل البيانات في البداية على قاعدة بيانات Azure لمثيل خادم PostgreSQL المرن.

  • checkpoint_completion_target: نوصي بقيمة 0.9.

  • max_wal_size: يمكن تعيين إلى القيمة القصوى المسموح بها على مثيل خادم مرن لقاعدة بيانات Azure ل PostgreSQL، وهو 64 غيغابايت أثناء تحميل البيانات الأولي.

  • wal_compression: يمكن تشغيل هذا. يمكن أن يؤدي تمكين هذه المعلمة إلى تكبد بعض التكاليف الإضافية لوحدة المعالجة المركزية للضغط أثناء تسجيل سجل الكتابة المسبقة (WAL) وإلغاء الضغط أثناء إعادة تشغيل WAL.

التوصيات

قبل أن تبدأ تحميل بيانات أولية على مثيل خادم مرن لقاعدة بيانات Azure ل PostgreSQL، نوصي بما يلي:

  • تعطيل قابلية الوصول العالية على الخادم. يمكنك تمكينه بعد اكتمال التحميل الأولي على الأساسي.
  • إنشاء نسخ متماثلة للقراءة بعد اكتمال تحميل البيانات الأولية.
  • اجعل التسجيل ضئيلا أو قم بتعطيله معا أثناء تحميل البيانات الأولية (على سبيل المثال، تعطيل pgaudit، pg_stat_statements، مخزن الاستعلام).

إعادة إنشاء الفهارس وإضافة قيود

بافتراض إسقاط الفهارس والقيود قبل التحميل الأولي، نوصي باستخدام قيم عالية في maintenance_work_mem (كما ذكر سابقا) لإنشاء فهارس وإضافة قيود. بالإضافة إلى ذلك، بدءا من الإصدار 11 من PostgreSQL، يمكن تعديل المعلمات التالية لإنشاء فهرس متوازي أسرع بعد تحميل البيانات الأولية:

  • max_parallel_workers: تعيين الحد الأقصى لعدد العمال الذين يمكن للنظام دعمهم للاستعلامات المتوازية.

  • max_parallel_maintenance_workers: يتحكم في الحد الأقصى لعدد عمليات العامل، والتي يمكن استخدامها في CREATE INDEX.

يمكنك أيضا إنشاء الفهارس عن طريق إجراء الإعدادات الموصى بها على مستوى الجلسة. فيما يلي مثال على كيفية القيام بذلك:

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

أفضل الممارسات لتحميل البيانات المتزايدة

يتم وصف أفضل الممارسات لأحمال البيانات المتزايدة هنا:.

جداول التقسيم

نوصي دائما بتقسيم الجداول الكبيرة. تتضمن بعض مزايا التقسيم، خاصة أثناء الأحمال المتزايدة:

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

الحفاظ على إحصائيات الجدول المحدثة

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

إنشاء فهارس على قيود المفتاح الخارجي

يمكن أن يكون إنشاء فهارس على المفاتيح الخارجية في الجداول التابعة مفيدا في السيناريوهات التالية:

  • تحديثات البيانات أو عمليات الحذف في الجدول الأصل. عند تحديث البيانات أو حذفها في الجدول الأصل، يتم إجراء عمليات البحث على الجدول التابع. يمكنك فهرسة المفاتيح الخارجية على الجدول التابع لجعل عمليات البحث أسرع.
  • الاستعلامات، حيث يمكنك رؤية الجداول الأصل والتابعة التي تنضم إلى أعمدة المفاتيح.

تحديد الفهارس غير المستخدمة

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

يمكنك تحديد الفهارس غير المستخدمة بطريقتين: بواسطة Query Store واستعلام استخدام الفهرس.

مخزن الاستعلامات

تساعد ميزة Query Store في تحديد الفهارس، والتي يمكن إسقاطها استنادا إلى أنماط استخدام الاستعلام في قاعدة البيانات. للحصول على إرشادات خطوة بخطوة، راجع Query Store.

بعد تمكين Query Store على الخادم، يمكنك استخدام الاستعلام التالي لتحديد الفهارس التي يمكن إسقاطها عن طريق الاتصال بقاعدة بيانات azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

استخدام الفهرس

يمكنك أيضا استخدام الاستعلام التالي لتحديد الفهارس غير المستخدمة:

SELECT
    t.schemaname,
    t.tablename,
    c.reltuples::bigint                            AS num_rows,
 pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
    psai.indexrelname                              AS index_name,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique",
    psai.idx_scan                                  AS number_of_scans,
    psai.idx_tup_read                              AS tuples_read,
    psai.idx_tup_fetch                             AS tuples_fetched
FROM
 pg_tables t
    LEFT JOIN pg_class c ON t.tablename = c.relname
    LEFT JOIN pg_index i ON c.oid = i.indrelid
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
    t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

number_of_scans tuples_readتشير الأعمدة و و إلى tuples_fetched الفهرس usage.number_of_scans قيمة عمود صفرية كفهرس لا يتم استخدامه.

ضبط معلمة الخادم

إشعار

اتبع التوصيات الواردة في المعلمات التالية فقط إذا كانت هناك مساحة كافية من الذاكرة والقرص.

  • maintenance_work_mem: يمكن تعيين هذه المعلمة إلى 2 غيغابايت كحد أقصى على قاعدة بيانات Azure لمثيل خادم PostgreSQL المرن. maintenance_work_mem يساعد على تسريع إنشاء الفهرس وإضافات المفاتيح الخارجية.

  • checkpoint_timeout: في مثيل خادم Azure Database for PostgreSQL المرن، checkpoint_timeout يمكن زيادة القيمة إلى 10 أو 15 دقيقة من الإعداد الافتراضي وهو 5 دقائق. يمكن أن تؤدي الزيادة checkpoint_timeout إلى قيمة أكثر أهمية، مثل 15 دقيقة، إلى تقليل تحميل الإدخال/الإخراج، ولكن الجانب السلبي هو أن الاسترداد يستغرق وقتا أطول إذا كان هناك عطل. نوصي بدراسة متأنية قبل إجراء التغيير.

  • checkpoint_completion_target: نوصي بقيمة 0.9.

  • max_wal_size: تعتمد هذه القيمة على SKU والتخزين وعبء العمل. يوضح المثال التالي طريقة واحدة للوصول إلى القيمة الصحيحة ل max_wal_size.

خلال ساعات الذروة من العمل، تصل إلى قيمة عن طريق القيام بما يلي:

أ. خذ رقم تسلسل سجل WAL الحالي (LSN) عن طريق تشغيل الاستعلام التالي:

SELECT pg_current_wal_lsn ();

ب. انتظر عدد الثوان checkpoint_timeout . خذ WAL LSN الحالي عن طريق تشغيل الاستعلام التالي:

SELECT pg_current_wal_lsn ();

جـ. استخدم النتيجتين للتحقق من الفرق بالجيجابايت:

SELECT round (pg_wal_lsn_diff('LSN value when running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;
  • wal_compression: يمكن تشغيل هذا. يمكن أن يؤدي تمكين هذه المعلمة إلى تكلفة إضافية لوحدة المعالجة المركزية للضغط أثناء تسجيل WAL وإلغاء الضغط أثناء إعادة تشغيل WAL.

المحتوى ذو الصلة

  • Last updated on