In dieser Studie wollte ich untersuchen, welche Leistungsverbesserungen erzielt werden können, wenn ich ClickHouse als Datenquelle anstelle von PostgreSQL verwende. Ich kenne die Leistungs Vorteile, die ich durch die Verwendung von ClickHouse erhalte. Werden diese Vorteile erhalten bleiben, wenn ich über ein Foreign Data Wrapper (FDW) auf ClickHouse von PostgreSQL zugreife?
Die untersuchten Datenbankumgebungen sind PostgreSQL v11, clickhousedb_fdw und die ClickHouse-Datenbank. Letztendlich werden wir aus PostgreSQL v11 verschiedene SQL-Abfragen ausführen, die über unser clickhousedb_fdw an die ClickHouse-Datenbank weitergeleitet werden. Dann werden wir sehen, wie sich die Leistung des FDW im Vergleich zu denselben Abfragen verhält, die in nativem PostgreSQL und nativem ClickHouse ausgeführt werden.
Clickhouse-Datenbank
ClickHouse ist ein Open-Source-Spaltenbasiertes Datenbankmanagementsystem, das eine Leistung von 100 bis 1000 Mal schneller erreichen kann als traditionelle Datenbankansätze und in der Lage ist, mehr als eine Milliarde Zeilen in weniger als einer Sekunde zu verarbeiten.
Clickhousedb_fdw
clickhousedb_fdw ist ein Open-Source-Projekt von Percona, das ein Foreign Data Wrapper für die ClickHouse-Datenbank bereitstellt. .
.
Wie Sie sehen werden, bietet es ein FDW für ClickHouse, das es ermöglicht, SELECT from und INSERT INTO für die ClickHouse-Datenbank von PostgreSQL v11 auszuführen.
FDW unterstützt erweiterte Funktionen wie Aggregation und Joins. Dies erhöht die Leistung erheblich, indem die Ressourcen des Remote-Servers für diese ressourcenintensiven Operationen genutzt werden.
Benchmark-Umgebung
- Supermicro-Server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 Sockets / 28 Kerne / 56 Threads
- Speicher: 256 GB RAM
- Speicher: Samsung SM863 1.9TB Enterprise SSD
- Dateisystem: ext4/xfs
- Betriebssystem: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: Version 11
Benchmark-Tests
Statt einen von der Maschine generierten Datensatz für diesen Test zu verwenden, haben wir die Daten "Leistungsberichte über die Betriebsdauer von 1987 bis 2018" verwendet. Sie können auf die Daten zugreifen .
Die Datenbankgröße beträgt 85 GB und enthält eine Tabelle mit 109 Spalten.
Benchmark-Abfragen
Hier sind die Abfragen, die ich verwendet habe, um ClickHouse, clickhousedb_fdw und PostgreSQL zu vergleichen.
Q#
Abfrage enthält Aggregationen und GROUP BY
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
WÄHLE TagDerWoche, COUNT(*) AS c VON ontime WO DepDelay>10 UND Jahr >= 2000 UND Jahr <= 2008 GRUPPIERE NACH TagDerWoche BESTELLE NACH c DESC;
Q3
WÄHLE Herkunft, COUNT(*) AS c VON ontime WO DepDelay>10 UND Jahr >= 2000 UND Jahr <= 2008 GRUPPIERE NACH Herkunft BESTELLE NACH c DESC LIMIT 10;
Q4
WÄHLE Anbieter, COUNT() VON ontime WO DepDelay>10 UND Jahr = 2007 GRUPPIERE NACH Anbieter BESTELLE NACH COUNT() DESC;
Q5
WÄHLE a.Anbieter, c, c2, c1000/c2 als c3 VON (WÄHLE Anbieter, COUNT() AS c VON ontime WO DepDelay>10 UND Jahr=2007 GRUPPIERE NACH Anbieter) a INNEN VERBINDEN (WÄHLE Anbieter, COUNT(*) AS c2 VON ontime WO Jahr=2007 GRUPPIERE NACH Anbieter)b ON a.Anbieter=b.Anbieter BESTELLE NACH c3 DESC;
Q6
WÄHLE a.Anbieter, c, c2, c1000/c2 als c3 VON (WÄHLE Anbieter, COUNT() AS c VON ontime WO DepDelay>10 UND Jahr >= 2000 UND Jahr = 2000 UND Jahr <= 2008 GRUPPIERE NACH Anbieter)b ON a.Anbieter=b.Anbieter BESTELLE NACH c3 DESC;
Q7
WÄHLE Anbieter, AVG(DepDelay) * 1000 AS c3 VON ontime WO Jahr >= 2000 UND Jahr <= 2008 GRUPPIERE NACH Anbieter;
Q8
WÄHLE Jahr, AVG(DepDelay) VON ontime GRUPPIERE NACH Jahr;
Q9
WÄHLE Jahr, COUNT(*) AS c1 VON ontime GRUPPIERE NACH Jahr;
Q10
WÄHLE AVG(cnt) VON (WÄHLE Jahr, Monat, COUNT(*) AS cnt VON ontime WO DepDel15=1 GRUPPIERE NACH Jahr, Monat) a;
Q11
WÄHLE AVG(c1) VON (WÄHLE Jahr, Monat, COUNT(*) AS c1 VON ontime GRUPPIERE NACH Jahr, Monat) a;
Q12
WÄHLE HerkunftsStadtName, ZielStadtName, COUNT(*) AS c VON ontime GRUPPIERE NACH HerkunftsStadtName, ZielStadtName BESTELLE NACH c DESC LIMIT 10;
Q13
WÄHLE HerkunftsStadtName, COUNT(*) AS c VON ontime GRUPPIERE NACH HerkunftsStadtName BESTELLE NACH c DESC LIMIT 10;
Abfrage Enthält Joins
Q14
WÄHLE a.Jahr, c1/c2 VON (WÄHLE Jahr, COUNT()1000 AS c1 VON ontime WO DepDelay>10 GRUPPIERE NACH Jahr) a INNEN VERBINDEN (WÄHLE Jahr, COUNT(*) AS c2 VON ontime GRUPPIERE NACH Jahr) b ON a.Jahr=b.Jahr BESTELLE NACH a.Jahr;
Q15
WÄHLE a.Jahr, c1/c2 VON (WÄHLE Jahr, COUNT()1000 AS c1 VON fontime WO DepDelay>10 GRUPPIERE NACH Jahr) a INNEN VERBINDEN (WÄHLE Jahr, COUNT(*) AS c2 VON fontime GRUPPIERE NACH Jahr) b ON a.Jahr=b.Jahr;
Tabelle-1: Abfragen, die im Benchmark verwendet wurden
Abfrageausführungen
Hier sind die Ergebnisse jeder Abfrage, die bei verschiedenen Datenbankkonfigurationen ausgeführt wurden: PostgreSQL mit und ohne Indizes, hauseigenes ClickHouse und clickhousedb_fdw. Die Zeit wird in Millisekunden angezeigt.
Q#
PostgreSQL
PostgreSQL (Indexiert)
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: Zeit, die benötigt wird, um die im Benchmark verwendeten Abfragen auszuführen
Ergebnisse anzeigen
Das Diagramm zeigt die Ausführungszeit der Abfrage in Millisekunden, die X-Achse zeigt die Abfragenummer aus den oben genannten Tabellen und die Y-Achse zeigt die Ausführungszeit in Millisekunden. Die Ergebnisse von ClickHouse und die Daten, die aus Postgres mit clickhousedb_fdw abgerufen wurden, werden angezeigt. Aus der Tabelle ist ersichtlich, dass es einen enormen Unterschied zwischen PostgreSQL und ClickHouse gibt, aber nur einen minimalen Unterschied zwischen ClickHouse und clickhousedb_fdw.
Dieses Diagramm zeigt den Unterschied zwischen ClickhouseDB und clickhousedb_fdw. Bei den meisten Abfragen sind die Overhead-Kosten von FDW nicht so hoch und kaum spürbar, außer bei Q12. Diese Abfrage umfasst Joins und die ORDER BY-Klausel. Aufgrund der ORDER BY-Klausel werden GROUP/BY und ORDER BY nicht von ClickHouse übersprungen.
In Tabelle 2 sehen wir einen Anstieg der Zeit in den Abfragen Q12 und Q13. Ich wiederhole, dies ist auf das ORDER BY-Vorschlag zurückzuführen. Um dies zu bestätigen, habe ich die Abfragen Q-14 und Q-15 mit und ohne das ORDER BY-Vorschlag ausgeführt. Ohne das ORDER BY-Vorschlag beträgt die Abschlusszeit 259 ms, mit dem ORDER BY-Vorschlag jedoch 1364212. Zur Fehlersuche dieser Abfrage erkläre ich beide Abfragen, und hier sind die Ergebnisse der Erläuterung.
Q15: 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";Q15: Abfrage ohne ORDER BY-Klausel
ABFRAGEPLAN
Hash Join (cost=2250.00..128516.06 rows=50000000 width=12)
Ausgabe: fontime."Year", (((count(*) * 1000)) / b.c2)
Innere Einzigartigkeit: wahr Hash-Bedingung: (fontime."Year" = b."Year")
-> Fremdscan (cost=1.00..-1.00 rows=100000 width=12)
Ausgabe: fontime."Year", ((count(*) * 1000))
Beziehungen: Aggregat auf (fontime)
Entfernte SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"
-> Hash (cost=999.00..999.00 rows=100000 width=12)
Ausgabe: b.c2, b."Year"
-> Unterabfrage-Scan auf b (cost=1.00..999.00 rows=100000 width=12)
Ausgabe: b.c2, b."Year"
-> Fremdscan (cost=1.00..-1.00 rows=100000 width=12)
Ausgabe: fontime_1."Year", (count(*))
Beziehungen: Aggregat auf (fontime)
Entfernte SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 Zeilen)Q14: 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";Q14: Abfrageplan mit ORDER BY-Klausel
ABFRAGEPLAN
Merge Join (cost=2.00..628498.02 rows=50000000 width=12)
Ausgabe: fontime."Year", (((count(*) * 1000)) / (count(*)))
Innere Einzigartigkeit: wahr Merge-Bedingung: (fontime."Year" = fontime_1."Year")
-> Gruppenaggregat (cost=1.00..499.01 rows=1 width=12)
Ausgabe: fontime."Year", (count(*) * 1000)
Gruppenschlüssel: fontime."Year"
-> Fremdscan on public.fontime (cost=1.00..-1.00 rows=100000 width=4)
Entfernte SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10))
ORDER BY "Year" ASC
-> Gruppenaggregat (cost=1.00..499.01 rows=1 width=12)
Ausgabe: fontime_1."Year", count(*) Gruppenschlüssel: fontime_1."Year"
-> Fremdscan on public.fontime fontime_1 (cost=1.00..-1.00 rows=100000 width=4)
Entfernte SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 Zeilen)Ausgabe
Die Ergebnisse dieser Experimente zeigen, dass ClickHouse wirklich gute Leistung bietet und clickhousedb_fdw die Leistungsvorteile von ClickHouse aus PostgreSQL heraus anbietet. Obwohl bei der Verwendung von clickhousedb_fdw einige Overhead-Kosten anfallen, sind sie unbedeutend und stehen in einem Verhältnis zur Leistung, die bei einem nativen Betrieb in der ClickHouse-Datenbank erzielt wird. Dies bestätigt auch, dass fdw in PostgreSQL hervorragende Ergebnisse liefert.
Telegram-Chat zu ClickHouse
Telegram-Chat zu PostgreSQL
Quelle: habr.com
