Энэ судалгаанд би PostgreSQL гэхээсээ илүү ClickHouse мэдээллийн эх сурвалжийг ашигласнаар гүйцэтгэлийн ямар сайжруулалт хийж болохыг харахыг хүссэн. Би ClickHouse-ийг ашигласнаар бүтээмжийн ашиг тусаа мэддэг. Хэрэв би Гадаад Мэдээллийн Боодол (FDW) ашиглан PostgreSQL-ээс ClickHouse-д хандвал эдгээр давуу талууд үргэлжлэх үү?
Судлагдсан мэдээллийн сангийн орчин нь PostgreSQL v11, clickhousedb_fdw, ClickHouse мэдээллийн сан юм. Эцэст нь PostgreSQL v11-ээс бид clickhousedb_fdw-ээр дамжуулан ClickHouse мэдээллийн сан руу чиглүүлсэн янз бүрийн SQL асуулга ажиллуулах болно. Дараа нь бид FDW-ийн гүйцэтгэлийг уугуул PostgreSQL болон үндсэн ClickHouse дээр ажилладаг ижил асуулгатай харьцуулахыг харах болно.
Clickhouse мэдээллийн сан
ClickHouse бол нэг секунд хүрэхгүй хугацаанд тэрбум гаруй мөрийг боловсруулах чадвартай, уламжлалт мэдээллийн сангаас 100-1000 дахин хурдан гүйцэтгэлд хүрэх боломжтой, нээлттэй эхийн багана мэдээллийн удирдлагын систем юм.
Clickhousedb_fdw
clickhousedb_fdw - ClickHouse өгөгдлийн сангийн гадаад өгөгдлийн багц буюу FDW нь Percona-ийн нээлттэй эхийн төсөл юм. .
.
Таны харж байгаагаар, энэ нь PostgreSQL v11 серверээс ClickHouse мэдээллийн сангаас SELECT болон INSERT INTO хийх боломжийг олгодог ClickHouse-д зориулсан FDW-г өгдөг.
FDW нь нэгтгэх, нэгдэх зэрэг дэвшилтэт функцуудыг дэмждэг. Энэ нь эдгээр нөөц их шаарддаг үйлдлүүдэд алсын серверийн нөөцийг ашигласнаар гүйцэтгэлийг ихээхэн сайжруулдаг.
Жишиг орчин
- Supermicro сервер:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 залгуур / 28 цөм / 56 утас
- Санах ой: 256ГБ хэмжээтэй санах ой
- Хадгалах: Samsung SM863 1.9TB Enterprise SSD
- Файлын систем: ext4/xfs
- Үйлдлийн систем: Linux smblade01 4.15.0-42-ерөнхий #45~16.04.1-Ubuntu
- PostgreSQL: хувилбар 11
Жишиг туршилтууд
Энэ туршилтанд машинаас үүсгэсэн өгөгдлийн багцыг ашиглахын оронд бид 1987-2018 он хүртэлх "Операторын тайлагнасан цагийн бүтээмж"-ийн өгөгдлийг ашигласан. Та өгөгдөлд хандах боломжтой .
Өгөгдлийн сангийн хэмжээ 85 ГБ бөгөөд 109 багана бүхий нэг хүснэгтийг хангана.
Жишиг асуулга
ClickHouse, clickhousedb_fdw болон PostgreSQL-ийг харьцуулахдаа ашигласан асуулгууд энд байна.
Q#
Асуулга нь нэгтгэл болон бүлэгт агуулна
Q1
Долоо хоногийн өдрийг СОНГОХ, (*) AS c-ээр тоолох. ХААНА Он >= 2000 БОЛОН 2008 он <= XNUMX Долоо хоногийн өдөр БҮЛЭГЛЭХ c DESC-ээр ЗАХИАЛАХ;
Q2
DayOfWeek СОНГОХ, (*) AS c AS C FROM on time WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 BY BY BY BY BY DESC DESC;
Q3
SELECT Origin, count(*) AS c FROM on time WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY BY Origin ORDER BY c DESC LIMIT 10;
Q4
Операторыг сонгох, тоолох() ХААНА DepDelay>10 БА Он цаг = 2007 ЗАХИАЛГААР БҮЛЭГЛЭХ ЗАХИАЛГА ТООЛЛОО() DESC;
Q5
СОНГОХ a.Carrier, c, c2, cc1000-аас 2/c3 ( Тээвэрлэгчийг сонгох, тоолох() AS c FROM on time WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM on time WHERE Year=2007 GROUP BY Carrier)b on a.Carrier=b.Carrier ORDER c3 DESC-ээр;
Q6
СОНГОХ a.Carrier, c, c2, cc1000-аас 2/c3 ( Тээвэрлэгчийг сонгох, тоолох() AS c FROM FROM WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( Carrier SELECT, (*) AS c2 AS FROM on time WHERE Year >= 2000 AND Year <= UP 2008 GROY Carrier ) b on a.Carrier=b.Carrier ORDER BY BY c3 DESC;
Q7
Оператор компанийг SELECT, avg(DepDelay) * 1000 AS c3 ХААНА ХААНА Он >= 2000 БА Он <= 2008 Оператороор бүлэг;
Q8
SELECT Year, ag(DepDelay) FROM on time GROUP BY BY;
Q9
Оныг сонгох, оноор нь цагийн бүлгээс c1 гэж тоолох (*);
Q10
SELECT avg(cnt) FROM (СОНГОХ жил,Сар,тоолоо(*) AS cnt FROM цаг хугацаанд нь ХААНА DepDel15=1 ГРУПП БҮЛГИЙГ жил,сар) a;
Q11
дундж(c1)-аас (Жи,Сар,тоолоо(*)-г c1-ээр цагийн бүлгээс Жил,Сараар сонгох) a;
Q12
OriginCityName, DestCityName, count(*) AS c-г цагийн БҮЛГЭЭС OriginCityName, DestCityName ЗАХИАЛГААР С DESC LIMIT 10;
Q13
OriginCityName СОНГОХ, (*) AS c AS C FROM OriginCityName GROUP BY BY OriginCityName ЗАХИАЛАХ c DESC LIMIT 10;
Асуулга нь нэгдмэлүүдийг агуулна
Q14
СОНГОХ a.Year, c1/c2 FROM ( Он, тоо сонгох()1000 гэж c1 цаг хугацаанаас нь ХААНА DepDelay>10 ГРУПП БҮЛЭГ) a INNER JOIN (Жилийг сонгох, тоолох(*)-ыг цаг тухайд нь БҮЛЭГ ) ) b on a.Year=b.Year ORDER BY a.Year;
Q15
СОНГОХ a.”Year”, c1/c2 FROM ( “Жил сонгох”, тоолох()1000 as c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (“Жил”-ийг сонго, (*) гэж c2 гэж тоолно(*) фонтны БҮЛГЭЭС “Year” ) b on a.”Year”=b. "Жил";
Хүснэгт-1: Жишиг судалгаанд ашигласан асуулга
Асуулгын гүйцэтгэл
Өгөгдлийн сангийн янз бүрийн тохиргоонд ажиллуулж байгаа асуулга бүрийн үр дүнг энд харуулав: индекстэй болон индексгүй PostgreSQL, үндсэн ClickHouse болон clickhousedb_fdw. Цагийг миллисекундээр харуулав.
Q#
PostgreSQL
PostgreSQL (Индексжүүлсэн)
clickhouse
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
Хүснэгт-1: Жишиг шинжилгээнд ашигласан асуулгыг гүйцэтгэхэд зарцуулсан хугацаа
Үр дүнг харах
График нь асуулгын гүйцэтгэлийн хугацааг миллисекундээр, X тэнхлэгт дээрх хүснэгтүүдийн асуулгын дугаарыг, Y тэнхлэгт гүйцэтгэх хугацааг миллисекундээр харуулав. ClickHouse-ын үр дүн болон clickhousedb_fdw ашиглан postgres-ээс авсан өгөгдлийг харуулав. Хүснэгтээс PostgreSQL болон ClickHouse хоёрын хооронд асар их ялгаа байгааг харж болно, гэхдээ ClickHouse болон clickhousedb_fdw хооронд хамгийн бага ялгаа байна.
Энэ график нь ClickhouseDB болон clickhousedb_fdw хоёрын ялгааг харуулж байна. Ихэнх асуулгад FDW нэмэлт зардал нь тийм ч өндөр биш бөгөөд 12-р улиралаас бусад тохиолдолд тийм ч чухал биш юм. Энэ асуулгад нэгдэх болон ORDER BY заалт багтана. ORDER BY GROUP/BY заалтын дагуу ORDER BY нь ClickHouse-д буудаггүй.
Хүснэгт 2-оос бид Q12 болон Q13 асуулгад цаг хугацааны өсөлтийг харж байна. Дахин хэлэхэд энэ нь ORDER BY заалтаас үүдэлтэй. Үүнийг батлахын тулд би Q-14, Q-15 асуултуудыг ORDER BY заалттай, заалтгүйгээр ажиллуулсан. ORDER BY заалтгүй бол дуусгах хугацаа 259 ms, ORDER BY заалттай бол 1364212 байна. Энэ асуулгыг дибаг хийхийн тулд би асуулгын аль алиныг нь тайлбарлаж байгаа бөгөөд энд тайлбарын үр дүн байна.
Асуулт 15: 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";Асуулт 15: 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)Асуулт 14: 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";Асуулт 14: 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)дүгнэлт
Эдгээр туршилтуудын үр дүн нь ClickHouse нь үнэхээр сайн гүйцэтгэлийг санал болгодог бөгөөд clickhousedb_fdw нь PostgreSQL-ээс ClickHouse-ийн гүйцэтгэлийн давуу талыг санал болгодог. Хэдийгээр clickhousedb_fdw-г ашиглахад тодорхой хэмжээний зардал гардаг ч энэ нь ClickHouse мэдээллийн сан дээр ажиллахад хүрсэн гүйцэтгэлтэй харьцуулшгүй бага юм. Энэ нь PostgreSQL дахь fdw нь маш сайн үр дүнг өгдөг болохыг баталж байна.
Clickhouse-ээр дамжуулан Telegram чат
PostgreSQL ашиглан Telegram чат
Эх сурвалж: www.habr.com