Trong nghiên cứu này, tôi muốn xem có thể đạt được những cải tiến hiệu suất nào bằng cách sử dụng nguồn dữ liệu ClickHouse thay vì PostgreSQL. Tôi biết những lợi ích về năng suất mà tôi nhận được khi sử dụng ClickHouse. Những lợi ích này có tiếp tục không nếu tôi truy cập ClickHouse từ PostgreSQL bằng Trình bao bọc dữ liệu nước ngoài (FDW)?
Các môi trường cơ sở dữ liệu được nghiên cứu là cơ sở dữ liệu PostgreSQL v11, clickhousedb_fdw và ClickHouse. Cuối cùng, từ PostgreSQL v11, chúng tôi sẽ chạy nhiều truy vấn SQL khác nhau được định tuyến thông qua clickhousedb_fdw tới cơ sở dữ liệu ClickHouse. Sau đó, chúng ta sẽ so sánh hiệu suất của FDW với các truy vấn tương tự chạy trong PostgreSQL gốc và ClickHouse gốc.
Cơ sở dữ liệu Clickhouse
ClickHouse là một hệ thống quản lý cơ sở dữ liệu dạng cột nguồn mở có thể đạt hiệu suất nhanh hơn 100-1000 lần so với các phương pháp tiếp cận cơ sở dữ liệu truyền thống, có khả năng xử lý hơn một tỷ hàng trong vòng chưa đầy một giây.
Clickhousedb_fdw
clickhousedb_fdw - Trình bao bọc dữ liệu ngoài cho cơ sở dữ liệu ClickHouse hoặc FDW, là một dự án nguồn mở của Percona. .
.
Như bạn sẽ thấy, điều này cung cấp một FDW cho ClickHouse cho phép CHỌN và CHÈN VÀO cơ sở dữ liệu ClickHouse từ máy chủ PostgreSQL v11.
FDW hỗ trợ các tính năng nâng cao như tổng hợp và tham gia. Điều này cải thiện đáng kể hiệu suất bằng cách sử dụng tài nguyên của máy chủ từ xa cho các hoạt động sử dụng nhiều tài nguyên này.
Môi trường chuẩn
- Máy chủ siêu nhỏ:
- CPU Intel® Xeon® E5-2683 v3 @ 2.00GHz
- 2 socket/28 lõi/56 luồng
- Bộ nhớ: 256GB RAM
- Bộ nhớ: SSD doanh nghiệp Samsung SM863 1.9TB
- Hệ thống tập tin: ext4/xfs
- HĐH: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: phiên bản 11
Kiểm tra điểm chuẩn
Thay vì sử dụng một số tập dữ liệu do máy tạo cho thử nghiệm này, chúng tôi đã sử dụng dữ liệu "Năng suất theo thời gian Thời gian của người vận hành được báo cáo" từ năm 1987 đến năm 2018. Bạn có thể truy cập dữ liệu .
Kích thước cơ sở dữ liệu là 85 GB, cung cấp một bảng gồm 109 cột.
Truy vấn điểm chuẩn
Dưới đây là các truy vấn tôi đã sử dụng để so sánh ClickHouse, clickhousedb_fdw và PostgreSQL.
Q#
Truy vấn chứa các tập hợp và nhóm theo
Q1
CHỌN DayOfWeek, count(*) AS c FROM ontime WHERE Năm >= 2000 VÀ Năm <= 2008 NHÓM THEO DayOfWeek ĐẶT HÀNG THEO c DESC;
Q2
CHỌN DayOfWeek, đếm(*) AS c TỪ thời gian ở đâu DepDelay>10 VÀ Năm >= 2000 VÀ Năm <= 2008 NHÓM THEO DayOfWeek ĐẶT HÀNG THEO c DESC;
Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP THE Origin ORDER THEO c DESC LIMIT 10;
Q4
CHỌN Nhà cung cấp dịch vụ, số lượng () TỪ thời gian ở đâu DepDelay>10 VÀ Năm = 2007 NHÓM THEO Nhà cung cấp ĐẶT HÀNG THEO số lượng() DESC;
Q5
CHỌN a.Nhà cung cấp dịch vụ, c, c2, c1000/c2 dưới dạng c3 TỪ ( CHỌN Nhà cung cấp dịch vụ, đếm() 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 trên a.Carrier=b.Carrier ORDER BỞI c3 DESC;
Q6
CHỌN a.Nhà cung cấp dịch vụ, c, c2, c1000/c2 dưới dạng c3 TỪ ( CHỌN Nhà cung cấp dịch vụ, đếm() 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 Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
CHỌN Nhà cung cấp dịch vụ, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Năm >= 2000 VÀ Năm <= 2008 NHÓM THEO Nhà cung cấp dịch vụ;
Q8
CHỌN Năm, trung bình (DepDelay) TỪ NHÓM đúng giờ THEO Năm;
Q9
chọn Năm, tính(*) là c1 từ nhóm ontime theo Năm;
Q10
CHỌN avg(cnt) TỪ (CHỌN Năm,Tháng,số(*) NHƯ cnt TỪ thời gian ở ĐÂU DepDel15=1 NHÓM THEO Năm,Tháng) a;
Q11
chọn avg(c1) từ (chọn Năm,Tháng,đếm(*) làm c1 từ nhóm đúng giờ theo Năm,Tháng) a;
Q12
CHỌN OriginCityName, DestCityName, count(*) AS c TỪ NHÓM đúng giờ THEO OriginCityName, DestCityName ĐẶT HÀNG THEO c GIỚI HẠN MÔ TẢ 10;
Q13
CHỌN OriginCityName, count(*) AS c TỪ NHÓM ontime THEO OriginCityName ĐẶT HÀNG THEO c GIỚI HẠN MÔ TẢ 10;
Truy vấn chứa các liên kết
Q14
CHỌN a.Năm, c1/c2 TỪ ( chọn Năm, đếm()1000 dưới dạng c1 từ ontime WHERE DepDelay>10 NHÓM THEO Năm) a INNER THAM GIA (chọn Năm, đếm(*) dưới dạng c2 từ ontime GROUP BY Year ) b trên a.Year=b.Year ĐẶT HÀNG THEO a.Year;
Q15
CHỌN a.”Năm”, c1/c2 TỪ ( chọn “Năm”, count()1000 as c1 FROM fontime WHERE “DepDelay”>10 NHÓM THEO “Năm”) a INNER THAM GIA (chọn “Năm”, đếm(*) là c2 TỪ NHÓM fontime THEO “Năm”) b trên a.”Năm”=b. "Năm";
Bảng-1: Các truy vấn được sử dụng trong điểm chuẩn
Thực thi truy vấn
Dưới đây là kết quả của từng truy vấn khi chạy trong các cài đặt cơ sở dữ liệu khác nhau: PostgreSQL có và không có chỉ mục, ClickHouse gốc và clickhousedb_fdw. Thời gian được hiển thị bằng mili giây.
Q#
PostgreSQL
PostgreSQL (Đã lập chỉ mục)
ClickNhà
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
Bảng-1: Thời gian thực hiện các truy vấn được sử dụng trong điểm chuẩn
Xem Kết quả
Biểu đồ hiển thị thời gian thực hiện truy vấn tính bằng mili giây, trục X hiển thị số truy vấn từ các bảng ở trên và trục Y hiển thị thời gian thực hiện tính bằng mili giây. Kết quả ClickHouse và dữ liệu được lấy từ postgres bằng clickhousedb_fdw được hiển thị. Từ bảng, bạn có thể thấy rằng có sự khác biệt rất lớn giữa PostgreSQL và ClickHouse, nhưng có sự khác biệt tối thiểu giữa ClickHouse và clickhousedb_fdw.
Biểu đồ này cho thấy sự khác biệt giữa ClickhouseDB và clickhousedb_fdw. Trong hầu hết các truy vấn, chi phí FDW không cao và hầu như không đáng kể ngoại trừ Q12. Truy vấn này bao gồm các phép nối và mệnh đề ORDER BY. Do mệnh đề ORDER BY GROUP/BY nên ORDER BY không thả xuống ClickHouse.
Trong Bảng 2, chúng ta thấy bước nhảy thời gian trong truy vấn Q12 và Q13. Một lần nữa, điều này là do mệnh đề ORDER BY gây ra. Để xác nhận điều này, tôi đã chạy truy vấn Q-14 và Q-15 có và không có mệnh đề ORDER BY. Nếu không có mệnh đề ORDER BY thì thời gian hoàn thành là 259 mili giây và với mệnh đề ORDER BY là 1364212. Để gỡ lỗi truy vấn này, tôi đang giải thích cả hai truy vấn và đây là kết quả giải thích.
Câu 15: Không có điều khoản 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: Truy vấn không có mệnh đề 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: Truy vấn với mệnh đề 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: Kế hoạch truy vấn với mệnh đề 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)Đầu ra
Kết quả của những thử nghiệm này cho thấy ClickHouse mang lại hiệu suất thực sự tốt và clickhousedb_fdw mang lại lợi ích về hiệu suất của ClickHouse từ PostgreSQL. Mặc dù có một số chi phí khi sử dụng clickhousedb_fdw, nhưng nó không đáng kể và có thể so sánh với hiệu suất đạt được khi chạy nguyên bản trên cơ sở dữ liệu ClickHouse. Điều này cũng xác nhận rằng fdw trong PostgreSQL mang lại kết quả tuyệt vời.
Trò chuyện Telegram qua Clickhouse
Trò chuyện Telegram bằng PostgreSQL
Nguồn: www.habr.com