ProHoster > Blog > Administrare > Configurarea regulatorilor PID: este așa de înfricoșător cum îl descriu? Partea 1. Sistem monoconductor

Configurarea regulatorilor PID: este așa de înfricoșător cum îl descriu? Partea 1. Sistem monoconductor

În această cercetare, am dorit să investighez ce îmbunătățiri de performanță pot fi obținute folosind sursa de date ClickHouse în loc de PostgreSQL. Știu ce beneficii de performanță primesc utilizând ClickHouse. Vor fi păstrate aceste avantaje dacă accesez ClickHouse din PostgreSQL prin intermediul unei interfețe externe de date (FDW)?

Mediile de baze de date investigate sunt PostgreSQL v11, clickhousedb_fdw și baza de date ClickHouse. În final, din PostgreSQL v11, vom rula diferite interogări SQL, redirecționate prin clickhousedb_fdw către baza de date ClickHouse. Apoi, vom observa cum se compară performanța FDW cu aceleași interogări executate în PostgreSQL nativ și ClickHouse nativ.

Baza de date Clickhouse

ClickHouse este un sistem de gestionare a bazelor de date de tip coloană cu sursă deschisă, capabil să atingă performanțe de 100-1000 de ori mai rapide decât abordările tradiționale de baze de date, fiind capabil să proceseze peste un miliard de rânduri în mai puțin de o secundă.

Clickhousedb_fdw

clickhousedb_fdw este o interfață externă a bazei de date ClickHouse, sau FDW, un proiect open-source dezvoltat de Percona. Iată un link către depozitul proiectului GitHub.

În martie, am scris un blog care vă povestește mai multe despre FDW-ul nostru.

După cum veți observa, acesta oferă FDW pentru ClickHouse, care permite SELECT from și INSERT INTO în baza de date ClickHouse de pe serverul PostgreSQL v11.

FDW suportă funcții avansate, cum ar fi agregarea și join-ul. Acest lucru crește semnificativ performanța prin utilizarea resurselor serverului la distanță pentru aceste operații intensive în resurse.

Mediul de benchmark

  • Server Supermicro:
    • CPU Intel® Xeon® E5-2683 v3 @ 2.00GHz
    • 2 socluri / 28 nuclee / 56 fire
    • Memorie: 256GB RAM
    • Stocare: Samsung SM863 1.9TB SSD Enterprise
    • Sistem de fișiere: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: versiunea 11

Teste de benchmark

În loc să folosim un set de date generat de mașină pentru acest test, am folosit datele „Performanța pe timp, raportată despre timpul de funcționare al operatorului” din 1987 până în 2018. Puteți accesa datele prin intermediul scriptului nostru, disponibil aici.

Dimensiunea bazei de date este de 85 GB, având o tabelă cu 109 coloane.

Interogări de benchmark

Iată interogările pe care le-am folosit pentru a compara ClickHouse, clickhousedb_fdw și PostgreSQL.

Q#
Interogare conține agregate și Group By

Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

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

Q4
SELECT Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;

Q5
SELECT a.Carrier, c, c2, c1000/c2 as c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year=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 as c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year = 2000 AND Year <= 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 Year >= 2000 AND Year <= 2008 GROUP BY Carrier;

Q8
SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;

Q9
select Year, count(*) as c1 from ontime group by Year;

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

Q11
select avg(c1) from (select Year,Month,count(*) as c1 from ontime group by Year,Month) a;

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;

Query Contains Joins

Q14
SELECT a.Year, c1/c2 FROM ( select Year, count()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;

Q15
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”;

Table-1: Queries used in benchmark

Query executions

Iată rezultatele fiecărei interogări efectuate în diferite configurații ale bazei de date: PostgreSQL cu indici și fără, ClickHouse propriu și clickhousedb_fdw. Timpul este exprimat în milisecunde.

Q#
PostgreSQL
PostgreSQL (Indexed)
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

Table-1: Time taken to execute the queries used in benchmark

Vizualizare rezultate

Grafica arată timpul de execuție al interogării în milisecunde, axa X arată numărul interogării din tabelele de mai sus, iar axa Y arată timpul de execuție în milisecunde. Rezultatele ClickHouse și datele obținute din postgres prin clickhousedb_fdw sunt prezentate. Din tabel reiese că există o diferență uriașă între PostgreSQL și ClickHouse, dar o diferență minimă între ClickHouse și clickhousedb_fdw.

Configurarea regulatorilor PID: este așa de înfricoșător cum îl descriu? Partea 1. Sistem monoconductor

Această grafică arată diferența dintre ClickhouseDB și clickhousedb_fdw. În majoritatea interogărilor costurile suplimentare FDW nu sunt atât de mari și abia sunt semnificative, cu excepția Q12. Această interogare include uniri și o clauză ORDER BY. Din cauza clauzei ORDER BY GROUP/BY și ORDER BY nu pot fi omise până la ClickHouse.

În tabelul 2, observăm o creștere a timpului în cererile Q12 și Q13. Reiterând, aceasta este cauzată de utilizarea propoziției ORDER BY. Pentru a confirma acest lucru, am executat cererile Q-14 și Q-15 cu și fără propoziția ORDER BY. Fără propoziția ORDER BY, timpul de finalizare este de 259 ms, iar cu propoziția ORDER BY - 1364212. Pentru a depana această cerere, explic ambele cereri, iar aici sunt prezentate rezultatele explicației.

Q15: Fără Clauza 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";

Q15: Cerere Fără Clauza ORDER BY

PLANUL CERERII                                                      
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))        
Relații: Agregare pe (fontime)        
SQL URemote: 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 pe 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(*))                    
Relații: Agregare pe (fontime)                    
SQL URemote: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)

Q14: Cerere Cu Clauza 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: Planul Cererii cu Clauza ORDER BY

PLANUL CERERII 
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 pe public.fontime  (cost=1.00..-1.00 rows=100000 width=4)               
SQL URemote: 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 pe public.fontime fontime_1  (cost=1.00..-1.00 rows=100000 width=4) 
              
SQL URemote: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)

Ieșire

Rezultatele acestor experimente arată că ClickHouse oferă cu adevărat o performanță bună, iar clickhousedb_fdw aduce avantajele performanței ClickHouse din PostgreSQL. Deși utilizarea clickhousedb_fdw implică unele costuri suplimentare, acestea sunt nesemnificative și comparabile cu performanța obținută prin utilizarea nativă a bazei de date ClickHouse. Aceasta confirmă de asemenea că fdw în PostgreSQL oferă rezultate remarcabile.

Chat Telegram pentru Clickhouse https://t.me/clickhouse_ru
Chat Telegram pentru PostgreSQL https://t.me/pgsql

Sursa: habr.com

Cumpără un hosting fiabil pentru site-uri cu protecție DDoS, servere VPS VDS 🔥 Cumpără un hosting fiabil pentru site-uri cu protecție DDoS, servere VPS VDS | ProHoster