ISUCON5 まとめ

ISUCON5のまとめです

Transcript

1. ISUCON5 ·ͱΊ kita.h
2. ISUCON5 ༧બ໰୊ ʮISUxiʯ • ͓୊͸ʮSNSʯ • ೔ه΍଍੻ɺίϝϯτɺ༑ୡϦετͳͲ • σʔλϕʔεΛࠅ࢖͢Δػೳ͕ଟ͍ • mi◦iͩΑͶଟ෼ɻxi͍ͬͯͭͯΔ͠ • ϛυϧ΢ΣΞͷνϡʔχϯά͚ͩͰେ͖͘είΞΛ্ ͛Δͷ͸ͪΐͬͱݫͦ͠͏
3. ISUCON5 ༧બͰͷϝΠϯτϐοΫ • ʮN+1໰୊ʯ͕ϝΠϯ • ʮN+1໰୊ʯ΋ؚΊ੍ͯݶ࣌ؒҎ಺ʹ͸ରॲ͖͠Εͳ ͍͘Β͍ଟ͘ͷ໰୊͕ʮ/ʯʹࠐΊΒΕͯͨ • ʮ/ʯϖʔδͷϘτϧωοΫʹ͍ͭͯΛجຊతʹղઆ
4. N+1 • ʮ/ʯϖʔδͰ͸”is_friend?”ͱ”get_user”ͷେྔͷݺͼग़͠ ͕͋ͬͨ • ͜ΕΒ͸”user_id”ʹඥͮ͘σʔλͳͷͰSQLͰjoin͢Δ͜ͱ Ͱݺͼग़͠Λͳͤ͘Δ • ΋͘͠͸ɺ”users”ςʔϒϧʹ͸5000͔݅͠σʔλ͕ͳ͍ͷ Ͱશ݅Ωϟογϡ͢Δ͜ͱͰΫΤϦͷݺͼग़͠Λແͤͨ͘ • ʮ༑ୡΛ΍ΊΔʯػೳ͕ͳ͔ͬͨͷͰɺ”relations”ςʔϒϧ ΋ॳظσʔλ50ສ݅Λશ݅Ωϟογϡ͢Δ͜ͱͰΫΤϦͷ ݺͼग़͠Λ཈͑ΒΕͨ
5. ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτ • ʮ༑ͩͪͷ೔هΤϯτϦʯͱʮ༑ͩͪͷίϝϯτʯ͕ “is_friend?”ΛେྔʹݺΜͰ͍ͨ • ༑ͩͪͷ࠷৽೔هΤϯτϦΛ10݅औಘ͍ͨ͠ͱ͍͏Ϋ ΤϦ͸ɺ”relations”ςʔϒϧΛjoin͢Δ͔ɺ༑ͩͪͷid ϦετΛ࢖͏͜ͱͰ͜Μͳײ͡ʹ SELECT * FROM entries WHERE user_id IN (:friend_ids:) ORDER BY id DESC LIMIT 10
6. ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτ • ༑ͩͪͷ࠷৽ίϝϯτ10݅͸”permitted?”ʹ͋ΔN+1 ໰୊͕͋ΔͨΊҰखؒඞཁ • ҎԼͷ৚݅Λߟྀ͢Δ ‣ private=0(ެ։)ͷΤϯτϦ΁ͷίϝϯτ͸දࣔ͢Δ ‣ private=1(༑ͩͪݶఆެ։)ͷΤϯτϦ΁ͷίϝϯτ ͸permited?͕trueͳΒOK
7. ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτ • 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
8. ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτ • “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;
9. ଍͋ͱ • ಉ͡೔ʹಉ͡Ϣʔβ͕෇͚ͨ଍͋ͱ͸࠷৽ͷ΋ͷ͚ͩ ࢒ΔΑ͏ʹߋ৽͢Ε͹දࣔ͢Δͱ͖ͷGROUP BYΛͳ ͤ͘Δ • ͭ·Γɺಉ͡೔ʹಉ͡Ϣʔβ͕଍͋ͱΛ෇͚Δ࣌͸ INSERTͷ୅ΘΓʹUPDATE͢Δɺ͍ΘΏΔUPSERTΛ͠ ͍ͨɻREPLACEจͰ؆୯ʹͰ͖Δ REPLACE INTO footprints (user_id, owner_id, date) VALUES (?,?,NOW())
10. ೔هΤϯτϦͷຊจ • ʮ/ʯϖʔδͰ͸೔هΤϯτϦͷλΠτϧ͔͠࢖Θͳ͍ ͷʹɺλΠτϧͱຊจ͕ಉ͡ΧϥϜʹอଘ͞Ε͍ͯΔͷ ͰαΠζͷେ͖ͳຊจΛຖճऔಘ͍ͯ͠Δͷ͕ແବ • λΠτϧ͚ͩมߋ͢ΔΑ͏ʹ͢Ε͹OK SELECT id, SUBSTRING_INDEX(body, ‘\n’, 1) AS title FROM entries WHERE user_id = ? ORDER BY created_at LIMIT 5
11. ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτɹผղ • ʮISUxiʯͰ͸͜͜·ͰͰ͖Ε͹े෼ߴ଎ԽͰ͖Δ • ͔͠͠ɺ΋ͬͱେ͖ͳσʔληοτΛ΋ͭݱ࣮ͷSNSͰ ͸ʮ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτʯͷॲཧ͸͜ͷ· ·Ͱ͸࣮༻ʹ଱͑ͳ͍ • ͜Ε͡Όͪΐͬͱ͖͍ͭˣ SELECT * FROM entries WHERE user_id IN (:friend_ids:) ORDER BY id DESC LIMIT 10
12. ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτɹผղ • Ϣʔβ1ਓ͋ͨΓ100ਓલޙͷ༑͕ͩͪډͯɺϢʔβ1ਓ ͋ͨΓ໿100݅ͷΤϯτϦΛ࣋ͭISUxiͰ͸ɺ༑ͩͪͷ ࠷৽ΤϯτϦ10݅Λऔಘ͢Δ͖ͬ͞ͷΫΤϦ͸ҎԼͷ Α͏ʹಈ࡞͢Δ ‣ ͢΂ͯͷΤϯτϦͷத͔Β༑ͩͪ(໿100ਓ)ͷΤϯτ Ϧ(ͻͱΓ͋ͨΓ໿100݅)Λ·ͣநग़(߹ܭ໿10000݅) ‣ ฒͼॱΛ౤ߘॱʹฒͼସ͑Δ ‣ ࠷৽ΤϯτϦ10݅Λฦ͢
13. ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτɹผղ • ͜Ε͸༑͕ͩͪͨ͘͞Μ͍ΔϢʔβ΍ΤϯτϦΛଟ͘౤ߘ ͍ͯ͠Δ༑ͩͪΛ࣋ͭϢʔβ͸ޮ཰Α͘σʔλΛநग़Ͱ͖ ͳ͘ͳΔ • ϑϨϯυλΠϜϥΠϯॲཧΛͲ͏࣮૷͢΂͖͔͕೉͍͠໰୊ • ࢀরޮ཰ʹಛԽͨ͠Ξϓϩʔν͸ɺࢀর࣌ʹ༑ͩͪͷදࣔ ͍͍ͯ͠ΤϯτϦΛ൑அ͔͖ͯ͠ूΊΔͷͰ͸ͳ͘ɺΤϯ τϦ౤ߘ࣌ʹͦͷΤϯτϦΛද͍͍ࣔͯ͠౤ߘऀͷ༑ͩͪ ͷ࠷৽ΤϯτϦҰཡʹΤϯτϦΛ഑৴͢Δ • ͜ͷΞϓϩʔν͸ɺpushܕ΍inboxܕͱݺ͹Ε͍ͯΔ
14. ༑ͩͪͷ೔هΤϯτϦ/ίϝϯτɹผղ • ͜ͷΞϓϩʔνͰinboxςʔϒϧʹ഑৴͞ΕͨΤϯτϦ ͸ɺ༑ͩͪͷ਺΍༑͕ͩͪ౤ߘͨ͠ΤϯτϦͷ਺ʹؔ ΘΒͣҎԼͷΫΤϦͰ࠷৽ͷΤϯτϦ͚ͩΛऔΓग़ͤ Δ
15. ΈΜͳͷ΍ͬͯͨ͜ͱ • entries͔ΒtitleΧϥϜΛ෼཭͢Δ • /initializeͰͷRedisσʔλॳظԽ • ϕϯν։࢝࣌ʹ/initializeΞΫηε͕དྷͯɺͦ͜ͰॳظσʔλҎ֎ͷ σʔλ͸࡟আ͞ΕΔ͕ɺͦͷλΠϛϯάͰRedisͷσʔλ΋੔߹ੑ ΛͱͬͯॳظԽ͢Δ • MySQLͷॳظσʔλʹରԠ͢ΔRedisͷॳظσʔληοτΛ࡞ Γɺ”redis-cli config set appendoonly yes”ͱͯ͠ɺaofϑΝΠϧΛ ు͖ग़͠ɺinitialize࣌ʹredis-cliͰಡΈࠐΊ͹2ඵͰϩʔυ͕ऴΘΔ
16. ΈΜͳ͕ϋϚͬͨϙΠϯτ • AppArmorͷ͍ͤͰmy.cnfΛมߋͯ͠΋ɺͦΕ͕దԠ͞ Ε͍ͯͳ͍ ‣ apt-get purgeͰ࠷ॳʹফ͓ͯ͘͠ͷ͕ݡ͍Έ͍ͨ • iptables΋ಉ༷ʹpurge͕ྑ͍ • ϘϦϡʔϜ͕݁ߏ͋ͬͯେมͩͬͨΈ͍ͨ