ProHoster > Blog > Beheer > Performance testing of analytical queries in PostgreSQL, ClickHouse, and clickhousedb_fdw (PostgreSQL)

Performance testing of analytical queries in PostgreSQL, ClickHouse, and clickhousedb_fdw (PostgreSQL)

In dit onderzoek wilde ik kijken welke prestatieverbeteringen mogelijk zijn door ClickHouse als gegevensbron te gebruiken in plaats van PostgreSQL. Ik weet welke prestatievoordelen ik krijg bij het gebruik van ClickHouse. Zullen deze voordelen behouden blijven als ik toegang krijg tot ClickHouse vanuit PostgreSQL via een Foreign Data Wrapper (FDW)?

De onderzochte databases zijn PostgreSQL v11, clickhousedb_fdw en de ClickHouse-database. Uiteindelijk zullen we vanuit PostgreSQL v11 verschillende SQL-query's uitvoeren, die via onze clickhousedb_fdw naar de ClickHouse-database worden gerouteerd. Vervolgens zullen we zien hoe de prestaties van FDW zich verhouden tot dezelfde query's die in de native PostgreSQL en native ClickHouse worden uitgevoerd.

Clickhouse-database

ClickHouse is een open-source kolomgeoriënteerd databasesysteem dat tot 100-1000 keer sneller kan presteren dan traditionele databasebenaderingen en in staat is om meer dan een miljard rijen in minder dan een seconde te verwerken.

Clickhousedb_fdw

clickhousedb_fdw is een Foreign Data Wrapper voor de ClickHouse-database en is een open-source project van Percona. Hier is de link naar de GitHub-repository van het project.

In maart schreef ik een blog die meer vertelt over onze FDW.

Zoals je zult zien, biedt dit FDW voor ClickHouse, waarmee je SELECT from en INSERT INTO de ClickHouse-database vanuit de PostgreSQL v11-server kunt doen.

FDW ondersteunt geavanceerde functies zoals aggregate en join. Dit verhoogt de prestaties aanzienlijk door de resources van de externe server te gebruiken voor deze resource-intensieve bewerkingen.

Benchmarkomgeving

  • Supermicro-server:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 sockets / 28 cores / 56 threads
    • Geheugen: 256GB RAM
    • Opslag: Samsung SM863 1.9TB Enterprise SSD
    • Bestandssysteem: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: versie 11

Benchmarktests

In plaats van een dataset die door een machine is gegenereerd te gebruiken voor deze test, hebben we gegevens van 'Prestatie in de tijd, gerapporteerd over de operationele tijd' van 1987 tot 2018 gebruikt. Je kunt toegang krijgen tot de gegevens met ons script, dat hier beschikbaar is.

De grootte van de database is 85 GB en bevat één tabel met 109 kolommen.

Benchmarkquery's

Hier zijn de query's die ik heb gebruikt om ClickHouse, clickhousedb_fdw en PostgreSQL te vergelijken.

Q#
Query Bevat Aggregaten en Groeperen Op

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 as 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 on a.Carrier=b.Carrier ORDER BY c3 DESC;

Q6
SELECT a.Carrier, c, c2, c1000/c2 as c3 FROM ( SELECT 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
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
select Year, count(*) as c1 from ontime group by Year;

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;

Query Contains Joins

Q14
SELECT a.Year, c1/c2 FROM ( select Year, count()1000 as c1 from ontime WHERE DepDelay > 10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from 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 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";

Table-1: Queries used in benchmark

Query executions

Hier zijn de resultaten van elke query bij uitvoering in verschillende database-instellingen: PostgreSQL met en zonder indexen, eigen ClickHouse, en clickhousedb_fdw. De tijd wordt weergegeven in milliseconden.

Q#
PostgreSQL
PostgreSQL (Geïndexeerd)
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: Tijdsduur voor het uitvoeren van de queries gebruikt in benchmark

Bekijk resultaten

De grafiek toont de uitvoeringstijd van de query in milliseconden; de X-as toont het nummer van de query uit de hierboven genoemde tabellen, terwijl de Y-as de uitvoeringstijd in milliseconden toont. De resultaten van ClickHouse en de gegevens verkregen uit Postgres via clickhousedb_fdw zijn weergegeven. Uit de tabel blijkt dat er een enorm verschil is tussen PostgreSQL en ClickHouse, maar een minimaal verschil tussen ClickHouse en clickhousedb_fdw.

Performance testing of analytical queries in PostgreSQL, ClickHouse, and clickhousedb_fdw (PostgreSQL)

Deze grafiek toont het verschil tussen ClickhouseDB en clickhousedb_fdw. In de meeste queries zijn de overheadkosten van FDW niet zo groot en nauwelijks merkbaar, behalve bij Q12. Deze query bevat joins en een ORDER BY-clausule. Vanwege de ORDER BY GROUP/BY en ORDER BY worden niet omgezet naar ClickHouse.

In tabel 2 zien we een sprongetje in de tijden van de aanvragen Q12 en Q13. Ter herinnering, dit wordt veroorzaakt door de ORDER BY-clausule. Om dit te bevestigen, heb ik de aanvragen Q-14 en Q-15 uitgevoerd, met en zonder de ORDER BY-clausule. Zonder de ORDER BY-clausule bedraagt de voltooiingstijd 259 ms, en met de ORDER BY-clausule is dat 1364212. Voor het debuggen van deze aanvraag leg ik beide aanvragen uit, en hier zijn de resultaten van de uitleg.

Q15: Zonder ORDER BY Clausule

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: Vraag Zonder ORDER BY Clausule

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: Vraag Met ORDER BY Clausule

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: Vraagplan met ORDER BY Clausule

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)

Uitslag

De resultaten van deze experimenten tonen aan dat ClickHouse echt goede prestaties biedt, terwijl clickhousedb_fdw de prestatievoordelen van ClickHouse in PostgreSQL brengt. Hoewel er bij het gebruik van clickhousedb_fdw enkele overheadkosten zijn, zijn deze verwaarloosbaar en vergelijkbaar met de prestaties die worden behaald bij een natuurlijke uitvoering in de ClickHouse-database. Dit bevestigt ook dat fdw in PostgreSQL opmerkelijke resultaten oplevert.

Telegram-chat over ClickHouse https://t.me/clickhouse_ru
Telegram-chat over PostgreSQL https://t.me/pgsql

Bron: habr.com

Koop betrouwbare webhosting met bescherming tegen DDoS, VPS VDS servers 🔥 Koop betrouwbare webhosting met bescherming tegen DDoS, VPS VDS servers | ProHoster