ProHoster > Blog > Adminisztráció > Teljesítménytesztelés analitikai lekérdezésekben PostgreSQL, ClickHouse és clickhousedb_fdw (PostgreSQL)

Teljesítménytesztelés analitikai lekérdezésekben PostgreSQL, ClickHouse és clickhousedb_fdw (PostgreSQL)

Ebben a tanulmányban azt szerettem volna megvizsgálni, hogy milyen teljesítményjavulás érhető el, ha a ClickHouse adatforrást használom a PostgreSQL helyett. Tudom, hogy milyen teljesítményelőnyöket biztosít a ClickHouse használata. Ezek az előnyök megmaradnak, ha a PostgreSQL-ből külső adatburok (FDW) segítségével érek el ClickHouse-t?

Az egyszer megvizsgált adatbázisok a PostgreSQL v11, a clickhousedb_fdw és a ClickHouse adatbázis. Végül a PostgreSQL v11-ről különböző SQL-lekérdezéseket fogunk futtatni, amelyeket a clickhousedb_fdw-n keresztül irányítunk a ClickHouse adatbázisba. Ezután megnézzük, hogyan hasonlít a FDW teljesítménye ugyanazokkal a lekérdezésekkel, amelyek natívan a PostgreSQL-ben és a ClickHouse-ban futnak.

Clickhouse adatbázis

A ClickHouse egy nyílt forráskódú oszlop alapú adatbázis-kezelő rendszer, amely 100-1000-szer gyorsabb teljesítményt képes elérni, mint a hagyományos adatbázis-megközelítések, és képes több mint egymilliárd sort feldolgozni kevesebb, mint egy másodperc alatt.

Clickhousedb_fdw

A clickhousedb_fdw a ClickHouse adatbázis külső adatburokja (FDW), amely a Percona nyílt forráskódú projektje. Itt van egy link a projekt GitHub repositoryjához.

Márciusban írtam egy blogbejegyzést, amely többet mesél a FDW-nkről.

Ahogy láthatja, ez egy FDW-t biztosít a ClickHouse számára, amely lehetővé teszi a SELECT from és az INSERT INTO parancsok használatát a ClickHouse adatbázisban a PostgreSQL v11-ről.

A FDW fejlett funkciókat is támogat, mint az aggregate és a join. Ez jelentősen növeli a teljesítményt azáltal, hogy a távoli szerver erőforrásait használja ezekhez a munkaigényes műveletekhez.

Benchmark környezet

  • Supermicro szerver:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 socket / 28 mag / 56 szál
    • Memória: 256GB RAM
    • Tárhely: Samsung SM863 1.9TB Enterprise SSD
    • Fájlrendszer: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: 11-es verzió

Benchmark tesztek

A teszthez nem használtunk valamilyen gép által generált adatokat, hanem a "Teljesítmény időben, amely az üzemeltető munkaidőjáról számol be" 1987 és 2018 között. Hozzáférhet az adatokhoz a mi skriptünkkel, amely itt elérhető.

Az adatbázis mérete 85 GB, amelyben egy táblázat található 109 oszlopból.

Benchmark lekérdezések

Itt vannak a lekérdezések, amelyeket a ClickHouse, a clickhousedb_fdw és a PostgreSQL összehasonlításához használtam.

Q#
A lekérdezés aggregátumokat és csoportosítást tartalmaz

Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
Válassza ki a DayOfWeek-t, az összesített számláló (c) értékét az ontime táblából, ahol a DepDelay > 10, és az Év >= 2000 és Év <= 2008, csoportosítva a DayOfWeek szerint, a szám szerint csökkenő sorrendben;

Q3
Válassza ki az Origin-t, az összesített számláló (c) értékét az ontime táblából, ahol a DepDelay > 10, és az Év >= 2000 és Év <= 2008, csoportosítva az Origin szerint, a szám szerint csökkenő sorrendben, maximum 10;

Q4
Válassza ki a Carrier-t, az összesített számláló () az ontime táblából, ahol a DepDelay > 10, és az Év = 2007, csoportosítva a Carrier szerint, a szám szerint csökkenő sorrendben;Q5

Válassza ki a a.Carrier-t, c, c2, c
1000/c2 mint c3 az (Válassza ki a Carrier-t, az összesített számláló () értékét az ontime táblából, ahol a DepDelay > 10, és az Év = 2007, csoportosítva a Carrier szerint) a INNER JOIN (Válassza ki a Carrier-t, az összesített számláló (c2) értékét az ontime táblából, ahol az Év = 2007, csoportosítva a Carrier szerint)b on a.Carrier=b.Carrier, csökkenő c3 sorrendben;Q6

) mint c az ontime táblából, ahol a DepDelay > 10, és az Év >= 2000, és Év = 2000, és Év <= 2008, csoportosítva a Carrier szerint) b on a.Carrier=b.Carrier, csökkenő c3 sorrendben;
1000/c2 mint c3 az (Válassza ki a Carrier-t, az összesített számláló () értékét az ontime táblából, ahol a DepDelay > 10, és az Év = 2007, csoportosítva a Carrier szerint) a INNER JOIN (Válassza ki a Carrier-t, az összesített számláló (c2) értékét az ontime táblából, ahol az Év = 2007, csoportosítva a Carrier szerint)b on a.Carrier=b.Carrier, csökkenő c3 sorrendben;Q7

Válassza ki a Carrier-t, az átlagos DepDelay * 1000 értékét mint c3 az ontime táblából, ahol az Év >= 2000, és Év <= 2008, csoportosítva a Carrier szerint;
Q8

Válassza ki az Évet, az átlagos DepDelay-t az ontime táblából, csoportosítva az Év szerint;
Q9

Válassza ki az Évet, az összesített számláló (c1) értékét az ontime táblából, csoportosítva az Év szerint;
Q10

Válassza ki az átlagos cnt értékét az (Válassza ki az Évet, Hónapot, az összesített számláló (cnt) értékét az ontime táblából, ahol a DepDel15 = 1, csoportosítva az Év, Hónap szerint) a;
Q11

Válassza ki az átlagos c1 értékét az (Válassza ki az Évet, Hónapot, az összesített számláló (c1) értékét az ontime táblából, csoportosítva az Év, Hónap szerint) a;
Q12

Válassza ki az OriginCityName-t, a DestCityName-t, az összesített számláló (c) értékét az ontime táblából, csoportosítva az OriginCityName, DestCityName szerint, a szám szerint csökkenő sorrendben, maximum 10;
Q13

Válassza ki az OriginCityName-t, az összesített számláló (c) értékét az ontime táblából, csoportosítva az OriginCityName szerint, a szám szerint csökkenő sorrendben, maximum 10;
Query tartalmaz csatlakozásokat

Q14

Válassza ki az a.Year-t, c1/c2 értékét az (Válassza ki az Évet, az összesített számláló (
1000 mint c1 az ontime táblából, ahol a DepDelay > 10, csoportosítva az Év szerint) a INNER JOIN (Válassza ki az Évet, az összesített számláló (c2) értékét az ontime táblából, csoportosítva az Év szerint) b on a.Year=b.Year, a.Year szerint csökkenő sorrendben;)Q15

Válassza ki az a.Year-t, c1/c2 értékét az (Válassza ki az Évet, az összesített számláló (
1000 mint c1 az ontime táblából, ahol a DepDelay > 10, csoportosítva az Év szerint) a INNER JOIN (Válassza ki az Évet, az összesített számláló (c2) értékét az ontime táblából, csoportosítva az Év szerint) b on a.Year=b.Year;)Table-1: A benchmarkban használt lekérdezések

Lekérdezések végrehajtása

Itt láthatók a lekérdezések eredményei különböző adatbázis-beállításokkal: PostgreSQL indexekkel és anélkül, saját ClickHouse és clickhousedb_fdw. Az idő millikondratumban van megadva.

PostgreSQL (Rendezett)

Q#
PostgreSQL
ClickHouse
clickhousedb_fdw
Table-1: Az elkészített lekérdezések végrehajtásához szükséges idő

Q1
27920
19634
23
57

Q2
35124
17301
50
80

Q3
34046
15618
67
115

Q4
31632
7667
25
37

Válassza ki a a.Carrier-t, c, c2, c
47220
8976
27
60

) mint c az ontime táblából, ahol a DepDelay > 10, és az Év >= 2000, és Év = 2000, és Év <= 2008, csoportosítva a Carrier szerint) b on a.Carrier=b.Carrier, csökkenő c3 sorrendben;
58233
24368
55
153

Válassza ki a Carrier-t, az átlagos DepDelay * 1000 értékét mint c3 az ontime táblából, ahol az Év >= 2000, és Év <= 2008, csoportosítva a Carrier szerint;
30566
13256
52
91

Válassza ki az Évet, az átlagos DepDelay-t az ontime táblából, csoportosítva az Év szerint;
38309
60511
112
179

Válassza ki az Évet, az összesített számláló (c1) értékét az ontime táblából, csoportosítva az Év szerint;
20674
37979
31
81

Válassza ki az átlagos cnt értékét az (Válassza ki az Évet, Hónapot, az összesített számláló (cnt) értékét az ontime táblából, ahol a DepDel15 = 1, csoportosítva az Év, Hónap szerint) a;
34990
20102
56
148

Válassza ki az átlagos c1 értékét az (Válassza ki az Évet, Hónapot, az összesített számláló (c1) értékét az ontime táblából, csoportosítva az Év, Hónap szerint) a;
30489
51658
37
155

Válassza ki az OriginCityName-t, a DestCityName-t, az összesített számláló (c) értékét az ontime táblából, csoportosítva az OriginCityName, DestCityName szerint, a szám szerint csökkenő sorrendben, maximum 10;
39357
33742
186
1333

Válassza ki az OriginCityName-t, az összesített számláló (c) értékét az ontime táblából, csoportosítva az OriginCityName szerint, a szám szerint csökkenő sorrendben, maximum 10;
29912
30709
101
384

Válassza ki az a.Year-t, c1/c2 értékét az (Válassza ki az Évet, az összesített számláló (
54126
39913
124
1364212

Válassza ki az a.Year-t, c1/c2 értékét az (Válassza ki az Évet, az összesített számláló (
97258
30211
245
259

Eredmények megtekintése

A grafikon bemutatja a lekérdezés végrehajtásának idejét millikondratumban, az X tengely a fenti táblázatok lekérdezésszámát, míg az Y tengely a végrehajtásig eltelt időt mutatja. Az eredmények között a ClickHouse és a PostgreSQL-ből nyert adatok a clickhousedb_fdw mintát is tartalmazzák. A táblázatból látható, hogy hatalmas különbség van a PostgreSQL és a ClickHouse között, de minimális eltérés a ClickHouse és a clickhousedb_fdw között.

Ez a grafikon a ClickhouseDB és a clickhousedb_fdw közötti eltéréseket mutatja. A legtöbb lekérdezés esetében az FDW költségek nem túlságosan magasak, és alig észlelhetők, kivéve a Q12-t. Ez a kérdés csatlakozásokat és ORDER BY kifejezést foglal magában. Az ORDER BY GROUP/BY és az ORDER BY nem süllyed le a ClickHouse-hoz.

Teljesítménytesztelés analitikai lekérdezésekben PostgreSQL, ClickHouse és clickhousedb_fdw (PostgreSQL)

Az ORDER BY GROUP/BY és az ORDER BY nem süllyed le a ClickHouse-hoz.

A 2. táblázatban látható a Q12 és Q13 lekérdezések végrehajtási idejének ugrása. Ismétlem, ezt az ORDER BY javaslat okozza. Ennek megerősítésére végrehajtottam a Q-14 és Q-15 lekérdezéseket ORDER BY javaslattal és anélkül. ORDER BY javaslat nélkül a befejezési idő 259 ms, míg ORDER BY javaslattal 1364212 ms. A lekérdezés hibakeresése érdekében mindkét lekérdezést elmagyarázom, és itt olvasható az eredmény.

Q15: ORDER BY záradék nélkül

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: Lekérdezés ORDER BY záradék nélkül

LEKÉRDEZÉSI TERV                                                      
Hash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  
Kimenet: fontime."Year", (((count(*) * 1000)) / b.c2)  
Belső egyedi: true   Hash Cond: (fontime."Year" = b."Year")  
->  Külső lekérdezés  (cost=1.00..-1.00 rows=100000 width=12)        
Kimenet: fontime."Year", ((count(*) * 1000))        
Kapcsolatok: Összesítési lekérdezés a (fontime)        
Távoli SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"  
->  Hash  (cost=999.00..999.00 rows=100000 width=12)        
Kimenet: b.c2, b."Year"        
->  Keresés a b al-lekérdezésen  (cost=1.00..999.00 rows=100000 width=12)              
Kimenet: b.c2, b."Year"              
->  Külső lekérdezés  (cost=1.00..-1.00 rows=100000 width=12)                    
Kimenet: fontime_1."Year", (count(*))                    
Kapcsolatok: Összesítési lekérdezés a (fontime)                    
Távoli SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)

Q14: Lekérdezés ORDER BY záradékkal

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: Lekérdezési terv ORDER BY záradékkal

LEKÉRDEZÉSI TERV 
Merge Join  (cost=2.00..628498.02 rows=50000000 width=12)   
Kimenet: fontime."Year", (((count(*) * 1000)) / (count(*)))   
Belső egyedi: true   Merge Cond: (fontime."Year" = fontime_1."Year")   
->  Csoportosított összesítés  (cost=1.00..499.01 rows=1 width=12)        
Kimenet: fontime."Year", (count(*) * 1000)         
Csoport azonosító: fontime."Year"         
->  Külső lekérdezés a nyilvános.fontime  (cost=1.00..-1.00 rows=100000 width=4)               
Távoli SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Year" ASC   
->  Csoportosított összesítés  (cost=1.00..499.01 rows=1 width=12)         
Kimenet: fontime_1."Year", count(*)         
Csoport azonosító: fontime_1."Year"         
->  Külső lekérdezés a nyilvános.fontime fontime_1  (cost=1.00..-1.00 rows=100000 width=4) 
              
Távoli SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)

Következtetés

Ezek a kísérletek azt mutatják, hogy a ClickHouse valóban jó teljesítményt nyújt, a clickhousedb_fdw pedig a ClickHouse teljesítményének előnyeit kínálja a PostgreSQL-ből. Bár a clickhousedb_fdw használatakor vannak bizonyos költségek, ezek elenyészőek és összehasonlíthatók a ClickHouse adatbázisban elért natív teljesítménnyel. Ez azt is megerősíti, hogy a PostgreSQL fdw figyelemre méltó eredményeket biztosít.

ClickHouse Telegram csevegés https://t.me/clickhouse_ru
PostgreSQL Telegram csevegés https://t.me/pgsql

Forrás: habr.com

Megbízható hosting vásárlása weboldalak számára, DDoS védelemmel, VPS VDS szerverek. 🔥 Megbízható webtárhely vásárlása DDoS védelemmel, VPS VDS szerverek | ProHoster