ç§ã®ååã¯ããã¹ã»ãã·ã¥ã³ãã§ããGazinformservice 瀟ã®è£œåããŒã ã®ãœãããŠã§ã¢éçºè²¬ä»»è
ã§ãã
ãã®èšäºã¯ãã§ã®è¬æŒã«åºã¥ããŠäœæãããŸããã
ãã®èšäºã¯ XNUMX ã€ã®éšåã§æ§æãããŸãã
- æ¥ç¶ãä¿è·ããæ¹æ³ã
- ã¢ã¯ã·ã§ã³ã®ç£æ»ãšã¯äœã§ããããŸããããŒã¿ããŒã¹åŽã§äœãèµ·ãã£ãŠããã®ããããŒã¿ããŒã¹ãžã®æ¥ç¶ãèšé²ããæ¹æ³ã¯äœã§ããã
- ããŒã¿ããŒã¹èªäœã®ããŒã¿ãä¿è·ããæ¹æ³ãšããã®ããã«å©çšã§ãããã¯ãããžãŒã
DBMS ã»ãã¥ãªãã£ã® XNUMX ã€ã®ã³ã³ããŒãã³ã: æ¥ç¶ä¿è·ãã¢ã¯ãã£ããã£ç£æ»ãããŒã¿ä¿è·
æ¥ç¶ãä¿è·ãã
ããŒã¿ããŒã¹ã«ã¯çŽæ¥ãŸã㯠Web ã¢ããªã±ãŒã·ã§ã³çµç±ã§éæ¥çã«æ¥ç¶ã§ããŸããéåžžãããžãã¹ ãŠãŒã¶ãŒãã€ãŸã DBMS ãæäœãã人ã¯ãDBMS ãšéæ¥çã«å¯Ÿè©±ããŸãã
æ¥ç¶ã®ä¿è·ã«ã€ããŠè©±ãåã«ãã»ãã¥ãªãã£å¯Ÿçã®æ§é ã決å®ããéèŠãªè³ªåã«çããå¿ èŠããããŸãã
- XNUMX 人ã®ããžãã¹ ãŠãŒã¶ãŒã¯ XNUMX 人㮠DBMS ãŠãŒã¶ãŒã«çžåœããŸãã?
- DBMS ããŒã¿ãžã®ã¢ã¯ã»ã¹ãå¶åŸ¡ãã API ãéããŠã®ã¿æäŸããããããããšãããŒãã«ã«çŽæ¥ã¢ã¯ã»ã¹ããããã
- DBMS ãå¥ã®ä¿è·ãããã»ã°ã¡ã³ãã«å²ãåœãŠãããŠãããã©ããã誰ãã©ã®ããã«å¯Ÿè©±ãããã
- ããŒãªã³ã°/ãããã·å±€ãšäžéå±€ã䜿çšããããã©ãããããã«ãããæ¥ç¶ã®æ§ç¯æ¹æ³ãããŒã¿ããŒã¹ã®äœ¿çšè ã«é¢ããæ å ±ãå€æŽãããå¯èœæ§ããããŸãã
次ã«ãæ¥ç¶ãä¿è·ããããã«ã©ã®ãããªããŒã«ã䜿çšã§ããããèŠãŠã¿ãŸãããã
- ããŒã¿ããŒã¹ ãã¡ã€ã¢ãŠã©ãŒã« ã¯ã©ã¹ ãœãªã¥ãŒã·ã§ã³ã䜿çšããŸããä¿è·å±€ãè¿œå ãããšãå°ãªããšã DBMS ã§äœãèµ·ãã£ãŠãããã®éææ§ãé«ãŸããæ倧ã§ã¯è¿œå ã®ããŒã¿ä¿è·ãæäŸã§ããããã«ãªããŸãã
- ãã¹ã¯ãŒãããªã·ãŒã䜿çšããŸãããããã®äœ¿çšã¯ãã¢ãŒããã¯ãã£ã®æ§ç¯æ¹æ³ã«ãã£ãŠç°ãªããŸãããããã®å ŽåããDBMS ã«æ¥ç¶ãã Web ã¢ããªã±ãŒã·ã§ã³ã®æ§æãã¡ã€ã«å
ã® XNUMX ã€ã®ãã¹ã¯ãŒãã§ã¯ä¿è·ããã«ã¯ååã§ã¯ãããŸããããŠãŒã¶ãŒãšãã¹ã¯ãŒãã®æŽæ°ãå¿
èŠãã©ãããå¶åŸ¡ã§ãã DBMS ããŒã«ãå€æ°ãããŸãã
ãŠãŒã¶ãŒè©äŸ¡æ©èœã®è©³çŽ°ã«ã€ããŠã¯ããã¡ããã芧ãã ããã
ãã㧠ãMS SQL è匱æ§è©äŸ¡è ã«ã€ããŠãç¥ãããšãã§ããŸãããã㧠. - å¿ èŠãªæ å ±ã§ã»ãã·ã§ã³ã®ã³ã³ããã¹ããå å®ãããŸããã»ãã·ã§ã³ãäžéæãªå ŽåãDBMS ã®ãã¬ãŒã ã¯ãŒã¯å ã§èª°ãäœæ¥ããŠããã®ããããããŸãããå®è¡ãããŠããæäœã®ãã¬ãŒã ã¯ãŒã¯å ã§ã誰ãäœãããªãè¡ã£ãŠããã®ãã«é¢ããæ å ±ãè¿œå ã§ããŸãããã®æ å ±ã¯ç£æ»ã§ç¢ºèªã§ããŸãã
- DBMS ãšãšã³ã ãŠãŒã¶ãŒã®éã«ãããã¯ãŒã¯ãåé¢ãããŠããããå¥ã® VLAN å ã«ãªãå Žåã¯ãSSL ãæ§æããŸãããã®ãããªå Žåãã³ã³ã·ã¥ãŒããš DBMS èªäœã®éã®ãã£ãã«ãä¿è·ããããšãäžå¯æ¬ ã§ããã»ãã¥ãªã㣠ããŒã«ã¯ãªãŒãã³ãœãŒã¹ã§ãå ¥æã§ããŸãã
ãã㯠DBMS ã®ããã©ãŒãã³ã¹ã«ã©ã®ãããªåœ±é¿ãåãŒããŸãã?
PostgreSQL ã®äŸãèŠãŠãSSL ã CPU è² è·ã«ã©ã®ãããªåœ±é¿ãåãŒããã¿ã€ãã³ã°ãå¢å ããTPS ãæžå°ãããããŸããSSL ãæå¹ã«ããå Žåã«å€ãã®ãªãœãŒã¹ãæ¶è²»ããããã©ããã確èªããŠã¿ãŸãããã
pgbench ã䜿çšãã PostgreSQL ã®ããŒãã¯ãããã©ãŒãã³ã¹ ãã¹ããå®è¡ããããã®ç°¡åãªããã°ã©ã ã§ããåäžã®äžé£ã®ã³ãã³ãããå Žåã«ãã£ãŠã¯äžŠåããŒã¿ããŒã¹ ã»ãã·ã§ã³ã§ç¹°ãè¿ãå®è¡ããå¹³åãã©ã³ã¶ã¯ã·ã§ã³ ã¬ãŒããèšç®ããŸãã
ãã¹ã 1 SSL ãªããš SSL äœ¿çš â æ¥ç¶ã¯ãã©ã³ã¶ã¯ã·ã§ã³ããšã«ç¢ºç«ãããŸãã
pgbench.exe --connect -c 10 -t 5000 "host=192.168.220.129 dbname=taskdb user=postgres sslmode=require
sslrootcert=rootCA.crt sslcert=client.crt sslkey=client.key"
vs
pgbench.exe --connect -c 10 -t 5000 "host=192.168.220.129 dbname=taskdb user=postgres"
ãã¹ã 2 SSL ãªããš SSL äœ¿çš â ãã¹ãŠã®ãã©ã³ã¶ã¯ã·ã§ã³ã¯ XNUMX ã€ã®æ¥ç¶ã§å®è¡ãããŸãã
pgbench.exe -c 10 -t 5000 "host=192.168.220.129 dbname=taskdb user=postgres sslmode=require
sslrootcert=rootCA.crt sslcert=client.crt sslkey=client.key"
vs
pgbench.exe -c 10 -t 5000 "host=192.168.220.129 dbname=taskdb user=postgres"
ãã®ä»ã®èšå®:
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 5000
number of transactions actually processed: 50000/50000
ãã¹ãçµæ:
SSLãªã
SSL
ãã©ã³ã¶ã¯ã·ã§ã³ããšã«æ¥ç¶ã確ç«ãããŸã
å¹³åã¬ã€ãã³ã·ãŒ
171.915ããªç§
187.695ããªç§
æ¥ç¶ç¢ºç«ãå«ã tps
58.168112
53.278062
確ç«äžã®æ¥ç¶ãé€ã tps
64.084546
58.725846
CPU
èŠèŽè
ã®ïŒïŒ%ã
èŠèŽè
ã®ïŒïŒ%ã
ãã¹ãŠã®ãã©ã³ã¶ã¯ã·ã§ã³ã¯ XNUMX ã€ã®æ¥ç¶ã§å®è¡ãããŸã
å¹³åã¬ã€ãã³ã·ãŒ
6.722ããªç§
6.342ããªç§
æ¥ç¶ç¢ºç«ãå«ã tps
1587.657278
1576.792883
確ç«äžã®æ¥ç¶ãé€ã tps
1588.380574
1577.694766
CPU
èŠèŽè
ã®ïŒïŒ%ã
èŠèŽè
ã®ïŒïŒ%ã
è»œè² è·ã§ã¯ãSSL ã®åœ±é¿ã¯æž¬å®èª€å·®ã«å¹æµããŸãã転éãããããŒã¿éãéåžžã«å€§ããå Žåã¯ãç¶æ³ãç°ãªãå ŽåããããŸãããã©ã³ã¶ã¯ã·ã§ã³ããšã« XNUMX ã€ã®æ¥ç¶ã確ç«ããå Žå (ããã¯ãŸãã§ãéåžžãæ¥ç¶ã¯ãŠãŒã¶ãŒéã§å ±æãããŸã)ãå€æ°ã®æ¥ç¶/åæãããå Žåã圱é¿ã¯å°ã倧ãããªãå¯èœæ§ããããŸããã€ãŸããããã©ãŒãã³ã¹ãäœäžãããªã¹ã¯ã¯ãããŸãããä¿è·ã䜿çšããªãã»ã©å€§ããªå·®ã¯ãããŸããã
åäœã¢ãŒããæ¯èŒãããšãåãã»ãã·ã§ã³å ã§äœæ¥ããŠããããç°ãªãã»ãã·ã§ã³å ã§äœæ¥ããŠããããšãã倧ããªéããããããšã«æ³šæããŠãã ãããããã¯åœç¶ã®ããšã§ãããåæ¥ç¶ã®äœæã«ãªãœãŒã¹ãè²»ããããŸãã
Zabbix ãä¿¡é Œã¢ãŒãã§æ¥ç¶ããå Žåãã€ãŸã md5 ããã§ãã¯ããããèªèšŒã®å¿ èŠããªãã£ãå ŽåããããŸããã次ã«ã顧客㯠md5 èªèšŒã¢ãŒããæå¹ã«ããããã«èŠæ±ããŸãããããã«ããCPUã«å€§ããªè² è·ãããããããã©ãŒãã³ã¹ãäœäžããŠããŸããŸãããç§ãã¡ã¯æé©åããæ¹æ³ãæ¢ãå§ããŸããããã®åé¡ã®è§£æ±ºçã® XNUMX ã€ã¯ããããã¯ãŒã¯å¶éãå®è£ ããDBMS çšã«å¥ã® VLAN ãäœæãã誰ãã©ãããæ¥ç¶ããŠããããæ確ã«ããèšå®ãè¿œå ããèªèšŒãåé€ããããšã§ãããŸããèªèšŒèšå®ãæé©åããŠèªèšŒãæå¹ã«ããéã®ã³ã¹ããåæžããããšãã§ããŸãããäžè¬ã«ãããŸããŸãªèªèšŒæ¹æ³ã®äœ¿çšã¯ããã©ãŒãã³ã¹ã«åœ±é¿ãäžãããããDBMS çšã®ãµãŒã㌠(ããŒããŠã§ã¢) ã®èšç®èœåãèšèšããéã«ã¯ããããã®èŠçŽ ãèæ ®ããå¿ èŠããããŸãã
çµè«: å€ãã®ãœãªã¥ãŒã·ã§ã³ã§ã¯ãèªèšŒã«ãããå°ããªãã¥ã¢ã³ã¹ã§ãããããžã§ã¯ãã«å€§ããªåœ±é¿ãäžããå¯èœæ§ããããå®çšŒåç°å¢ã«å®è£ ãããŠåããŠãããæããã«ãªãã®ã¯åé¡ã§ãã
ã¢ã¯ã·ã§ã³ç£æ»
ç£æ»ã¯ DBMS ã ãã§ã¯ãããŸãããç£æ»ãšã¯ãããŸããŸãªã»ã°ã¡ã³ãã§äœãèµ·ãã£ãŠãããã«é¢ããæ å ±ãååŸããããšã§ããããã¯ãããŒã¿ããŒã¹ ãã¡ã€ã¢ãŠã©ãŒã«ãŸã㯠DBMS ãæ§ç¯ãããŠãããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã®ããããã§ãã
åçšã®ãšã³ã¿ãŒãã©ã€ãº ã¬ãã«ã® DBMS ã§ã¯ç£æ»ã«åé¡ã¯ãããŸãããããªãŒãã³ ãœãŒã¹ã§ã¯å¿ ããããããšã¯éããŸããã PostgreSQL ã«ã¯æ¬¡ã®ãããªæ©èœããããŸãã
- ããã©ã«ãã®ãã° - çµã¿èŸŒã¿ã®ãã°ã
- æ¡åŒµæ©èœ: pgaudit - ããã©ã«ãã®ãã®ã³ã°ã ãã§ã¯äžååãªå Žåã¯ãããã€ãã®åé¡ã解決ããå¥ã®èšå®ã䜿çšã§ããŸãã
ãããªå ã®ã¬ããŒããžã®è¿œå :
ãåºæ¬çãªã¹ããŒãã¡ã³ãã®ãã®ã³ã°ã¯ãlog_statement = all ã䜿çšããæšæºã®ãã®ã³ã°æ©èœã«ãã£ãŠæäŸã§ããŸãã
ããã¯ç£èŠããã®ä»ã®çšéã«ã¯èš±å®¹ãããŸãããç£æ»ã«éåžžå¿ èŠãªè©³çŽ°ã¬ãã«ã¯æäŸãããŸããã
ããŒã¿ããŒã¹äžã§å®è¡ããããã¹ãŠã®æäœã®ãªã¹ããååŸããã ãã§ã¯ååã§ã¯ãããŸããã
ãŸããç£æ»äººã«ãšã£ãŠèå³æ·±ãç¹å®ã®èšè¿°ãèŠã€ããããšãå¯èœã§ããå¿ èŠããããŸãã
æšæºãã°ã¯ãŠãŒã¶ãŒãèŠæ±ããå 容ã瀺ããŸãããpgAudit ã¯ããŒã¿ããŒã¹ãã¯ãšãªãå®è¡ãããšãã«äœãèµ·ãã£ããã®è©³çŽ°ã«çŠç¹ãåœãŠãŸãã
ããšãã°ãç£æ»äººã¯ãç¹å®ã®ããŒãã«ãææžåãããã¡ã³ããã³ã¹æéå ã«äœæãããããšã確èªãããå ŽåããããŸãã
ããã¯ãåºæ¬çãªç£æ»ãš grep ã䜿çšããç°¡åãªã¿ã¹ã¯ã®ããã«æãããããããŸãããã次ã®ãã㪠(æå³çã«æ··ä¹±ããã) äŸã衚瀺ããããã©ãã§ããããã
ãããŸã$$
ãã®ã³
|| 'CREATE TABLE ã€ã³ããŒã' ãå®è¡ããŸãã 'ant_table(id int)';
çµäº$$;
æšæºã®ãã°ã§ã¯æ¬¡ã®ããšãåŸãããŸãã
ãã°: ã¹ããŒãã¡ã³ã: DO $$
ãã®ã³
|| 'CREATE TABLE ã€ã³ããŒã' ãå®è¡ããŸãã 'ant_table(id int)';
çµäº$$;
ããŒãã«ãåçã«äœæãããå Žåãç®çã®ããŒãã«ãèŠã€ããã«ã¯ãããçšåºŠã®ã³ãŒãã®ç¥èãå¿ èŠã«ãªãå ŽåããããŸãã
åçŽã«ããŒãã«åã§æ€çŽ¢ããæ¹ãæãŸãããããããã¯çæ³çã§ã¯ãããŸããã
ãã㧠pgAudit ã圹ã«ç«ã¡ãŸãã
åãå ¥åã«å¯ŸããŠããã°ã«æ¬¡ã®åºåãçæãããŸãã
ç£æ»: ã»ãã·ã§ã³,33,1ââXNUMX,é¢æ°,DO,,,"DO $$
ãã®ã³
|| 'CREATE TABLE ã€ã³ããŒã' ãå®è¡ããŸãã 'ant_table(id int)';
çµäº$$;"
ç£æ»: SESSION,33,2,DDL,CREATE TABLE,TABLE,public. important_table,CREATE TABLE important_table (id INT)
DO ãããã¯ã ãã§ãªããã¹ããŒãã¡ã³ã ã¿ã€ãããªããžã§ã¯ã ã¿ã€ãããã«ããŒã ãå«ã CREATE TABLE ã®å šæããã°ã«èšé²ããããããæ€çŽ¢ã容æã«ãªããŸãã
SELECT ã¹ããŒãã¡ã³ããš DML ã¹ããŒãã¡ã³ãããã°ã«èšé²ããå Žåãã¹ããŒãã¡ã³ãã§åç §ãããé¢ä¿ããšã«åå¥ã®ãšã³ããªããã°ã«èšé²ããããã« pgAudit ãæ§æã§ããŸãã
ç¹å®ã®ããŒãã«ã«è§Šãããã¹ãŠã®ã¹ããŒãã¡ã³ããèŠã€ããããã«è§£æããå¿
èŠã¯ãããŸãã(
ãã㯠DBMS ã®ããã©ãŒãã³ã¹ã«ã©ã®ãããªåœ±é¿ãåãŒããŸãã?
å®å šãªç£æ»ãæå¹ã«ããŠãã¹ããå®è¡ããPostgreSQL ã®ããã©ãŒãã³ã¹ã«äœãèµ·ããããèŠãŠã¿ãŸãããããã¹ãŠã®ãã©ã¡ãŒã¿ã«å¯ŸããŠæ倧ã®ããŒã¿ããŒã¹ ãã°ãæå¹ã«ããŸãããã
æ§æãã¡ã€ã«ã«ã¯ã»ãšãã©äœãå€æŽããŸãããæãéèŠãªããšã¯ãæ倧éã®æ å ±ãååŸããããã« debug5 ã¢ãŒãããªã³ã«ããããšã§ãã
postgresql.conf
log_destination = 'stderr'
ãã®ã³ã°ã³ã¬ã¯ã¿ãŒ = ãªã³
log_truncate_on_rotation = ãªã³
log_rotation_age = 1d
log_rotation_size = 10MB
log_min_messages = ãããã°5
log_min_error_statement = ãããã°5
log_min_duration_statement = 0
debug_print_parse = ãªã³
debug_print_rewrite = ãªã³
debug_print_plan = ãªã³
debug_pretty_print = ãªã³
log_checkpoints = ãªã³
log_connections = ãªã³
log_disconnections = ãªã³
log_duration = ãªã³
log_hostname = ãªã³
log_lock_waits = ãªã³
log_replication_commands = ãªã³
log_temp_files = 0
log_timezone = 'ãšãŒããã/ã¢ã¹ã¯ã¯'
1 CPUã2,8 GHzã2 GB RAMã40 GB HDD ã®ãã©ã¡ãŒã¿ãŒãåãã PostgreSQL DBMS ã§ã次ã®ã³ãã³ãã䜿çšã㊠XNUMX ã€ã®è² è·ãã¹ããå®è¡ããŸãã
$ pgbench -p 3389 -U postgres -i -s 150 benchmark
$ pgbench -p 3389 -U postgres -c 50 -j 2 -P 60 -T 600 benchmark
$ pgbench -p 3389 -U postgres -c 150 -j 2 -P 60 -T 600 benchmark
è©ŠéšçµæïŒ
ãã®ã³ã°ãªã
ãã®ã³ã°ãã
ããŒã¿ããŒã¹ã®åèšå
å¡«æé
43,74ç§
53,23ç§
ã©ã
èŠèŽè
ã®ïŒïŒ%ã
èŠèŽè
ã®ïŒïŒ%ã
CPU
èŠèŽè
ã®ïŒïŒ%ã
èŠèŽè
ã®ïŒïŒ%ã
ãã¹ã 1 (50 æ¥ç¶)
10åéã®ãã©ã³ã¶ã¯ã·ã§ã³æ°
74169
32445
ãã©ã³ã¶ã¯ã·ã§ã³/ç§
123
54
å¹³åã¬ã€ãã³ã·
405ããªç§
925ããªç§
ãã¹ã 2 (150 ã®æ¥ç¶ã100 ã®æ¥ç¶ãå¯èœ)
10åéã®ãã©ã³ã¶ã¯ã·ã§ã³æ°
81727
31429
ãã©ã³ã¶ã¯ã·ã§ã³/ç§
136
52
å¹³åã¬ã€ãã³ã·
550ããªç§
1432ããªç§
ãµã€ãºã«ã€ããŠ
DBãµã€ãº
2251 MB
2262 MB
ããŒã¿ããŒã¹ã®ãã°ãµã€ãº
0 MB
4587 MB
çµè«ïŒå®å šãªç£æ»ã¯ããŸãè¯ããã®ã§ã¯ãããŸãããç£æ»ããã®ããŒã¿ã¯ãããŒã¿ããŒã¹èªäœã®ããŒã¿ãšåããããã以äžã®å€§ããã«ãªããŸãã DBMS ã®æäœæã«çæããããã°ã®éã¯ãéçšç°å¢ã§ããããåé¡ã§ãã
ä»ã®ãã©ã¡ãŒã¿ãèŠãŠã¿ãŸãããã
- é床ã¯ããŸãå€ãããŸãã: ãã°ãªã - 43,74 ç§ããã°ãã - 53,23 ç§ã
- ç£æ»ãã¡ã€ã«ãçæããå¿ èŠããããããRAM ãš CPU ã®ããã©ãŒãã³ã¹ãäœäžããŸããããã¯çç£æ§ã«ãããŠãé¡èã§ãã
æ¥ç¶æ°ãå¢ãããšãåœç¶ãªããããã©ãŒãã³ã¹ã¯è¥å¹²äœäžããŸãã
ç£æ»ã®ããäŒæ¥ã§ã¯ãããã«å°é£ã«ãªããŸãã
- ããããã®ããŒã¿ããããŸãã
- ç£æ»ã¯ãSIEM ã® syslog ãä»ããã ãã§ãªãããã¡ã€ã«ã§ãå¿ èŠã§ããsyslog ã«åé¡ãçºçããå ŽåãããŒã¿ãä¿åãããŠãããã¡ã€ã«ãããŒã¿ããŒã¹ã®è¿ãã«ååšããå¿ èŠããããŸãã
- ç£æ»ã«ã¯å€ãã®ã¹ããŒã¹ãå æãããããI/O ãã£ã¹ã¯ãç¡é§ã«ããªãããã«å¥ã®ã·ã§ã«ããå¿ èŠã§ãã
- æ å ±ã»ãã¥ãªãã£ã®åŸæ¥å¡ã«ã¯ã©ãã§ã GOST æšæºãå¿ èŠã§ãããå·ã®èå¥ãå¿ èŠã«ãªãããšããããŸãã
ããŒã¿ãžã®ã¢ã¯ã»ã¹ãå¶éãã
åçš DBMS ããã³ãªãŒãã³ãœãŒã¹ã§ããŒã¿ãä¿è·ããããŒã¿ã«ã¢ã¯ã»ã¹ããããã«äœ¿çšãããŠãããã¯ãããžãŒãèŠãŠã¿ãŸãããã
äžè¬çã«äœ¿çšã§ãããã®:
- ããã·ãŒãžã£ãšé¢æ°ã®æå·åãšé£èªå (ã©ããã³ã°) - ã€ãŸããå¯èªã³ãŒããå€èªäžèœã«ããå¥åã®ããŒã«ãšãŠãŒãã£ãªãã£ã§ãã確ãã«ãå€æŽããããšãããªãã¡ã¯ã¿ãªã³ã°ããŠæ»ãããšãã§ããŸããããã®ã¢ãããŒãã¯ãå°ãªããšã DBMS åŽã§å¿ èŠã«ãªãå ŽåããããŸããã©ã€ã»ã³ã¹å¶éã®ããžãã¯ãŸãã¯èªå¯ããžãã¯ã¯ãããã·ãŒãžã£ããã³æ©èœã¬ãã«ã§æ£ç¢ºã«æå·åãããŸãã
- è¡ããšã«ããŒã¿ã®è¡šç€ºãå¶éãã (RLS) ãšã¯ãããŸããŸãªãŠãŒã¶ãŒã XNUMX ã€ã®ããŒãã«ãåç §ããŠãããã®ããŒãã«å ã®è¡ã®æ§æãç°ãªãå Žåãã€ãŸããè¡ã¬ãã«ã§èª°ãã«äœãã衚瀺ã§ããªãå Žåã§ãã
- 衚瀺ãããããŒã¿ã®ç·šé (ãã¹ãã³ã°) ã§ã¯ãããŒãã«ã® XNUMX ã€ã®åã®ãŠãŒã¶ãŒã«ããŒã¿ãŸãã¯ã¢ã¹ã¿ãªã¹ã¯ã®ã¿ã衚瀺ãããŸããã€ãŸããäžéšã®ãŠãŒã¶ãŒã®æ å ±ã¯éããããŸãããã®ãã¯ãããžãŒã¯ãã¢ã¯ã»ã¹ ã¬ãã«ã«åºã¥ããŠãã©ã®ãŠãŒã¶ãŒã«äœã衚瀺ãããã決å®ããŸãã
- ã»ãã¥ãªã㣠DBA/ã¢ããªã±ãŒã·ã§ã³ DBA/DBA ã®ã¢ã¯ã»ã¹å¶åŸ¡ã¯ãããã DBMS èªäœãžã®ã¢ã¯ã»ã¹ãå¶éããããšã§ããã€ãŸããæ å ±ã»ãã¥ãªãã£æ åœè ãããŒã¿ããŒã¹ç®¡çè ãã¢ããªã±ãŒã·ã§ã³ç®¡çè ããåé¢ã§ããŸãããªãŒãã³ãœãŒã¹ã«ã¯ãã®ãããªãã¯ãããžã¯ã»ãšãã©ãããŸããããåçš DBMS ã«ã¯å€æ°ãããŸãããããã¯ããµãŒããŒèªäœã«ã¢ã¯ã»ã¹ã§ãããŠãŒã¶ãŒãå€æ°ããå Žåã«å¿ èŠã«ãªããŸãã
- ãã¡ã€ã«ã·ã¹ãã ã¬ãã«ã§ãã¡ã€ã«ãžã®ã¢ã¯ã»ã¹ãå¶éããŸãããã£ã¬ã¯ããªã«å¯Ÿããæš©éãšã¢ã¯ã»ã¹ç¹æš©ãä»äžããŠãå管çè ãå¿ èŠãªããŒã¿ã®ã¿ã«ã¢ã¯ã»ã¹ã§ããããã«ããããšãã§ããŸãã
- 匷å¶çãªã¢ã¯ã»ã¹ãšã¡ã¢ãªã®ã¯ãªã¢ - ãããã®ãã¯ãããžã¯ã»ãšãã©äœ¿çšãããŸããã
- DBMS ããçŽæ¥ã®ãšã³ãããŒãšã³ãæå·åã¯ããµãŒããŒåŽã§ããŒç®¡çãè¡ãã¯ã©ã€ã¢ã³ãåŽã®æå·åã§ãã
- ããŒã¿æå·åãããšãã°ãåæåæå·åã¯ãããŒã¿ããŒã¹ã®åäžåãæå·åããã¡ã«ããºã ã䜿çšããå Žåã§ãã
ãã㯠DBMS ã®ããã©ãŒãã³ã¹ã«ã©ã®ãããªåœ±é¿ãäžããŸãã?
PostgreSQL ã®åæåæå·åã®äŸãèŠãŠã¿ãŸãããã pgcrypto ã¢ãžã¥ãŒã«ããããéžæãããã£ãŒã«ããæå·åããã圢åŒã§ä¿åã§ããŸããããã¯ãäžéšã®ããŒã¿ã®ã¿ãéèŠãªå Žåã«äŸ¿å©ã§ããæå·åããããã£ãŒã«ããèªã¿åãããã«ãã¯ã©ã€ã¢ã³ãã¯åŸ©å·ããŒãéä¿¡ãããµãŒããŒã¯ããŒã¿ã埩å·ããŠã¯ã©ã€ã¢ã³ãã«è¿ããŸããããŒããªããã°ã誰ãããªãã®ããŒã¿ã«å¯ŸããŠäœãããããšãã§ããŸããã
pgcryptoã§ãã¹ãããŠã¿ãŸããããæå·åãããããŒã¿ãšéåžžã®ããŒã¿ãå«ãããŒãã«ãäœæããŠã¿ãŸãããã以äžã¯ããŒãã«ãäœæããããã®ã³ãã³ãã§ããæåã®è¡ã«ã¯ãDBMS ç»é²ã䜿çšããŠæ¡åŒµæ©èœèªäœãäœæãã䟿å©ãªã³ãã³ãããããŸãã
CREATE EXTENSION pgcrypto;
CREATE TABLE t1 (id integer, text1 text, text2 text);
CREATE TABLE t2 (id integer, text1 bytea, text2 bytea);
INSERT INTO t1 (id, text1, text2)
VALUES (generate_series(1,10000000), generate_series(1,10000000)::text, generate_series(1,10000000)::text);
INSERT INTO t2 (id, text1, text2) VALUES (
generate_series(1,10000000),
encrypt(cast(generate_series(1,10000000) AS text)::bytea, 'key'::bytea, 'bf'),
encrypt(cast(generate_series(1,10000000) AS text)::bytea, 'key'::bytea, 'bf'));
次ã«ãåããŒãã«ããããŒã¿ãµã³ãã«ãäœæããå®è¡ã¿ã€ãã³ã°ãèŠãŠã¿ãŸãããã
æå·åæ©èœã®ãªãããŒãã«ããéžæãã:
psql -c "timing" -c "select * from t1 limit 1000;" "host=192.168.220.129 dbname=taskdb
user=postgres sslmode=disable" > 1.txt
ã¹ããããŠã©ããããªã³ã«ãªã£ãŠããŸãã
ID |ããã¹ã1 |ããã¹ã2
ââ+ââ-+ââ-
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
...
997 | 997 | 997
998 | 998 | 998
999 | 999 | 999
1000 | 1000 | 1000
(1000è¡)
æé: 1,386ããªç§
æå·åæ©èœä»ãããŒãã«ããã®éžæïŒ
psql -c "timing" -c "select id, decrypt(text1, 'key'::bytea, 'bf'),
decrypt(text2, 'key'::bytea, 'bf') from t2 limit 1000;"
"host=192.168.220.129 dbname=taskdb user=postgres sslmode=disable" > 2.txt
ã¹ããããŠã©ããããªã³ã«ãªã£ãŠããŸãã
ID |埩å·å |埩å·åãã
â+ââââââ+ââââ
1 | x31 | Ã31
2 | x32 | Ã32
3 | x33 | Ã33
...
999 | x393939 | Ã393939
1000 | x31303030 | Ã31303030
(1000è¡)
æé: 50,203ããªç§
ãã¹ãçµæ:
æå·åãªã
Pgcrypto (埩å·å)
1000 è¡ã®ãµã³ãã«
1,386ããªç§
50,203ããªç§
CPU
èŠèŽè
ã®ïŒïŒ%ã
èŠèŽè
ã®ïŒïŒ%ã
ã©ã
+ 5ïŒ
æå·åã¯ããã©ãŒãã³ã¹ã«å€§ããªåœ±é¿ãäžããŸããæå·åãããããŒã¿ã®åŸ©å·åæäœ (éåžžã埩å·åã¯äŸç¶ãšããŠããžãã¯ã«ã©ãããããŠãã) ã«ã¯å€§éã®ãªãœãŒã¹ãå¿ èŠãšãªããããã¿ã€ãã³ã°ãå¢å ããŠããããšãããããŸããã€ãŸããäžéšã®ããŒã¿ãå«ããã¹ãŠã®åãæå·åãããšããèãã¯ãããã©ãŒãã³ã¹ã®äœäžã䌎ããŸãã
ãã ããæå·åã¯ãã¹ãŠã®åé¡ã解決ããç¹å¹è¬ã§ã¯ãããŸããã埩å·åãããããŒã¿ãšãããŒã¿ã埩å·åããŠéä¿¡ããããã»ã¹äžã®åŸ©å·åããŒã¯ãµãŒããŒäžã«ãããŸãããããã£ãŠãã·ã¹ãã 管çè ãªã©ãããŒã¿ããŒã¹ ãµãŒããŒãžã®å®å šãªã¢ã¯ã»ã¹æš©ãæã€äººç©ãããŒãååããå¯èœæ§ããããŸãã
ãã¹ãŠã®ãŠãŒã¶ãŒã®åå šäœã« XNUMX ã€ã®ããŒãããå Žå (ãã¹ãŠã®ãŠãŒã¶ãŒã§ã¯ãªããéãããã»ããã®ã¯ã©ã€ã¢ã³ãã®å Žåã§ã)ãããã¯å¿ ãããé©åã§æ£ãããšã¯éããŸããããã®ããã圌ãã¯ãšã³ãããŒãšã³ãã®æå·åãå®è¡ãå§ããDBMS ã§ã¯ã¯ã©ã€ã¢ã³ãåŽãšãµãŒããŒåŽã§ããŒã¿ãæå·åãããªãã·ã§ã³ãæ€èšãå§ããåãã㌠ã³ã³ãã㌠ã¹ãã¬ãŒãž (DBMS äžã§ããŒç®¡çãæäŸããå¥ã®è£œå) ãç»å ŽããŸãããåŽã
åçšããã³ãªãŒãã³ãœãŒã¹ DBMS ã®ã»ãã¥ãªãã£æ©èœ
æ©èœ
ã¿ã€ã
ãã¹ã¯ãŒãããªã·ãŒ
ç£æ»
ããã·ãŒãžã£ãšé¢æ°ã®ãœãŒã¹ ã³ãŒããä¿è·ãã
RLS
Encryption
ãªã©ã¯ã«
åæ¥ã®
+
+
+
+
+
MSQL
åæ¥ã®
+
+
+
+
+
åæ¥ã®
+
+
+
+
ãšã¯ã¹ãã³ã·ã§ã³
PostgreSQL
ç¡æç
ãšã¯ã¹ãã³ã·ã§ã³
ãšã¯ã¹ãã³ã·ã§ã³
-
+
ãšã¯ã¹ãã³ã·ã§ã³
MongoDB
ç¡æç
-
+
-
-
MongoDB Enterprise ã§ã®ã¿å©çšå¯èœ
ãã®è¡šã¯å®å
šã«ã¯çšé ãã§ãããçŸç¶ã¯æ¬¡ã®ãšããã§ããåçšè£œåã§ã¯ãã»ãã¥ãªãã£ã®åé¡ã¯é·ãé解決ãããŠããŸããããªãŒãã³ãœãŒã¹ã§ã¯ãååãšããŠãã»ãã¥ãªãã£ã®ããã«äœããã®çš®é¡ã®ã¢ããªã³ã䜿çšãããŠãããå€ãã®æ©èœãæ¬ èœããŠããŸãã ãæã«ã¯äœããè¿œå ããå¿
èŠããããŸããããšãã°ããã¹ã¯ãŒã ããªã·ãŒ - PostgreSQL ã«ã¯ããŸããŸãªæ¡åŒµæ©èœããããŸã (
å¿ èŠãªãã®ãã©ãã«ããªãå Žåã¯ã©ãããã°ããã§ãã?ããšãã°ã顧客ãå¿ èŠãšããæ©èœãåããŠããªãç¹å®ã® DBMS ã䜿çšããããšããŸãã
ãã®åŸãCrypto DB ã Garda DB ãªã©ãããŸããŸãª DBMS ã§åäœãããµãŒãããŒã㣠ãœãªã¥ãŒã·ã§ã³ã䜿çšã§ããŸããåœå ã»ã°ã¡ã³ãã®ãœãªã¥ãŒã·ã§ã³ã«ã€ããŠè©±ããŠããå Žåã圌ãã¯ãªãŒãã³ãœãŒã¹ããã GOST ã«ã€ããŠããç¥ã£ãŠããŸãã
XNUMX çªç®ã®ãªãã·ã§ã³ã¯ãå¿ èŠãªãã®ãèªåã§äœæããããã·ãŒãžã£ ã¬ãã«ã§ã¢ããªã±ãŒã·ã§ã³ã«ããŒã¿ ã¢ã¯ã»ã¹ãšæå·åãå®è£ ããããšã§ãã確ãã«ãGOSTã®å Žåã¯ããã«å°é£ã«ãªããŸãããã ããäžè¬çã«ã¯ãå¿ èŠã«å¿ããŠããŒã¿ãé衚瀺ã«ã㊠DBMS ã«é 眮ããå¿ èŠã«å¿ããŠã¢ããªã±ãŒã·ã§ã³ ã¬ãã«ã§ååŸããŠåŸ©å·åããããšãã§ããŸããåæã«ãã¢ããªã±ãŒã·ã§ã³å ã§ãããã®ã¢ã«ãŽãªãºã ãä¿è·ããæ¹æ³ãããã«æ€èšããŠãã ãããç§ãã¡ã®æèŠã§ã¯ããã㯠DBMS ã¬ãã«ã§è¡ãã¹ãã§ãããã®ã»ããããé«éã«åäœããããã§ãã
ãã®ã¬ããŒãã¯æåã«çºè¡šãããŸããã
ãã®ãããã¯ã«é¢ããŠä»ã«äœãèªãã¹ãã:
Ceph ãè¶ ãããã®: MCS ã¯ã©ãŠã ããã㯠ã¹ãã¬ãŒãž .å床éžæããå¿ èŠããªãããã«ãããžã§ã¯ãã®ããŒã¿ããŒã¹ãéžæããæ¹æ³ .
åºæïŒ habr.com