Бұл зерттеуде мен PostgreSQL емес, ClickHouse деректер көзін пайдалану арқылы өнімділікті жақсартуға қандай қол жеткізуге болатынын көргім келді. Мен ClickHouse пайдалану арқылы алатын өнімділік артықшылықтарын білемін. Егер мен ClickHouse-ға PostgreSQL жүйесінен шетелдік деректер орауыш (FDW) арқылы кірсем, бұл артықшылықтар жалғаса ма?
Зерттелетін дерекқор орталары – PostgreSQL v11, clickhousedb_fdw және ClickHouse дерекқоры. Сайып келгенде, PostgreSQL v11 нұсқасынан біз clickhousedb_fdw арқылы ClickHouse дерекқорына бағытталатын әртүрлі SQL сұрауларын іске қосамыз. Содан кейін біз FDW өнімділігі жергілікті PostgreSQL және жергілікті ClickHouse жүйесінде орындалатын бірдей сұраулармен қалай салыстыратынын көреміз.
Clickhouse деректер базасы
ClickHouse – бір секундтан аз уақыт ішінде миллиардтан астам жолды өңдеуге қабілетті, дәстүрлі дерекқор тәсілдеріне қарағанда өнімділікке 100-1000 есе жылдам қол жеткізе алатын ашық бастапқы дерекқорды басқару жүйесі.
Clickhousedb_fdw
clickhousedb_fdw - ClickHouse дерекқорына арналған сыртқы деректер ораушысы немесе FDW, Percona ұсынған ашық бастапқы жоба болып табылады. .
.
Көріп отырғаныңыздай, бұл PostgreSQL v11 серверінен ClickHouse дерекқорынан SELECT және INSERT INTO мүмкіндік беретін ClickHouse үшін FDW береді.
FDW біріктіру және біріктіру сияқты кеңейтілген мүмкіндіктерді қолдайды. Бұл ресурсты көп қажет ететін операциялар үшін қашықтағы сервердің ресурстарын пайдалану арқылы өнімділікті айтарлықтай жақсартады.
Эталондық орта
- Supermicro сервері:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 ГГц
- 2 розетка / 28 өзек / 56 жіп
- Жад: 256 ГБ жедел жады
- Жад: Samsung SM863 1.9TB Enterprise SSD
- Файлдық жүйе: ext4/xfs
- ОЖ: Linux smblade01 4.15.0-42-жалпы №45~16.04.1-Ubuntu
- PostgreSQL: 11 нұсқасы
Эталондық сынақтар
Осы сынақ үшін кейбір машинада жасалған деректер жинағын пайдаланудың орнына, біз 1987 жылдан 2018 жылға дейін «Оператор уақыты бойынша есеп беретін өнімділік» деректерін қолдандық. Деректерге қол жеткізуге болады .
Дерекқор өлшемі 85 бағаннан тұратын бір кестені қамтамасыз ететін 109 ГБ.
Эталондық сұраулар
Міне, мен ClickHouse, clickhousedb_fdw және PostgreSQL салыстыру үшін пайдаланған сұраулар.
Q#
Сұрау жиынтықтарды және топтауды қамтиды
Q1
Аптаның күнін ТАҢДАҢЫЗ, (*) c ЕСЕПТЕГЕН УАҚЫТТЫ ҚАЙДА Жыл >= 2000 ЖӘНЕ Жыл <= 2008 Аптаның күні БОЙЫНША ТОПТАУ c ДЕСК БОЙЫНША ТАПСЫРЫС;
Q2
Аптаның күнін ТАҢДАҢЫЗ, (*) c ЕСЕПТЕГЕН УАҚЫТТЫ ҚАЙДА DepDelay>10 ЖӘНЕ Жыл >= 2000 ЖӘНЕ Жыл <= 2008 Аптаның күні бойынша ТОПТАУ c ДЕСК БОЙЫНША ТАПСЫРЫС;
Q3
Шығу жерін ТАҢДАҢЫЗ, (*) АСҚАН АСҚАН БЕРІЛГЕН УАҚЫТТЫ ҚАЙДА DepDelay>10 ЖӘНЕ Жыл >= 2000 ЖӘНЕ 2008 Жылы <= 10 Шығу орны бойынша ТОПТАУ ТАРТИП БОЙЫНША c DESC LIMIT XNUMX;
Q4
Тасымалдаушыны ТАҢДАУ, санау() УАҚЫТТЫ ҚАЙДА DepDelay>10 ЖӘНЕ ЖЫЛ = 2007 ТОПТАУ ТАПСЫРЫСЫ БОЙЫНША ТАПСЫРЫС санау() DESC;
Q5
ТАҢДАУ a.Carrier, c, c2, c1000/c2 c3 FROM ретінде ( Тасымалдаушыны ТАҢДАУ, санау() AS c уақытылы ҚАЙДА DepDelay>10 ЖӘНЕ Жылы=2007 ТОПТАУ ТАПСЫРЫСЫ ) a ІШКІ ҚОСЫЛУ ( Тасымалдаушыны ТАҢДАҢЫЗ,(*) c2 РЕТІНДЕ, УАҚЫТТАН ҚАЙДА Жыл=2007 Тасымалдаушы бойынша ТОПТАУ) a.Carrier=b.Carrier ТАПСЫРЫСЫНДА c3 DESC BY;
Q6
ТАҢДАУ a.Carrier, c, c2, c1000/c2 c3 FROM ретінде ( Тасымалдаушыны ТАҢДАУ, санау() AS c уақытылы ҚАЙДА DepDelay>10 ЖӘНЕ Жыл >= 2000 ЖӘНЕ Жылы <= 2008 ТОБЫ Тасымалдаушы бойынша) a INTER JOIN ( Тасымалдаушыны ТАҢДАҢЫЗ, (*) c2 ретінде есептеңіз, WHERE Жыл >= 2000 ЖӘНЕ <= 2008 GRO UP Тасымалдаушы ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
Тасымалдаушыны ТАҢДАУ, орт.(DepDelay) * 1000 AS c3 УАҚЫТТЫ ҚАЙДА Жыл >= 2000 ЖӘНЕ <= 2008 ЖЫЛ.
Q8
Жылды ТАҢДАУ, орт.(DepDelay) ЖЫЛҒА ТАҢДАУ;
Q9
Жылды таңдаңыз, Жыл бойынша уақыт тобынан c1 ретінде санаңыз (*);
Q10
ТАҢДАУ орт.(cnt) FROM (Жыл,Ай,санау(*) ТАҢДАУ АС cnt УАҚЫТТЫ ҚАЙДА DepDel15=Жыл,ай бойынша 1 ТОП) a;
Q11
ортаңғы(c1) ішінен таңдаңыз (Жыл,Ай,сан(*) c1 ретінде Жыл,Ай бойынша уақыт тобынан таңдаңыз) a;
Q12
OriginCityName, DestCityName, count(*) AS C FROM OriginCityName, DestCityName ТАПСЫРЫСЫ БОЙЫНША ТАҢДАУ C DESC LIMIT 10;
Q13
OriginCityName ТАҢДАУ, санау(*) AS c ФОМН УАҚЫТТЫ ТОПТАЙДАН OriginCityName БОЙЫНША ТАПСЫРЫС БОЙЫНША ТАҢДАУ c DESC LIMIT 10;
Сұрауда біріктірулер бар
Q14
ТАҢДАУ a.Year, c1/c2 FROM ( Жылды таңдау, санау()1000 уақытынан бастап c1 ретінде WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (Жылды таңдаңыз, санау(*) уақыты бойынша CROUP BY ) b on a.Year=b.Year ORDER BY a.Year;
Q15
ТАҢДАУ a.”Year”, c1/c2 FROM ( “Жыл” таңдау, санау()1000 ретінде c1 шрифтінен ҚАЙДА “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (“Жыл” таңдаңыз, c2 ретінде санаңыз(*) қаріп уақытынан FROM “Year” ) b on a.”Year”=b. «Жыл»;
Кесте-1: салыстыруда пайдаланылған сұраулар
Сұраныстарды орындау
Мұнда әртүрлі дерекқор параметрлерінде іске қосылған кездегі сұраулардың әрқайсысының нәтижелері берілген: индекстері бар және индекстері жоқ 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
Кесте-1: Эталонда пайдаланылған сұрауларды орындауға кететін уақыт
Нәтижелерді көру
График сұраудың орындалу уақытын миллисекундпен көрсетеді, X осі жоғарыдағы кестелердегі сұрау нөмірін, ал Y осі орындау уақытын миллисекундпен көрсетеді. ClickHouse нәтижелері мен postgres-тен clickhousedb_fdw көмегімен алынған деректер көрсетіледі. Кестеден 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";14-сұрақ: 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 чаты
Ақпарат көзі: www.habr.com