Бул изилдөөдө мен PostgreSQL эмес, ClickHouse маалымат булагын колдонуу менен кандай натыйжалуулукту жакшыртууга болорун көргүм келди. Мен ClickHouse колдонуудан алынган өндүрүмдүүлүктүн артыкчылыктарын билем. Чет элдик маалымат пакетин (FDW) колдонуп PostgreSQLден ClickHouseга кирсем, бул артыкчылыктар улана береби?
Окулган маалымат базасы чөйрөлөрү PostgreSQL v11, clickhousedb_fdw жана ClickHouse маалымат базасы. Акыр-аягы, PostgreSQL v11ден биз ClickHouse маалымат базасына биздин clickhousedb_fdw аркылуу багытталган ар кандай SQL сурамдарын иштетебиз. Андан кийин биз FDWтин өндүрүмдүүлүгү жергиликтүү PostgreSQLде жана жергиликтүү ClickHouseда иштеген ошол эле сурамдарга кандай салыштырарын көрөбүз.
Clickhouse маалымат базасы
ClickHouse - бул ачык булак мамычалык маалымат базасын башкаруу системасы, ал салттуу маалымат базасына караганда 100-1000 эсе тезирээк иштей алат, бир секундага жетпеген убакытта миллиарддан ашык саптарды иштетүүгө жөндөмдүү.
Clickhousedb_fdw
clickhousedb_fdw - ClickHouse маалымат базасы үчүн тышкы маалымат орогуч же FDW, Perconaдан ачык булак долбоору. .
.
Көрүнүп тургандай, бул ClickHouse үчүн FDW менен камсыз кылат, ал PostgreSQL v11 серверинен ClickHouse маалымат базасын SELECT жана INSERT INTO мүмкүндүк берет.
FDW бириктирүү жана кошулуу сыяктуу өркүндөтүлгөн функцияларды колдойт. Бул ресурсту көп талап кылган операциялар үчүн алыскы сервердин ресурстарын колдонуу менен аткарууну кыйла жакшыртат.
Эталондук чөйрө
- Supermicro сервери:
- 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
- PostgreSQL: версия 11
Эталондук тесттер
Бул сыноо үчүн кээ бир машинада түзүлгөн маалымат топтомун колдонуунун ордуна, биз 1987-жылдан 2018-жылга чейин "Оператордун убактысы боюнча өндүрүмдүүлүк" маалыматтарын колдондук. Сиз маалыматтарга кире аласыз .
Маалымат базасынын көлөмү 85 ГБ, 109 тилкеден турган бир таблицаны камсыз кылат.
Бенчмарк сурамдары
Бул жерде мен ClickHouse, clickhousedb_fdw жана PostgreSQLди салыштыруу үчүн колдонгон суроолор.
Q#
Суроо агрегаттарды жана топту камтыйт
Q1
Аптанын күнүн ТАНДОО, (*) ЭСКЕ АЛУУ КЕРЕК ЖЫЛ >= 2000 ЖАНА ЖЫЛ <= 2008 ЖЫЛДЫН КҮНДҮГҮН ЖАНА ТАРТИП С ТҮЗӨТҮҮ БОЮНЧА ТОПТОО;
Q2
Аптанын күнүн ТАНДОО, (*) КАЧАН өз убагында c КЕРЕК КАЙДА DepDelay>10 ЖАНА Жыл >= 2000 ЖАНА Жылы <= 2008 Жылдын DayOfWeek АРТЫКЧЫЛЫГЫ БОЮНЧА ТОПТОП С ТАРТИП БЕРҮҮ;
Q3
ТАҢДАҢЫЗ Origin, AS C FROM OF FROM DepDelay>10 ЖАНА Жылы >= 2000 ЖАНА 2008 Жылы <= 10 ЧЫГЫШЫ БОЮНЧА ТОПТОР ТАРТИП C DESC LIMIT XNUMX;
Q4
SELECT Оператор, count() КАЙДА DepDelay>10 ЖАНА ЖЫЛ = 2007 ТОП БЕРҮҮЧҮ ТАРТИП БОЮНЧА САНОО() DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 катары c3 FROM ( Оператор тандоо, эсептөө() AS c өз убагында КАЙДАН DepDelay>10 ЖАНА Жылы=2007 ТОПТОМУ Оператор боюнча ) a ИЧКИ КОШУЛУУ ( Ташуучуну ТАҢДА,(*) c2 AS КЕРЕК УБАКТЫНАН КАЙДА Жыл=2007 Оператор БОЮНЧА ГРУППА) a.Carrier=b.Carrier БУЮРТЫНДА BY c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 катары c3 FROM ( Оператор тандоо, эсептөө() AS c өз убагында КАЙДА DepDelay>10 ЖАНА Жыл >= 2000 ЖАНА Жылы <= 2008 ГРУППА Оператор боюнча) а ИЧКИ КОШУЛУУ ( Оператор ТАҢДА, (*) c2 катары эсептеп өз убагында КАЙДА Жыл >= 2000 ЖАНА <= 2008 GRO UP Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
ТАҢДАУ Оператор, ort(DepDelay) * 1000 AS c3 УАКЫТЫНАН КАЙДА ЖЫЛ >= 2000 ЖАНА ЖЫЛ <= 2008 ТОПТУРУУЧУ БОЮНЧА;
Q8
Жылды ТАҢДА, орт.(DepDelay) Убактылуу ТОПТОН ЖЫЛ БОЮНЧА;
Q9
Жылды тандаңыз, (*) Жыл боюнча убакыт тобунан c1 катары санаңыз;
Q10
ТАҢДАҢЫЗ орт(cnt) FROM (Жылды,Айды,сананы(*) АС cnt FROM өз убагында КАЙДА DepDel15=1 ГРУППА ЖЫЛ,Ай) a;
Q11
орт(c1) ичинен (Жыл,Ай,сан(*) c1 катары убакыт тобунан Жыл,Ай боюнча тандаңыз) a;
Q12
OriginCityName, DestCityName, count(*) AS C FROM TIME GROUP BY OriginCityName, DestCityName ТАРТИП БОЮНЧА ТАҢДАҢЫЗ c DESC LIMIT 10;
Q13
OriginCityName ТАНДОО, c AS C AS (*) OriginCityName ТАРТИБИ БОЮНЧА ТАРТИБИ C DESC LIMIT 10;
Суроо кошулмаларды камтыйт
Q14
SELECT a.Year, c1/c2 FROM (тандоо Year, count()1000 as c1 on time from WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (Жылды тандаңыз, c2 катары санаңыз (*) өз убагында GROUP BY ) b on a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a.”Year”, c1/c2 FROM ( “Жылды танда”, сана ()1000 as c1 fontime FROM WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (“Жыл” тандаңыз, 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 натыйжалары жана clickhousedb_fdw аркылуу postgres алынган маалыматтар көрсөтүлгөн. Таблицадан PostgreSQL менен ClickHouse ортосунда чоң айырма бар экенин көрө аласыз, бирок ClickHouse менен clickhousedb_fdw ортосунда минималдуу айырма бар.
Бул график ClickhouseDB менен clickhousedb_fdw ортосундагы айырманы көрсөтөт. Көпчүлүк суроо-талаптарда FDW кошумча чыгымы анчалык деле жогору эмес жана 12-чейректи эске албаганда, анча деле маанилүү эмес. Бул суроо кошулмаларды жана ORDER BY пунктун камтыйт. ORDER BY GROUP/BY пунктунан улам, ORDER BY ClickHouse'га түшпөйт.
2-таблицада биз Q12 жана Q13 сурамдарында убакыттын секирүүсүн көрөбүз. Дагы, бул ORDER BY пунктунан келип чыккан. Муну ырастоо үчүн мен Q-14 жана Q-15 сурамдарын ORDER BY пункту менен жана ансыз аткардым. ORDER BY пункту жок аяктоо убактысы 259 мс, ал эми ORDER BY пункту менен 1364212. Бул суроонун мүчүлүштүктөрүн оңдоо үчүн мен эки суроону тең түшүндүрүп жатам жана бул жерде түшүндүрмөнүн натыйжалары.
С15: 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";С15: ORDER BY пункту жок суроо
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: 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 PostgreSQLден ClickHouse эффективдүүлүгүн сунуштайт. Clickhousedb_fdw колдонууда бир аз ашыкча чыгым бар болсо да, бул анча деле маанилүү эмес жана ClickHouse маалымат базасында жергиликтүү иштетүү аркылуу жетишилген көрсөткүчтөргө салыштырууга болот. Бул PostgreSQLдеги fdw эң сонун натыйжаларды берерин тастыктайт.
Clickhouse аркылуу Telegram чат
PostgreSQL аркылуу Telegram чат
Source: www.habr.com