æ¨å¹´ã¨åããåè·TVerååã® @ããã 㨠@teraken ã¨ãã¼ã ãã«ãã«ã¢ã«ãã« ã¨ã㦠isucon12 ã«åå ãã¦ãã¾ããï¼
æ¨å¹´ãããã¦æ°å¹´åããã¤ãè¯ãã¨ãã(30-40ä½ããã)ã§æ¬é¸åºå ´ã§ããªãã¦æããæãããã¦ãã¾ããããä»å¹´ã¯ã¤ãã«åæ¬é¸åºå ´åºæ¥ã¾ããï¼å¬ããï¼
ã¹ã³ã¢ã¯ 30616 ã§ãã(æé« 35383)ã14ä½ã§ããã
isucon.net
æçµæ§æ
ãããªæãããµã¼ãåæ£ã»ã¨ãã©ã§ãã¦ãªãã®ãæãã¾ããâ¦
- App1
- App2
- Goã¢ã㪠/ SQLite (çµæçã«ã¯ã¡ãã³ã£ã¨ã ãè² è·åæ£ããã¦ãæ°ãããï¼
- App3
- ãªã«ããã¦ãªã (æ¬å½ã¯app2ã¨åãæ§æã«ããã¤ããã ã£ããã© app2 ãæ³å®ãã使ãããªãã£ãã®ã§å´ä¸)
ãªããSQLiteã®MySQLå㯠ãã¦ããã¾ãã
åé¡ãã¼ã
isuportsã¨ãããisuconã·ã¹ãã ãSaaSåãä¼æ¥ã«æä¾ããã¨ãããã«ãããã³ããµã¼ãã¹ã®ãªã¼ãã¼ãã¼ãã®é«éåããã¦æ¬²ããï¼ã¨ãããã¼ãã§ããã
isuconã®isuconåï¼
åæï¼ãã¼ã å
ã§ã®é²ãæ¹ã«ã¤ãã¦
ä»åãé²ããã«ããã£ã¦äºåã«ãã¼ã ã§ããã¤ãå
±æãããã¨ãããã¾ããããã®ä¸ã§ä»¥ä¸ã®ï¼ã¤ããã¾ãæ©è½ããããªã¨æãã¾ãã
- ã¬ã®ã¥ã¬ã¼ã·ã§ã³ã¯ ã¿ããªã§ ã¡ããã¨èªã
- ã³ã¹ãã®è¯ãæ¹åããæãä»ãã
ï¼ï¼ã«ã¤ãã¦ã¯ãéå»ã®çµé¨ããããã¨åã
ã«æµãèªã¿ãã¦ãã¾ã£ã¦ã¹ã³ã¢ã«é¢ããéè¦ãªè¨è¼ãè¦éããã¨ãå¤ãã£ãã®ãåçã§ãããããã§ä»åã¯ç»é¢ã·ã§ã¢ãã¦ã¿ããªã§èªã¿é²ããã¨ãããã¨ããã¾ããã
ãããçµæ§è¯ãã¦ãèªåãè¦éãã¦ãã¨ãããããããããããã¨ãã¼ãã¨ãè¨ããªããã¿ããªã§è£å¡«ããã£ã¦é²ããããå
容ãã»ã¼ã»ã¼ç解ãã¦é²ããããã®ãããã£ãã§ãã
ï¼ï¼ã«é¢ãã¦ã¯éå»ã®åçãè¸ã¾ãã¦ãå¹æããããã§ããããªãé£æ度ã®é«ãã¨ãããæãä»ããã®ã§ã¯ãªã æããã«æ¹åã§ãã¦ç°¡åã«ã§ãããã¨ããããçå®ã«æ½°ãã¦ãã ã¨ããã®ãæèãã¾ããã
ããããã¦ãããã¨ã«ãã£ã¦å¤ãªã¨ããã§ããããåºç¤ããçå®ã«ã¹ã³ã¢ã¢ãããããã¨ãã§ããçµæã¨ãã¦ãã®ç©ã¿éããçãã¦æ£ããã¹ã³ã¢ã¢ããåºæ¥ãã¨æãã¾ãã
ãããªãã®ãããã
ã¬ã®ã¥ã¬ã¼ã·ã§ã³ç¢ºèªå¾ãèªåã®AWSã¢ã«ã¦ã³ãã§CloudFormationã®ã¹ã¿ãã¯ãèµ·åããã®ã§ããããªãã¨ã¨ã©ã¼ãåºã¦ãã¾ãã¾ãã
ãã¡ããã¡ãç¦ã£ã¦éå¶ããã«ã質åãã¦ãã¾ãã¾ããâ¦ãææ°ãããããã¾ãã¦ç³ã訳ããã¾ããã§ããï¼
ããã¦å³å¾å·¦å¾ããçµæãã°ã¤ã³ãã¦ããAWSã¢ã«ã¦ã³ããééã£ã¦ãããã¨ãçºè¦ããã¡ã³ãã¼ã®ãµãã¼ãã«ãã£ã¦æ£ããã¢ã«ã¦ã³ãã§ã¹ã¿ãã¯ãä½ãç´ããã¨ãåºæ¥ã¾ãããããã¯æ¬å½ã«ç¦ãã¾ããâ¦ãããã§15åãããæ½°ãã¦ãã¾ã£ãã®ãæãã¾ãã¾ã
ã¡ãªã¿ã«ããã¯å®å
¨ã«ãã©ã°ã§ããï¼å®ã¯ãã°ã¤ã³æåãã¦ããªãã£ãã¨ãã ð
ã¡ã³ãã¼ãã¼ã«
以ä¸ã®ãããªæãã§ãããã©ã³ã¹ãè¯ãã
- toritori0318: å
¨ä½è¨è¨ããã足åããã£ããã¢ããªå¨ãæ¹åããããã¼ã¿ã¹ãã¢å¨ãæ¹åããã
- ããã: ã¢ããªã´ãªã´ãªæ¹åãã¦ã
- teraken: å
¨è¬ã§ç´°ããé¨åãæ¾ã£ã¦ããããã¡ã³ã¿ã«å¼·
åæ
ã¿ããªã®èå¯ï¼ä»ã«ããã£ãããï¼ã
- å¤é¨èªè¨¼ã¯ååã¨ä¼¼ã¦ãæãã£ã½ã
- 429ã¹ãã¼ã¿ã¹ã³ã¼ãã¯ãããããããã©ä¸å¿é ã«å
¥ãã¦ãã
- ã¨ãããããªã¼ãã¼ãã¼ãã¯Redisã ããããã¨æ¡çªããã£ãã·ã¥ã§ã使ããããªã®ã§ç¢ºå®ã«ä½¿ãæ¹éã§
- ã¢ããªãdocker-composeã§åãã¨ã
- SQLite????ããããã£ã±ããã¡ã¤ã«ãã!ã©ããã
- MySQLå´ã¯ãã¼ã¿å°ãªããã©SQLiteçµæ§ãã£ã±ãããâ¦
- flock?
SQLiteã©ããããï¼åé¡
SQLiteãªãã¦ã¾ã¨ãã«ãã¥ã¼ãã³ã°ãããã¨ããªãæ¬å½ã«æ©ã¿ã¾ãããã以ä¸ã®çç±ã«ããMySQLã«ç½®ãæããã®ã¯ä¸æ¦ããã¾ããã
- ãããã移è¡ã ãã§ç¸å½ãªæéãåãããï¼ãã¼ã¿ç§»è¡ã ããããªãããã¼ã¿æ´åã»ã³ã¼ãå¤æ´ãªã©å½±é¿ã大ããï¼
- ãã¤ãMySQLã«ç§»ããããã¨ãã£ã¦ããã©ã¼ãã³ã¹ãä¸ãã確信ãæã¦ãªãã£ã
- æ¢ã«ãã¡ã¤ã«åæ£ã§ãã¦ããããå®ã¯ä½¿ãæ¹ã«ãã£ã¦ã¯ç¾ç¶ã®æ§æã®ã»ããããã©ã¼ãã³ã¹è¯ãå¯è½æ§ããã
- SQLiteã®ãµã¼ãåæ£å¯¾å¿ã«é¢ãã¦ã¯ããã¤ãã¢ã¤ãã¢ããã£ãã®ã§ãªãã¨ããªããã
- ããã¤ãã¯Redisã«æãããããã
çµæMySQL移è¡ã¯ãªã¹ã¯ãé«ãä¸ç¢ºå®æ§ãé«ããã ã£ããããæåã®æ¹éã«åãã¾ã㯠ã³ã¹ãã®è¯ãã§ããæ¹åãçå®ã«ãã£ã¦ãã ãã¨ã«ãã¾ããã
ãã£ããã¨ã¾ã¨ã
â»çµæ§å£é ãã¼ã¹ã§ããåããããã¨ãå¤ããããã¾ãSlackã«æ®ã£ã¦ããªãã£ãããééã£ã¦ããé¨åãããããããã¾ããï¼ï¼ã¾ããåããã¥ããæç« ã«ãªã£ã¦ãã¾ã£ã¦ãã¿ã¾ããã
åºç¤
èªåã¯ã¨ãããããã¤ãã©ãããããã¤ã·ã§ã«ãã¢ãã¿ãªã³ã°ç³»ã®ãã¼ã«å
¥ãããaliaså
¥ãããå¨è¾ºæºåãé²ãã¦ãã³ãå®è¡ãalp / pt-query-digest / SQLITE_TRACE_FILE / pprof ãªã©ã§ç¶æ³ææ¡ãã¡ã³ãã¼ã«å
±æã
ãã£ã±ãranking APIããªãã¨ãããªãã¨ãªã¼ã¨ããæãã
ãã®å¾ã¯åã
以ä¸ã®ãããªå¯¾å¿ãé²ãã¦ãã¾ããã
- @teraken docker-composeå¥ãã
- @toritori0318 sqliteã®distinctãããããã®ã¨ãã«indexè²¼ã
- @ããã idgen Rediså
- @toritori0318 MySQLã®GROUP BYçãindex
- @ããã visit history Rediså
ãã®è¾ºã§ 6627 ç¹ãããã
SQLiteãexplainã¨ãã§ããã®ããªï¼ã¨ã°ã°ã£ããæ®éã«åºæ¥ããã ã£ãã®ã§ãæéããã£ã¦ãããªã¯ã¨ãªè¨ç»è¦ãªããã¤ã³ããã¯ã¹è²¼ã£ãããã¦ãæ®éã«ãã¥ã¼ãã³ã°é²ãããã¾ããã
ä¸ç¤
429 Too Meny Erroræã®æåãæ°ã«ãªãã®ã§æ¤è¨¼ã³ã¼ãã@terakenã«ä¾é ¼ã
並è¡ãã¦èªåããã¡ãã¨ã¢ããªè¦å§ããããã£ã±flockå¨ãããããã¯ãã¦ããã ãªã¼ã¨ããã¨ããã§ã試ãã«ããä¸åæ¶ãã¦ã¿ããã¨flockãã¦ãã¨ãæ¶ãã¦ã¿ãã æ®éã«éã£ãâ¦ï¼ï¼
ãã®æåãRedisåã®å¯¾å¿ã¨é¢é£ãã¦ããã©ããã¾ã§ããã£ã¦ãªãã®ã§ãããã¨ããããåé¡ç¡ããããªã®ã§ãã®ã¾ã¾æ¡ç¨ã
ããã¦ã¹ã³ã¢ã 10900 ã¸ã
ãã®å¾ãplayer_scoreã®ãªã¹ãããplayerå¼ãN+1ãçºè¦ããã®ã§ããã¯ãµã¯ãã¨å¯¾å¿ã
ãã®æç¹ã§ã¹ã³ã¢ 14433 ã
initæã«ã¯sqliteã«indexè²¼ã£ã¦ããã©ãcreateTenantæã«ã¯indexè²¼ã£ã¦ãªãã£ãã®ã«æ°ã¥ããã®ã§ããã対å¿ã
ãã®æç¹ã§ã¹ã³ã¢ 14914 ã
ã¡ãªã¿ã«ãã®æç¹ã§RedisãMySQLã¯çµæ§ç©ºãã¦ãã¦ãã»ã¼7å²ãããã¯ã¢ããªè² è·ã«ãªã£ã¦ã¾ããã
並è¡ãã¦@ããããã¬ãªã¬ãªRedisåããã«ã¯ã¤ã³ãµã¼ãã§ã¢ããªæ¹åãã¦ãã®ã§ããã®è¾ºãã§ããããã¢ããªã®ãµã¼ãåæ£èããªãã¨ãªã¼ã¨ãããã¨ã§ã¤ã³ãã©æ§æèãã¦ãããã¦ãã¾ããã
é話ä¼é¡ãã¢ããªã®åæ£ã»SQLiteå調åã«ã¤ãã¦ã®è¨è¨
ã¢ããªéè² è·ã ãSQLiteåæ£åé¡ãããããã©ã£ã¡ãåæã«åæ£ããããªã¼ããã¦ã©ãããããã
å®ã¯ããã¯ãdouble(triple) writeã§ããããããï¼ãã¨å½åããèãã¦ã¾ããã
ããã¦å®éPOST APIã®ãªã¯ã¨ã¹ãæ°ãå°ãªããã ããPOST APIã ãå
¨ãµã¼ãã«æ¸ãè¾¼ã¿ãã¦ãGETãåæ£ããæ¹æ³ã
ããã¯å²ã¨æ¥åå®ç¸¾ã¨ãã¦ããã£ã¦ãããã¨ã ã£ãã1èãæ¹ã¯ééã£ã¦ãªãã¯ãâ¦
èªåã¯æ®æ®µããNginx / OpenRestyã§ãã£ããã課é¡è§£æ±ºããã®ãçãããã¨ãã¦ããã®ã§ 2 ãNginxã§shadow proxyã§ããã®ãæãåºããããä¸æãè¡ã£ãããã£ããããªï¼ã¨æã£ã¦å®éã«ä½¿ã£ããã¨ã¯ãªãã£ããã§ããææ¦ãã¦ã¿ããã¨ã«ãã¦ã¿ã¾ããã
ã¤ã¡ã¼ã¸ã¨ãã¦ã¯ä»¥ä¸ã®ãããªæã(keepaliveã¨ããããã¼ã¯æãã¦ã¾ã)ã
upstream app1 {
server 127.0.0.1:3000;
}
upstream app2 {
server 192.168.0.12:3000;
}
upstream app3 {
server 192.168.0.13:3000;
}
upstream app123 {
server 192.168.0.11:3000;
server 192.168.0.12:3000;
server 192.168.0.13:3000;
}
server {
location ~ ^/api/player/competition/(\w+)/ranking {
proxy_pass http://app123;
}
location ~ ^/api/organizer/competitions/add {
proxy_pass http://app1;
post_action @mirror2;
}
location @mirror2 {
internal;
proxy_pass http://app2$request_uri;
post_action @mirror3;
}
location @mirror3 {
internal;
proxy_pass http://app3$request_uri;
}
ã¯ã¦ãã¦ä¸æãããã®ã§ããããâ¦
çµç¤
ãããããã¦ãããã¡ã« @ããã ãplayerãã£ãã·ã¥ããbulkã¤ã³ãµã¼ããªã©å¯¾å¿ããã¹ã³ã¢ 30000è¶
ãï¼
ããã¦èªåãä¸è¨ã®APIåæ£å®è£
åºæ¥ãã¤ããã ã£ãã®ã§ãããã§ä¸æãããã°çµæ§ä¸ããã¯ãâ¦ï¼ãã¨ããæå¾
ãè¾¼ãã¦é©ç¨ãã¦ã¿ãæ ã¹ã³ã¢ã¯ã»ã¼å¾®å¢â¦ ð ãªãã§ï¼
ãã°è¦ãã¨ãPOSTã¯ãã©ã¼ãªã³ã°ããã¦ãã£ã½ããã©GETç³»ãããã¾ãåæ£ããã¦ããããè² è·ãapp1ã«åã£ãã¾ã¾ãããã¯æªã ã«ä½æ
nginxã®upstreamã§ã©ã¦ã³ãããã³åæ£ãã¦ãããªãã®ãããããã£ã¦ãªãã®ã§ææ³æ¦ã§æ¤è¨¼ãããæã
ã¾ãRedisãMySQLãå¥ãµã¼ãã«åæ£ãããã¨ããæãinitializeã§çµæ§éãå¦çããã¦ãããã30ç§ã«éã«åããã³ã±ãäºè±¡ã«ãçµå±æçµçã«ã¯ãµã¼ãåæ£ãæ®ã©ã§ãã¦ãªãç¶æ
ã«ãªã£ã¦ãã¾ãã¾ããã
ããã¦ãªããã¹ã³ã¢ãä¸ãããã¨ãããã¦ãªãã®ã«ã ãã ãä¸ãã£ã¦ããããã以ä¸ãã£ã¦ããªã¹ã¯é«ããªãããã¨ã®ãã¨ã§åèµ·åãã§ãã¯ãããã¨ã«ã
åèµ·åãã§ãã¯
æ®ã30åãããã§ãµã¼ããï¼å°ãã¤åèµ·åãã¹ãããã㨠çç±ãããããinitializeãã³ã±ãäºè±¡ ãçºçãå
¨å¡ç¦ãï½
çµå±ããªããããã°ãªããé©ç¨ããã¨ã³ã±ããã¨ãããã¨ãçºè¦ãæ¥ãã§æ»ãããããªãã§initializeã«å½±é¿ãããã®ãæªã ã«ããã£ã¦ãªãâ¦ã
ããã¦ããã«ãµã¼ã2ãåèµ·åãã¹ãããã¨ãdocker-composeãå¤ããæ°è¦ä½æãµã¼ãã¹ãdisableã«ãªã£ã¦ã¦ããã«åèµ·åãã¹ã失æ ð±
ãã®æç¹ã§æ®ã10åãåã£ã¦ãããã©ç¦ãããµã¼ãã¹ãenableã«ãã¦rebootã³ãã³ãå®è¡ãåé¡ãªããã¼ã¢ã³èµ·åããã¦ãããã¨ã確èªã
ããã¯æ¬å½ã«ãã¤ãã¤ããâ¦
ããã¦æå¾ã«ãã³ãå®è¡ãã¦
30616
ã§Finishï¼
ææ³
ã¨ã«ãã楽ããã£ãï¼ãã£ã¦ãæä¸ã§ãã¡ã³ãã¼ã¨ããã®ãã¼ï¼ãè¨ããªãããã£ã¦ã¾ããã
æ¬é¸ãååºå ´ãªã®ã§æ¬å½ã«æ¥½ãã¿ã§ãã ãã£ããæºåãã¦è¯ãçµæãæ®ããããã«ããã°ãã¾ãï¼
éå¶ã®çæ§ãæ¬å½ã«ãã®å¤§è¦æ¨¡ãªå¤§ä¼ã§å®å®ããã¢ããªã»ã¤ã³ãã©ã®ãæä¾ãã¾ãç´ æ´ãããåé¡ãä½æãã¦ããã ãã¾ãã¦æ¬å½ã«ãããã¨ããããã¾ããï¼
2022/7/26追è¨
@ããã è¦ç¹ã®ããã°ãå
¬éããã¾ããã®ã§ã¢ããªæ¹åã®è©³ç´°ã¯ãã¡ããã覧ãã ããã¾ãã
techblog.tver.co.jp
ãã¾ã
ã³ããããã°ç¬ã£ã