In dieser Studie wollte ich sehen, welche Leistungsverbesserungen durch die Verwendung einer ClickHouse-Datenquelle anstelle von PostgreSQL erzielt werden können. Ich kenne die Produktivitätsvorteile, die ich durch die Verwendung von ClickHouse erhalte. Bleiben diese Vorteile bestehen, wenn ich über PostgreSQL mit einem Foreign Data Wrapper (FDW) auf ClickHouse zugreife?
Die untersuchten Datenbankumgebungen sind PostgreSQL v11, clickhousedb_fdw und ClickHouse-Datenbank. Letztendlich werden wir ab PostgreSQL v11 verschiedene SQL-Abfragen ausführen, die über unser clickhousedb_fdw an die ClickHouse-Datenbank weitergeleitet werden. Anschließend sehen wir, wie sich die Leistung von FDW im Vergleich zu denselben Abfragen verhält, die in nativem PostgreSQL und nativem ClickHouse ausgeführt werden.
Clickhouse-Datenbank
ClickHouse ist ein spaltenbasiertes Open-Source-Datenbankverwaltungssystem, das eine 100- bis 1000-mal schnellere Leistung als herkömmliche Datenbankansätze erzielen kann und über eine Milliarde Zeilen in weniger als einer Sekunde verarbeiten kann.
Clickhousedb_fdw
clickhousedb_fdw – Der externe Daten-Wrapper für die ClickHouse-Datenbank (FDW) ist ein Open-Source-Projekt von Percona. .
.
Wie Sie sehen werden, stellt dies ein FDW für ClickHouse bereit, das SELECT aus der ClickHouse-Datenbank und INSERT INTO vom PostgreSQL v11-Server aus ermöglicht.
FDW unterstützt erweiterte Funktionen wie Aggregation und Join. Dadurch wird die Leistung erheblich verbessert, da die Ressourcen des Remote-Servers für diese ressourcenintensiven Vorgänge genutzt werden.
Benchmark-Umgebung
- Supermicro-Server:
- Intel® Xeon® CPU E5-2683 v3 bei 2.00 GHz
- 2 Sockel / 28 Kerne / 56 Threads
- Speicher: 256GB RAM
- Speicher: Samsung SM863 1.9 TB Enterprise SSD
- Dateisystem: ext4/xfs
- Betriebssystem: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: Version 11
Benchmark-Tests
Anstatt für diesen Test einen maschinengenerierten Datensatz zu verwenden, haben wir die Daten „Produktivität nach gemeldeter Bedienerzeit“ von 1987 bis 2018 verwendet. Sie können auf die Daten zugreifen .
Die Datenbankgröße beträgt 85 GB und bietet eine Tabelle mit 109 Spalten.
Benchmark-Abfragen
Hier sind die Abfragen, die ich zum Vergleich von ClickHouse, clickhousedb_fdw und PostgreSQL verwendet habe.
Q#
Abfrage enthält Aggregate und Gruppierung nach
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 als 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 auf a.Carrier=b.Carrier ORDER BY c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 als c3 FROM ( SELECT Carrier, count() 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
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
Wählen Sie Jahr aus, zählen Sie(*) als c1 aus der Pünktlichkeitsgruppe nach Jahr;
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;
Abfrage enthält Joins
Q14
SELECT a.Year, c1/c2 FROM ( select Year, count()1000 als c1 von ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (wählen Sie Year, count(*) als c2 von 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 als c1 FROM Fontime WHERE „DepDelay“>10 GROUP BY „Year“) a INNER JOIN (wählen Sie „Year“, count(*) als c2 FROM Fontime GROUP BY „Year“) b auf a.“Year“=b. "Jahr";
Tabelle 1: Im Benchmark verwendete Abfragen
Abfrageausführungen
Hier sind die Ergebnisse jeder Abfrage, wenn sie in verschiedenen Datenbankeinstellungen ausgeführt wird: PostgreSQL mit und ohne Indizes, natives ClickHouse und clickhousedb_fdw. Die Zeit wird in Millisekunden angezeigt.
Q#
PostgreSQL
PostgreSQL (indiziert)
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
Tabelle 1: Zeitaufwand für die Ausführung der im Benchmark verwendeten Abfragen
Ergebnisse anzeigen
Das Diagramm zeigt die Ausführungszeit der Abfrage in Millisekunden, die X-Achse zeigt die Abfragenummer aus den obigen Tabellen und die Y-Achse zeigt die Ausführungszeit in Millisekunden. ClickHouse-Ergebnisse und Daten, die mit clickhousedb_fdw aus Postgres abgerufen wurden, werden angezeigt. Aus der Tabelle können Sie ersehen, dass es einen großen Unterschied zwischen PostgreSQL und ClickHouse gibt, aber nur einen minimalen Unterschied zwischen ClickHouse und clickhousedb_fdw.
Diese Grafik zeigt den Unterschied zwischen ClickhouseDB und clickhousedb_fdw. Bei den meisten Abfragen ist der FDW-Overhead nicht so hoch und fällt bis auf Q12 kaum ins Gewicht. Diese Abfrage umfasst Joins und eine ORDER BY-Klausel. Aufgrund der ORDER BY GROUP/BY-Klausel wird ORDER BY nicht an ClickHouse weitergeleitet.
In Tabelle 2 sehen wir den Zeitsprung in den Abfragen Q12 und Q13. Auch dies wird durch die ORDER BY-Klausel verursacht. Um dies zu bestätigen, habe ich die Abfragen Q-14 und Q-15 mit und ohne die ORDER BY-Klausel ausgeführt. Ohne die ORDER BY-Klausel beträgt die Abschlusszeit 259 ms und mit der ORDER BY-Klausel 1364212. Um diese Abfrage zu debuggen, erkläre ich beide Abfragen und hier sind die Ergebnisse der Erklärung.
F15: Ohne ORDER BY-Klausel
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";F15: Abfrage ohne ORDER BY-Klausel
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)F14: Abfrage mit ORDER BY-Klausel
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";F14: Abfrageplan mit ORDER BY-Klausel
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)Abschluss
Die Ergebnisse dieser Experimente zeigen, dass ClickHouse eine wirklich gute Leistung bietet und clickhousedb_fdw die Leistungsvorteile von ClickHouse von PostgreSQL bietet. Bei der Verwendung von clickhousedb_fdw entsteht zwar ein gewisser Mehraufwand, dieser ist jedoch vernachlässigbar und vergleichbar mit der Leistung, die durch die native Ausführung in der ClickHouse-Datenbank erzielt wird. Dies bestätigt auch, dass fdw in PostgreSQL hervorragende Ergebnisse liefert.
Telegram-Chat über Clickhouse
Telegram-Chat mit PostgreSQL
Source: habr.com