Dalam kajian ini, saya ingin melihat peningkatan prestasi yang boleh dicapai dengan menggunakan sumber data ClickHouse dan bukannya PostgreSQL. Saya tahu faedah produktiviti yang saya dapat daripada menggunakan ClickHouse. Adakah faedah ini akan berterusan jika saya mengakses ClickHouse daripada PostgreSQL menggunakan Foreign Data Wrapper (FDW)?
Persekitaran pangkalan data yang dikaji ialah pangkalan data PostgreSQL v11, clickhousedb_fdw dan ClickHouse. Akhirnya, daripada PostgreSQL v11 kami akan menjalankan pelbagai pertanyaan SQL yang dihalakan melalui clickhousedb_fdw kami ke pangkalan data ClickHouse. Kami kemudian akan melihat bagaimana prestasi FDW dibandingkan dengan pertanyaan yang sama yang dijalankan dalam PostgreSQL asli dan ClickHouse asli.
Pangkalan Data Clickhouse
ClickHouse ialah sistem pengurusan pangkalan data kolumnar sumber terbuka yang boleh mencapai prestasi 100-1000 kali lebih pantas daripada pendekatan pangkalan data tradisional, mampu memproses lebih satu bilion baris dalam masa kurang dari satu saat.
Clickhousedb_fdw
clickhousedb_fdw - Pembungkus data luaran untuk pangkalan data ClickHouse, atau FDW, ialah projek sumber terbuka daripada Percona. .
.
Seperti yang anda akan lihat, ini menyediakan FDW untuk ClickHouse yang membenarkan SELECT daripada, dan INSERT INTO, pangkalan data ClickHouse daripada pelayan PostgreSQL v11.
FDW menyokong ciri lanjutan seperti agregat dan sertai. Ini meningkatkan prestasi dengan ketara dengan menggunakan sumber pelayan jauh untuk operasi intensif sumber ini.
Persekitaran penanda aras
- Pelayan supermikro:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 soket / 28 teras / 56 benang
- Memori: 256GB RAM
- Storan: Samsung SM863 1.9TB Enterprise SSD
- Sistem fail: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generik #45~16.04.1-Ubuntu
- PostgreSQL: versi 11
Ujian penanda aras
Daripada menggunakan beberapa set data yang dijana mesin untuk ujian ini, kami menggunakan data "Produktiviti mengikut Masa Dilaporkan Masa Operator" dari 1987 hingga 2018. Anda boleh mengakses data .
Saiz pangkalan data ialah 85 GB, menyediakan satu jadual 109 lajur.
Pertanyaan Penanda Aras
Berikut ialah pertanyaan yang saya gunakan untuk membandingkan ClickHouse, clickhousedb_fdw dan PostgreSQL.
Q#
Pertanyaan Mengandungi Agregat dan Kumpulan Mengikut
Q1
PILIH DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
PILIH DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
PILIH Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
PILIH Pembawa, kira() DARI masa tepat DIMANA DepDelay>10 DAN Tahun = 2007 KUMPULAN MENGIKUT Pembawa PESANAN MENGIKUT kiraan() DESC;
Q5
PILIH a. Pembawa, c, c2, c1000/c2 sebagai c3 DARI ( PILIH Pembawa, kira() 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 on a.Carrier=b.Pesanan Pembawa OLEH c3 DESC;
Q6
PILIH a. Pembawa, c, c2, c1000/c2 sebagai c3 DARI ( PILIH Pembawa, kira() 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 Pembawa ) b on a.Carrier=b.Pesanan Pembawa OLEH c3 DESC;
Q7
PILIH Pembawa, purata(DepDelay) * 1000 AS c3 DARI masa di mana Tahun >= 2000 DAN Tahun <= 2008 KUMPULAN MENGIKUT Pembawa;
Q8
PILIH Tahun, purata(DepDelay) DARI KUMPULAN mengikut Tahun mengikut masa;
Q9
pilih Tahun, kira(*) sebagai c1 daripada kumpulan tepat masa mengikut Tahun;
Q10
PILIH purata(cnt) DARI (PILIH Tahun,Bulan,kira(*) SEBAGAI cnt DARI pada masa DI MANA DepDel15=1 KUMPULAN MENGIKUT Tahun,Bulan) a;
Q11
pilih purata(c1) daripada (pilih Tahun,Bulan,kiraan(*) sebagai c1 daripada kumpulan tepat masa mengikut Tahun,Bulan) a;
Q12
PILIH OriginCityName, DestCityName, kira(*) SEBAGAI c DARI KUMPULAN ontime OLEH OriginCityName, DestCityName ORDER OLEH c DESC LIMIT 10;
Q13
PILIH OriginCityName, kira(*) AS c DARI KUMPULAN ontime OLEH OriginCityName ORDER OLEH c DESC LIMIT 10;
Pertanyaan Mengandungi Gabungan
Q14
PILIH a.Tahun, c1/c2 DARI ( pilih Tahun, kira()1000 sebagai c1 dari masa tepat WHERE DepDelay>10 KUMPULAN MENGIKUT Tahun) a INNER JOIN (pilih Tahun, kira(*) sebagai c2 dari ontime KUMPULAN MENGIKUT Tahun ) b pada a.Year=b.Year ORDER BY a.Year;
Q15
PILIH a.”Tahun”, c1/c2 DARI ( pilih “Tahun”, kira()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 on a.”Year”=b. "Tahun";
Jadual-1: Pertanyaan yang digunakan dalam penanda aras
Pelaksanaan pertanyaan
Berikut ialah keputusan setiap pertanyaan apabila dijalankan dalam tetapan pangkalan data yang berbeza: PostgreSQL dengan dan tanpa indeks, ClickHouse asli dan clickhousedb_fdw. Masa ditunjukkan dalam milisaat.
Q#
PostgreSQL
PostgreSQL (Berindeks)
Klik Rumah
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
Jadual-1: Masa yang diambil untuk melaksanakan pertanyaan yang digunakan dalam penanda aras
Lihat keputusan
Graf menunjukkan masa pelaksanaan pertanyaan dalam milisaat, paksi X menunjukkan nombor pertanyaan daripada jadual di atas, dan paksi Y menunjukkan masa pelaksanaan dalam milisaat. Keputusan ClickHouse dan data yang diambil daripada postgres menggunakan clickhousedb_fdw ditunjukkan. Daripada jadual, anda boleh melihat bahawa terdapat perbezaan besar antara PostgreSQL dan ClickHouse, tetapi perbezaan minimum antara ClickHouse dan clickhousedb_fdw.
Graf ini menunjukkan perbezaan antara ClickhouseDB dan clickhousedb_fdw. Dalam kebanyakan pertanyaan, overhed FDW tidak begitu tinggi dan tidak begitu ketara kecuali untuk Q12. Pertanyaan ini termasuk gabungan dan klausa ORDER BY. Kerana klausa ORDER BY GROUP/BY, ORDER BY tidak jatuh ke ClickHouse.
Dalam Jadual 2 kita melihat lonjakan masa dalam pertanyaan Q12 dan Q13. Sekali lagi, ini disebabkan oleh klausa ORDER BY. Untuk mengesahkan ini, saya menjalankan pertanyaan Q-14 dan Q-15 dengan dan tanpa klausa ORDER BY. Tanpa klausa ORDER BY masa penyiapan ialah 259ms dan dengan klausa ORDER BY ialah 1364212. Untuk menyahpepijat pertanyaan ini saya menerangkan kedua-dua pertanyaan dan berikut adalah hasil penjelasan.
S15: Tanpa PERINTAH MENGIKUT Klausa
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";S15: Pertanyaan Tanpa PESANAN MENGIKUT Klausa
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)S14: Pertanyaan Dengan ORDER BY Klausa
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";S14: Pelan Pertanyaan dengan ORDER BY Klausa
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)Output
Keputusan percubaan ini menunjukkan bahawa ClickHouse menawarkan prestasi yang sangat baik, dan clickhousedb_fdw menawarkan faedah prestasi ClickHouse daripada PostgreSQL. Walaupun terdapat beberapa overhed apabila menggunakan clickhousedb_fdw, ia boleh diabaikan dan setanding dengan prestasi yang dicapai dengan berjalan secara asli pada pangkalan data ClickHouse. Ini juga mengesahkan bahawa fdw dalam PostgreSQL memberikan hasil yang sangat baik.
Sembang telegram melalui Clickhouse
Sembang telegram menggunakan PostgreSQL
Sumber: www.habr.com