I lenei suʻesuʻega, na ou manaʻo e vaʻai pe o le a le faʻaleleia atili o faʻatinoga e mafai ona ausia e ala i le faʻaaogaina o se faʻamatalaga faʻamatalaga ClickHouse nai lo PostgreSQL. Ou te iloa le aoga o le gaosiga ou te maua mai le faʻaaogaina o le ClickHouse. O le a fa'aauau pea nei fa'amanuiaga pe a ou fa'aogaina le ClickHouse mai le PostgreSQL e fa'aoga ai le Fa'auma Fa'amaumauga a Fa'aifo (FDW)?
O siosiomaga faʻamaumauga o loʻo suʻesuʻeina o PostgreSQL v11, clickhousedb_fdw ma ClickHouse database. Mulimuli ane, mai le PostgreSQL v11 o le a matou faʻatautaia fesili SQL eseese e ala i la matou clickhousedb_fdw i le ClickHouse database. O le a tatou vaʻai pe faʻafefea ona faʻatusatusa le faʻatinoga a le FDW i fesili tutusa o loʻo faʻatautaia i le PostgreSQL ma le ClickHouse.
Clickhouse Database
ClickHouse o se faʻamatalaga faʻapipiʻi punaʻoa faʻapipiʻi faʻamaumauga e mafai ona ausia le faʻatinoga 100-1000 taimi vave nai lo faʻamatalaga masani faʻamaumauga, e mafai ona faʻagasolo i luga o le piliona laina i lalo ifo o le lua.
Clickhousedb_fdw
clickhousedb_fdw - O le afifiina o faʻamatalaga i fafo mo le ClickHouse database, poʻo le FDW, o se poloketi tatala mai Percona. .
.
E pei ona e vaʻaia, o loʻo tuʻuina atu se FDW mo ClickHouse e mafai ai ona FILIFILI mai, ma INSERT INTO, le ClickHouse database mai le PostgreSQL v11 server.
E lagolagoina e le FDW foliga faʻapitoa e pei o le tuʻufaʻatasia ma le faʻatasi. O lenei mea e faʻaleleia atili ai le faʻatinoga e ala i le faʻaaogaina o punaoa a le server mamao mo nei faʻagaioiga-malosi.
Siosiomaga fa'ailoga
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 sockets / 28 cores / 56 filo
- Manatua: 256GB o RAM
- Teuina: Samsung SM863 1.9TB Enterprise SSD
- Faiga faila: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: lomiga 11
Su'ega fa'atatau
Nai lo le faʻaaogaina o faʻamaumauga faʻapipiʻi masini mo lenei suʻega, na matou faʻaogaina le "Productivity by Time Reported Operator Time" faʻamaumauga mai le 1987 i le 2018. E mafai ona e mauaina faʻamatalaga .
Ole tele ole database ole 85 GB, e maua ai le laulau e tasi ole 109 koluma.
Fesili Fa'atatau
O fesili ia na ou faʻaaogaina e faʻatusatusa ai ClickHouse, clickhousedb_fdw ma PostgreSQL.
Q#
Ole Su'esu'ega o lo'o i ai fa'atasiga ma fa'avasegaga ile
Q1
FILIFILI AsoOfWeek, faitau(*) AS c MAI taimi taimi WHERE Tausaga >= 2000 MA Tausaga <= 2008 VAEGA I LE DayOfWeek ORDER BY c DESC;
Q2
FILIFILI AsoOfWeek, faitau(*) AS c MAI le taimi taimi WHERE DepDelay>10 MA Tausaga >= 2000 MA Tausaga <= 2008 VAEGA I LE DayOfWeek ORDER BY c DESC;
Q3
FILIFILI Origin, faitau(*) AS c MAI le taimi taimi WHERE DepDelay>10 MA Tausaga >= 2000 MA Tausaga <= 2008 VAEGA I LE AUAAGA ORDER BY c DESC LIMIT 10;
Q4
FILIFILI le ave, faitau() MAI le taimi taimi WHERE DepDelay>10 MA Tausaga = 2007 FA'AVAE E LE LA'U FA'A'UINA O'OGA I LE faitauga() DESC;
Q5
FILIFILI a.Taavale, c, c2, c1000/c2 e pei o le c3 MAI ( FILIFILI Avea, faitau() AS c MAI le taimi taimi WHERE DepDelay>10 MA Tausaga=2007 GROUP BY Carrier ) a INNER SOI ( FILIFILI Carrier, faitau(*) AS c2 MAI le taimi taimi WHERE Tausaga=2007 GROUP BY Carrier)b on a.Carrier=b.Carrier ORDER SAUNIA E c3 DESC;
Q6
FILIFILI a.Taavale, c, c2, c1000/c2 e pei o le c3 MAI ( FILIFILI Avea, faitau() AS c MAI le taimi taimi WHERE DepDelay>10 MA Tausaga >= 2000 MA Tausaga <= 2008 GROUP BY Carrier) a INNER SOI ( FILIFILI le Va'a, faitau (*) AS c2 MAI le taimi taimi WHERE Tausaga >= 2000 MA Tausaga <= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier POLOA E C3 DESC;
Q7
FILIFILI le Va'a, avefe'au(DepDelay) * 1000 AS c3 MAI le taimi ole WHERE Tausaga >= 2000 MA Tausaga <= 2008 FA'AVAE E FA'AVAE;
Q8
FILIFILI Tausaga, ave(DepDelay) MAI le taimi taimi GROUP I LE Tausaga;
Q9
filifili le Tausaga, faitau(*) e pei o le c1 mai le kulupu fa'atasi ile Tausaga;
Q10
FILIFILI fua(cnt) MAI (FILIFILI Tausaga,Masina, faitau(*) AS cnt MAI le taimi FEIA DepDel15=1 VAEGA I Tausaga,Masina) a;
Q11
filifili avg(c1) mai (filifili Tausaga,Masina, faitau(*) e pei o le c1 mai le taimi taimi vaega i le Tausaga,Masina) a;
Q12
FILIFILI OriginCityName, DestCityName, faitau(*) AS c MAI le taimi taimi GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
FILIFILI OriginCityName, faitau(*) AS c MAI le taimi taimi GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Fesili o lo'o i ai So'ai
Q14
FILIFILI a. Tausaga, c1/c2 MAI ( filifili Tausaga, faitau()1000 e pei o le c1 mai le taimi taimi WHERE DepDelay>10 GROUP I LE Tausaga) a INNER JOIN (filifili Tausaga, faitau (*) e pei o le c2 mai taimi taimi GROUP BY Year ) b i luga ole a.Year=b.Year ORDER BY a.Year;
Q15
FILIFILI a.” Tausaga”, c1/c2 MAI ( filifili “Tausaga”, faitau()1000 as c1 MAI fontime WHERE “DepDelay”>10 GROUP I “Year”) a INNER JOIN (filifili “Year”, faitau(*) as c2 MAI fontime GROUP I “Year” ) b i luga o le a.”Tausaga”=b. "Tausaga";
Laulau-1: Fa'amatalaga fa'aoga ile fa'ailoga
Fa'asalaga fa'atatau
O fa'ai'uga nei o fesili ta'itasi pe a fa'atautaia i fa'amaumauga tu'ufa'atasiga eseese: PostgreSQL fa'atasi ma le leai o ni fa'asinomaga, tagatanu'u ClickHouse ma clickhousedb_fdw. Ua fa'aalia le taimi ile milliseconds.
Q#
PostgreSQL
PostgreSQL (Fa'asinotonu)
Kiliki Fale
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
Laulau-1: Taimi e fa'atino ai fa'amatalaga o lo'o fa'aogaina ile fa'ailoga
Va'ai i'uga
O le kalafi o loʻo faʻaalia ai le taimi o le faʻatinoina o fesili i milliseconds, o le X axis o loʻo faʻaalia ai le numera o fesili mai laulau i luga, ma le Y axis o loʻo faʻaalia le taimi faʻatino i milliseconds. ClickHouse i'uga ma fa'amaumauga na maua mai postgres fa'aaoga clickhousedb_fdw o lo'o fa'aalia. Mai le laulau e mafai ona e vaʻaia o loʻo i ai se eseesega tele i le va o PostgreSQL ma ClickHouse, ae laʻititi le eseesega i le va o ClickHouse ma clickhousedb_fdw.
O lenei kalafi o loʻo faʻaalia ai le eseesega i le va o ClickhouseDB ma clickhousedb_fdw. I le tele o fesili, o le FDW overhead e le maualuga ma e tau le taua vagana ai le Q12. O lenei fesili e aofia ai faʻatasi ma se ORDER BY clause. Ona o le ORDER BY GROUP/BY clause, ORDER BY e le pa'ū i lalo ile ClickHouse.
I le Laulau 2 matou te vaʻai i le oso o le taimi i fesili Q12 ma Q13. Toe fo'i, e mafua mai i le ORDER BY clause. Ina ia faʻamaonia lenei mea, na ou faʻatautaia fesili Q-14 ma Q-15 faʻatasi ma le leai o le ORDER BY clause. A aunoa ma le ORDER BY clause o le taimi e maeʻa ai o le 259ms ma le ORDER BY clause o le 1364212. Ina ia debug lenei fesili o loʻo ou faʻamatalaina uma fesili ma o taunuuga ia o le faʻamatalaga.
Q15: A aunoa ma le ORDER BY Fuaiupu
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: Su'esu'ega e aunoa ma se ORDER BY Fuaiupu
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: Fesili ma le ORDER BY Fuaiupu
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: Fuafuaga Su'esu'e ma ORDER BY Fuaiupu
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)iʻuga
O faʻaiʻuga o nei suʻega o loʻo faʻaalia ai o le ClickHouse e ofoina atu le lelei tele, ma o le clickhousedb_fdw e ofoina atu faʻamanuiaga faʻatinoga o ClickHouse mai PostgreSQL. E ui o loʻo i ai se faʻauluuluga pe a faʻaaoga clickhousedb_fdw, e faʻatauvaʻa ma faʻatusatusa i le faʻatinoga na ausia e ala i le taʻavale faʻapitoa i luga o le ClickHouse database. E faʻamaonia ai foi o le fdw i PostgreSQL e maua ai faʻamanuiaga sili.
Telegram talatalanoaga e ala ile Clickhouse
Telegram talatalanoaga faʻaaoga PostgreSQL
puna: www.habr.com