Dans cette étude, je voulais voir quelles améliorations de performances pourraient être obtenues en utilisant une source de données ClickHouse plutôt que PostgreSQL. Je connais les avantages de productivité que je tire de l'utilisation de ClickHouse. Ces avantages continueront-ils si j'accède à ClickHouse depuis PostgreSQL à l'aide d'un Foreign Data Wrapper (FDW) ?
Les environnements de bases de données étudiés sont PostgreSQL v11, clickhousedb_fdw et la base de données ClickHouse. En fin de compte, à partir de PostgreSQL v11, nous exécuterons diverses requêtes SQL acheminées via notre clickhousedb_fdw vers la base de données ClickHouse. Nous verrons ensuite comment les performances de FDW se comparent aux mêmes requêtes exécutées dans PostgreSQL natif et ClickHouse natif.
Base de données Clickhouse
ClickHouse est un système de gestion de base de données en colonnes open source qui peut atteindre des performances 100 à 1000 XNUMX fois plus rapides que les approches de base de données traditionnelles, capable de traiter plus d'un milliard de lignes en moins d'une seconde.
Cliquezhousedb_fdw
clickhousedb_fdw - Le wrapper de données externe pour la base de données ClickHouse, ou FDW, est un projet open source de Percona. .
.
Comme vous le verrez, cela fournit un FDW pour ClickHouse qui permet de sélectionner et d'insérer dans la base de données ClickHouse à partir du serveur PostgreSQL v11.
FDW prend en charge des fonctionnalités avancées telles que l'agrégation et la jointure. Cela améliore considérablement les performances en utilisant les ressources du serveur distant pour ces opérations gourmandes en ressources.
Environnement de référence
- Serveur Supermicro :
- Processeur Intel® Xeon® E5-2683 v3 à 2.00 GHz
- 2 sockets / 28 cœurs / 56 threads
- Mémoire vive: RAM de 256GB
- Stockage : SSD d'entreprise Samsung SM863 1.9 To
- Système de fichiers : ext4/xfs
- Système d'exploitation : Linux smblade01 4.15.0-42-générique #45~16.04.1-Ubuntu
- PostgreSQL : version 11
Tests de référence
Au lieu d'utiliser un ensemble de données générées par la machine pour ce test, nous avons utilisé les données « Productivité selon le temps déclaré par l'opérateur » de 1987 à 2018. Vous pouvez accéder aux données .
La taille de la base de données est de 85 Go, fournissant une table de 109 colonnes.
Requêtes de référence
Voici les requêtes que j'ai utilisées pour comparer ClickHouse, clickhousedb_fdw et PostgreSQL.
Q#
La requête contient des agrégats et un regroupement par
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Année >= 2000 AND Année <= 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 Origine, count(*) AS c FROM ontime WHERE DepDelay>10 AND Année >= 2000 AND Année <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SELECT Transporteur, compte() FROM ontime OÙ DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count () DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 comme 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 sur a.Carrier=b.Carrier ORDER PAR c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 comme 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 Transporteur ) b sur a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Année >= 2000 AND Année <= 2008 GROUP BY Carrier ;
Q8
SELECT Année, moyenne (DepDelay) FROM ontime GROUP BY Année ;
Q9
sélectionnez Année, comptez (*) comme c1 du groupe ontime par année ;
Q10
SELECT avg(cnt) FROM (SELECT Année,Mois,compte(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Année,Mois) a;
Q11
sélectionnez avg(c1) dans (sélectionnez Année, Mois, compte (*) comme c1 dans le groupe de disponibilité par Année, Mois) 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;
La requête contient des jointures
Q14
SELECT a.Year, c1/c2 FROM (sélectionnez Year, count()1000 comme c1 à partir de ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (sélectionnez Année, comptez (*) comme c2 à partir de ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a."Année", c1/c2 FROM (sélectionnez "Année", compte()1000 comme c1 FROM fontime WHERE "DepDelay">10 GROUP BY "Year") a INNER JOIN (sélectionnez "Year", comptez (*) comme c2 FROM fontime GROUP BY "Year") b sur a."Year"=b. "Année";
Tableau 1 : Requêtes utilisées dans le benchmark
Exécutions de requêtes
Voici les résultats de chacune des requêtes exécutées dans différents paramètres de base de données : PostgreSQL avec et sans index, ClickHouse natif et clickhousedb_fdw. Le temps est affiché en millisecondes.
Q#
PostgreSQL
PostgreSQL (indexé)
Cliquez Maison
clichousedb_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
Tableau 1 : Temps nécessaire à l'exécution des requêtes utilisées dans le benchmark
Voir les résultats
Le graphique montre le temps d'exécution de la requête en millisecondes, l'axe X montre le numéro de requête des tableaux ci-dessus et l'axe Y montre le temps d'exécution en millisecondes. Les résultats ClickHouse et les données récupérées de postgres à l'aide de clickhousedb_fdw sont affichés. Dans le tableau, vous pouvez voir qu'il existe une énorme différence entre PostgreSQL et ClickHouse, mais une différence minime entre ClickHouse et clickhousedb_fdw.
Ce graphique montre la différence entre ClickhouseDB et clickhousedb_fdw. Dans la plupart des requêtes, la surcharge FDW n’est pas si élevée et n’est guère significative, sauf pour Q12. Cette requête inclut des jointures et une clause ORDER BY. En raison de la clause ORDER BY GROUP/BY, ORDER BY ne descend pas vers ClickHouse.
Dans le tableau 2, nous voyons le saut temporel dans les requêtes Q12 et Q13. Encore une fois, cela est dû à la clause ORDER BY. Pour confirmer cela, j'ai exécuté les requêtes Q-14 et Q-15 avec et sans la clause ORDER BY. Sans la clause ORDER BY, le temps d'exécution est de 259 ms et avec la clause ORDER BY, il est de 1364212. Pour déboguer cette requête, j'explique les deux requêtes et voici les résultats de l'explication.
Q15 : Sans clause 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";Q15 : Requête sans clause 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)Q14 : Requête avec la clause 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";Q14 : Plan de requête avec la clause 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)conclusion
Les résultats de ces expériences montrent que ClickHouse offre de très bonnes performances et que clickhousedb_fdw offre les avantages en termes de performances de ClickHouse de PostgreSQL. Bien qu'il y ait une certaine surcharge lors de l'utilisation de clickhousedb_fdw, elle est négligeable et comparable aux performances obtenues en s'exécutant de manière native sur la base de données ClickHouse. Cela confirme également que fdw dans PostgreSQL fournit d'excellents résultats.
Chat par télégramme via Clickhouse
Chat par télégramme utilisant PostgreSQL
Source: habr.com