🔠 🙆 😖 Around the World dalam 4 Detik di Columnstore (Bagian 1) 🤴 🧑🏿‍🤝‍🧑🏽 👨‍👩‍👧

Dalam artikel ini, saya akan mempertimbangkan untuk meningkatkan kecepatan laporan. Dengan laporan, maksud saya setiap permintaan ke database yang menggunakan fungsi agregat. Juga, saya akan menyentuh masalah yang berkaitan dengan sumber daya yang dihabiskan untuk produksi dan dukungan laporan, baik manusia dan mesin.

Dalam contoh, saya akan menggunakan dataset yang berisi 52.608.000 catatan.

Dengan menggunakan contoh cadangan analitis yang tidak sulit, saya akan menunjukkan bahwa bahkan komputer yang lemah dapat diubah menjadi alat yang baik untuk menganalisis jumlah data yang “layak” tanpa banyak usaha.

Setelah menyiapkan eksperimen yang tidak rumit, kita akan melihat bahwa tabel biasa bukan sumber yang cocok untuk kueri analitik.

Jika pembaca dapat dengan mudah menguraikan singkatan OLTP dan OLAP, mungkin masuk akal untuk langsung menuju bagian Columnstore

Dua pendekatan untuk bekerja dengan data

Di sini saya akan singkat, karena Ada lebih dari cukup informasi tentang topik ini di Internet.

Jadi, pada level tertinggi, hanya ada dua pendekatan untuk bekerja dengan data: OLTP dan OLAP.

OLTP - dapat diterjemahkan sebagai pemrosesan transaksi instan. Bahkan, kita berbicara tentang pemrosesan online transaksi pendek yang bekerja dengan sejumlah kecil data. Misalnya, merekam, memperbarui, atau menghapus pesanan. Dalam sebagian besar kasus, pesanan adalah jumlah data yang sangat kecil, selama pemrosesan yang Anda tidak bisa takut dengan kunci panjang yang diberlakukan oleh RDBMS modern.

OLAP - dapat diterjemahkan sebagai pemrosesan analitis dari sejumlah besar transaksi pada suatu waktu. Setiap laporan menggunakan pendekatan khusus ini, karena dalam sebagian besar kasus, laporan tersebut menghasilkan ringkasan, angka agregat untuk bagian-bagian tertentu.

Setiap pendekatan memiliki teknologinya sendiri. Sebagai contoh, untuk OLTP itu adalah PostgreSQL, dan untuk OLAP itu adalah Microsoft SQL Server Analysis Services. Sementara PostgresSQL menggunakan format terkenal untuk menyimpan data dalam tabel, beberapa format berbeda diciptakan untuk OLAP. Ini adalah tabel multidimensi, ember yang diisi dengan pasangan nilai kunci dan toko kolom favorit saya. Tentang yang terakhir lebih terinci di bawah ini.

Mengapa dua pendekatan dibutuhkan?

Tercatat bahwa setiap gudang data cepat atau lambat menghadapi dua jenis pemuatan: sering membaca (menulis dan memperbarui, tentu saja) dari jumlah data yang sangat kecil dan pembacaan yang jarang, tetapi data dalam jumlah yang sangat besar. Sebenarnya, ini adalah kegiatan, misalnya, dari box office dan kepala. Meja kas, bekerja sepanjang hari, mengisi penyimpanan dengan potongan-potongan kecil data, sementara pada akhirnya volume akumulasi, jika bisnis berjalan dengan baik, mencapai ukuran yang mengesankan. Pada gilirannya, manajer pada akhir hari ingin tahu berapa banyak uang yang diperoleh box office per hari.

Jadi, dalam OLTP kami memiliki tabel dan indeks. Kedua alat ini sangat bagus untuk merekam aktivitas box office dengan semua detailnya. Indeks menyediakan pencarian cepat untuk pesanan yang direkam sebelumnya, sehingga mengubah pesanan itu mudah. Tetapi untuk memenuhi kebutuhan pemimpin, kita perlu mempertimbangkan seluruh jumlah akumulasi data per hari. Selain itu, sebagai aturan, manajer tidak membutuhkan semua detail dari semua pesanan. Yang benar-benar perlu dia ketahui adalah berapa banyak uang yang dihasilkan box office pada umumnya. Tidak masalah di mana kantor tiket berada, ketika ada istirahat makan siang, siapa yang bekerja untuk itu, dll. OLAP ada saat itu, sehingga dalam periode waktu yang singkat sistem dapat menjawab pertanyaan - berapa banyak yang telah diterima perusahaan secara keseluruhan tanpa membaca urutan masing-masing pesanan dan semua rinciannya. Bisakah OLAP menggunakan tabel dan indeks yang sama dengan OLTP? Jawabannya adalah tidak, setidaknya tidak seharusnya. Pertama, karena OLAP tidak membutuhkan semua detail yang dicatat dalam tabel. Masalah ini diselesaikan dengan menyimpan data dalam format lain selain tabel dua dimensi. Kedua, informasi yang dianalisis sering tersebar di berbagai tabel, yang melibatkan banyak asosiasi mereka, termasuk asosiasi dari tipe self-join. Untuk mengatasi masalah ini, sebagai aturan, mereka mengembangkan skema basis data khusus. Skema ini dioptimalkan untuk beban OLAP, serta skema normalisasi normal untuk beban OLTP.

Apa yang terjadi ketika OLAP menggunakan skema OLTP

Bahkan, saya memperkenalkan bagian ini sehingga artikel ini dengan jelas memenuhi persyaratan saya sendiri untuk format materi seperti itu, yaitu masalah, solusi, kesimpulan.

Kami mencantumkan sejumlah kelemahan menggunakan skema OLTP untuk analisis data.

Terlalu banyak indeks.

Seringkali, Anda harus membuat indeks khusus untuk mendukung laporan. Indeks ini menerapkan skema penyimpanan data OLAP. Mereka tidak digunakan oleh bagian OLTP dari aplikasi, sambil mengerahkan beban di atasnya, membutuhkan dukungan konstan dan mengambil ruang disk.
Jumlah data yang dibaca melebihi yang dibutuhkan.
Kurangnya skema data yang jelas.

Faktanya adalah bahwa seringkali informasi yang disampaikan oleh laporan dalam satu bentuk tersebar dalam tabel yang berbeda. Informasi tersebut memerlukan transformasi konstan dengan cepat. Contoh paling sederhana adalah jumlah pendapatan, yang terdiri dari uang tunai dan uang non tunai. Contoh mencolok lainnya adalah hierarki data. Karena pengembangan aplikasi bersifat progresif dan tidak selalu diketahui apa yang akan dibutuhkan di masa depan, hierarki yang sama dalam makna dapat disimpan dalam tabel yang berbeda. Dan sementara akuisisi sambil terbang secara aktif digunakan dalam OLAP, ini adalah hal yang sedikit berbeda.
Kompleksitas permintaan yang berlebihan.

Karena Skema OLTP berbeda dari OLAP. Lapisan perangkat lunak yang sangat terkait diperlukan yang membawa skema data OLTP ke bentuk yang tepat.
Kompleksitas dukungan, debugging, dan pengembangan.

Secara umum, kita dapat mengatakan bahwa semakin kompleks basis kode, semakin sulit untuk mempertahankannya dalam keadaan sehat. Ini adalah aksioma.
Kompleksitas cakupan tes.

Banyak salinan yang rusak karena diskusi tentang cara mendapatkan database yang penuh dengan semua skrip tes, tetapi lebih baik untuk mengatakan bahwa dengan memiliki skema data yang lebih sederhana, tugas meliput dengan tes disederhanakan berkali-kali.
Debugging kinerja tanpa akhir.

Ada kemungkinan besar bahwa pengguna akan memesan laporan yang "berat" untuk server database. Probabilitas ini meningkat seiring waktu. Perlu dicatat bahwa OLAP juga rentan terhadap masalah ini, tetapi tidak seperti OLTP, sumber daya OLAP dalam hal ini jauh lebih tinggi.

Kolom toko

Artikel ini akan fokus pada format penyimpanan toko kolom, tetapi tanpa detail tingkat rendah. Format lain yang disebutkan di atas juga patut mendapat perhatian, tetapi ini adalah topik untuk artikel lain.

Sebenarnya, format columnstore sudah dikenal selama 30 tahun, tetapi itu belum diterapkan dalam RDBMS sampai saat ini. Inti dari kolomstore adalah bahwa data disimpan bukan dalam baris, tetapi dalam kolom. Yaitu pada satu halaman (semua diketahui 8 Kb) server merekam data hanya dari satu bidang. Demikian juga dengan masing-masing bidang pada tabel. Ini diperlukan agar Anda tidak harus membaca informasi tambahan. Mari kita bayangkan tabel dengan 10 bidang dan kueri yang hanya memiliki satu bidang yang ditentukan dalam pernyataan SELECT. Jika tabel biasa disimpan dalam format berbasis baris, server akan dipaksa untuk membaca semua 10 bidang, tetapi pada saat yang sama mengembalikan hanya satu. Ternyata server membaca informasi 9 kali lebih banyak dari yang diperlukan. Columnstore sepenuhnya memecahkan masalah ini, karena format penyimpanan memungkinkan Anda membaca hanya satu bidang yang dipesan. Semua ini terjadi karena unit penyimpanan dalam RDBMS adalah halaman. Yaitu server selalu menulis dan membaca setidaknya satu halaman. Satu-satunya pertanyaan adalah berapa banyak bidang yang ada di sana.

Bagaimana Columnstore Dapat Sangat Membantu

Untuk menjawab ini harus memiliki angka yang tepat. Ayo kita ambil. Tetapi angka apa yang dapat memberikan gambaran yang akurat?

Jumlah ruang disk.
Performa permintaan.
Toleransi kesalahan.
Kemudahan implementasi.
Keterampilan baru apa yang harus dimiliki pengembang untuk bekerja dengan struktur baru.

Ruang disk

Mari kita buat tabel sederhana, isi dengan data dan periksa berapa banyak ruang yang dibutuhkan.

create foreign table cstore_table ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz');

Seperti yang Anda perhatikan, saya membuat tabel eksternal. Faktanya adalah PostgreSQL tidak memiliki dukungan kolom toko bawaan. Tetapi PostgreSQL memiliki sistem ekstensi yang kuat. Salah satunya memungkinkan untuk membuat tabel toko kolom. Tautan di akhir artikel.

pglz - memberi tahu ekstensi bahwa data harus dikompres menggunakan algoritma bawaan di PostgreSQL;
trd - waktu transaksi;
op, itu, bagian atau pengukuran analitik;
m1, m2, m3, m4, m5 - indikator atau ukuran numerik;

Mari kita masukkan jumlah data yang "layak" dan lihat berapa banyak ruang yang dibutuhkan pada disk. Pada saat yang sama, kami memeriksa kinerja sisipan. Karena Saya menaruh eksperimen saya di laptop rumah, saya sedikit organik dalam jumlah data. Selain itu, yang bahkan lebih baik, saya akan menggunakan HDD yang menjalankan OS guest Fedora 30. Host OS - Windows 10 Home Edition. Prosesor Intel Core 7. Guest OS menerima 4 GB RAM. Versi PostgreSQL - PostgreSQL 10.10 pada x86_64-pc-linux-gnu, dikompilasi oleh gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64-bit. Saya akan bereksperimen dengan kumpulan data dengan jumlah catatan 52 608 000.

 explain (analyze) insert into cstore_table select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d;