PostgreSQL, ClickHouse અને clickhousedb_fdw (PostgreSQL) માં વિશ્લેષણાત્મક પ્રશ્નોના પ્રદર્શનનું પરીક્ષણ કરવું

આ અભ્યાસમાં, હું એ જોવા માંગતો હતો કે PostgreSQL ને બદલે ClickHouse ડેટા સ્ત્રોતનો ઉપયોગ કરીને કયા પ્રદર્શન સુધારણાઓ પ્રાપ્ત કરી શકાય. ક્લિકહાઉસનો ઉપયોગ કરવાથી મને જે ઉત્પાદકતા લાભો મળે છે તે હું જાણું છું. જો હું ફોરેન ડેટા રેપર (FDW) નો ઉપયોગ કરીને PostgreSQL માંથી ClickHouse ને ઍક્સેસ કરું તો શું આ લાભો ચાલુ રહેશે?

અભ્યાસ કરેલ ડેટાબેઝ પર્યાવરણો PostgreSQL v11, clickhousedb_fdw અને ClickHouse ડેટાબેઝ છે. આખરે, PostgreSQL v11 થી અમે અમારા clickhousedb_fdw દ્વારા ClickHouse ડેટાબેઝ પર રૂટ કરાયેલ વિવિધ SQL ક્વેરીઝ ચલાવીશું. અમે પછી જોઈશું કે FDW નું પ્રદર્શન મૂળ PostgreSQL અને મૂળ ક્લિકહાઉસમાં ચાલતી સમાન ક્વેરીઝ સાથે કેવી રીતે સરખાવવામાં આવે છે.

ક્લિકહાઉસ ડેટાબેઝ

ક્લિકહાઉસ એ ઓપન સોર્સ કોલમર ડેટાબેઝ મેનેજમેન્ટ સિસ્ટમ છે જે પરંપરાગત ડેટાબેઝ અભિગમો કરતાં 100-1000 ગણી ઝડપી કામગીરી હાંસલ કરી શકે છે, જે એક સેકન્ડ કરતાં ઓછા સમયમાં એક અબજ પંક્તિઓ પર પ્રક્રિયા કરવામાં સક્ષમ છે.

Clickhousedb_fdw

clickhousedb_fdw - ક્લિકહાઉસ ડેટાબેઝ માટે બાહ્ય ડેટા રેપર, અથવા FDW, પરકોનાનો એક ઓપન સોર્સ પ્રોજેક્ટ છે. અહીં પ્રોજેક્ટના GitHub ભંડારની લિંક છે.

માર્ચમાં મેં એક બ્લોગ લખ્યો હતો જે તમને અમારા FDW વિશે વધુ જણાવે છે.

તમે જોશો તેમ, આ ClickHouse માટે FDW પ્રદાન કરે છે જે PostgreSQL v11 સર્વરમાંથી ClickHouse ડેટાબેઝમાંથી SELECT, અને INSERT INTO ને મંજૂરી આપે છે.

FDW અદ્યતન સુવિધાઓને સપોર્ટ કરે છે જેમ કે એગ્રીગેટ અને જોડાઓ. આ સંસાધન-સઘન કામગીરી માટે રિમોટ સર્વરના સંસાધનોનો ઉપયોગ કરીને પ્રભાવને નોંધપાત્ર રીતે સુધારે છે.

બેન્ચમાર્ક પર્યાવરણ

• સુપરમાઇક્રો સર્વર:
  • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
  • 2 સોકેટ્સ / 28 કોરો / 56 થ્રેડો
  • મેમરી: 256 જીબી રેમ
  • સંગ્રહ: Samsung SM863 1.9TB Enterprise SSD
  • ફાઇલસિસ્ટમ: ext4/xfs
• OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
• પોસ્ટગ્રેએસક્યુએલ: સંસ્કરણ 11

બેન્ચમાર્ક પરીક્ષણો

આ પરીક્ષણ માટે કેટલાક મશીન-જનરેટેડ ડેટા સેટનો ઉપયોગ કરવાને બદલે, અમે 1987 થી 2018 સુધીના "સમયની જાણ કરેલ ઓપરેટર સમય દ્વારા ઉત્પાદકતા" ડેટાનો ઉપયોગ કર્યો. તમે ડેટા એક્સેસ કરી શકો છો અહીં ઉપલબ્ધ અમારી સ્ક્રિપ્ટનો ઉપયોગ કરીને.

ડેટાબેઝનું કદ 85 GB છે, જે 109 કૉલમનું એક ટેબલ પૂરું પાડે છે.

બેન્ચમાર્ક ક્વેરીઝ

ક્લિકહાઉસ, clickhousedb_fdw અને PostgreSQL ની સરખામણી કરવા માટે મેં ઉપયોગમાં લીધેલી ક્વેરીઝ અહીં છે.

Q#
ક્વેરી એગ્રીગેટ્સ અને ગ્રૂપ દ્વારા સમાવે છે

Q1
અઠવાડિયે દિવસ પસંદ કરો, ઓનટાઇમથી c તરીકે ગણો (*) જ્યાં વર્ષ >= 2000 અને વર્ષ <= 2008 ગ્રૂપ બાય ડેઓફ વીકના ક્રમમાં c DESC દ્વારા;

Q2
અઠવાડિયે દિવસ પસંદ કરો, ઓનટાઇમથી c તરીકે ગણો (*) જ્યાં DepDelay>10 અને વર્ષ >= 2000 અને વર્ષ <= 2008 ગ્રૂપ બાય ડેઓફ વીક ઓર્ડર c DESC દ્વારા;

Q3
ઓરિજિન પસંદ કરો, ઓનટાઇમથી ગણતરી કરો (*) જ્યાં DepDelay>10 અને વર્ષ >= 2000 અને વર્ષ <= 2008 ગ્રૂપ ઑરિજિન ઑર્ડર C DESC LIMIT 10 દ્વારા;

Q4
વાહક પસંદ કરો, ગણતરી() ઓનટાઇમથી જ્યાં વિલંબ> 10 અને વર્ષ = 2007 જૂથ વાહક દ્વારા ગણતરી દ્વારા ઓર્ડર() DESC;

Q5
પસંદ કરો a.વાહક, c, c2, c1000/c2 FROM c3 તરીકે ( કેરિયર પસંદ કરો, ગણતરી () ઓનટાઇમથી જ્યાં વિલંબ> 10 અને વર્ષ = 2007 વાહક દ્વારા જૂથ C2 DESC દ્વારા;

Q6
પસંદ કરો a.વાહક, c, c2, c1000/c2 FROM c3 તરીકે ( કેરિયર પસંદ કરો, ગણતરી () ઓનટાઇમથી c તરીકે જ્યાં વિલંબ> 10 અને વર્ષ >= 2000 અને વર્ષ <= વાહક દ્વારા 2008 જૂથ) એક આંતરિક જોડાઓ ( કેરિયર પસંદ કરો, ગણતરી કરો (*) ઓનટાઇમથી c2 તરીકે જ્યાં વર્ષ >= 2000 અને વર્ષ <= 2008 કેરિયર ) b પર a.Carrier=b.Carrier ORDER by c3 DESC;

Q7
વાહક પસંદ કરો, સરેરાશ(DepDelay) * 1000 AS c3 ઓનટાઇમ જ્યાં વર્ષ >= 2000 અને વર્ષ <= 2008 ગ્રૂપ બાય કેરિયર;

Q8
વર્ષ પસંદ કરો, સરેરાશ(DepDelay) ઓનટાઇમથી ગ્રૂપ બાય વર્ષ;

Q9
વર્ષ પસંદ કરો, વર્ષ દ્વારા ઑનટાઇમ જૂથમાંથી c1 તરીકે ગણો (*);

Q10
સરેરાશ (cnt) માંથી પસંદ કરો (વર્ષ, મહિનો, ગણતરી (*) ઓનટાઇમથી cnt તરીકે પસંદ કરો જ્યાં DepDel15=1 વર્ષ, મહિનો દ્વારા જૂથ) a;

Q11
માંથી સરેરાશ(c1) પસંદ કરો (વર્ષ,મહિનો,ગણતરી(*) તરીકે c1 તરીકે ઓનટાઇમ જૂથમાંથી વર્ષ,મહિના દ્વારા પસંદ કરો) a;

Q12
OriginCityName, DestCityName, કાઉન્ટ(*) તરીકે પસંદ કરો ઓનટાઇમથી ગ્રૂપ દ્વારા OriginCityName, DestCityName ઓર્ડર સી DESC લિમિટ 10 દ્વારા;

Q13
OriginCityName પસંદ કરો, C DESC LIMIT 10 દ્વારા ઑરિજિનસિટીનામ દ્વારા ઑનટાઇમ ગ્રૂપ તરીકે ગણતરી કરો

ક્વેરી જોડાઓ સમાવે છે

Q14
પસંદ કરો a.Year, c1/c2 FROM (વર્ષ પસંદ કરો, ગણતરી()ઓનટાઇમથી c1000 તરીકે 1 જ્યાં DepDelay>વર્ષ દ્વારા 10 ગ્રૂપ) એક આંતરિક જોડાઓ (વર્ષ પસંદ કરો, વર્ષ દ્વારા ઓનટાઇમ ગ્રુપમાંથી c2 તરીકે ગણો) b પર a.Year=b.Year ORDER BY a.Year;

Q15
પસંદ કરો a.”વર્ષ”, c1/c2 FROM ( “વર્ષ” પસંદ કરો, ગણતરી()ફોન્ટાઈમથી c1000 તરીકે 1 જ્યાં “DepDelay”>10 ગ્રૂપ બાય “વર્ષ”) એક આંતરિક જોડાઓ ("વર્ષ" પસંદ કરો, "વર્ષ" દ્વારા ફોન્ટાઈમ ગ્રુપમાંથી c2 તરીકે ગણો (*) ) b પર a.”Year”=b. "વર્ષ";

કોષ્ટક-1: બેન્ચમાર્કમાં વપરાતી ક્વેરી

ક્વેરી અમલ

જ્યારે વિવિધ ડેટાબેઝ સેટિંગ્સમાં ચલાવવામાં આવે ત્યારે દરેક ક્વેરીનાં પરિણામો અહીં છે: અનુક્રમણિકાઓ સાથે અને વગર PostgreSQL, મૂળ ClickHouse અને clickhousedb_fdw. સમય મિલિસેકંડમાં બતાવવામાં આવે છે.

Q#
પોસ્ટગ્રે એસક્યુએલ
PostgreSQL (અનુક્રમિત)
ક્લીકહાઉસ
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

કોષ્ટક-1: બેન્ચમાર્કમાં વપરાતી ક્વેરી ચલાવવામાં લાગેલો સમય

પરિણામો જુઓ

આલેખ ક્વેરી એક્ઝેક્યુશનનો સમય મિલીસેકન્ડમાં બતાવે છે, X અક્ષ ઉપરના કોષ્ટકોમાંથી ક્વેરી નંબર બતાવે છે અને Y અક્ષ મિલિસેકન્ડમાં એક્ઝેક્યુશનનો સમય બતાવે છે. Clickhousedb_fdw નો ઉપયોગ કરીને પોસ્ટગ્રેસમાંથી મેળવેલ ક્લિકહાઉસ પરિણામો અને ડેટા બતાવવામાં આવે છે. કોષ્ટકમાંથી તમે જોઈ શકો છો કે PostgreSQL અને ClickHouse વચ્ચે ઘણો મોટો તફાવત છે, પરંતુ ClickHouse અને clickhousedb_fdw વચ્ચે ન્યૂનતમ તફાવત છે.

આ ગ્રાફ ClickhouseDB અને clickhousedb_fdw વચ્ચેનો તફાવત દર્શાવે છે. મોટાભાગની ક્વેરીઝમાં, FDW ઓવરહેડ એટલું ઊંચું નથી અને Q12 સિવાય ભાગ્યે જ નોંધપાત્ર છે. આ ક્વેરી માં જોડાવા અને ઓર્ડર બાય કલમનો સમાવેશ થાય છે. ORDER BY GROUP/BY કલમને કારણે, ORDER BY ક્લિકહાઉસ પર ડ્રોપ ડાઉન થતો નથી.

કોષ્ટક 2 માં આપણે Q12 અને Q13 પ્રશ્નોમાં સમયનો ઉછાળો જોઈએ છીએ. ફરીથી, આ ORDER BY કલમને કારણે થાય છે. આની પુષ્ટિ કરવા માટે, મેં ORDER BY કલમ સાથે અને તેના વગર Q-14 અને Q-15 ક્વેરીઝ ચલાવી. ORDER BY કલમ વિના પૂર્ણ થવાનો સમય 259ms છે અને ORDER BY કલમ સાથે તે 1364212 છે. આ ક્વેરી ડીબગ કરવા માટે હું બંને ક્વેરી સમજાવું છું અને અહીં સમજૂતીના પરિણામો છે.

Q15: કલમ દ્વારા ઓર્ડર વિના

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";

પ્રશ્ન15: કલમ દ્વારા ઓર્ડર વિના પ્રશ્ન

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)

પ્રશ્ન 14: કલમ દ્વારા ઓર્ડર સાથે પ્રશ્ન

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";

પ્રશ્ન 14: કલમ દ્વારા ઓર્ડર સાથે ક્વેરી પ્લાન

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)

નિષ્કર્ષ

આ પ્રયોગોના પરિણામો દર્શાવે છે કે ક્લિકહાઉસ ખરેખર સારું પ્રદર્શન આપે છે, અને clickhousedb_fdw પોસ્ટગ્રેએસક્યુએલમાંથી ક્લિકહાઉસના પ્રદર્શન લાભો પ્રદાન કરે છે. જ્યારે clickhousedb_fdw નો ઉપયોગ કરતી વખતે કેટલાક ઓવરહેડ હોય છે, ત્યારે તે ક્લિકહાઉસ ડેટાબેઝ પર નેટીવલી ચલાવીને પ્રાપ્ત કરેલા પ્રદર્શન સાથે નગણ્ય અને તુલનાત્મક છે. આ પણ પુષ્ટિ કરે છે કે PostgreSQL માં fdw ઉત્તમ પરિણામો પ્રદાન કરે છે.

ક્લિકહાઉસ દ્વારા ટેલિગ્રામ ચેટ https://t.me/clickhouse_ru
PostgreSQL નો ઉપયોગ કરીને ટેલિગ્રામ ચેટ https://t.me/pgsql

સોર્સ: www.habr.com