Σε αυτήν τη μελέτη, ήθελα να δω ποιες βελτιώσεις απόδοσης θα μπορούσαν να επιτευχθούν χρησιμοποιώντας μια πηγή δεδομένων ClickHouse αντί για την PostgreSQL. Γνωρίζω τα οφέλη παραγωγικότητας που αποκομίζω από τη χρήση του ClickHouse. Θα συνεχιστούν αυτά τα πλεονεκτήματα εάν αποκτήσω πρόσβαση στο ClickHouse από την PostgreSQL χρησιμοποιώντας ξένο περιτύλιγμα δεδομένων (FDW);
Τα περιβάλλοντα βάσης δεδομένων που μελετήθηκαν είναι η βάση δεδομένων PostgreSQL v11, clickhousedb_fdw και ClickHouse. Τελικά, από την PostgreSQL v11 θα εκτελούμε διάφορα ερωτήματα SQL που δρομολογούνται μέσω του clickhousedb_fdw στη βάση δεδομένων ClickHouse. Στη συνέχεια, θα δούμε πώς η απόδοση του FDW συγκρίνεται με τα ίδια ερωτήματα που εκτελούνται στο εγγενές PostgreSQL και στο εγγενές ClickHouse.
Βάση δεδομένων Clickhouse
Το ClickHouse είναι ένα σύστημα διαχείρισης βάσης δεδομένων ανοικτού κώδικα στηλών που μπορεί να επιτύχει απόδοση 100-1000 φορές πιο γρήγορα από τις παραδοσιακές προσεγγίσεις βάσεων δεδομένων, ικανό να επεξεργαστεί περισσότερες από ένα δισεκατομμύριο σειρές σε λιγότερο από ένα δευτερόλεπτο.
Clickhousedb_fdw
clickhousedb_fdw - Το εξωτερικό περιτύλιγμα δεδομένων για τη βάση δεδομένων ClickHouse ή FDW, είναι ένα έργο ανοιχτού κώδικα από την Percona. .
.
Όπως θα δείτε, αυτό παρέχει ένα FDW για το ClickHouse που επιτρέπει SELECT από και INSERT INTO, τη βάση δεδομένων ClickHouse από τον διακομιστή PostgreSQL v11.
Το FDW υποστηρίζει προηγμένες λειτουργίες, όπως το aggregate και το join. Αυτό βελτιώνει σημαντικά την απόδοση χρησιμοποιώντας τους πόρους του απομακρυσμένου διακομιστή για αυτές τις λειτουργίες έντασης πόρων.
Περιβάλλον αναφοράς
- Διακομιστής Supermicro:
- CPU Intel® Xeon® E5-2683 v3 @ 2.00 GHz
- 2 υποδοχές / 28 πυρήνες / 56 κλωστές
- Μνήμη: 256 GB μνήμης RAM
- Αποθήκευση: Samsung SM863 1.9TB Enterprise SSD
- Σύστημα αρχείων: ext4/xfs
- ΛΣ: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: έκδοση 11
Δοκιμές αναφοράς
Αντί να χρησιμοποιήσουμε κάποιο σύνολο δεδομένων που δημιουργείται από μηχανή για αυτήν τη δοκιμή, χρησιμοποιήσαμε τα δεδομένα "Παραγωγικότητα κατά χρόνο αναφοράς χρόνου χειριστή" από το 1987 έως το 2018. Μπορείτε να αποκτήσετε πρόσβαση στα δεδομένα .
Το μέγεθος της βάσης δεδομένων είναι 85 GB, παρέχοντας έναν πίνακα 109 στηλών.
Ερωτήματα συγκριτικής αξιολόγησης
Εδώ είναι τα ερωτήματα που χρησιμοποίησα για τη σύγκριση των ClickHouse, clickhousedb_fdw και PostgreSQL.
Q#
Το ερώτημα περιέχει συγκεντρωτικά στοιχεία και ομάδα κατά
Q1
ΕΠΙΛΕΞΤΕ Ημέρα της Εβδομάδας, μετρήστε(*) ΩΣ c ΑΠΟ την ώρα ΠΟΥ Έτος >= 2000 ΚΑΙ Έτος <= 2008 ΟΜΑΔΑ ΑΝΑ ΗΜΕΡΑ ΤΗΣ ΕΒΔΟΜΑΔΑΣ ΤΑΞΗ ΚΑΤΑ c ΠΕΡΙΦ.
Q2
ΕΠΙΛΕΞΤΕ DayOfWeek, μετρήστε(*) ΩΣ c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP B 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
ΕΠΙΛΕΞΤΕ Φορέα, μετρήστε() FROM ontime WHERE DepDelay>10 AND Year = 2007 ΟΜΑΔΑ ΑΝΑ ΠΑΡΑΓΩΓΗ ΑΝΑ πλήθος() DESC;
Q5
ΕΠΙΛΟΓΗ a.Carrier, c, c2, c1000/c2 ως 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 σε a.Carrier=b.ORDERCa BY c3 DESC;
Q6
ΕΠΙΛΟΓΗ a.Carrier, c, c2, c1000/c2 ως c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) μια ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 BRO Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Έτος >= 2000 ΚΑΙ Έτος <= 2008 GROUP BY Carrier;
Q8
ΕΠΙΛΕΞΤΕ Έτος, μέσος όρος(DepDelay) FROM ontime GROUP BY BY?
Q9
επιλέξτε Έτος, μετρήστε(*) ως c1 από ομάδα χρόνου ανά Έτος.
Q10
ΕΠΙΛΟΓΗ μέσου (cnt) FROM (ΕΠΙΛΟΓΗ Έτος, Μήνας, μέτρηση(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY BY Year, Month) a;
Q11
επιλέξτε το μέσο όρο (c1) από (επιλέξτε Έτος, Μήνας, μέτρηση(*) ως c1 από την ομάδα χρόνου ανά Έτος, Μήνας) a;
Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
ΕΠΙΛΟΓΗ OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Το ερώτημα περιέχει συνδέσεις
Q14
SELECT a.Year, c1/c2 FROM ( επιλέξτε Year, count()1000 ως c1 από ontime WHERE DepDelay>10 GROUP BY THE Year) μια ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ (επιλέξτε Έτος, μετρήστε(*) ως c2 από ontime GROUP BY BY Year ) b σε a.Year=b.Year ORDER BY A.Year;
Q15
SELECT a."Year", c1/c2 FROM ( επιλέξτε "Year", count()1000 ως c1 FROM fontime WHERE "DepDelay">10 GROUP BY BY "Year") μια ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ (επιλέξτε "Year", μετρήστε(*) ως c2 FROM fontime GROUP BY BY "Year" ) b on a."Year"=b. "Ετος";
Πίνακας-1: Ερωτήματα που χρησιμοποιούνται στο σημείο αναφοράς
Εκτελέσεις ερωτημάτων
Ακολουθούν τα αποτελέσματα καθενός από τα ερωτήματα όταν εκτελούνται σε διαφορετικές ρυθμίσεις βάσης δεδομένων: PostgreSQL με και χωρίς ευρετήρια, εγγενές ClickHouse και clickhousedb_fdw. Ο χρόνος εμφανίζεται σε χιλιοστά του δευτερολέπτου.
Q#
PostgreSQL
PostgreSQL (Σε ευρετήριο)
Κάντε κλικ στο σπίτι
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
Πίνακας-1: Χρόνος που απαιτείται για την εκτέλεση των ερωτημάτων που χρησιμοποιούνται στο σημείο αναφοράς
Προβολή αποτελεσμάτων
Το γράφημα δείχνει τον χρόνο εκτέλεσης του ερωτήματος σε χιλιοστά του δευτερολέπτου, ο άξονας X δείχνει τον αριθμό του ερωτήματος από τους παραπάνω πίνακες και ο άξονας Y δείχνει τον χρόνο εκτέλεσης σε χιλιοστά του δευτερολέπτου. Εμφανίζονται τα αποτελέσματα του ClickHouse και τα δεδομένα που ανακτήθηκαν από το postgres χρησιμοποιώντας το clickhousedb_fdw. Από τον πίνακα μπορείτε να δείτε ότι υπάρχει τεράστια διαφορά μεταξύ PostgreSQL και ClickHouse, αλλά ελάχιστη διαφορά μεταξύ ClickHouse και clickhousedb_fdw.
Αυτό το γράφημα δείχνει τη διαφορά μεταξύ ClickhouseDB και clickhousedb_fdw. Στα περισσότερα ερωτήματα, τα γενικά έξοδα FDW δεν είναι τόσο υψηλά και είναι ελάχιστα σημαντικά εκτός από το Q12. Αυτό το ερώτημα περιλαμβάνει συνδέσεις και μια ρήτρα ORDER BY. Λόγω της ρήτρας ORDER BY GROUP/BY, ORDER BY δεν εμφανίζεται στο ClickHouse.
Στον Πίνακα 2 βλέπουμε το χρονικό άλμα στα ερωτήματα Q12 και Q13. Και πάλι, αυτό προκαλείται από την ρήτρα ORDER BY. Για να το επιβεβαιώσω, έτρεξα τα ερωτήματα Q-14 και Q-15 με και χωρίς την ρήτρα ORDER BY. Χωρίς τον όρο ORDER BY ο χρόνος ολοκλήρωσης είναι 259ms και με τον όρο ORDER BY είναι 1364212. Για τον εντοπισμό σφαλμάτων αυτού του ερωτήματος εξηγώ και τα δύο ερωτήματα και εδώ είναι τα αποτελέσματα της εξήγησης.
Ε15: Χωρίς ρήτρα 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";Ε15: Ερώτημα Χωρίς ΠΑΡΑΓΓΕΛΙΑ ΚΑΤΑ ρήτρα
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)Ε14: Ερώτημα με ORDER BY Clauuse
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";Ε14: Σχέδιο ερωτήματος με ρήτρα 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)Παραγωγή
Τα αποτελέσματα αυτών των πειραμάτων δείχνουν ότι το ClickHouse προσφέρει πραγματικά καλή απόδοση και το clickhousedb_fdw προσφέρει τα οφέλη απόδοσης του ClickHouse από την PostgreSQL. Αν και υπάρχει κάποια επιβάρυνση κατά τη χρήση του clickhousedb_fdw, είναι αμελητέα και συγκρίσιμη με την απόδοση που επιτυγχάνεται με την εγγενή εκτέλεση στη βάση δεδομένων ClickHouse. Αυτό επιβεβαιώνει επίσης ότι το fdw στην PostgreSQL παρέχει εξαιρετικά αποτελέσματα.
Συνομιλία μέσω Telegram μέσω Clickhouse
Συνομιλία μέσω Telegram χρησιμοποιώντας PostgreSQL
Πηγή: www.habr.com