ការសាកល្បងដំណើរការនៃសំណួរវិភាគនៅក្នុង PostgreSQL, ClickHouse និង clickhousedb_fdw (PostgreSQL)

នៅក្នុងការសិក្សានេះ ខ្ញុំចង់ឃើញការកែលម្អការអនុវត្តដែលអាចសម្រេចបានដោយប្រើប្រភពទិន្នន័យ ClickHouse ជាជាង PostgreSQL ។ ខ្ញុំដឹងពីអត្ថប្រយោជន៍ផលិតភាពដែលខ្ញុំទទួលបានពីការប្រើប្រាស់ ClickHouse ។ តើអត្ថប្រយោជន៍ទាំងនេះនឹងបន្តដែរឬទេ ប្រសិនបើខ្ញុំចូលប្រើ ClickHouse ពី PostgreSQL ដោយប្រើ Foreign Data Wrapper (FDW)?

បរិស្ថានមូលដ្ឋានទិន្នន័យដែលបានសិក្សាគឺ PostgreSQL v11, clickhousedb_fdw និងមូលដ្ឋានទិន្នន័យ ClickHouse ។ ទីបំផុត ពី PostgreSQL v11 យើងនឹងដំណើរការសំណួរ SQL ផ្សេងៗដែលបញ្ជូនតាមរយៈ clickhousedb_fdw របស់យើងទៅកាន់មូលដ្ឋានទិន្នន័យ ClickHouse ។ បន្ទាប់មកយើងនឹងឃើញពីរបៀបដែលការអនុវត្តរបស់ FDW ប្រៀបធៀបទៅនឹងសំណួរដូចគ្នាដែលកំពុងដំណើរការនៅក្នុង PostgreSQL ដើម និង ClickHouse ដើម។

មូលដ្ឋានទិន្នន័យ Clickhouse

ClickHouse គឺជាប្រព័ន្ធគ្រប់គ្រងមូលដ្ឋានទិន្នន័យ columnar ប្រភពបើកចំហ ដែលអាចសម្រេចបាននូវការអនុវត្ត 100-1000 ដងលឿនជាងវិធីសាស្រ្តមូលដ្ឋានទិន្នន័យប្រពៃណី ដែលមានសមត្ថភាពដំណើរការលើជួរមួយពាន់លានក្នុងរយៈពេលតិចជាងមួយវិនាទី។

Clickhousedb_fdw

clickhousedb_fdw - កញ្ចប់ទិន្នន័យខាងក្រៅសម្រាប់មូលដ្ឋានទិន្នន័យ ClickHouse ឬ FDW គឺជាគម្រោងប្រភពបើកចំហពី Percona ។ នេះគឺជាតំណភ្ជាប់ទៅកាន់ឃ្លាំង GitHub របស់គម្រោង.

នៅខែមីនាខ្ញុំបានសរសេរប្លក់មួយដែលប្រាប់អ្នកបន្ថែមអំពី FDW របស់យើង។.

ដូចដែលអ្នកនឹងឃើញ វាផ្តល់នូវ FDW សម្រាប់ ClickHouse ដែលអនុញ្ញាតឱ្យ SELECT ពី និងបញ្ចូលទៅក្នុងមូលដ្ឋានទិន្នន័យ ClickHouse ពីម៉ាស៊ីនមេ PostgreSQL v11 ។

FDW គាំទ្រមុខងារកម្រិតខ្ពស់ដូចជា សរុប និងចូលរួម។ នេះធ្វើអោយប្រសើរឡើងយ៉ាងខ្លាំងនូវការអនុវត្តដោយប្រើធនធាននៃម៉ាស៊ីនមេពីចម្ងាយសម្រាប់ប្រតិបត្តិការដែលពឹងផ្អែកលើធនធានទាំងនេះ។

បរិស្ថានស្តង់ដារ

• ម៉ាស៊ីនមេ Supermicro៖
  • ស៊ីភីយូ Intel® Xeon® E5-2683 v3 @ 2.00GHz
  • 2 រន្ធ / 28 ស្នូល / 56 ខ្សែស្រឡាយ
  • មេម៉ូរី៖ រ៉េមទំហំ ៨ ជីកាបៃ
  • ទំហំផ្ទុក៖ Samsung SM863 1.9TB Enterprise SSD
  • ប្រព័ន្ធឯកសារ៖ ext4/xfs
• ប្រព័ន្ធប្រតិបត្តិការ៖ Linux smblade01 4.15.0-42-generic #45~16.04.1-ubuntu
• PostgreSQL៖ កំណែ ១១

ការធ្វើតេស្តគោល

ជំនួសឱ្យការប្រើសំណុំទិន្នន័យដែលបង្កើតដោយម៉ាស៊ីនមួយចំនួនសម្រាប់ការធ្វើតេស្តនេះ យើងបានប្រើទិន្នន័យ "ផលិតភាពតាមពេលវេលាដែលបានរាយការណ៍ពីម៉ោងប្រតិបត្តិការ" ពីឆ្នាំ 1987 ដល់ឆ្នាំ 2018 ។ អ្នកអាចចូលប្រើទិន្នន័យ ដោយប្រើស្គ្រីបរបស់យើងដែលមាននៅទីនេះ.

ទំហំ​មូលដ្ឋាន​ទិន្នន័យ​គឺ 85 GB ដែល​ផ្តល់​តារាង​មួយ​នៃ 109 ជួរ​ឈរ។

សំណួរគោល

នេះគឺជាសំណួរដែលខ្ញុំបានប្រើដើម្បីប្រៀបធៀប ClickHouse, clickhousedb_fdw និង PostgreSQL ។

Q#
សំណួរមានសរុប និងក្រុមដោយ

Q1
ជ្រើសរើស DayOfWeek, រាប់(*) ជា c ពីម៉ោងណាដែលឆ្នាំ >= 2000 និងឆ្នាំ <= 2008 ក្រុមតាម DayOfWeek បញ្ជាដោយ c DESC;

Q2
ជ្រើសរើស DayOfWeek, រាប់(*) ជា c ពីម៉ោងធ្វើការ ដែល DepDelay>10 និងឆ្នាំ>= 2000 និងឆ្នាំ <= 2008 ក្រុមតាមថ្ងៃនៃសប្តាហ៍ បញ្ជាដោយ c DESC;

Q3
ជ្រើសរើសប្រភពដើម, រាប់(*) ជា c ចាប់ពីពេលកំណត់ ដែល DepDelay>10 និងឆ្នាំ>= 2000 និងឆ្នាំ <= 2008 ក្រុមតាមលំដាប់ដើម BY c DESC LIMIT 10;

Q4
ជ្រើសរើសក្រុមហ៊ុនដឹកជញ្ជូន រាប់() ចាប់ពីម៉ោងណាដែល DepDelay> 10 និងឆ្នាំ = 2007 ក្រុមដោយក្រុមហ៊ុនដឹកជញ្ជូន ការបញ្ជាទិញដោយរាប់() DESC;

Q5
ជ្រើសរើស a.Carrier, c, c2, c1000/c2 ជា c3 ពី ( SELECT ក្រុមហ៊ុនដឹកជញ្ជូន រាប់() ក្នុងនាមជា c ចាប់ពីពេលកំណត់ 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 ដោយ c3 DESC;

Q6
ជ្រើសរើស a.Carrier, c, c2, c1000/c2 ជា c3 ពី ( SELECT ក្រុមហ៊ុនដឹកជញ្ជូន រាប់() ក្នុងនាមជា c ពីពេលវេលាដែល DepDelay>10 និងឆ្នាំ>= 2000 និងឆ្នាំ <= 2008 ជាក្រុមដោយក្រុមហ៊ុនដឹកជញ្ជូន) ការចូលរួមខាងក្នុង (ជ្រើសរើសក្រុមហ៊ុនដឹកជញ្ជូន រាប់(*) ជា c2 ពីម៉ោងបន្តបន្ទាប់នៅណាឆ្នាំ>= 2000 និងឆ្នាំ <= 2008 Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 ចាប់ពីម៉ោងណាដែលឆ្នាំ>= 2000 និងឆ្នាំ <= 2008 GROUP BY Carrier;

Q8
SELECT Year, avg(DepDelay) ពីក្រុមតាមពេលវេលាតាមឆ្នាំ;

Q9
ជ្រើសរើសឆ្នាំ រាប់(*) ជា c1 ពីក្រុមទាន់ពេលដោយឆ្នាំ;

Q10
SELECT avg(cnt) FROM (SELECT Year, Month, count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year, Month) a;

Q11
ជ្រើសរើស avg(c1) ពី (ជ្រើសរើសឆ្នាំ ខែ រាប់(*) ជា c1 ពីក្រុមទាន់ពេលដោយឆ្នាំ ខែ) a;

Q12
SELECT OriginCityName, DestCityName, count(*) ជា c ពីក្រុមទាន់ពេលដោយ OriginCityName, DestCityName បញ្ជាដោយ c DESC LIMIT 10;

Q13
ជ្រើសរើស OriginCityName, count(*) ជា c ពីក្រុមទាន់ពេលដោយ OriginCityName បញ្ជាដោយ c DESC LIMIT 10;

សំណួរមានការចូលរួម

Q14
ជ្រើសរើស a.Year, c1/c2 FROM (ជ្រើសរើសឆ្នាំ, រាប់()1000 ជា c1 ចាប់ពីពេលកំណត់ WHERE DepDelay> 10 GROUP BY Year) a Inner JOIN (ជ្រើសរើស Year, count(*) as c2 from ontime GROUP BY Year) b on a.Year=b.Year ORDER BY a.Year;

Q15
ជ្រើសរើស “ឆ្នាំ”, c1/c2 ពី (ជ្រើសរើស “ឆ្នាំ”, រាប់()1000 as c1 FROM fontime where “DepDelay”> 10 GROUP BY “Year”) a Inner JOIN (ជ្រើសរើស “Year” រាប់(*) ជា c2 ពី fontime GROUP BY “Year”) b on a.”Year”=b. "ឆ្នាំ";

តារាង-១៖ សំណួរដែលប្រើជាគោល

ការប្រតិបត្តិសំណួរ

នេះគឺជាលទ្ធផលនៃសំណួរនីមួយៗ នៅពេលដំណើរការក្នុងការកំណត់មូលដ្ឋានទិន្នន័យផ្សេងៗគ្នា៖ PostgreSQL ដែលមាន និងគ្មានលិបិក្រម ប្រភពដើម ClickHouse និង clickhousedb_fdw ។ ពេលវេលាត្រូវបានបង្ហាញជាមីលីវិនាទី។

Q#
PostgreSQL
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

Table-1: ពេលវេលាដែលត្រូវយកទៅប្រតិបត្តិសំណួរដែលប្រើក្នុងគោល

មើលលទ្ធផល

ក្រាហ្វបង្ហាញពេលវេលាប្រតិបត្តិសំណួរគិតជាមីលីវិនាទី អ័ក្ស X បង្ហាញលេខសំណួរពីតារាងខាងលើ ហើយអ័ក្ស Y បង្ហាញពេលវេលាប្រតិបត្តិគិតជាមិល្លីវិនាទី។ លទ្ធផល ClickHouse និងទិន្នន័យដែលបានទាញយកពី postgres ដោយប្រើ clickhousedb_fdw ត្រូវបានបង្ហាញ។ ពីតារាងអ្នកអាចមើលឃើញថាមានភាពខុសគ្នាយ៉ាងខ្លាំងរវាង PostgreSQL និង ClickHouse ប៉ុន្តែភាពខុសគ្នាតិចតួចរវាង ClickHouse និង clickhousedb_fdw ។

ក្រាហ្វនេះបង្ហាញពីភាពខុសគ្នារវាង ClickhouseDB និង clickhousedb_fdw ។ នៅក្នុងសំណួរភាគច្រើន ការចំណាយលើ FDW មិនខ្ពស់ទេ ហើយស្ទើរតែមិនសំខាន់ លើកលែងតែ Q12 ។ សំណួរនេះរួមបញ្ចូលការភ្ជាប់ និងការបញ្ជាទិញតាមឃ្លា។ ដោយសារតែ ORDER BY GROUP/BY clause, ORDER BY មិនទម្លាក់ទៅ ClickHouse ទេ។

នៅក្នុងតារាងទី 2 យើងឃើញពេលវេលាលោតក្នុងសំណួរ Q12 និង Q13 ។ ជាថ្មីម្តងទៀត នេះគឺបណ្តាលមកពី ORDER BY clause។ ដើម្បីបញ្ជាក់រឿងនេះ ខ្ញុំបានដំណើរការសំណួរ Q-14 និង Q-15 ដោយមាន និងគ្មាន ORDER BY clause។ បើគ្មាន ORDER BY clause ពេលវេលាបញ្ចប់គឺ 259ms ហើយជាមួយនឹង ORDER BY clause វាគឺ 1364212។ ដើម្បីបំបាត់បញ្ហាសំណួរនេះ ខ្ញុំកំពុងពន្យល់ទាំងសំណួរ ហើយនេះគឺជាលទ្ធផលនៃការពន្យល់។

សំណួរទី 15: ដោយគ្មានការបញ្ជាទិញតាមឃ្លា

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: ផែនការសំណួរជាមួយ ORDER BY Clause

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