Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
ISUCON5 まとめ
Search
Sponsored
·
SiteGround - Reliable hosting with speed, security, and support you can count on.
→
changkita
January 08, 2018
Programming
0
74
ISUCON5 まとめ
ISUCON5のまとめです
changkita
January 08, 2018
Tweet
Share
More Decks by changkita
See All by changkita
仮想通貨こわい
kitah
0
36
ISUCON7 まとめ
kitah
0
35
強化学習
kitah
0
93
Other Decks in Programming
See All in Programming
Apache Iceberg V3 and migration to V3
tomtanaka
0
160
生成AIを使ったコードレビューで定性的に品質カバー
chiilog
1
260
AI時代のキャリアプラン「技術の引力」からの脱出と「問い」へのいざない / tech-gravity
minodriven
21
7.2k
Lambda のコードストレージ容量に気をつけましょう
tattwan718
0
120
AIと一緒にレガシーに向き合ってみた
nyafunta9858
0
230
AIによる高速開発をどう制御するか? ガードレール設置で開発速度と品質を両立させたチームの事例
tonkotsuboy_com
7
2.3k
2026年 エンジニアリング自己学習法
yumechi
0
130
組織で育むオブザーバビリティ
ryota_hnk
0
170
Implementation Patterns
denyspoltorak
0
280
【卒業研究】会話ログ分析によるユーザーごとの関心に応じた話題提案手法
momok47
0
200
Spinner 軸ズレ現象を調べたらレンダリング深淵に飲まれた #レバテックMeetup
bengo4com
1
230
React 19でつくる「気持ちいいUI」- 楽観的UIのすすめ
himorishige
11
7.4k
Featured
See All Featured
The Power of CSS Pseudo Elements
geoffreycrofte
80
6.2k
Data-driven link building: lessons from a $708K investment (BrightonSEO talk)
szymonslowik
1
910
Large-scale JavaScript Application Architecture
addyosmani
515
110k
So, you think you're a good person
axbom
PRO
2
1.9k
Unlocking the hidden potential of vector embeddings in international SEO
frankvandijk
0
170
Visual Storytelling: How to be a Superhuman Communicator
reverentgeek
2
430
Exploring the relationship between traditional SERPs and Gen AI search
raygrieselhuber
PRO
2
3.6k
Utilizing Notion as your number one productivity tool
mfonobong
3
220
Tell your own story through comics
letsgokoyo
1
810
Digital Projects Gone Horribly Wrong (And the UX Pros Who Still Save the Day) - Dean Schuster
uxyall
0
340
Discover your Explorer Soul
emna__ayadi
2
1.1k
Are puppies a ranking factor?
jonoalderson
1
2.7k
Transcript
ISUCON5 ·ͱΊ kita.h
ISUCON5 ༧બ ʮISUxiʯ • ͓ʮSNSʯ • هɺίϝϯτɺ༑ୡϦετͳͲ • σʔλϕʔεΛࠅ͢Δػೳ͕ଟ͍ •
mi◦iͩΑͶଟɻxi͍ͬͯͭͯΔ͠ • ϛυϧΣΞͷνϡʔχϯά͚ͩͰେ͖͘είΞΛ্ ͛Δͷͪΐͬͱݫͦ͠͏
ISUCON5 ༧બͰͷϝΠϯτϐοΫ • ʮN+1ʯ͕ϝΠϯ • ʮN+1ʯؚΊ੍ͯݶ࣌ؒҎʹରॲ͖͠Εͳ ͍͘Β͍ଟ͘ͷ͕ʮ/ʯʹࠐΊΒΕͯͨ • ʮ/ʯϖʔδͷϘτϧωοΫʹ͍ͭͯΛجຊతʹղઆ
N+1 • ʮ/ʯϖʔδͰ”is_friend?”ͱ”get_user”ͷେྔͷݺͼग़͠ ͕͋ͬͨ • ͜ΕΒ”user_id”ʹඥͮ͘σʔλͳͷͰSQLͰjoin͢Δ͜ͱ Ͱݺͼग़͠Λͳͤ͘Δ • ͘͠ɺ”users”ςʔϒϧʹ5000͔݅͠σʔλ͕ͳ͍ͷ Ͱશ݅Ωϟογϡ͢Δ͜ͱͰΫΤϦͷݺͼग़͠Λແͤͨ͘
• ʮ༑ୡΛΊΔʯػೳ͕ͳ͔ͬͨͷͰɺ”relations”ςʔϒϧ ॳظσʔλ50ສ݅Λશ݅Ωϟογϡ͢Δ͜ͱͰΫΤϦͷ ݺͼग़͠Λ͑ΒΕͨ
༑ͩͪͷهΤϯτϦ/ίϝϯτ • ʮ༑ͩͪͷهΤϯτϦʯͱʮ༑ͩͪͷίϝϯτʯ͕ “is_friend?”ΛେྔʹݺΜͰ͍ͨ • ༑ͩͪͷ࠷৽هΤϯτϦΛ10݅औಘ͍ͨ͠ͱ͍͏Ϋ ΤϦɺ”relations”ςʔϒϧΛjoin͢Δ͔ɺ༑ͩͪͷid ϦετΛ͏͜ͱͰ͜Μͳײ͡ʹ SELECT *
FROM entries WHERE user_id IN (:friend_ids:) ORDER BY id DESC LIMIT 10
༑ͩͪͷهΤϯτϦ/ίϝϯτ • ༑ͩͪͷ࠷৽ίϝϯτ10݅”permitted?”ʹ͋ΔN+1 ͕͋ΔͨΊҰखؒඞཁ • ҎԼͷ݅Λߟྀ͢Δ ‣ private=0(ެ։)ͷΤϯτϦͷίϝϯτදࣔ͢Δ ‣ private=1(༑ͩͪݶఆެ։)ͷΤϯτϦͷίϝϯτ
permited?͕trueͳΒOK
༑ͩͪͷهΤϯτϦ/ίϝϯτ • SQLʹ͢Δͱ SELECT * FROM comments c JOIN entries
e ON c.entry_id = e.id WHERE c.user_id IN (:friend_ids:) AND ( e.private = 0 OR e.private = 1 AND (e.user_id = :my_user_id OR e.user_id IN (:friend_ids:)) ) ORDER BY c.id DESC LIMIT 10
༑ͩͪͷهΤϯτϦ/ίϝϯτ • “entries/comments”Λऔಘ͢Δ͕݅มΘͬͨͷͰඞཁ ͳINDEX(user_id)ΛՃͯ͠ɺཁΒͳ͘ͳͬͨ INDEX(created_at)ޮͷͨΊམͱ͓ͯ͘͠ͱྑ͍ • DROP TABLE “ςʔϒϧ໊”ͰςʔϒϧΛফ͢ ALTER
TABLE comments ADD INDEX user_id (user_id), DROP INDEX created_at; ALTER TABLE entries DROP INDEX created_at;
͋ͱ • ಉ͡ʹಉ͡Ϣʔβ͕͚ͨ͋ͱ࠷৽ͷͷ͚ͩ ΔΑ͏ʹߋ৽͢Εදࣔ͢Δͱ͖ͷGROUP BYΛͳ ͤ͘Δ • ͭ·Γɺಉ͡ʹಉ͡Ϣʔβ͕͋ͱΛ͚Δ࣌ INSERTͷΘΓʹUPDATE͢Δɺ͍ΘΏΔUPSERTΛ͠ ͍ͨɻREPLACEจͰ؆୯ʹͰ͖Δ
REPLACE INTO footprints (user_id, owner_id, date) VALUES (?,?,NOW())
هΤϯτϦͷຊจ • ʮ/ʯϖʔδͰهΤϯτϦͷλΠτϧ͔͠Θͳ͍ ͷʹɺλΠτϧͱຊจ͕ಉ͡ΧϥϜʹอଘ͞Ε͍ͯΔͷ ͰαΠζͷେ͖ͳຊจΛຖճऔಘ͍ͯ͠Δͷ͕ແବ • λΠτϧ͚ͩมߋ͢ΔΑ͏ʹ͢ΕOK SELECT id, SUBSTRING_INDEX(body,
‘\n’, 1) AS title FROM entries WHERE user_id = ? ORDER BY created_at LIMIT 5
༑ͩͪͷهΤϯτϦ/ίϝϯτɹผղ • ʮISUxiʯͰ͜͜·ͰͰ͖ΕेߴԽͰ͖Δ • ͔͠͠ɺͬͱେ͖ͳσʔληοτΛͭݱ࣮ͷSNSͰ ʮ༑ͩͪͷهΤϯτϦ/ίϝϯτʯͷॲཧ͜ͷ· ·Ͱ࣮༻ʹ͑ͳ͍ • ͜Ε͡Όͪΐͬͱ͖͍ͭˣ SELECT
* FROM entries WHERE user_id IN (:friend_ids:) ORDER BY id DESC LIMIT 10
༑ͩͪͷهΤϯτϦ/ίϝϯτɹผղ • Ϣʔβ1ਓ͋ͨΓ100ਓલޙͷ༑͕ͩͪډͯɺϢʔβ1ਓ ͋ͨΓ100݅ͷΤϯτϦΛ࣋ͭISUxiͰɺ༑ͩͪͷ ࠷৽ΤϯτϦ10݅Λऔಘ͢Δ͖ͬ͞ͷΫΤϦҎԼͷ Α͏ʹಈ࡞͢Δ ‣ ͯ͢ͷΤϯτϦͷத͔Β༑ͩͪ(100ਓ)ͷΤϯτ Ϧ(ͻͱΓ͋ͨΓ100݅)Λ·ͣநग़(߹ܭ10000݅) ‣
ฒͼॱΛߘॱʹฒͼସ͑Δ ‣ ࠷৽ΤϯτϦ10݅Λฦ͢
༑ͩͪͷهΤϯτϦ/ίϝϯτɹผղ • ͜Ε༑͕ͩͪͨ͘͞Μ͍ΔϢʔβΤϯτϦΛଟ͘ߘ ͍ͯ͠Δ༑ͩͪΛ࣋ͭϢʔβޮΑ͘σʔλΛநग़Ͱ͖ ͳ͘ͳΔ • ϑϨϯυλΠϜϥΠϯॲཧΛͲ͏࣮͖͔͕͍͢͠ • ࢀরޮʹಛԽͨ͠Ξϓϩʔνɺࢀর࣌ʹ༑ͩͪͷදࣔ ͍͍ͯ͠ΤϯτϦΛஅ͔͖ͯ͠ूΊΔͷͰͳ͘ɺΤϯ
τϦߘ࣌ʹͦͷΤϯτϦΛද͍͍ࣔͯ͠ߘऀͷ༑ͩͪ ͷ࠷৽ΤϯτϦҰཡʹΤϯτϦΛ৴͢Δ • ͜ͷΞϓϩʔνɺpushܕinboxܕͱݺΕ͍ͯΔ
༑ͩͪͷهΤϯτϦ/ίϝϯτɹผղ • ͜ͷΞϓϩʔνͰinboxςʔϒϧʹ৴͞ΕͨΤϯτϦ ɺ༑ͩͪͷ༑͕ͩͪߘͨ͠ΤϯτϦͷʹؔ ΘΒͣҎԼͷΫΤϦͰ࠷৽ͷΤϯτϦ͚ͩΛऔΓग़ͤ Δ
ΈΜͳͷͬͯͨ͜ͱ • entries͔ΒtitleΧϥϜΛ͢Δ • /initializeͰͷRedisσʔλॳظԽ • ϕϯν։࢝࣌ʹ/initializeΞΫηε͕དྷͯɺͦ͜ͰॳظσʔλҎ֎ͷ σʔλআ͞ΕΔ͕ɺͦͷλΠϛϯάͰRedisͷσʔλ߹ੑ ΛͱͬͯॳظԽ͢Δ •
MySQLͷॳظσʔλʹରԠ͢ΔRedisͷॳظσʔληοτΛ࡞ Γɺ”redis-cli config set appendoonly yes”ͱͯ͠ɺaofϑΝΠϧΛ ు͖ग़͠ɺinitialize࣌ʹredis-cliͰಡΈࠐΊ2ඵͰϩʔυ͕ऴΘΔ
ΈΜͳ͕ϋϚͬͨϙΠϯτ • AppArmorͷ͍ͤͰmy.cnfΛมߋͯ͠ɺͦΕ͕దԠ͞ Ε͍ͯͳ͍ ‣ apt-get purgeͰ࠷ॳʹফ͓ͯ͘͠ͷ͕ݡ͍Έ͍ͨ • iptablesಉ༷ʹpurge͕ྑ͍ •
ϘϦϡʔϜ͕݁ߏ͋ͬͯେมͩͬͨΈ͍ͨ