W tym badaniu chciałem zobaczyć, jaką poprawę wydajności można osiągnąć, korzystając ze źródła danych ClickHouse zamiast PostgreSQL. Znam korzyści związane z produktywnością, jakie czerpię z korzystania z ClickHouse. Czy te korzyści będą kontynuowane, jeśli uzyskam dostęp do ClickHouse z PostgreSQL przy użyciu opakowania danych zagranicznych (FDW)?
Badane środowiska baz danych to PostgreSQL v11, clickhousedb_fdw i baza danych ClickHouse. Docelowo, począwszy od PostgreSQL v11, będziemy uruchamiać różne zapytania SQL kierowane przez nasz clickhousedb_fdw do bazy danych ClickHouse. Następnie zobaczymy, jak wydajność FDW wypada w porównaniu z tymi samymi zapytaniami działającymi w natywnym PostgreSQL i natywnym ClickHouse.
Baza danych Clickhouse
ClickHouse to system zarządzania kolumnowymi bazami danych typu open source, który może osiągnąć wydajność 100–1000 razy większą niż tradycyjne podejścia do baz danych i jest w stanie przetworzyć ponad miliard wierszy w mniej niż sekundę.
Clickhousedb_fdw
clickhousedb_fdw — zewnętrzne opakowanie danych dla bazy danych ClickHouse, w skrócie FDW, to projekt open source firmy Percona. .
.
Jak zobaczysz, zapewnia to FDW dla ClickHouse, które umożliwia WYBIERANIE i WSTAWIANIE bazy danych ClickHouse z serwera PostgreSQL v11.
FDW obsługuje zaawansowane funkcje, takie jak agregowanie i łączenie. To znacznie poprawia wydajność poprzez wykorzystanie zasobów serwera zdalnego do operacji wymagających dużych zasobów.
Środowisko wzorcowe
- Serwer Supermicro:
- Procesor Intel® Xeon® E5-2683 v3 @ 2.00 GHz
- 2 gniazda / 28 rdzeni / 56 wątków
- Pamięć: 256GB RAM
- Pamięć: Samsung SM863 1.9 TB Enterprise SSD
- System plików: ext4/xfs
- System operacyjny: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: wersja 11
Testy porównawcze
Zamiast wykorzystywać do tego testu zestaw danych wygenerowanych maszynowo, wykorzystaliśmy dane „Produktywność według czasu raportowanego czasu operatora” z lat 1987–2018. Możesz uzyskać dostęp do danych .
Rozmiar bazy danych wynosi 85 GB, co zapewnia jedną tabelę zawierającą 109 kolumn.
Zapytania porównawcze
Oto zapytania, których użyłem do porównania ClickHouse, clickhousedb_fdw i PostgreSQL.
Q#
Zapytanie zawiera agregaty i grupowanie według
Q1
WYBIERZ Dzień Tygodnia, count(*) AS c FROM ontime WHERE Rok >= 2000 ORAZ Rok <= 2008 GRUPUJ WG Dnia Tygodnia ZAMÓW PRZEZ c DESC;
Q2
WYBIERZ Dzień Tygodnia, count(*) AS c FROM ontime GDZIE DepDelay>10 ORAZ Rok >= 2000 ORAZ Rok <= 2008 GRUPUJ WG Dnia Tygodnia ZAMÓW PRZEZ c DESC;
Q3
WYBIERZ Pochodzenie, liczba(*) AS c FROM ontime GDZIE DepDelay>10 ORAZ Rok >= 2000 ORAZ Rok <= 2008 GRUPUJ WG Pochodzenia ZAMÓW WG c OPIS LIMIT 10;
Q4
WYBIERZ przewoźnika, liczba () OD ontime GDZIE DepDelay>10 ORAZ Rok = 2007 GRUPUJ WG przewoźnika ZAMÓW WG liczby () DESC;
Q5
WYBIERZ a.Przewoźnik, c, c2, c1000/c2 jako c3 OD ( WYBIERZ przewoźnika, liczba () 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 na a.Carrier=b.Carrier ZAMÓWIENIE PRZEZ c3 OPIS;
Q6
WYBIERZ a.Przewoźnik, c, c2, c1000/c2 jako c3 OD ( WYBIERZ przewoźnika, liczba () 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 Przewoźnik ) b na a.Przewoźnik=b.Przewoźnik ZAMÓW PRZEZ c3 DESC;
Q7
WYBIERZ przewoźnika, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Rok >= 2000 ORAZ Rok <= 2008 GROUP BY Carrier;
Q8
WYBIERZ rok, śr.(DelayDelay) Z ontime GROUP BY Rok;
Q9
wybierz rok, count(*) jako c1 z grupy ontime według roku;
Q10
WYBIERZ śr.(cnt) Z (WYBIERZ Rok,Miesiąc,liczba(*) AS cnt FROM ontime GDZIE DepDel15=1 GRUPA WG Roku,Miesiąca) a;
Q11
wybierz średnią (c1) z (wybierz rok, miesiąc, liczbę (*) jako c1 z grupy ontime według roku, miesiąca) a;
Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ZAMÓW PRZEZ c DESC LIMIT 10;
Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Zapytanie zawiera sprzężenia
Q14
WYBIERZ a.Rok, c1/c2 Z ( wybierz Rok, liczba()1000 jako c1 z ontime GDZIE DepDelay>10 GROUP BY Year) a INNER JOIN (wybierz rok, policz(*) jako c2 z ontime GROUP BY Year ) b on a.Year=b.Year ZAMÓW WG a.Rok;
Q15
WYBIERZ a.”Rok”, c1/c2 Z (wybierz „Rok”, liczba()1000 jako c1 Z fontime WHERE „DepDelay”>10 GRUPUJ WG „Roku”) a POŁĄCZENIE WEWNĘTRZNE (wybierz „Rok”, policz(*) jako c2 Z fontime GROUP WG „Roku” ) b na a.”Rok”=b. "Rok";
Tabela-1: Zapytania użyte w benchmarku
Wykonania zapytań
Oto wyniki każdego zapytania uruchomionego w różnych ustawieniach bazy danych: PostgreSQL z indeksami i bez, natywny ClickHouse i clickhousedb_fdw. Czas jest pokazywany w milisekundach.
Q#
PostgreSQL
PostgreSQL (indeksowany)
Kliknij Dom
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
Tabela-1: Czas potrzebny na wykonanie zapytań używanych w benchmarku
Pokaż wyniki
Wykres przedstawia czas wykonania zapytania w milisekundach, oś X przedstawia numer zapytania z powyższych tabel, a oś Y przedstawia czas wykonania w milisekundach. Pokazane są wyniki ClickHouse i dane pobrane z postgres przy użyciu clickhousedb_fdw. Z tabeli widać, że istnieje ogromna różnica między PostgreSQL i ClickHouse, ale minimalna różnica między ClickHouse i clickhousedb_fdw.
Ten wykres pokazuje różnicę między ClickhouseDB i clickhousedb_fdw. W większości zapytań obciążenie FDW nie jest tak wysokie i nie jest znaczące, z wyjątkiem Q12. To zapytanie zawiera złączenia i klauzulę ORDER BY. Ze względu na klauzulę ORDER BY GROUP/BY, ORDER BY nie spada do ClickHouse.
W tabeli 2 widzimy przeskok czasowy w zapytaniach Q12 i Q13. Ponownie jest to spowodowane klauzulą ORDER BY. Aby to potwierdzić, uruchomił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 wynosi 1364212. Aby zdebugować to zapytanie, wyjaśniam oba zapytania i oto wyniki wyjaśnienia.
Pytanie 15: Bez klauzuli 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";Pytanie 15: Zapytanie bez klauzuli 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)Pytanie 14: Zapytanie z klauzulą 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";Pytanie 14: Plan zapytania z klauzulą 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)Wniosek
Wyniki tych eksperymentów pokazują, że ClickHouse oferuje naprawdę dobrą wydajność, a clickhousedb_fdw oferuje korzyści wydajnościowe ClickHouse z PostgreSQL. Chociaż korzystanie z clickhousedb_fdw wiąże się z pewnymi narzutami, są one znikome i porównywalne z wydajnością osiągniętą dzięki natywnemu uruchomieniu w bazie danych ClickHouse. Potwierdza to również, że fdw w PostgreSQL zapewnia doskonałe wyniki.
Czat telegramowy za pośrednictwem Clickhouse
Czat telegramowy przy użyciu PostgreSQL
Źródło: www.habr.com