Dahulu kala, di galaksi Solar yang sangat jauh, bahkan sebelum menjadi bagian dari alam semesta Rostelecom, sebuah produk webProxy yang kecil muncul tidak hanya perlu untuk menyaring lalu lintas jaringan, tetapi juga untuk membangun statistik di atasnya dengan penyimpanan berikutnya. Pada saat itu, basis data kolom tidak sepopuler sekarang. Satu-satunya analog yang cocok adalah basis data HP Vertica berbayar. Bagaimana di Galaxy Surya mereka memecahkan masalah ini dan apa yang akhirnya mereka datangi, kami akan katakan di bawah luka.
Pertama, kami memutuskan untuk membuat database kami sendiri. Sebagai hasilnya, ia ditulis dalam OCaml dengan penyimpanan biner kolom (representasi teks dikompresi melalui lz4) dan bahasa querynya sendiri, cukup fleksibel, pada ekspresi S. Partisi dilakukan per hari.
Contoh permintaan:
Itu bukan pilihan yang paling nyaman dan cepat, tetapi diperluas dan disesuaikan.
Waktu berlalu, begitu pula kebutuhan untuk mempercepat pembangunan statistik dan laporan lalu lintas. Karena itu, kami mulai mempertimbangkan opsi lain:
- postgres murni;
- Postgres + cstore_fdw;
- Clickhouse;
- Elastis
Perbandingan Postgres vs Elastis
Pada tahap pertama, kami membandingkan Elastic dan Postgres + cstore. Postgres dianggap paling dekat, karena sudah digunakan dalam sistem, dan keahlian tersedia untuk bekerja dengannya.
Elastis juga aktif digunakan di perusahaan. Terlepas dari "daya tarik" pencarian teks lengkap dan kecepatannya, Elastic harus ditinggalkan karena terlalu banyak volume yang ditempati oleh data pada disk. Dalam hal kecepatan, Elastic menang pada kueri sederhana sekitar 3 kali, misalnya, pada kueri "TOP 20 situs dalam seminggu". Dan pada situs yang lebih kompleks - hingga 9 kali: "TOP 20 situs untuk lalu lintas per bulan."
Namun, itu lebih baik daripada markasnya sendiri, yang membutuhkan waktu beberapa menit untuk melakukan ini versus 5-6 detik di Elastis dan 15-55 detik di Postgres.
Perbandingan Postgres vs Clickhouse
Sumber data
Dengan https://github.com/wizardjedi/clickhouse-test kami mengambil wadah dengan Postgres dan Clickhouse. Wadah ini dirancang untuk membuat tabel.
Tampilan tabel untuk Postgres:
Kunci primer harus dihapus, karena tabel asing di Postgres tidak mengizinkan ini.
Untuk Clickhouse, membuat tabel seperti itu adalah sebagai berikut:
Untuk membiasakan diri dengan proses menginstal cstore untuk Postgres, buka https://github.com/citusdata/cstore_fdw .
Juga, ketika menginstal cstore, Anda perlu menginstal paket postgresql-server-dev-XY
Saat membandingkan kinerja, ukuran data berikut (dalam megabita) digunakan:
Data sumber hanyalah kueri sql yang mencantumkan semua tuple, yaitu. data mentah.
Selama eksekusi query, terutama yang berat, selain data, dimensi database diukur.
Terungkap bahwa untuk Clickhouse mereka pasti tidak bertambah.
Parameter Sistem Komputer
Pabrikan: Intel
Baris: core i5
Model: 8250U
Frekuensi jam: 1.60GHz per inti
Core: 4
RAM: 16 GB
SSD: 256 GB
Memuat data ke dalam basis data
Untuk volume data yang sedemikian besar di Clickhouse, mereka dimuat dengan sangat cepat: 1 jam 40 menit (ini untuk 600 juta tupel).
Pada awalnya, kami berencana untuk mengunduh semuanya dalam satu file, tetapi kesalahan "bad_alloc" ditampilkan. Rupanya, karena ketidakmampuan Clickhouse untuk mengalokasikan memori. Tidak ada solusi yang ditemukan. Oleh karena itu, 600 juta tuple dibagi menjadi 30 file, masing-masing 20 juta, dalam hal ini, setiap file diunduh sedikit lebih dari 3 menit.
Dengan Postgres, segalanya menjadi lebih rumit, tetapi hanya pada awalnya. Mengunduh file sql mentah yang berisi perintah INSERT INTO <table_name> (atribut) VALUES tuples memakan waktu. Oleh karena itu, semuanya dikonversi ke format csv dan perintah COPY <table_name> FROM WITH CSV dieksekusi.
Perlu dicatat bahwa pertama-tama kita memuat data ke dalam tabel Postgres biasa, dari tempat kita menyalinnya ke tabel asing, yang dikendalikan oleh cstore. Akibatnya, memuat Postgres dari file csv juga memakan waktu kurang dari dua jam.
Perbandingan kinerja
Perbandingan kinerja Postgres dan Clickhouse ditunjukkan pada tabel di bawah ini. Tetapi tanpa membangun indeks dan mengubah parameter basis data. Pada titik tertentu, memori pada disk hampir habis, dan karena itu menjadi perlu untuk menghapus tabel reguler yang tidak terkompresi dari Postgres. Saat ini, hanya tabel yang tersedia di Clickhouse dan Postgres cstore.
Rupanya, cstore berfokus pada atribut pertama yang ditentukan saat membuatnya. Dengan kata lain, dia mengurutkan semua data dengan itu. Ini dapat dengan mudah diperhatikan, karena pertanyaan yang berhubungan dengan EventDate lebih cepat dieksekusi di cstore daripada di Postgres.
Saat menjalankan query, Postgres kadang-kadang memakan waktu hingga 27 GB pada drive eksternal untuk file-file sementara.
Clickhouse membutuhkan banyak RAM.
Dalam file konfigurasi /etc/clickhouse/users.xml, <max_memory_usage> 12000000000 </max_memory_usage> dan <max_bytes_before_external_sort> 1000000000 </max_btes_before_external_sort> ditentukan.
Untuk beberapa pertanyaan, RAM tidak cukup, itulah sebabnya kami harus meningkatkannya. Setelah itu, pemrosesan permintaan berlanjut, tetapi pada permintaan terakhir itu masih terganggu. Ada beberapa parameter lain yang tersedia untuk membatasi konsumsi memori https://clickhouse.yandex/docs/ru/query_language/queries/ .
Kebetulan kami menambahkan sedikit lebih banyak data ke Clickhouse: 695_640_2000 tuple bukan 600_000_000, tetapi itu tidak menghentikannya untuk menang.
Di cstore_fdw, Anda dapat mengonfigurasi berbagai parameter https://github.com/citusdata/cstore_fdw/issues/174 , https://github.com/citusdata/cstore_fdw , yang memengaruhi kinerja.
Partisi
Adapun untuk mempartisi, itu juga di Clickhouse https://github.com/yandex/ClickHouse/blob/master/docs/ru/table_engines/custom_partitioning_key.md , https://clickhouse.yandex/docs/ru/table_engines/custom_partitioning_key / , dan di Postgres (versi 10 dan 11). Contoh partisi di clickhouse dapat ditemukan di https://github.com/yandex/ClickHouse/blob/master/dbms/tests/queries/0_stateless/00502_custom_partitioning_local.sql dan https://github.com/yick/ClickHouse/issues/1513 .
Penggunaan partisi di Postgres dimungkinkan asalkan cstore hanya berfungsi dengan tabel asing, karena Anda perlu membuat server untuk itu, dan Anda tidak dapat menentukan server untuk tabel biasa. Tabel asing tidak dapat dibagi menjadi partisi, itu sendiri dapat bertindak sebagai partisi. Oleh karena itu, hanya ada satu cara yang memungkinkan untuk menggunakan partisi: buat tabel induk biasa, Anda bisa melampirkan tabel asing di dalamnya dalam bentuk partisi, yang sudah bekerja pada cstore_fdw.
Di Clickhouse, mempartisi berfungsi di luar kotak.
Kesimpulan
Akibatnya, kami memutuskan untuk menggunakan Clickhouse, karena cerdas: selalu setidaknya 10 kali lebih cepat daripada analog. Pada server memori, biasanya ada lebih dari 32 Gb, 64, dan 128, jadi pertanyaan di atas meja sekitar 50 Gb akan berjalan dengan baik. Jika tabelnya sangat besar, yaitu mempartisi, atau memperbaiki parameter server clickhouse akan membantu.