ProHoster > Blog > Administracja > Testowanie wydajności zapytań analitycznych w PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)

Testowanie wydajności zapytań analitycznych w PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)

W tym badaniu chciałem zbadać, jakie poprawy wydajności można osiągnąć, używając źródła danych ClickHouse zamiast PostgreSQL. Znam korzyści wydajnościowe, jakie oferuje ClickHouse. Czy te zalety będą zachowane, jeśli uzyskam dostęp do ClickHouse z PostgreSQL za pomocą zewnętrznej powłoki danych (FDW)?

Badanymi środowiskami baz danych są PostgreSQL v11, clickhousedb_fdw i baza danych ClickHouse. Ostatecznie z PostgreSQL v11 wykonamy różne zapytania SQL, kierowane przez nasz clickhousedb_fdw do bazy danych ClickHouse. Następnie zobaczymy, jak wydajność FDW porównuje się z tymi samymi zapytaniami wykonywanymi w natywnym PostgreSQL i natywnym ClickHouse.

Baza danych Clickhouse

ClickHouse to system zarządzania bazami danych typu kolumnowego z otwartym kodem źródłowym, który może osiągać wydajność od 100 do 1000 razy szybciej niż tradycyjne podejścia do baz danych, zdolny do przetwarzania ponad miliarda wierszy w mniej niż sekundę.

Clickhousedb_fdw

clickhousedb_fdw to zewnętrzna powłoka danych dla bazy danych ClickHouse, lub FDW, będąca projektem z otwartym kodem źródłowym od Percona. Oto link do repozytorium projektu GitHub.

W marcu napisałem bloga, który opowiada więcej o naszym FDW.

Jak zobaczycie, to udostępnia FDW dla ClickHouse, które pozwala na SELECT from i INSERT INTO bazy danych ClickHouse z serwera PostgreSQL v11.

FDW wspiera zaawansowane funkcje, takie jak agregaty i łączenia. To znacznie zwiększa wydajność, wykorzystując zasoby zdalnego serwera do tych zasobożernych operacji.

Środowisko benchmarkowe

  • Serwer Supermicro:
    • Procesor Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 gniazda / 28 rdzeni / 56 wątków
    • Pamięć: 256 GB RAM
    • Przechowywanie: Samsung SM863 1.9TB SSD Enterprise
    • System plików: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: wersja 11

Testy benchmarkowe

Zamiast używać jakiegoś zbioru danych wygenerowanego przez maszynę do tego testu, użyliśmy danych „Wydajność czasu, informująca o czasie pracy operatora” z lat 1987-2018. Możesz uzyskać dostęp do danych za pomocą naszego skryptu, dostępnego tutaj.

Rozmiar bazy danych wynosi 85 GB, zapewniając jedną tabelę z 109 kolumnami.

Zapytania benchmarkowe

Oto zapytania, które użyłem do porównania ClickHouse, clickhousedb_fdw i PostgreSQL.

Q#
Zapytanie zawiera agregaty i Group By

Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q3
SELECT 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
SELECT Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;

Q5
SELECT a.Carrier, c, c2, c1000/c2 as c3 FROM ( SELECT Carrier, count() 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.Carrier ORDER BY c3 DESC;

Q6
SELECT a.Carrier, c, c2, c1000/c2 as c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year = 2000 AND Year <= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;

Q8
SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;

Q9
select Year, count(*) as c1 from ontime group by Year;

Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;

Q11
select avg(c1) from (select Year,Month,count(*) as c1 from ontime group by Year,Month) a;

Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;

Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;

Query Contains Joins

Q14
SELECT a.Year, c1/c2 FROM ( select Year, count()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;

Q15
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";

Table-1: Queries used in benchmark

Query executions

Oto wyniki każdego z zapytań przy wykonywaniu w różnych ustawieniach bazy danych: PostgreSQL z indeksami i bez nich, własny ClickHouse i clickhousedb_fdw. Czas jest podawany w milisekundach.

Q#
PostgreSQL
PostgreSQL (Indexed)
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

Table-1: Czas wykonania zapytań użytych w benchmarku

Przeglądanie wyników

Wykres pokazuje czas wykonania zapytania w milisekundach, oś X pokazuje numer zapytania z powyższych tabel, a oś Y pokazuje czas wykonania w milisekundach. Wyniki ClickHouse i dane uzyskane z postgres za pomocą clickhousedb_fdw są pokazane. Z tabeli widać, że istnieje ogromna różnica między PostgreSQL a ClickHouse, ale minimalna różnica między ClickHouse a clickhousedb_fdw.

Testowanie wydajności zapytań analitycznych w PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)

Ten wykres pokazuje różnicę między ClickhouseDB a clickhousedb_fdw. W większości zapytań koszty FDW nie są tak duże i są ledwo zauważalne, z wyjątkiem Q12. To zapytanie zawiera połączenia i klauzulę ORDER BY. Z powodu klauzuli ORDER BY GROUP/BY i ORDER BY nie opadają do ClickHouse.

W tabeli 2 widzimy skok czasu w zapytaniach Q12 i Q13. Powtarzam, jest to spowodowane użyciem klauzuli ORDER BY. Aby to potwierdzić, wykonałem zapytania Q-14 i Q-15 z klauzulą ORDER BY i bez niej. Bez klauzuli ORDER BY czas zakończenia wynosi 259 ms, a z klauzulą ORDER BY — 1364212. Dla debugowania tego zapytania wyjaśniam oba zapytania, a tutaj przedstawione są wyniki wyjaśnienia.

Q15: Bez klauzuli ORDER BY

bm=# WYJAŚNIJ SZCZEGÓŁOWO WYBIERZ a."Rok", c1/c2 
     Z (WYBIERZ "Rok", count(*)*1000 JAKO c1 Z fontime GDY "DepDelay" > 10 GRUPUJ PO "Rok") a
     WEWNĘTRZNE DOŁĄCZENIE (WYBIERZ "Rok", count(*) AS c2 Z fontime GRUPUJ PO "Rok") b NA a."Rok"=b."Rok";

Q15: Zapytanie bez klauzuli ORDER BY

PLAN ZAPYTU                                                      
Połączenie Hash  (koszt=2250.00..128516.06 wierszy=50000000 szerokość=12)  
Wyjście: fontime."Rok", (((count(*) * 1000)) / b.c2)  
Unikalny Wewnętrzny: prawda   Warunek Hash: (fontime."Rok" = b."Rok")  
->  Skanowanie Zdalne  (koszt=1.00..-1.00 wierszy=100000 szerokość=12)        
Wyjście: fontime."Rok", ((count(*) * 1000))        
Relacje: Agregacja na (fontime)        
Zdalne SQL: WYBIERZ "Rok", (count(*) * 1000) Z "default".ontime GDY (("DepDelay" > 10)) GRUPUJ PO "Rok"  
->  Hash  (koszt=999.00..999.00 wierszy=100000 szerokość=12)        
Wyjście: b.c2, b."Rok"        
->  Skanowanie Podzapytania na b  (koszt=1.00..999.00 wierszy=100000 szerokość=12)              
Wyjście: b.c2, b."Rok"              
->  Skanowanie Zdalne  (koszt=1.00..-1.00 wierszy=100000 szerokość=12)                    
Wyjście: fontime_1."Rok", (count(*))                    
Relacje: Agregacja na (fontime)                    
Zdalne SQL: WYBIERZ "Rok", count(*) Z "default".ontime GRUPUJ PO "Rok"(16 wierszy)

Q14: Zapytanie z klauzulą ORDER BY

bm=# WYJAŚNIJ SZCZEGÓŁOWO WYBIERZ a."Rok", c1/c2 Z (WYBIERZ "Rok", count(*)*1000 JAKO c1 Z fontime GDY "DepDelay" > 10 GRUPUJ PO "Rok") a 
     WEWNĘTRZNE DOŁĄCZENIE (WYBIERZ "Rok", count(*) jako c2 Z fontime GRUPUJ PO "Rok") b  NA a."Rok"= b."Rok" 
     ZAMÓW PO a."Rok";

Q14: Plan zapytania z klauzulą ORDER BY

PLAN ZAPYTU 
Połączenie Merge  (koszt=2.00..628498.02 wierszy=50000000 szerokość=12)   
Wyjście: fontime."Rok", (((count(*) * 1000)) / (count(*)))   
Unikalny Wewnętrzny: prawda   Warunek Merge: (fontime."Rok" = fontime_1."Rok")   
->  Agregacja Grupowa  (koszt=1.00..499.01 wierszy=1 szerokość=12)        
Wyjście: fontime."Rok", (count(*) * 1000)         
Klucz grupujący: fontime."Rok"         
->  Skanowanie Zdalne na public.fontime  (koszt=1.00..-1.00 wierszy=100000 szerokość=4)               
Zdalne SQL: WYBIERZ "Rok" Z "default".ontime GDY (("DepDelay" > 10)) 
            ZAMÓW PO "Rok" ASC   
->  Agregacja Grupowa  (koszt=1.00..499.01 wierszy=1 szerokość=12)         
Wyjście: fontime_1."Rok", count(*)         Klucz grupujący: fontime_1."Rok"         
->  Skanowanie Zdalne na public.fontime fontime_1  (koszt=1.00..-1.00 wierszy=100000 szerokość=4) 
              
Zdalne SQL: WYBIERZ "Rok" Z "default".ontime ZAMÓW PO "Rok" ASC(16 wierszy)

Wnioski

Wyniki tych eksperymentów pokazują, że ClickHouse oferuje naprawdę dobrą wydajność, a clickhousedb_fdw przynosi korzyści wydajności ClickHouse z PostgreSQL. Chociaż korzystanie z clickhousedb_fdw wiąże się z pewnymi kosztami, są one niewielkie i porównywalne z wydajnością osiąganą podczas naturalnego uruchamiania w bazie danych ClickHouse. Potwierdza to również, że fdw w PostgreSQL zapewnia znakomite wyniki.

Telegramowy czat o Clickhouse https://t.me/clickhouse_ru
Telegramowy czat o PostgreSQL https://t.me/pgsql

Źródło: habr.com

Kup solidny hosting stron z ochroną przed DDoS, serwery VPS VDS 🔥 Kup solidny hosting stron z ochroną przed DDoS, serwery VPS VDS | ProHoster