ในการศึกษานี้ ฉันต้องการดูว่าการปรับปรุงประสิทธิภาพสามารถทำได้โดยใช้แหล่งข้อมูล ClickHouse แทนที่จะเป็น PostgreSQL ฉันรู้ถึงประโยชน์ด้านประสิทธิภาพการทำงานที่ฉันได้รับจากการใช้ ClickHouse สิทธิประโยชน์เหล่านี้จะดำเนินต่อไปหรือไม่หากฉันเข้าถึง ClickHouse จาก PostgreSQL โดยใช้ Foreign Data Wrapper (FDW)
สภาพแวดล้อมฐานข้อมูลที่ศึกษาคือฐานข้อมูล PostgreSQL v11, clickhousedb_fdw และ ClickHouse ท้ายที่สุดแล้ว จาก PostgreSQL v11 เราจะเรียกใช้คำสั่ง SQL ต่างๆ ที่ส่งผ่าน clickhousedb_fdw ไปยังฐานข้อมูล ClickHouse จากนั้นเราจะดูว่าประสิทธิภาพของ FDW เป็นอย่างไรเมื่อเปรียบเทียบกับข้อความค้นหาเดียวกันที่ทำงานใน PostgreSQL ดั้งเดิมและ ClickHouse ดั้งเดิม
ฐานข้อมูลคลิกเฮาส์
ClickHouse เป็นระบบจัดการฐานข้อมูลแบบเรียงเป็นแนวแบบโอเพ่นซอร์สที่สามารถบรรลุประสิทธิภาพได้เร็วกว่าวิธีฐานข้อมูลแบบเดิมถึง 100-1000 เท่า สามารถประมวลผลมากกว่าหนึ่งพันล้านแถวในเวลาไม่ถึงวินาที
คลิกเฮาส์db_fdw
clickhousedb_fdw - wrapper ข้อมูลภายนอกสำหรับฐานข้อมูล ClickHouse หรือ FDW เป็นโครงการโอเพ่นซอร์สจาก Percona .
.
ดังที่คุณจะเห็น สิ่งนี้มี FDW สำหรับ ClickHouse ที่อนุญาตให้ SELECT จาก และ INSERT INTO ซึ่งเป็นฐานข้อมูล ClickHouse จากเซิร์ฟเวอร์ PostgreSQL v11
FDW รองรับฟีเจอร์ขั้นสูง เช่น การรวมและการเข้าร่วม สิ่งนี้ช่วยปรับปรุงประสิทธิภาพได้อย่างมากโดยใช้ทรัพยากรของเซิร์ฟเวอร์ระยะไกลสำหรับการดำเนินการที่ใช้ทรัพยากรมากเหล่านี้
สภาพแวดล้อมมาตรฐาน
- เซิร์ฟเวอร์ซูเปอร์ไมโคร:
- ซีพียู Intel® Xeon® E5-2683 v3 @ 2.00GHz
- 2 ซ็อกเก็ต / 28 คอร์ / 56 เธรด
- หน่วยความจำ: RAM 256GB
- พื้นที่จัดเก็บข้อมูล: Samsung SM863 1.9TB Enterprise SSD
- ระบบไฟล์: ext4/xfs
- ระบบปฏิบัติการ: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: เวอร์ชัน 11
การทดสอบเกณฑ์มาตรฐาน
แทนที่จะใช้ชุดข้อมูลที่เครื่องสร้างขึ้นสำหรับการทดสอบนี้ เราใช้ข้อมูล "ประสิทธิภาพการทำงานตามเวลาที่รายงานเวลาของผู้ปฏิบัติงาน" ตั้งแต่ปี 1987 ถึง 2018 คุณสามารถเข้าถึงข้อมูลได้ .
ขนาดฐานข้อมูลคือ 85 GB โดยหนึ่งตารางประกอบด้วย 109 คอลัมน์
แบบสอบถามเปรียบเทียบ
นี่คือข้อความค้นหาที่ฉันใช้ในการเปรียบเทียบ ClickHouse, clickhousedb_fdw และ PostgreSQL
Q#
แบบสอบถามประกอบด้วยมวลรวมและจัดกลุ่มตาม
Q1
เลือก DayOfWeek นับ (*) AS c จากเวลาที่กำหนด WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
เลือก DayOfWeek นับ (*) AS c จากเวลาที่กำหนด โดยที่ DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
เลือกจุดเริ่มต้น นับ (*) AS c จากเวลาที่กำหนด WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
เลือกผู้ให้บริการนับ () จากเวลาที่กำหนด WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() รายละเอียด;
Q5
เลือก a.Carrier, c, c2, c1000/c2 เป็น c3 FROM ( SELECT Carrier, count() AS c จาก 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 บน a.Carrier=b.Carrier ORDER โดย c3 DESC;
Q6
เลือก a.Carrier, c, c2, c1000/c2 เป็น c3 FROM ( SELECT Carrier, count() AS c จาก 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 Carrier ) b บน a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
เลือกผู้ให้บริการ avg (DepDelay) * 1000 AS c3 จาก ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8
เลือกปี เฉลี่ย (DepDelay) จาก ontime GROUP BY Year;
Q9
เลือกปี นับ (*) เป็น c1 จากกลุ่มเวลาตรงตามปี
Q10
SELECT avg(cnt) FROM (เลือกปี,เดือน,นับ(*) AS cnt จาก ontime WHERE DepDel15=1 GROUP BY Year,เดือน) a;
Q11
เลือก avg(c1) จาก (เลือก Year,Month,count(*) เป็น c1 จากกลุ่ม ontime ตามปี,เดือน) a;
Q12
เลือก OriginCityName, DestCityName, count(*) AS c จาก ontime GROUP BY OriginCityName, DestCityName เรียงตาม c DESC LIMIT 10;
Q13
เลือก OriginCityName นับ (*) AS c จาก ontime GROUP BY OriginCityName เรียงตาม c DESC จำกัด 10;
แบบสอบถามประกอบด้วยการรวม
Q14
SELECT a.Year, c1/c2 FROM ( เลือกปี, นับ()1000 เป็น c1 จาก ontime WHERE DepDelay>10 GROUP BY Year) INNER JOIN (เลือก Year, นับ (*) เป็น c2 จาก ontime GROUP BY Year ) b บน a.Year=b.Year ORDER BY a.Year;
Q15
เลือก a”ปี”, c1/c2 จาก ( เลือก “ปี”, นับ()1000 เป็น c1 จากฟอนต์ไทม์ โดยที่ “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (เลือก “Year”, นับ(*) เป็น c2 จากฟอนต์ไทม์ GROUP BY “Year” ) b บน a.”Year”=b "ปี";
ตารางที่ 1: ข้อความค้นหาที่ใช้ในการวัดประสิทธิภาพ
การดำเนินการค้นหา
ต่อไปนี้เป็นผลลัพธ์ของการสืบค้นแต่ละรายการเมื่อทำงานในการตั้งค่าฐานข้อมูลที่แตกต่างกัน: PostgreSQL ที่มีและไม่มีดัชนี, ClickHouse แบบเนทีฟ และ clickhousedb_fdw เวลาจะแสดงเป็นมิลลิวินาที
Q#
PostgreSQL
PostgreSQL (จัดทำดัชนี)
คลิกเฮาส์
คลิกเฮาส์db_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 และข้อมูลที่ดึงมาจาก postgres โดยใช้ clickhousedb_fdw จะปรากฏขึ้น จากตาราง คุณจะเห็นว่ามีความแตกต่างอย่างมากระหว่าง 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 เวลาที่ใช้ในการทำให้เสร็จสิ้นคือ 259ms และส่วนคำสั่ง 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: แบบสอบถามโดยไม่ต้องเรียงลำดับตามข้อ
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: ค้นหาด้วย ORDER BY Clause
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: แผนการสืบค้นพร้อม ORDER BY Clause
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 ให้ประโยชน์ด้านประสิทธิภาพของ ClickHouse จาก PostgreSQL แม้ว่าจะมีค่าใช้จ่ายบางส่วนเมื่อใช้ clickhousedb_fdw แต่ก็ไม่สำคัญและเทียบได้กับประสิทธิภาพที่ได้รับจากการรันบนฐานข้อมูล ClickHouse นอกจากนี้ยังเป็นการยืนยันว่า fdw ใน PostgreSQL ให้ผลลัพธ์ที่ยอดเยี่ยม
โทรเลขแชทผ่าน Clickhouse
โทรเลขแชทโดยใช้ PostgreSQL
ที่มา: will.com