Bagikan melalui

Praktik terbaik untuk mengunggah data secara massal ke Azure Database for PostgreSQL

Artikel ini membahas berbagai metode untuk memuat data secara massal ke instans server fleksibel Azure Database for PostgreSQL, bersama dengan praktik terbaik untuk pemuatan data awal dalam database kosong dan beban data bertahap.

Memuat metode

Metode pemuatan data berikut diatur secara berurutan dari sebagian besar memakan waktu hingga paling sedikit memakan waktu:

  • Jalankan perintah rekaman INSERT tunggal.
  • Batch menjadi 100 hingga 1.000 baris per penerapan. Anda dapat menggunakan blok transaksi untuk membungkus beberapa rekaman per penerapan.
  • Jalankan INSERT dengan beberapa nilai baris.
  • Jalankan perintah COPY.

Metode yang disukai untuk memuat data ke dalam database adalah COPY perintah . COPY Jika perintah tidak mustahil, batch INSERT adalah metode terbaik berikutnya. Multi-utas dengan COPY perintah optimal untuk memuat data secara massal.

Langkah-langkah untuk mengunggah data massal

Berikut adalah langkah-langkah untuk mengunggah data secara massal ke instans server fleksibel Azure Database for PostgreSQL.

Langkah 1: Siapkan data Anda

Pastikan data Anda bersih dan diformat dengan benar untuk database.

Langkah 2: Pilih metode pemuatan

Pilih metode pemuatan yang sesuai berdasarkan ukuran dan kompleksitas data Anda.

Langkah 3: Jalankan metode pemuatan

Jalankan metode pemuatan yang dipilih untuk mengunggah data Anda ke database.

Langkah 4: Memverifikasi data

Setelah mengunggah, verifikasi bahwa data telah dimuat dengan benar ke dalam database.

Praktik terbaik untuk pemuatan data awal

Berikut adalah praktik terbaik untuk pemuatan data awal.

Jatuhkan indeks

Sebelum Anda melakukan pemuatan data awal, sebaiknya hilangkan semua indeks dalam tabel. Membuat indeks setelah data dimuat selalu lebih efisien.

Menghilangkan batasan

Batasan penurunan utama dijelaskan di sini:

  • Batasan kunci unik

Untuk mencapai performa yang kuat, sebaiknya hilangkan batasan kunci unik sebelum pemuatan data awal dan membuatnya kembali setelah beban data selesai. Namun, menghilangkan batasan kunci unik membatalkan perlindungan terhadap data duplikat.

  • Batasan kunci asing

Sebaiknya hilangkan batasan kunci asing sebelum pemuatan data awal dan membuatnya kembali setelah pemuatan data selesai.

Mengubah session_replication_role parameter untuk replica juga menonaktifkan semua pemeriksaan kunci asing. Namun, jika perubahan tidak digunakan dengan benar, perubahan tersebut dapat membuat data tidak konsisten.

Tabel yang tidak di-unlogged

Pertimbangkan pro dan kontra tabel yang tidak di-log sebelum menggunakannya dalam pemuatan data awal.

Menggunakan tabel yang tidak di-unlogged mempercepat pemuatan data. Data yang ditulis ke tabel yang tidak di-unlogg tidak ditulis ke log write-ahead.

Kerugian menggunakan tabel yang tidak dialokasikan adalah:

  • Mereka bukan crash-safe. Tabel yang tidak di-hapus secara otomatis terpotong setelah crash atau matikan yang tidak jelas.
  • Data dari tabel yang tidak di-unlogg tidak dapat direplikasi ke server siaga.

Untuk membuat tabel yang tidak di-log atau mengubah tabel yang sudah ada menjadi tabel yang tidak di-log, gunakan opsi berikut:

  • Buat tabel baru yang tidak di-unlogg dengan menggunakan sintaks berikut:

    CREATE UNLOGGED TABLE <tablename>;

  • Konversikan tabel yang dicatat yang sudah ada ke tabel yang tidak di-log dengan menggunakan sintaks berikut:

    ALTER TABLE <tablename> SET UNLOGGED;

Penyetelan parameter server

  • auto vacuum': It's best to turn off vakum otomatis' selama pemuatan data awal. Setelah beban awal selesai, kami sarankan Anda menjalankan manual VACUUM ANALYZE pada semua tabel dalam database lalu mengaktifkan auto vacuum.

Catatan

Ikuti rekomendasi di sini hanya jika ada cukup memori dan ruang disk.

  • maintenance_work_mem: Dapat diatur ke maksimum 2 gigabyte (GB) pada instans server fleksibel Azure Database for PostgreSQL. maintenance_work_mem membantu mempercepat vakum otomatis, indeks, dan pembuatan kunci asing.

  • checkpoint_timeout: Pada instans server fleksibel Azure Database for PostgreSQL, checkpoint_timeout nilai dapat ditingkatkan hingga maksimum 24 jam dari pengaturan default 5 menit. Sebaiknya tingkatkan nilai menjadi 1 jam sebelum Anda awalnya memuat data pada instans server fleksibel Azure Database for PostgreSQL.

  • checkpoint_completion_target: Kami merekomendasikan nilai 0,9.

  • max_wal_size: Dapat diatur ke nilai maksimum yang diizinkan pada instans server fleksibel Azure Database for PostgreSQL, yaitu 64 GB saat Anda melakukan pemuatan data awal.

  • wal_compression: Ini dapat diaktifkan. Mengaktifkan parameter ini dapat dikenakan beberapa biaya CPU tambahan untuk pemadatan selama pengelogan dan dekompresi log write-ahead (WAL) selama pemutaran ulang WAL.

Rekomendasi

Sebelum Anda memulai pemuatan data awal pada instans server fleksibel Azure Database for PostgreSQL, kami sarankan Anda:

  • Nonaktifkan ketersediaan tinggi di server. Anda dapat mengaktifkannya setelah beban awal selesai pada primer.
  • Buat replika baca setelah pemuatan data awal selesai.
  • Buat pengelogan minimal atau nonaktifkan semuanya bersama-sama selama pemuatan data awal (misalnya, nonaktifkan pgaudit, pg_stat_statements, penyimpanan kueri).

Membuat ulang indeks dan menambahkan batasan

Dengan asumsi Bahwa Anda menghilangkan indeks dan batasan sebelum beban awal, sebaiknya gunakan nilai tinggi di maintenance_work_mem (seperti yang disebutkan sebelumnya) untuk membuat indeks dan menambahkan batasan. Selain itu, dimulai dengan PostgreSQL versi 11, parameter berikut dapat dimodifikasi untuk pembuatan indeks paralel yang lebih cepat setelah pemuatan data awal:

  • max_parallel_workers: Mengatur jumlah maksimum pekerja yang dapat didukung sistem untuk kueri paralel.

  • max_parallel_maintenance_workers: Mengontrol jumlah maksimum proses pekerja, yang dapat digunakan dalam CREATE INDEX.

Anda juga dapat membuat indeks dengan membuat pengaturan yang direkomendasikan di tingkat sesi. Berikut adalah contoh cara melakukannya:

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);

Praktik terbaik untuk pemuatan data inkremental

Praktik terbaik untuk pemuatan data inkremental dijelaskan di sini:.

Tabel partisi

Kami selalu menyarankan agar Anda mempartisi tabel besar. Beberapa keuntungan pemartisian, terutama selama beban inkremental, meliputi:

  • Membuat partisi baru berdasarkan delta baru membuat penambahan data baru ke tabel menjadi efisien.
  • Mempertahankan tabel menjadi lebih mudah. Anda dapat menjatuhkan partisi selama beban data inkremental untuk menghindari penghapusan yang memakan waktu dalam tabel besar.
  • Autovacuum hanya akan dipicu pada partisi yang diubah atau ditambahkan selama beban inkremental, yang membuat mempertahankan statistik pada tabel lebih mudah.

Pertahankan statistik tabel terbaru

Memantau dan memelihara statistik tabel penting untuk performa kueri pada database. Ini juga termasuk skenario di mana Anda memiliki beban bertahap. PostgreSQL menggunakan proses daemon autovacuum untuk membersihkan tuple mati dan menganalisis tabel untuk menjaga statistik tetap diperbarui. Untuk informasi selengkapnya, lihat Pemantauan dan penyetelan autovacuum.

Membuat indeks pada batasan kunci asing

Membuat indeks pada kunci asing dalam tabel anak dapat bermanfaat dalam skenario berikut:

  • Pembaruan atau penghapusan data dalam tabel induk. Saat data diperbarui atau dihapus dalam tabel induk, pencarian dilakukan pada tabel anak. Anda dapat mengindeks kunci asing pada tabel anak untuk membuat pencarian lebih cepat.
  • Kueri, tempat Anda dapat melihat tabel induk dan anak yang bergabung pada kolom kunci.

Mengidentifikasi indeks yang tidak digunakan

Identifikasi indeks yang tidak digunakan dalam database dan letakkan. Indeks adalah overhead pada pemuatan data. Semakin sedikit indeks pada tabel, semakin baik performa selama penyerapan data.

Anda dapat mengidentifikasi indeks yang tidak digunakan dengan dua cara: menurut Penyimpanan Kueri dan kueri penggunaan indeks.

Penyimpanan Kueri

Fitur Penyimpanan Kueri membantu mengidentifikasi indeks, yang dapat dihilangkan berdasarkan pola penggunaan kueri pada database. Untuk panduan langkah demi langkah, lihat Penyimpanan Kueri.

Setelah mengaktifkan Penyimpanan Kueri di server, Anda bisa menggunakan kueri berikut untuk mengidentifikasi indeks yang bisa dihilangkan dengan menyambungkan ke database azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Penggunaan indeks

Anda juga bisa menggunakan kueri berikut untuk mengidentifikasi indeks yang tidak digunakan:

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;

Kolom number_of_scans, tuples_read, dan tuples_fetched akan menunjukkan indeks usage.number_of_scans nilai kolom nol poin sebagai indeks yang tidak digunakan.

Penyetelan parameter server

Catatan

Ikuti rekomendasi dalam parameter berikut hanya jika ada cukup ruang memori dan disk.

  • maintenance_work_mem: Parameter ini dapat diatur ke maksimum 2 GB pada instans server fleksibel Azure Database for PostgreSQL. maintenance_work_mem membantu mempercepat pembuatan indeks dan penambahan kunci asing.

  • checkpoint_timeout: Pada instans server fleksibel Azure Database for PostgreSQL, checkpoint_timeout nilai dapat ditingkatkan menjadi 10 atau 15 menit dari pengaturan default 5 menit. Meningkatkan checkpoint_timeout ke nilai yang lebih signifikan, seperti 15 menit, dapat mengurangi beban I/O, tetapi kelemahannya adalah dibutuhkan waktu lebih lama untuk pulih jika ada crash. Kami merekomendasikan pertimbangan yang cermat sebelum Anda membuat perubahan.

  • checkpoint_completion_target: Kami merekomendasikan nilai 0,9.

  • max_wal_size: Nilai ini tergantung pada SKU, penyimpanan, dan beban kerja. Contoh berikut menunjukkan salah satu cara untuk sampai pada nilai yang benar untuk max_wal_size.

Selama jam kerja sibuk, tiba di nilai dengan melakukan hal berikut:

sebuah. Ambil nomor urutan log WAL (LSN) saat ini dengan menjalankan kueri berikut:

SELECT pg_current_wal_lsn ();

b. Tunggu checkpoint_timeout jumlah detik. Ambil WAL LSN saat ini dengan menjalankan kueri berikut:

SELECT pg_current_wal_lsn ();

c. Gunakan dua hasil untuk memeriksa perbedaan dalam GB:

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: Ini dapat diaktifkan. Mengaktifkan parameter ini dapat dikenakan biaya CPU tambahan untuk pemadatan selama pengelogan WAL dan dekompresi selama pemutaran ulang WAL.

Konten terkait

  • Last updated on