Dalam studi ini, saya ingin melihat peningkatan kinerja apa yang dapat dicapai dengan menggunakan sumber data ClickHouse daripada PostgreSQL. Saya mengetahui manfaat produktivitas yang saya peroleh dari penggunaan ClickHouse. Apakah manfaat ini akan terus berlanjut jika saya mengakses ClickHouse dari PostgreSQL menggunakan Foreign Data Wrapper (FDW)?
Lingkungan database yang diteliti adalah database PostgreSQL v11, clickhousedb_fdw dan ClickHouse. Pada akhirnya, dari PostgreSQL v11 kita akan menjalankan berbagai query SQL yang dirutekan melalui clickhousedb_fdw ke database ClickHouse. Kita kemudian akan melihat bagaimana kinerja PLRT Asing dibandingkan dengan kueri yang sama yang berjalan di PostgreSQL asli dan ClickHouse asli.
Basis Data Clickhouse
ClickHouse adalah sistem manajemen basis data kolom sumber terbuka yang dapat mencapai kinerja 100-1000 kali lebih cepat dibandingkan pendekatan basis data tradisional, mampu memproses lebih dari satu miliar baris dalam waktu kurang dari satu detik.
Clickhousedb_fdw
clickhousedb_fdw - Pembungkus data eksternal untuk database ClickHouse, atau FDW, adalah proyek sumber terbuka dari Percona. .
.
Seperti yang akan Anda lihat, ini menyediakan FDW untuk ClickHouse yang memungkinkan SELECT dari, dan INSERT INTO, database ClickHouse dari server PostgreSQL v11.
FDW mendukung fitur-fitur canggih seperti agregat dan gabungan. Hal ini secara signifikan meningkatkan kinerja dengan menggunakan sumber daya server jarak jauh untuk operasi intensif sumber daya ini.
Lingkungan tolok ukur
- Server super mikro:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 soket / 28 core / 56 thread
- Memory: 256GB RAM
- Penyimpanan: SSD Perusahaan Samsung SM863 1.9TB
- Sistem file: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generik #45~16.04.1-Ubuntu
- PostgreSQL: versi 11
Tes tolok ukur
Daripada menggunakan beberapa kumpulan data yang dihasilkan mesin untuk pengujian ini, kami menggunakan data "Produktivitas berdasarkan Waktu yang Dilaporkan Operator" dari tahun 1987 hingga 2018. Anda dapat mengakses datanya .
Ukuran database adalah 85 GB, menyediakan satu tabel dengan 109 kolom.
Kueri Tolok Ukur
Berikut adalah pertanyaan yang saya gunakan untuk membandingkan ClickHouse, clickhousedb_fdw dan PostgreSQL.
Q#
Kueri Berisi Agregat dan Kelompokkan Berdasarkan
Q1
PILIH DayOfWeek, hitung(*) AS c DARI tepat waktu DIMANA Tahun >= 2000 DAN Tahun <= 2008 KELOMPOK BERDASARKAN DayOfWeek ORDER OLEH c DESC;
Q2
PILIH DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 KELOMPOK BERDASARkan DayOfWeek ORDER BY c DESC;
Q3
PILIH Asal, hitung(*) AS c DARI tepat waktu DIMANA DepDelay>10 DAN Tahun >= 2000 DAN Tahun <= 2008 KELOMPOK BERDASARKAN Asal ORDER OLEH c DESC LIMIT 10;
Q4
PILIH Operator, hitung() DARI tepat waktu DIMANA DepDelay>10 DAN Tahun = 2007 KELOMPOK OLEH Operator ORDER BERDASARKAN hitungan() DESK;
Q5
PILIH a.Pembawa, c, c2, c1000/c2 sebagai c3 DARI ( PILIH Pembawa, hitung() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b pada a.Carrier=b.Carrier ORDER OLEH c3 DESC;
Q6
PILIH a.Pembawa, c, c2, c1000/c2 sebagai c3 DARI ( PILIH Pembawa, hitung() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Operator ) b pada a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
PILIH Operator, rata-rata(DepDelay) * 1000 AS c3 FROM ontime WHERE Tahun >= 2000 DAN Tahun <= 2008 KELOMPOK BERDASARKAN Operator;
Q8
PILIH Tahun, rata-rata(DepDelay) DARI KELOMPOK tepat waktu BERDASARKAN Tahun;
Q9
pilih Tahun, hitung(*) sebagai c1 dari grup tepat waktu berdasarkan Tahun;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 KELOMPOK BERDASARKAN Tahun,Bulan) a;
Q11
pilih rata-rata(c1) dari (pilih Tahun,Bulan,hitung(*) sebagai c1 dari grup tepat waktu berdasarkan Tahun,Bulan) a;
Q12
PILIH OriginCityName, DestCityName, count(*) AS c DARI ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
PILIH OriginCityName, count(*) AS c DARI ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Kueri Berisi Gabungan
Q14
PILIH a.Tahun, c1/c2 DARI ( pilih Tahun, hitung()1000 sebagai c1 dari ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (pilih Tahun, hitung(*) sebagai c2 dari ontime GROUP BY Year ) b pada a.Year=b.Year ORDER BY a.Year;
Q15
PILIH a.”Tahun”, c1/c2 DARI ( pilih “Tahun”, hitung()1000 sebagai c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (pilih “Year”, count(*) as c2 FROM fontime GROUP BY “Year” ) b pada a.”Year”=b. "Tahun";
Tabel-1: Kueri yang digunakan dalam benchmark
Eksekusi kueri
Berikut adalah hasil dari setiap query ketika dijalankan dalam pengaturan database yang berbeda: PostgreSQL dengan dan tanpa indeks, ClickHouse asli dan clickhousedb_fdw. Waktu ditampilkan dalam milidetik.
Q#
PostgreSQL
PostgreSQL (Terindeks)
KlikRumah
clickhousedb_fdw
Q1
27920
19634
23
57
Q2
35124
17301
50
80
Q3
34046
15618
67
115
Q4
31632
7667
25
37
Q5
47220
8976
27
60
Q6
58233
24368
55
153
Q7
30566
13256
52
91
Q8
38309
60511
112
179
Q9
20674
37979
31
81
Q10
34990
20102
56
148
Q11
30489
51658
37
155
Q12
39357
33742
186
1333
Q13
29912
30709
101
384
Q14
54126
39913
124
1364212
Q15
97258
30211
245
259
Tabel-1: Waktu yang dibutuhkan untuk mengeksekusi kueri yang digunakan dalam benchmark
Lihat hasil
Grafik memperlihatkan waktu eksekusi kueri dalam milidetik, sumbu X memperlihatkan nomor kueri dari tabel di atas, dan sumbu Y memperlihatkan waktu eksekusi dalam milidetik. Hasil ClickHouse dan data yang diambil dari postgres menggunakan clickhousedb_fdw ditampilkan. Dari tabel Anda dapat melihat bahwa ada perbedaan besar antara PostgreSQL dan ClickHouse, namun perbedaan minimal antara ClickHouse dan clickhousedb_fdw.
Grafik ini menunjukkan perbedaan antara ClickhouseDB dan clickhousedb_fdw. Di sebagian besar pertanyaan, biaya overhead PRT asing tidak terlalu tinggi dan hampir tidak signifikan kecuali untuk Q12. Kueri ini mencakup gabungan dan klausa ORDER BY. Karena klausa ORDER BY GROUP/BY, ORDER BY tidak turun ke ClickHouse.
Pada Tabel 2 kita melihat lompatan waktu pada kueri Q12 dan Q13. Sekali lagi, hal ini disebabkan oleh klausa ORDER BY. Untuk mengonfirmasi hal ini, saya menjalankan kueri Q-14 dan Q-15 dengan dan tanpa klausa ORDER BY. Tanpa klausa ORDER BY waktu penyelesaiannya adalah 259ms dan dengan klausa ORDER BY adalah 1364212. Untuk men-debug query ini saya menjelaskan kedua query tersebut dan berikut hasil penjelasannya.
Q15: Tanpa Klausul ORDER BY
bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2
FROM (SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
INNER JOIN(SELECT "Year", count(*) AS c2 FROM fontime GROUP BY "Year") b ON a."Year"=b."Year";Q15: Kueri Tanpa Klausa ORDER BY
QUERY PLAN
Hash Join (cost=2250.00..128516.06 rows=50000000 width=12)
Output: fontime."Year", (((count(*) * 1000)) / b.c2)
Inner Unique: true Hash Cond: (fontime."Year" = b."Year")
-> Foreign Scan (cost=1.00..-1.00 rows=100000 width=12)
Output: fontime."Year", ((count(*) * 1000))
Relations: Aggregate on (fontime)
Remote SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"
-> Hash (cost=999.00..999.00 rows=100000 width=12)
Output: b.c2, b."Year"
-> Subquery Scan on b (cost=1.00..999.00 rows=100000 width=12)
Output: b.c2, b."Year"
-> Foreign Scan (cost=1.00..-1.00 rows=100000 width=12)
Output: fontime_1."Year", (count(*))
Relations: Aggregate on (fontime)
Remote SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)Q14: Kueri Dengan Klausul ORDER BY
bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2 FROM(SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
INNER JOIN(SELECT "Year", count(*) as c2 FROM fontime GROUP BY "Year") b ON a."Year"= b."Year"
ORDER BY a."Year";Q14: Rencana Kueri dengan Klausul ORDER BY
QUERY PLAN
Merge Join (cost=2.00..628498.02 rows=50000000 width=12)
Output: fontime."Year", (((count(*) * 1000)) / (count(*)))
Inner Unique: true Merge Cond: (fontime."Year" = fontime_1."Year")
-> GroupAggregate (cost=1.00..499.01 rows=1 width=12)
Output: fontime."Year", (count(*) * 1000)
Group Key: fontime."Year"
-> Foreign Scan on public.fontime (cost=1.00..-1.00 rows=100000 width=4)
Remote SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10))
ORDER BY "Year" ASC
-> GroupAggregate (cost=1.00..499.01 rows=1 width=12)
Output: fontime_1."Year", count(*) Group Key: fontime_1."Year"
-> Foreign Scan on public.fontime fontime_1 (cost=1.00..-1.00 rows=100000 width=4)
Remote SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)Keluaran
Hasil percobaan ini menunjukkan bahwa ClickHouse menawarkan kinerja yang sangat baik, dan clickhousedb_fdw menawarkan keunggulan kinerja ClickHouse dari PostgreSQL. Meskipun ada beberapa overhead saat menggunakan clickhousedb_fdw, hal ini dapat diabaikan dan sebanding dengan kinerja yang dicapai dengan menjalankan secara asli pada database ClickHouse. Ini juga menegaskan bahwa fdw di PostgreSQL memberikan hasil yang sangat baik.
Obrolan Telegram melalui Clickhouse
Obrolan Telegram menggunakan PostgreSQL
Sumber: www.habr.com