Bu çalışmada PostgreSQL yerine ClickHouse veri kaynağı kullanılarak ne gibi performans iyileştirmeleri sağlanabileceğini görmek istedim. ClickHouse'u kullanarak elde ettiğim üretkenlik avantajlarını biliyorum. ClickHouse'a PostgreSQL'den Yabancı Veri Sarmalayıcı (FDW) kullanarak erişirsem bu avantajlar devam edecek mi?
İncelenen veritabanı ortamları PostgreSQL v11, clickhousedb_fdw ve ClickHouse veritabanıdır. Son olarak PostgreSQL v11'den itibaren clickhousedb_fdw aracılığıyla ClickHouse veritabanına yönlendirilen çeşitli SQL sorgularını çalıştıracağız. Daha sonra FDW'nin performansının yerel PostgreSQL ve yerel ClickHouse'da çalışan aynı sorgularla nasıl karşılaştırıldığını göreceğiz.
Clickhouse Veritabanı
ClickHouse, geleneksel veritabanı yaklaşımlarına göre 100-1000 kat daha hızlı performansa ulaşabilen, bir milyardan fazla satırı bir saniyeden daha kısa sürede işleyebilen, açık kaynaklı sütunlu bir veritabanı yönetim sistemidir.
Clickhousedb_fdw
clickhousedb_fdw - ClickHouse veritabanı veya FDW için harici veri sarmalayıcı, Percona'nın açık kaynaklı bir projesidir. .
.
Göreceğiniz gibi bu, ClickHouse için PostgreSQL v11 sunucusundan ClickHouse veritabanından SELECT'e ve INSERT INTO'ya izin veren bir FDW sağlar.
FDW, toplama ve birleştirme gibi gelişmiş özellikleri destekler. Bu, kaynak yoğun işlemler için uzak sunucunun kaynaklarını kullanarak performansı önemli ölçüde artırır.
Karşılaştırma ortamı
- Süper mikro sunucu:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 soket / 28 çekirdek / 56 iş parçacığı
- Bellek: 256GB RAM
- Depolama: Samsung SM863 1.9 TB Kurumsal SSD
- Dosya sistemi: ext4/xfs
- İşletim Sistemi: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: sürüm 11
Benchmark testleri
Bu test için makine tarafından oluşturulan bazı veri setlerini kullanmak yerine, 1987'den 2018'e kadar "Zamana Göre Raporlanan Operatör Süresine Göre Üretkenlik" verilerini kullandık. Verilere ulaşabilirsiniz .
Veritabanı boyutu 85 GB'dir ve 109 sütundan oluşan bir tablo sağlar.
Karşılaştırma Sorguları
ClickHouse, clickhousedb_fdw ve PostgreSQL'i karşılaştırmak için kullandığım sorgular şunlardır.
Q#
Sorgu Toplamaları ve Gruplandırma Ölçütünü İçerir
Q1
Haftanın Günü'nü SEÇİN, count(*) AS c NEREDEN NEREDE Yıl >= 2000 VE Yıl <= 2008 Haftanın Gününe GÖRE GRUPLA c TANIMINA GÖRE SİPARİŞ;
Q2
Haftanın Günü'nü SEÇİN, sayı(*) OLARAK c'den itibaren ontime NEREDE DepDelay>10 VE Yıl >= 2000 VE Yıl <= 2008 Haftanın Gününe Göre Grupla c TANIMINA GÖRE SİPARİŞ;
Q3
Menşei SEÇİN, sayı(*) OLARAK c FROM ontime NEREDE DepDelay>10 VE Yıl >= 2000 VE Yıl <= 2008 Menşee Göre Grupla SİPARİŞE GÖRE c TANIM LİMİTİ 10;
Q4
Taşıyıcıyı SEÇİN, say() Zamanından itibaren NEREDE DepDelay>10 VE Yıl = 2007 Taşıyıcıya Göre Grup Sipariş Sayısına Göre() DESC;
Q5
a.Taşıyıcıyı SEÇİN, c, c2, c1000/c2 as c3 FROM ( Taşıyıcıyı SEÇİN, say() c FROM ontime WHERE DepDelay>10 VE Yıl=2007 GRUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GRUP BY Carrier)b on a.Carrier=b.Carrier ORDER c3 DESC'E GÖRE;
Q6
a.Taşıyıcıyı SEÇİN, c, c2, c1000/c2 as c3 FROM ( Taşıyıcıyı SEÇİN, say() c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GRUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Taşıyıcı ) b a.Taşıyıcı=b.Taşıyıcı SİPARİŞ BY c3 DESC;
Q7
Taşıyıcıyı SEÇİN, avg(DepDelay) * 1000 AS c3 ontime'DAN NEREDE Yıl >= 2000 VE Yıl <= 2008 Taşıyıcıya Göre GRUP;
Q8
ontime GRUPTAN YILA GÖRE Yıl, avg(DepDelay) SEÇİN;
Q9
Yıl'ı seçin, Yıla göre ontime grubundan (*) c1 olarak sayın;
Q10
SELECT avg(cnt) FROM (SELECT Year,Moth,count(*) AS cnt FROM ontime WHERE DepDel15=1 GRUP BY Yıl,Ay) a;
Q11
avg(c1)'i seçin (Yıl,Ay'a göre ontime grubundan c1 olarak Yıl,Ay,sayımı(*) seçin) a;
Q12
OriginCityName, DestCityName, count(*) AS c'Yİ SEÇİN ontime'DAN GRUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
OriginCityName'i SEÇİN, count(*) AS c FROM ontime'dan GRUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Sorgu Birleştirmeleri İçerir
Q14
SELECT a.Yıl, c1/c2 FROM ( Yılı seçin, say()1000 c1 olarak ontime'dan NEREDE DepDelay>10 GRUP BAZI YIL) a INNER JOIN (Yıl'ı seçin, (*) ontime'dan c2 olarak sayın GROUP BY Year ) b on a.Yıl=b.Yıl ORDER BY a.Yıl;
Q15
a.”Yıl”ı SEÇİN, c1/c2 FROM ( “Yıl”ı seçin, say()1000 olarak c1 FROM fontime NEREDE “DepDelay”>10 GRUP BY “Yıl”) a INNER JOIN (“Yıl”ı seçin, say(*) olarak c2 FROM fontime GROUP BY “Yıl” ) b üzerinde a.”Yıl”=b. "Yıl";
Tablo-1: Karşılaştırmada kullanılan sorgular
Sorgu yürütmeleri
Farklı veritabanı ayarlarında çalıştırıldığında sorguların her birinin sonuçları şunlardır: Dizinli ve dizinsiz PostgreSQL, yerel ClickHouse ve clickhousedb_fdw. Zaman milisaniye cinsinden gösterilir.
Q#
PostgreSQL
PostgreSQL (Dizinlenmiş)
Tıklama 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
Tablo-1: Karşılaştırmada kullanılan sorguların yürütülmesi için geçen süre
Sonuçları Görüntüle
Grafik, sorgu yürütme süresini milisaniye cinsinden gösterir, X ekseni yukarıdaki tablolardaki sorgu numarasını gösterir ve Y ekseni, yürütme süresini milisaniye cinsinden gösterir. ClickHouse sonuçları ve postgres'ten clickhousedb_fdw kullanılarak alınan veriler gösterilmektedir. Tablodan PostgreSQL ile ClickHouse arasında büyük bir fark olduğunu, ancak ClickHouse ile clickhousedb_fdw arasında çok az fark olduğunu görebilirsiniz.
Bu grafik, ClickhouseDB ile clickhousedb_fdw arasındaki farkı gösterir. Çoğu sorguda, FDW yükü o kadar yüksek değildir ve 12. Çeyrek dışında neredeyse hiç önemli değildir. Bu sorgu, birleştirmeleri ve ORDER BY yan tümcesini içerir. ORDER BY GROUP/BY deyimi nedeniyle ORDER BY, ClickHouse'a düşmez.
Tablo 2'de Q12 ve Q13 sorgularındaki zaman sıçramasını görüyoruz. Bu da yine ORDER BY deyiminden kaynaklanmaktadır. Bunu doğrulamak için, ORDER BY cümleciğiyle ve olmadan S-14 ve S-15 sorgularını çalıştırdım. ORDER BY cümlesi olmadan tamamlanma süresi 259 ms, ORDER BY cümlesi ile ise 1364212'dir. Bu sorguda hata ayıklamak için hem sorguları açıklıyorum hem de açıklamanın sonuçlarını burada veriyorum.
S15: ORDER BY Maddesi 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";S15: ORDER BY Cümlesi Olmadan Sorgu
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)S14: ORDER BY Cümlesiyle Sorgulama
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";S14: ORDER BY Maddesiyle Sorgu 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)Aviator apk
Bu deneylerin sonuçları, ClickHouse'un gerçekten iyi bir performans sunduğunu ve clickhousedb_fdw'nin PostgreSQL'den ClickHouse'un performans avantajlarını sunduğunu gösteriyor. Clickhousedb_fdw kullanıldığında bir miktar ek yük olsa da, bu ihmal edilebilir düzeydedir ve ClickHouse veritabanında yerel olarak çalıştırılarak elde edilen performansla karşılaştırılabilir. Bu aynı zamanda PostgreSQL'deki fdw'nin mükemmel sonuçlar sağladığını da doğrular.
Clickhouse aracılığıyla Telegram sohbeti
PostgreSQL kullanarak Telegram sohbeti
Kaynak: habr.com