ანალიტიკური მოთხოვნების შესრულების ტესტირება PostgreSQL, ClickHouse და clickhousedb_fdw (PostgreSQL)

ამ კვლევაში მინდოდა მენახა, თუ რა გაუმჯობესების მიღწევა შეიძლებოდა ClickHouse მონაცემთა წყაროს გამოყენებით, ვიდრე PostgreSQL. მე ვიცი პროდუქტიულობის სარგებელი, რომელსაც ვიღებ ClickHouse-ის გამოყენებით. გაგრძელდება თუ არა ეს შეღავათები, თუ მე მივმართავ ClickHouse-ს PostgreSQL-დან უცხოური მონაცემთა შეფუთვის (FDW) გამოყენებით?

შესწავლილი მონაცემთა ბაზის გარემო არის PostgreSQL v11, clickhousedb_fdw და ClickHouse მონაცემთა ბაზა. საბოლოო ჯამში, PostgreSQL v11-დან ჩვენ გავუშვით სხვადასხვა SQL მოთხოვნები, რომლებიც გადატანილია ჩვენი clickhousedb_fdw მეშვეობით ClickHouse მონაცემთა ბაზაში. ჩვენ შემდეგ დავინახავთ, თუ როგორ ადარებს FDW-ის შესრულება იმავე შეკითხვებს, რომლებიც გაშვებულია მშობლიურ PostgreSQL-ში და მშობლიურ ClickHouse-ში.

Clickhouse მონაცემთა ბაზა

ClickHouse არის ღია კოდის სვეტოვანი მონაცემთა ბაზის მართვის სისტემა, რომელსაც შეუძლია მიაღწიოს შესრულებას 100-1000-ჯერ უფრო სწრაფად, ვიდრე ტრადიციული მონაცემთა ბაზის მიდგომები, რომელსაც შეუძლია მილიარდზე მეტი სტრიქონის დამუშავება წამზე ნაკლებ დროში.

Clickhousedb_fdw

clickhousedb_fdw - გარე მონაცემების შეფუთვა ClickHouse მონაცემთა ბაზისთვის, ან FDW, არის ღია კოდის პროექტი Percona-სგან. აქ არის ბმული პროექტის GitHub საცავთან.

მარტში დავწერე ბლოგი, რომელიც უფრო მეტს გეტყვით ჩვენი FDW-ის შესახებ.

როგორც ხედავთ, ეს უზრუნველყოფს ClickHouse-ს FDW-ს, რომელიც საშუალებას აძლევს SELECT-დან და ჩასვათ ClickHouse მონაცემთა ბაზა PostgreSQL v11 სერვერიდან.

FDW მხარს უჭერს მოწინავე ფუნქციებს, როგორიცაა აგრეგაცია და შეერთება. ეს მნიშვნელოვნად აუმჯობესებს შესრულებას დისტანციური სერვერის რესურსების გამოყენებით ამ რესურსზე ინტენსიური ოპერაციებისთვის.

საორიენტაციო გარემო

• სუპერმიკრო სერვერი:
  • Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
  • 2 სოკეტი / 28 ბირთვი / 56 ძაფი
  • მეხსიერება: 256 GB ოპერატიული მეხსიერება
  • შენახვა: Samsung SM863 1.9TB Enterprise SSD
  • ფაილური სისტემა: ext4/xfs
• ოპერაციული სისტემა: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
• PostgreSQL: ვერსია 11

საორიენტაციო ტესტები

იმის ნაცვლად, რომ გამოგვეყენებინა მანქანით გენერირებული მონაცემთა ნაკრები ამ ტესტისთვის, ჩვენ გამოვიყენეთ "პროდუქტიულობა დროში მოხსენებული ოპერატორის დროით" მონაცემები 1987 წლიდან 2018 წლამდე. შეგიძლიათ მონაცემების წვდომა ჩვენი სკრიპტის გამოყენებით, რომელიც ხელმისაწვდომია აქ.

მონაცემთა ბაზის ზომაა 85 GB, რომელიც შეიცავს 109 სვეტის ერთ ცხრილს.

საორიენტაციო მოთხოვნები

აქ არის მოთხოვნები, რომლებიც გამოვიყენე ClickHouse, clickhousedb_fdw და PostgreSQL შესადარებლად.

Q#
მოთხოვნა შეიცავს აგრეგატებს და ჯგუფს

Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE წელი >= 2000 და წელი <= 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
აირჩიეთ ოპერატორი, დათვალეთ() FROM ontime WHERE DepDelay>10 და წელი = 2007 ჯგუფი ოპერატორის მიხედვით შეკვეთა რაოდენობის მიხედვით () DESC;

Q5
SELECT a.Carrier, c, c2, c1000/c2 როგორც 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.ORDER BY c3 DESC;

Q6
SELECT a.Carrier, c, c2, c1000/c2 როგორც c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year GROUP BY <= 2008 Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE წელი >= 2000 და წელი <= 2008 GROUP BY Carrier;

Q8
SELECT წელი, საშუალო (DepDelay) FROM ontime GROUP BY Year;

Q9
აირჩიეთ წელი, დაითვალეთ(*) როგორც c1 დროითი ჯგუფიდან წლის მიხედვით;

Q10
SELECT avg(cnt) FROM (SELECT წელი, თვე, რაოდენობა(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY BY, Month) a;

Q11
აირჩიეთ avg(c1) from (აირჩიეთ წელი, თვე, დათვლა (*) როგორც c1 დროითი ჯგუფიდან წლის, თვის მიხედვით) 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;

მოთხოვნა შეიცავს შეერთებებს

Q14
SELECT a.Year, c1/c2 FROM ( აირჩიეთ წელი, დათვლა()1000 როგორც c1 დროიდან WHERE DepDelay>10 GROUP BY By Year) შიდა შეერთება (აირჩიეთ წელი, დაითვალეთ (*) როგორც c2 დროიდან GROUP BY By Year ) b on a.Year=b.Year ORDER BY A.Year;

Q15
SELECT a.”Year”, c1/c2 FROM ( აირჩიეთ “Year”, count()1000 როგორც c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “Year”) შიდა შეერთება (აირჩიეთ “Year”, დაითვალეთ(*) როგორც c2 FROM fontime GROUP BY “Year” ) b on a.”Year”=b. "წელი";

ცხრილი-1: მოთხოვნები, რომლებიც გამოიყენება ბენჩმარკში

შეკითხვის შესრულება

აქ მოცემულია თითოეული მოთხოვნის შედეგები მონაცემთა ბაზის სხვადასხვა პარამეტრებში გაშვებისას: PostgreSQL ინდექსებით და მის გარეშე, მშობლიური ClickHouse და clickhousedb_fdw. დრო ნაჩვენებია მილიწამებში.

Q#
PostgreSQL
PostgreSQL (ინდექსირებული)
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

ცხრილი-1: ბენჩმარკში გამოყენებული მოთხოვნების შესრულების დრო

შედეგების ნახვა

გრაფიკი აჩვენებს მოთხოვნის შესრულების დროს მილიწამებში, X ღერძი აჩვენებს მოთხოვნის რიცხვს ზემოთ ცხრილებიდან, ხოლო Y ღერძი აჩვენებს შესრულების დროს მილიწამებში. ნაჩვენებია ClickHouse-ის შედეგები და მონაცემები, რომლებიც მიღებულია postgres-დან clickhousedb_fdw-ის გამოყენებით. ცხრილიდან ხედავთ, რომ დიდი განსხვავებაა PostgreSQL-სა და ClickHouse-ს შორის, მაგრამ მინიმალური განსხვავება ClickHouse-სა და clickhousedb_fdw-ს შორის.

ეს გრაფიკი აჩვენებს განსხვავებას ClickhouseDB-სა და clickhousedb_fdw-ს შორის. უმეტეს შეკითხვებში, FDW ზედნადები არც თუ ისე მაღალია და ძნელად მნიშვნელოვანი, გარდა Q12-ისა. ეს მოთხოვნა მოიცავს შეერთებებს და ORDER BY პუნქტს. ORDER BY GROUP/BY პუნქტის გამო, ORDER BY არ ჩამოდის ClickHouse-ზე.

ცხრილში 2 ჩვენ ვხედავთ დროის ნახტომს Q12 და Q13 შეკითხვებში. კიდევ ერთხელ, ეს გამოწვეულია ORDER BY პუნქტით. ამის დასადასტურებლად მე შევასრულე მოთხოვნები Q-14 და Q-15 ORDER BY პუნქტით და მის გარეშე. 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";

Q15: მოთხოვნა პუნქტის შეკვეთის გარეშე

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: შეკითხვა 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: შეკითხვის გეგმა ORDER BY პუნქტით

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)

გამოყვანის

ამ ექსპერიმენტების შედეგები აჩვენებს, რომ ClickHouse გთავაზობთ მართლაც კარგ შესრულებას და clickhousedb_fdw გთავაზობთ ClickHouse-ის შესრულების სარგებელს PostgreSQL-დან. მიუხედავად იმისა, რომ clickhousedb_fdw-ის გამოყენებისას არის გარკვეული ზედნადები, ის უმნიშვნელოა და შედარებადია ClickHouse მონაცემთა ბაზაში მშობლიურად გაშვებით მიღწეულ შესრულებასთან. ეს ასევე ადასტურებს, რომ fdw PostgreSQL-ში იძლევა შესანიშნავ შედეგებს.

Telegram ჩატი Clickhouse-ის საშუალებით https://t.me/clickhouse_ru
Telegram ჩატი PostgreSQL-ის გამოყენებით https://t.me/pgsql

წყარო: www.habr.com