ProHoster > Blog > Administration > Tests de performance des requĂȘtes analytiques dans PostgreSQL, ClickHouse et clickhousedb_fdw (PostgreSQL)

Tests de performance des requĂȘtes analytiques dans PostgreSQL, ClickHouse et clickhousedb_fdw (PostgreSQL)

Dans cette étude, je souhaitais examiner les améliorations de performance que l'on peut obtenir en utilisant la source de données ClickHouse plutÎt que PostgreSQL. Je connais les avantages en termes de performances que j'obtiens en utilisant ClickHouse. Ces avantages seront-ils maintenus si j'accÚde à ClickHouse depuis PostgreSQL grùce à un environnement de données étranger (FDW) ?

Les environnements de bases de donnĂ©es Ă©tudiĂ©s sont PostgreSQL v11, clickhousedb_fdw et la base de donnĂ©es ClickHouse. Au final, depuis PostgreSQL v11, nous allons exĂ©cuter diffĂ©rentes requĂȘtes SQL, routĂ©es Ă  travers notre clickhousedb_fdw vers la base de donnĂ©es ClickHouse. Ensuite, nous verrons comment la performance du FDW se compare Ă  celle des mĂȘmes requĂȘtes exĂ©cutĂ©es dans le PostgreSQL natif et le ClickHouse natif.

Base de données ClickHouse

ClickHouse est un systÚme de gestion de bases de données orienté colonnes, open source, qui peut atteindre des performances de 100 à 1000 fois supérieures à celles des approches traditionnelles des bases de données, capable de traiter plus d'un milliard de lignes en moins d'une seconde.

Clickhousedb_fdw

clickhousedb_fdw est un environnement de données étranger pour la base de données ClickHouse, ou FDW, qui est un projet open source de Percona. Voici le lien vers le dépÎt du projet GitHub.

En mars, j'ai Ă©crit un blog qui vous explique davantage notre FDW.

Comme vous le verrez, cela fournit un FDW pour ClickHouse, qui permet de faire SELECT from, et INSERT INTO, la base de données ClickHouse depuis le serveur PostgreSQL v11.

Le FDW prend en charge des fonctions avancées, telles que l'agrégation et les jointures. Cela améliore considérablement les performances en utilisant les ressources du serveur distant pour ces opérations gourmandes en ressources.

Environnement de benchmark

  • Serveur Supermicro :
    • Processeur IntelÂź XeonÂź CPU E5-2683 v3 @ 2.00GHz
    • 2 sockets / 28 cƓurs / 56 threads
    • MĂ©moire : 256 Go de RAM
    • Stockage : SSD d'entreprise Samsung SM863 de 1,9 To
    • SystĂšme de fichiers : ext4/xfs
  • OS : Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL : version 11

Tests de benchmark

Au lieu d'utiliser un jeu de données généré par la machine pour ce test, nous avons utilisé les données « Performance par temps, rapporté sur le temps de fonctionnement de l'opérateur » de 1987 à 2018. Vous pouvez accéder aux données avec notre script disponible ici.

La taille de la base de données est de 85 Go, avec une seule table de 109 colonnes.

RequĂȘtes de benchmark

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 groupe par

Q1
SÉLECTIONNEZ DayOfWeek, COUNT(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
SÉLECTIONNEZ DayOfWeek, COUNT(*) AS c FROM ontime WHERE DepDelay > 10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q3
SÉLECTIONNEZ 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
SÉLECTIONNEZ Carrier, COUNT() FROM ontime WHERE DepDelay > 10 AND Year = 2007 GROUP BY Carrier ORDER BY COUNT() DESC;

Q5
SÉLECTIONNEZ a.Carrier, c, c2, c1000/c2 AS c3 FROM ( SÉLECTIONNEZ Carrier, COUNT() AS c FROM ontime WHERE DepDelay > 10 AND Year = 2007 GROUP BY Carrier ) a INNER JOIN ( SÉLECTIONNEZ Carrier, COUNT(*) AS c2 FROM ontime WHERE Year = 2007 GROUP BY Carrier ) b ON a.Carrier = b.Carrier ORDER BY c3 DESC;

Q6
SÉLECTIONNEZ a.Carrier, c, c2, c1000/c2 AS c3 FROM ( SÉLECTIONNEZ Carrier, COUNT() AS c FROM ontime WHERE DepDelay > 10 AND Year >= 2000 AND Year = 2000 AND Year <= 2008 GROUP BY Carrier ) b ON a.Carrier = b.Carrier ORDER BY c3 DESC;

Q7
SÉLECTIONNEZ Carrier, AVG(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;

Q8
SÉLECTIONNEZ Year, AVG(DepDelay) FROM ontime GROUP BY Year;

Q9
SÉLECTIONNEZ Year, COUNT(*) AS c1 FROM ontime GROUP BY Year;

Q10
SÉLECTIONNEZ AVG(cnt) FROM (SÉLECTIONNEZ Year, Month, COUNT(*) AS cnt FROM ontime WHERE DepDel15 = 1 GROUP BY Year, Month) a;

Q11
SÉLECTIONNEZ AVG(c1) FROM (SÉLECTIONNEZ Year, Month, COUNT(*) AS c1 FROM ontime GROUP BY Year, Month) a;

Q12
SÉLECTIONNEZ OriginCityName, DestCityName, COUNT(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;

Q13
SÉLECTIONNEZ OriginCityName, COUNT(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;

La requĂȘte contient des jointures

Q14
SÉLECTIONNEZ a.Year, c1/c2 FROM ( SÉLECTIONNEZ Year, COUNT()1000 AS c1 FROM ontime WHERE DepDelay > 10 GROUP BY Year) a INNER JOIN (SÉLECTIONNEZ Year, COUNT(*) AS c2 FROM ontime GROUP BY Year) b ON a.Year = b.Year ORDER BY a.Year;

Q15
SÉLECTIONNEZ a.Year, c1/c2 FROM ( SÉLECTIONNEZ Year, COUNT()1000 AS c1 FROM fontime WHERE DepDelay > 10 GROUP BY Year) a INNER JOIN (SÉLECTIONNEZ Year, COUNT(*) AS c2 FROM fontime GROUP BY Year) b ON a.Year = b.Year;

Table-1 : RequĂȘtes utilisĂ©es dans l'Ă©valuation

ExĂ©cutions de requĂȘtes

Voici les rĂ©sultats de chaque requĂȘte exĂ©cutĂ©e dans diffĂ©rentes configurations de base de donnĂ©es : PostgreSQL avec et sans index, ClickHouse propre et clickhousedb_fdw. Le temps est affichĂ© en millisecondes.

Q#
PostgreSQL
PostgreSQL (Indexed)
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

Table-1 : Temps nĂ©cessaire pour exĂ©cuter les requĂȘtes utilisĂ©es dans l'Ă©valuation

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 la requĂȘte dans les tableaux ci-dessus, et l'axe Y montre le temps d'exĂ©cution en millisecondes. Les rĂ©sultats de ClickHouse et les donnĂ©es obtenues Ă  partir de PostgreSQL via clickhousedb_fdw sont affichĂ©s. Le tableau montre qu'il existe une Ă©norme diffĂ©rence entre PostgreSQL et ClickHouse, mais une diffĂ©rence minimale entre ClickHouse et clickhousedb_fdw.

Tests de performance des requĂȘtes analytiques dans PostgreSQL, ClickHouse et clickhousedb_fdw (PostgreSQL)

Ce graphique montre la diffĂ©rence entre ClickhouseDB et clickhousedb_fdw. Pour la plupart des requĂȘtes, les frais gĂ©nĂ©raux de FDW ne sont pas si importants et Ă  peine significatifs, sauf pour Q12. Cette requĂȘte inclut des jointures et une clause ORDER BY. En raison de la clause ORDER BY GROUP/BY, le ORDER BY n'est pas omis jusqu'Ă  ClickHouse.

Dans le tableau 2, nous observons une augmentation du temps des requĂȘtes Q12 et Q13. Je le rĂ©pĂšte, cela est dĂ» Ă  la clause ORDER BY. Pour le confirmer, 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, tandis qu'avec la clause ORDER BY, il est de 1364212. Pour dĂ©boguer cette requĂȘte, j'explique les deux requĂȘtes ici, et les rĂ©sultats de l'explication sont fournis.

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

PLAN DE REQUÊTE                                                      
Hash Join  (coût=2250.00..128516.06 lignes=50000000 largeur=12)  
Sortie : fontime."Year", (((count(*) * 1000)) / b.c2)  
Unique interne : vrai   Condition de hachage : (fontime."Year" = b."Year")  
->  Scan à distance  (coût=1.00..-1.00 lignes=100000 largeur=12)        
Sortie : fontime."Year", ((count(*) * 1000))        
Relations : Agrégation sur (fontime)        
SQL Ă  distance : SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"  
->  Hachage  (coût=999.00..999.00 lignes=100000 largeur=12)        
Sortie : b.c2, b."Year"        
->  Scan de sous-requĂȘte sur b  (coĂ»t=1.00..999.00 lignes=100000 largeur=12)              
Sortie : b.c2, b."Year"              
->  Scan à distance  (coût=1.00..-1.00 lignes=100000 largeur=12)                    
Sortie : fontime_1."Year", (count(*))                    
Relations : Agrégation sur (fontime)                    
SQL Ă  distance : SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 lignes)

Q14 : RequĂȘte avec 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 clause ORDER BY

PLAN DE REQUÊTE 
Merge Join  (coût=2.00..628498.02 lignes=50000000 largeur=12)   
Sortie : fontime."Year", (((count(*) * 1000)) / (count(*)))   
Unique interne : vrai   Condition de fusion : (fontime."Year" = fontime_1."Year")   
->  GroupAggregate  (coût=1.00..499.01 lignes=1 largeur=12)        
Sortie : fontime."Year", (count(*) * 1000)         
Clé de groupe : fontime."Year"         
->  Scan à distance sur public.fontime  (coût=1.00..-1.00 lignes=100000 largeur=4)               
SQL Ă  distance : SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Year" ASC   
->  GroupAggregate  (coût=1.00..499.01 lignes=1 largeur=12)         
Sortie : fontime_1."Year", count(*)         Clé de groupe : fontime_1."Year"         
->  Scan à distance sur public.fontime fontime_1  (coût=1.00..-1.00 lignes=100000 largeur=4) 
              
SQL Ă  distance : SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 lignes)

Sortie

Les résultats de ces expériences montrent que ClickHouse offre une performance réellement excellente, et que clickhousedb_fdw propose les avantages de la performance de ClickHouse dans PostgreSQL. Bien qu'il y ait certains coûts associés à l'utilisation de clickhousedb_fdw, ceux-ci sont minimes et comparables à la performance atteinte lors d'une exécution native dans la base de données ClickHouse. Cela confirme également que fdw dans PostgreSQL fournit des résultats remarquables.

Chat Telegram sur Clickhouse https://t.me/clickhouse_ru
Chat Telegram sur PostgreSQL https://t.me/pgsql

Source : habr.com

Acheter un hĂ©bergement fiable pour les sites avec protection DDoS, serveurs VPS VDS đŸ”„ Acheter un hĂ©bergement fiable pour les sites avec protection DDoS, serveurs VPS VDS | ProHoster