ProHoster > Blog > İdarə > PostgreSQL, ClickHouse və clickhousedb_fdw (PostgreSQL) proqramlarında analitik sorğuların performansının sınaqdan keçirilməsi

PostgreSQL, ClickHouse və clickhousedb_fdw (PostgreSQL) proqramlarında analitik sorğuların performansının sınaqdan keçirilməsi

Bu araşdırmada PostgreSQL deyil, ClickHouse məlumat mənbəyindən istifadə etməklə hansı performans təkmilləşdirmələrinin əldə oluna biləcəyini görmək istədim. ClickHouse istifadə edərək əldə etdiyim məhsuldarlıq faydalarını bilirəm. Xarici Məlumat Qapağı (FDW) istifadə edərək PostgreSQL-dən ClickHouse-a daxil olsam, bu üstünlüklər davam edəcəkmi?

Tədqiq olunan verilənlər bazası mühitləri PostgreSQL v11, clickhousedb_fdw və ClickHouse verilənlər bazasıdır. Nəhayət, PostgreSQL v11-dən biz clickhousedb_fdw vasitəsilə ClickHouse verilənlər bazasına yönləndirilən müxtəlif SQL sorğularını icra edəcəyik. Sonra FDW-nin performansının yerli PostgreSQL və yerli ClickHouse-da işləyən eyni sorğularla necə müqayisə olunduğunu görəcəyik.

Clickhouse verilənlər bazası

ClickHouse açıq mənbəli sütunlu verilənlər bazası idarəetmə sistemidir, performansı ənənəvi verilənlər bazası yanaşmalarından 100-1000 dəfə daha sürətli əldə edə bilir, bir saniyədən az müddətdə milyarddan çox satırı emal edə bilir.

Clickhousedb_fdw

clickhousedb_fdw - ClickHouse verilənlər bazası və ya FDW üçün xarici məlumat paketi Percona-dan açıq mənbə layihəsidir. Budur layihənin GitHub deposuna keçid.

Mart ayında mən sizə FDW haqqında daha çox məlumat verən bir blog yazdım.

Gördüyünüz kimi, bu, PostgreSQL v11 serverindən ClickHouse verilənlər bazasından SEÇMƏ və INSERT INTO imkan verən ClickHouse üçün FDW təmin edir.

FDW toplama və qoşulma kimi qabaqcıl xüsusiyyətləri dəstəkləyir. Bu, bu resurs tutumlu əməliyyatlar üçün uzaq serverin resurslarından istifadə etməklə performansı əhəmiyyətli dərəcədə yaxşılaşdırır.

Benchmark mühiti

  • Supermicro server:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 yuva / 28 nüvə / 56 ip
    • Yaddaş: 256GB operativ yaddaş
    • Yaddaş: Samsung SM863 1.9TB Enterprise SSD
    • Fayl sistemi: ext4/xfs
  • ƏS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: versiya 11

Benchmark testləri

Bu test üçün bəzi maşın tərəfindən yaradılan məlumat dəstindən istifadə etmək əvəzinə, biz 1987-ci ildən 2018-ci ilə qədər "Məhsuldarlığın Məlumatlı Operator Vaxtı" məlumatından istifadə etdik. Məlumatlara daxil ola bilərsiniz burada mövcud olan skriptimizdən istifadə edin.

Verilənlər bazası ölçüsü 85 sütundan ibarət bir cədvəl təmin edən 109 GB-dır.

Benchmark Sorğuları

ClickHouse, clickhousedb_fdw və PostgreSQL-i müqayisə etmək üçün istifadə etdiyim sorğular bunlardır.

Q#
Sorğu Aqreqatları və Qrupları ehtiva edir

Q1
HƏFTƏNİN GÜNÜNÜ SEÇİN, (*) c kimi hesablayın, HARƏDƏ İl >= 2000 VƏ 2008 <= XNUMX-ci il Həftənin Gününə görə QRUP SİFARİŞ EDİN.

Q2
HƏFTƏNİN GÜNÜNÜ SEÇİN, HARƏDƏ DepGecikmə>10 VƏ İl >= 2000 VƏ 2008 İli <= XNUMX-ci ilin vaxtından etibarən c kimi hesablayın.

Q3
Mənşəyi SEÇİN, (*) c kimi hesablayın HARƏDƏ DepDelay>10 VƏ İl >= 2000 VƏ İl <= 2008 Mənşə GÖRƏ QRUP SİFARİŞ C DESC LIMIT 10;

Q4
Daşıyıcını SEÇİN, sayın() VAXTINDAN HARADA DepDelay>10 VƏ İl = 2007 QRUP Daşıyıcıya GÖRƏ SİFARİŞ Sayıya görə() DESC;

Q5
SEÇİN a.Daşıyıcı, c, c2, c1000/c2 kimi c3 FROM ( Daşıyıcı SEÇ, say() AS c vaxtında HARƏDƏ DepDelay>10 VƏ 2007-ci il=Daşıyıcıya görə QRUP ) a DAXİLİ BİRLƏŞMƏ ( Daşıyıcı SEÇ,(*) c2 kimi hesabla, VAXTINDAN HARA İl=2007 Daşıyıcıya görə Qrup)b a.Daşıyıcıda=b.Daşıyıcı SİFARİŞ BY c3 DESC;

Q6
SEÇİN a.Daşıyıcı, c, c2, c1000/c2 kimi c3 FROM ( Daşıyıcı SEÇ, say() AS c vaxtında HARƏDƏ DepDelay>10 VƏ İl >= 2000 VƏ İLLƏR <= 2008 QRUP Daşıyıcıya görə) a DAXİLİ QOŞULMA ( Daşıyıcı SEÇİN, c2 kimi sayın(*) HARƏDƏ İl >= 2000 VƏ İl <= 2008 GRO UP Daşıyıcı ) b-də a.Daşıyıcı=b.Daşıyıcı SİFARİŞ BY c3 AÇIQLAMA;

Q7
Daşıyıcı SEÇİN, orta(DepDelay) * 1000 AS c3 VAXTINDAN HARADA İl >= 2000 VƏ İl <= 2008 Daşıyıcıya görə QRUP;

Q8
İl SEÇİN, orta(DepGecikdirmə) İlə GÖRƏ vaxtında QRUPDAN;

Q9
İl seçin, İllər üzrə iş vaxtı qrupundan c1 kimi hesablayın (*);

Q10
SEÇİN orta(cnt) FROM (İl,Ay,say(*) AS cnt FROM VAXTINDA SEÇİN HARƏDƏ DepDel15=İlə,Aya görə 1 QRUP) a;

Q11
orta (c1) arasından seçin (İl,Ay,say(*) kimi c1 kimi iş vaxtı qrupundan İl,Ay seçin) a;

Q12
SEÇİN OriginCityName, DestCityName, count(*) AS c FROM VAXTINDA QRUP OriginCityName BY, DestCityName SİPARİŞ BY c DESC LIMIT 10;

Q13
OriginCityName SEÇİN, (*) AS c AS QRUP OriginCityName BY SİFARİŞ BY SİFARİŞ QRUP AŞAĞILIQ LİMİT;

Sorğuda Qoşulmalar var

Q14
SEÇİN a.İl, c1/c2 FROM ( İl, say seçin()1000 vaxtından c1 olaraq HARADA DepDelay>10 QRUP İlə) a DAXİLİ QOŞULUŞ (İl seçin, c2 kimi sayın(*) İlə görə QRUP ) b a.İl=b.İlə SİFARİŞ a.İlə;

Q15
SEÇİN a.”İl”, c1/c2 FROM ( “İl seçin”, say()1000 olaraq c1 fontime FROM HERDE “DepDelay”>10 QROUP BY “Year”) a DAXİLİ QOŞULUŞ (“İl” seçin, c2 kimi sayın(*) fontime QRUP “İl” ) b üzrə a.”İl”=b. "İl";

Cədvəl-1: Bençmarkda istifadə edilən sorğular

Sorğu icraları

Budur, müxtəlif verilənlər bazası parametrlərində işlədilmiş sorğuların hər birinin nəticələri: indeksli və indekssiz PostgreSQL, yerli ClickHouse və clickhousedb_fdw. Vaxt millisaniyələrlə göstərilir.

Q#
PostgreSQL
PostgreSQL (İndeksli)
Basın Evi
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

Cədvəl-1: Bençmarkda istifadə edilən sorğuların yerinə yetirilməsi üçün sərf olunan vaxt

Nəticələrə baxın

Qrafik sorğunun icra müddətini millisaniyələrlə, X oxu yuxarıdakı cədvəllərdən sorğu nömrəsini, Y oxu isə icra müddətini millisaniyələrlə göstərir. ClickHouse nəticələri və clickhousedb_fdw istifadə edərək postgres-dən alınan məlumatlar göstərilir. Cədvəldən görə bilərsiniz ki, PostgreSQL və ClickHouse arasında böyük fərq var, lakin ClickHouse və clickhousedb_fdw arasında minimal fərq var.

PostgreSQL, ClickHouse və clickhousedb_fdw (PostgreSQL) proqramlarında analitik sorğuların performansının sınaqdan keçirilməsi

Bu qrafik ClickhouseDB və clickhousedb_fdw arasındakı fərqi göstərir. Əksər sorğularda FDW əlavə xərcləri Q12 istisna olmaqla, o qədər də yüksək deyil və çətin ki, əhəmiyyətlidir. Bu sorğuya birləşmələr və ORDER BY bəndi daxildir. ORDER BY GROUP/BY bəndinə görə ORDER BY ClickHouse-a düşmür.

Cədvəl 2-də biz Q12 və Q13 sorğularında vaxt sıçrayışını görürük. Yenə də buna ORDER BY bəndi səbəb olur. Bunu təsdiqləmək üçün mən Q-14 və Q-15 sorğularını ORDER BY bəndi ilə və olmadan icra etdim. ORDER BY bəndi olmadan tamamlama vaxtı 259ms, ORDER BY bəndi ilə isə 1364212-dir. Bu sorğuda debug etmək üçün mən həm sorğuları izah edirəm və burada izahatın nəticələri var.

Q15: SİFARİŞ BY Maddə olmadan

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-ci sual: ORDER BY bəndi olmadan sorğu

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-cü sual: ORDER BY bəndi ilə sorğu

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-cü Qrup: ORDER BY Maddə ilə Sorğu Planı

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)

Buraxılış

Bu təcrübələrin nəticələri göstərir ki, ClickHouse həqiqətən yaxşı performans təklif edir və clickhousedb_fdw PostgreSQL-dən ClickHouse-un performans üstünlüklərini təklif edir. Clickhousedb_fdw istifadə edərkən bir qədər əlavə xərc olsa da, bu, əhəmiyyətsizdir və ClickHouse verilənlər bazasında yerli olaraq işləməklə əldə edilən performansla müqayisə edilə bilər. Bu da PostgreSQL-də fdw-nin əla nəticələr verdiyini təsdiqləyir.

Clickhouse vasitəsilə Telegram söhbəti https://t.me/clickhouse_ru
PostgreSQL istifadə edərək Telegram söhbəti https://t.me/pgsql

Mənbə: www.habr.com

Добавить комментарий