בדיקת הביצועים של שאילתות אנליטיות ב-PostgreSQL, ClickHouse ו-clickhousedb_fdw (PostgreSQL)

במחקר זה, רציתי לראות אילו שיפורים בביצועים ניתן להשיג על ידי שימוש במקור נתונים של ClickHouse ולא ב-PostgreSQL. אני מכיר את יתרונות הפרודוקטיביות שאני מקבל מהשימוש ב-ClickHouse. האם ההטבות הללו ימשיכו אם איגש ל-ClickHouse מ-PostgreSQL באמצעות עטיפת נתונים זרה (FDW)?

סביבות מסד הנתונים הנלמדות הן PostgreSQL v11, clickhousedb_fdw ומסד הנתונים של ClickHouse. בסופו של דבר, מ-PostgreSQL v11 נריץ שאילתות SQL שונות המנותבות דרך clickhousedb_fdw שלנו למסד הנתונים של ClickHouse. לאחר מכן נראה כיצד הביצועים של FDW משתווים לאותן שאילתות הפועלות ב-PostgreSQL המקורי וב-ClickHouse.

מסד נתונים של קליקהאוס

ClickHouse היא מערכת קוד פתוח לניהול מסדי נתונים עמודים שיכולה להשיג ביצועים מהירים פי 100-1000 מגישות מסד נתונים מסורתיות, המסוגלת לעבד למעלה ממיליארד שורות בפחות משנייה.

Clickhousedb_fdw

clickhousedb_fdw - מעטפת הנתונים החיצונית עבור מסד הנתונים של ClickHouse, או FDW, הוא פרויקט קוד פתוח מבית Percona. הנה קישור למאגר GitHub של הפרויקט.

במרץ כתבתי בלוג שמספר לכם יותר על ה-FDW שלנו.

כפי שתראה, זה מספק FDW עבור ClickHouse המאפשר SELECT ממסד הנתונים של ClickHouse ו-INSERT INTO משרת PostgreSQL v11.

FDW תומך בתכונות מתקדמות כגון צבירה והצטרפות. זה משפר משמעותית את הביצועים על ידי שימוש במשאבים של השרת המרוחק לפעולות עתירות משאבים אלו.

סביבת בנצ'מרק

• שרת Supermicro:
  • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
  • 2 שקעים / 28 ליבות / 56 חוטים
  • זיכרון: 256 ג'יגה זיכרון RAM
  • אחסון: Samsung SM863 1.9TB Enterprise SSD
  • מערכת קבצים: ext4/xfs
• מערכת הפעלה: Linux smblade01 4.15.0-42-גנרי #45~16.04.1-Ubuntu
• PostgreSQL: גרסה 11

בדיקות בנצ'מרק

במקום להשתמש במערך נתונים שנוצר על ידי מכונה עבור בדיקה זו, השתמשנו בנתוני "פרודוקטיביות לפי זמן דיווח מפעיל זמן" מ-1987 עד 2018. אתה יכול לגשת לנתונים באמצעות הסקריפט שלנו הזמין כאן.

גודל מסד הנתונים הוא 85 GB, המספק טבלה אחת של 109 עמודות.

שאילתות בנצ'מרק

להלן השאילתות שהשתמשתי בהן כדי להשוות את ClickHouse, clickhousedb_fdw ו-PostgreSQL.

Q#
שאילתה מכילה אגרגטים וקבץ לפי

Q1
בחר DayOfWeek, count(*) AS c FROM ontime WHERE שנה >= 2000 ושנה <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
בחר DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 ושנה >= 2000 ושנה <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 ושנה >= 2000 ושנה <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;

Q4
SELECT Carrier, count() FROM ontime WHERE DepDelay>10 ושנה = 2007 קבוצה לפי ספק הזמנה לפי ספירה() DESC;

Q5
SELECT a.Carrier, c, c2, c1000/c2 כ-c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 ושנה=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 ORDER BY c3 DESC;

Q6
SELECT a.Carrier, c, c2, c1000/c2 כ-c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 ושנה >= 2000 ושנה <= 2008 GROUP BY Carrier) a INNER JOIN (בחר ספק, ספירה(*) AS c2 FROM ontime WHERE שנה >= 2000 ושנה <= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE שנה >= 2000 ושנה <= 2008 GROUP BY ספק;

Q8
בחר שנה, avg(DepDelay) FROM בזמן GROUP BY Year;

Q9
בחר שנה, ספירה(*) כ-c1 מקבוצת זמן לפי שנה;

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

Q11
בחר avg(c1) from (בחר שנה, חודש, ספירה(*) כ-c1 מקבוצת זמן לפי שנה, חודש) א;

Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;

Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;

שאילתה מכילה הצטרפות

Q14
SELECT a.Year, c1/c2 FROM (בחר שנה, count()1000 כ-c1 מ-Ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (בחר שנה, ספור(*) כ-c2 מ-Ontime GROUP BY Year ) b על a.Year=b.Year ORDER BY a.Year;

Q15
SELECT a."Year", c1/c2 FROM (בחר "Year", count()1000 כ-c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (בחרו “Year”, חשבו(*) כ-c2 FROM fontime GROUP לפי “Year” ) b on a.”Year”=b. "שָׁנָה";

טבלה-1: שאילתות בשימוש ב-benchmark

ביצוע שאילתות

להלן התוצאות של כל אחת מהשאילתות כשהן מופעלות בהגדרות מסד נתונים שונות: PostgreSQL עם וללא אינדקסים, native 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: הזמן שנדרש לביצוע השאילתות המשמשות ב-benchmark

צפה בתוצאות

הגרף מציג את זמן ביצוע השאילתה באלפיות שניות, ציר X מציג את מספר השאילתה מהטבלאות שלמעלה, וציר Y מציג את זמן הביצוע באלפיות שניות. מוצגים תוצאות ClickHouse ונתונים שאוחזרו מ-postgres באמצעות clickhousedb_fdw. מהטבלה ניתן לראות שיש הבדל עצום בין PostgreSQL ל-ClickHouse, אך הבדל מינימלי בין ClickHouse ל-clickhousedb_fdw.

גרף זה מציג את ההבדל בין ClickhouseDB ל-clickhousedb_fdw. ברוב השאילתות, תקורה של FDW אינה כל כך גבוהה והיא בקושי משמעותית למעט Q12. שאילתה זו כוללת הצטרפות וסעיף ORDER BY. בגלל סעיף ORDER BY GROUP/BY, ORDER BY לא יורד ל-ClickHouse.

בטבלה 2 אנו רואים את קפיצת הזמן בשאילתות Q12 ו-Q13. שוב, זה נגרם על ידי סעיף ORDER BY. כדי לאשר זאת, הרצתי שאילתות Q-14 ו-Q-15 עם ובלי סעיף ORDER BY. ללא סעיף ORDER BY זמן ההשלמה הוא 259ms ועם סעיף 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 מציע את יתרונות הביצועים של ClickHouse מ-PostgreSQL. אמנם יש תקורה מסוימת בעת שימוש ב-clickhousedb_fdw, אך הוא זניח וניתן להשוואה לביצועים שהושגו על ידי הפעלה מקורית על מסד הנתונים של ClickHouse. זה גם מאשר ש-fdw ב-PostgreSQL מספק תוצאות מצוינות.

צ'אט טלגרם דרך קליקהאוס https://t.me/clickhouse_ru
צ'אט בטלגרם באמצעות PostgreSQL https://t.me/pgsql

מקור: www.habr.com