Performance Bootcamp

Performance Bootcamp 2020/4/8 @ Recruit Technologies

ϦΫϧʔτ ΤϯδχΞϦϯάࣨ PE෦ ASG ݹ઒ཅհʢϑϧΧϫϤ΢εέʣ 2 ▪໾৬ ࣗݾ঺հ Application Solution
GroupͷάϧʔϓϚωʔδϟ γχΞιϑτ΢ΣΞΤϯδχΞ ͓ࣸਅ ໌Δ͍ද৘ͷ΋ͷ ϓϥΠϕʔτ΋Մ ▪GMͱͯ͠΍ͬͯΔ͜ͱ ιϑτ΢ΣΞΤϯδχΞମ੍ͮ͘Γ ࠾༻/ධՁ νʔϜϏϧσΟϯά/ίϯαϧςΟϯά ▪γχΞͱͯ͠΍ͬͯΔ͜ͱ R&D - React/Redux/Node.jsΛ࢖ͬͨϘΠϥʔϓϨʔτ - Consumer Driven Contract πʔϧ agreed࡞੒ ύϑΥʔϚϯενϡʔχϯά - ࣾ಺ISUCON։͍ͨΓ - ࣾ಺Web Page SpeedϋοΧιϯ։͍ͨΓ

Agenda • ඇػೳཁ݅ʹͩ͜ΘΖ͏ • ύϑΥʔϚϯε

ඇػೳཁ݅ʹͩ͜ΘΖ͏

ϓϩͷϓϩάϥϚʔͱ ϓϩ͡Όͳ͍ϓϩάϥϚʔͷ ҧ͍

ػೳཁ݅ͱඇػೳཁ݅ • ػೳཁ݅ɿ ࣮૷͠ͳ͖Ό͍͚ͳ͍ػೳ Ϣʔβʔ͔ΒͷཁٻΛ׎͑ΔγεςϜͷػೳ • ඇػೳཁ݅ɿ ඞͣ͠΋࣮૷͠ͳ͖Ό͍͚ͳ͍Θ͚Ͱ͸ͳ͍͕ɺ ϓϩδΣΫτΛ޲্ͤ͞ΔͨΊʹඞཁͳ͜ͱ

ඇػೳཁ݅ • ηΩϡϦςΟ • XSS/XSRF/DoS etc • ੑೳ • SPA/PWA/AMP
etc • ϝϯςφϯαϏϦςΟ • ςετॻ͘/e2e/storyboard etc

ػೳཁ݅Λຬͨͯ͠ظ೔ʹ௥ ΘΕΔதͰͲ͜·Ͱඇػೳཁ ݅ΛೖΕࠐΊΔ͔ͱ͍͏ͷ͕ ΤϯδχΞͷҰछͷೳྗ

๻ͷ৔߹

ύϑΥʔϚϯενϡʔχϯά େ޷͖ͩͬͨ

ύϑΥʔϚϯενϡʔχϯά • όοΫΤϯυͷσʔλϕʔεͱ͔શจݕࡧ෦ ෼ͷνϡʔχϯά΍ͬͯresponseͷ࣌ؒΛ୹ ͘͢Δͷ͕࢓ࣄͩͬͨɻ

ύϑΥʔϚϯενϡʔχϯά • όοΫΤϯυͷσʔλϕʔεͱ͔શจݕࡧ෦ ෼ͷνϡʔχϯά΍ͬͯresponseͷ࣌ؒΛ୹ ͘͢Δͷ͕࢓ࣄͩͬͨɻ ͕͜͜Զͷઓ͏৔ॴ

ରԠϒϥ΢βΛ IE => Chrome ʹ֦େͨ͠ͱ͖ͷ͜ͱ

ര଎ʹͳͬͨ

ϑϩϯτΤϯυͷٕज़͕ਐา ͢Δͱ๻ͷ΍ͬͯͨ͜ͱ͸ޡ ࠩʹมΘ͍ͬͯͬͨɻɻɻ

ඇػೳཁ݅Ͱੜ͖͍ͯͨࣗ෼ ͕ॳΊͯܦݧͨ͠িܸ

࣮ࡍʹ͸UXΛ࠷ߴʹ͢ΔͳΒ ϑϩϯτ͔ΒόοΫΤϯυ· Ͱશ෦ࣝΔඞཁ͕͋ΔΜͩͱ ஌ͬͨ

ϦΫΤετ͔ΒϨεϙϯε·ͰͰ͸ͳ͘ɺϖʔδ͕ಡΈ ࠐ·Ε࢝Ί͔ͯΒϢʔβʔͷظ଴ΛಘΔ·Ͱɻ

͔ͦ͜ΒϑϩϯτΤϯυʹڵຯ Λ࣋ͬͨ • Node.js͸JavaScriptֶ΅͏ͱͯ͠΍ͬͯͨΒ ָ͘͠ͳͬͯ΍ͬͯͨ • ϑϩϯτͰͳΜ͔ΞϓϦ΋࡞͍͕ͬͯͨɺ࣮ ࡍʹ͸స৬ͯ͠ػձΛ࡞࣮ͬͯࢪͨ͠ • ਺೥͔͚ͯɺͰ͖ΔΑ͏ʹͳͬͨ

ඇػೳཁ݅ʹͩ͜ΘΖ͏ɻ

ύϑΥʔϚϯε

ύϑΥʔϚϯε • ϑϩϯτΤϯυͷύϑΥʔϚϯε • όοΫΤϯυͷύϑΥʔϚϯε • ਪଌ͢ΔͳɺܭଌͤΑ

ϑϩϯτΤϯυͷ ύϑΥʔϚϯε

ϑϩϯτΤϯυύϑΥʔϚϯε 4FSWFS 4FTTJPO$BDIF %#"1*

Performance Bible ~200X

Performance Bible ~200X )551ϦΫΤετΛ࠷খݶʹ͠Α͏ $%/Λ࢖͓͏ $BDIF$POUSPMΛ࢖͓͏FUD Ϣʔβʔ͸ϑϩϯτΤϯυͰ 80 ~ 90%
ͷ࣌ؒ଴ػ͍ͯ͠Δ

Previous Web Applications

Previous Web Applications • جຊతʹϖʔδભҠͰͷॲཧ͕ϝΠϯ • SNS • ϒϩά •
ཱྀߦαΠτ • Ϩετϥϯݕࡧ etc

Previous Web Applications • جຊతʹϖʔδભҠͰͷॲཧ͕ϝΠϯ • SNS • ϒϩά •
ཱྀߦαΠτ • Ϩετϥϯݕࡧ etc ຖճϖʔδΛඳըͭͭ͠ɺϒϥ΢β ͷجຊػೳΛଟ༻ͯ͠࡞Δ

Web Performance ~200X • Measuring Time from Request to Response

Web Performance Tools ~200X • Page Speed Insights • Apache
Bench • jmeter • Requests / sec • Latency • Transfer / sec

• Page Speed Insights • Apache Bench • jmeter 3FRTTFD
ඵؒͰԿճϦΫΤετΛ͞͹͚͔ͨ USBOTGFSTFD ඵؒͰԿ#ZUFΛૹΕ͔ͨ -BUFODZ ߦ͔ͬͯΒฦͬͯ͘Δ·Ͱͷ࣌ؒ Web Performance Tools ~200X

• Page Speed Insights • Apache Bench • jmeter 3FRTTFD
ඵؒͰԿճϦΫΤετΛ͞͹͚͔ͨ USBOTGFSTFD ඵؒͰԿ#ZUFΛૹΕ͔ͨ -BUFODZ ߦ͔ͬͯΒฦͬͯ͘Δ·Ͱͷ࣌ؒ ඞવతʹ΍Δ͜ͱ͕ܾ·ͬͯ͘Δ Web Performance Tools ~200X

Web Performance ~ 200X • ΍Δ͜ͱ • ϦΫΤετճ਺ͷվળ • Ϩεϙϯε࣌ؒͷվળ
• Ϩεϙϯε͢ΔόΠτ਺ͷվળ

ϦΫΤετճ਺ͷվળ • αΠτΛߴ଎ʹ͢ΔҰ൪ͷํ๏͸ ͦ΋ͦ΋ϦΫΤετ͠ͳ͍͜ͱ • ۃྗϦΫΤετ਺ΛݮΒ͢ • ex: CSSΛ1ϑΝΠϧʹɺJS΋ಉ༷ɻ

ϦΫΤετճ਺ͷվળ • CSS Sprite • Inline ల։ • Cache Control

• CSS Sprite • Inline ల։ • Cache Control ϦΫΤετճ਺ͷվળ
$BDIF$POUSPM BTTFU͸%-ͨ͠ΒΩϟογϡͤ͞Δ $444QSJUF JDPO͸Ұͭʹ·ͱΊΔ *OMJOFల։ ͻͱ໨ݟͯग़ͯ͘ΔྖҬ͸$44ΛΠϯ ϥΠϯʹల։͢Δ

• CSS Sprite • Inline ల։ • Cache Control ϦΫΤετճ਺ͷվળ
$BDIF$POUSPM BTTFU͸%-ͨ͠ΒΩϟογϡͤ͞Δ $444QSJUF JDPO͸Ұͭʹ·ͱΊΔ *OMJOFల։ ͻͱ໨ݟͯग़ͯ͘ΔྖҬ͸$44ΛΠϯ ϥΠϯʹల։͢Δ ͳΔ΂͘ϦΫΤετ͠ͳ͍

Ϩεϙϯε࣌ؒͷվળ • Improve Backend Performance • Indexing, Join, denomalize •
Proper Middleware ΑΓྑ͍ϛυϧ΢ΣΞΛ࢖͏ 3FEJT NFNDBDIF %BUBCBTFͷ*OEFYΛషΔɺςʔϒϧΛ δϣΠϯ͢Δɺඇਖ਼نԽ͢ΔFUD

Ϩεϙϯε࣌ؒͷվળ • Improve Backend Performance • Indexing, Join, denomalize •
Proper Middleware ΑΓྑ͍ϛυϧ΢ΣΞΛ࢖͏ 3FEJT NFNDBDIF %BUBCBTFͷ*OEFYΛషΔɺςʔϒϧΛ δϣΠϯ͢Δɺඇਖ਼نԽ͢ΔFUD ΞϓϦέʔγϣϯαʔόͷத਎Λ վળͯ͠ɺϨεϙϯεΛྑ͘͢Δ

Ϩεϙϯε͢ΔόΠτ਺ͷվળ • ద੾ͳը૾ϑΥʔϚοτͷબ୒ɺϦαΠζ • imagemagick, AWS Lambda • webp, png,
gif, jpeg • JS, css ͷ minify, gzipԽ

gif, jpeg • JS, css ͷ minify, gzipԽ ద੾ͳը૾ϑΥʔϚοτͷબ୒ TDSJQU΍TUZMF͸NJOJGZͯ͠H[JQʹ͢Δ ͜ͱͰখͯ͘͞͠഑৴

gif, jpeg • JS, css ͷ minify, gzipԽ ద੾ͳը૾ϑΥʔϚοτͷબ୒ TDSJQU΍TUZMF͸NJOJGZͯ͠H[JQʹ͢Δ ͜ͱͰখͯ͘͞͠഑৴ Ϩεϙϯε͢ΔόΠτ਺͕ݮΕ͹ଳ Ҭͷѹഭ͕ݮΔɺ࣌ؒ΋গͳ͘ͳΔ

Summary ~200X • Bible: • High Performance Web Sites •
App: • جຊతʹϖʔδભҠ୯ҐͰ΍ΓऔΓ • Performance Techniques: • ϦΫΤετΛݮΒ͢ • JS/CSS/image Λ1ﬁleʹ͢Δɺ Inlineʹ͢ΔɺCache-ControlΛ࢖͏ • ϨεϙϯεΛվળ͢Δ • Backend ͷϨεϙϯεΛվળ͢ΔɺDBΑ͘͢Δɺద੾ͳMiddleware࢖͏ • Ϩεϙϯε͢ΔαΠζΛݮΒ͢ • ImageΛॖখ ͢ΔɺCSS/JSΛminify͢Δɺద੾ͳImage formatΛબͿɺѹॖ͢Δ

Web Performance 2010-2016

Performance Bible 2010-2016

Performance Bible 2010-2016 ॲཧͷத৺͕αʔό͔Βϒϥ΢β΁ ϨΠςϯγΛখ͘͞ɻ ϨΠςϯγΛԼ͛Δʹ͸ɿ ઀ଓίετΛԼ͛Δ 5$1'BTU0QFO )551
ඞཁͳσʔλ͚ͩऔͬͯࠩ෼Ͱߋ৽͢Δ 9)3 +4 ࣮ࡍͷϢʔβʔͷϞχλϦϯά݁ՌΛݟΔ 36.

Web Applications 2010-2016 • NetworkϨΠϠͰͷ࠷దԽ (HTTP/2) • Ajax௨৴ͱ෦෼ϨϯμϦϯά (SPA)

Web Applications 2010-2015 • NetworkϨΠϠͰͷ࠷దԽ (HTTP/2) • Ajax௨৴ͱ෦෼ϨϯμϦϯά (SPA) 41"Λجຊͱͯ͠ΑΓ(6*ΞϓϦέʔ
γϣϯͱͯ͠ͷଆ໘͕ڧ͘ͳͬͨɻ )551Λجຊͱͯ͠ΑΓωοτϫʔΫ ϨΠϠͰͷ࠷దԽ͕ਐΉΑ͏ʹͳͬͨ

Web Performance ~2016 • Network Optimization • HTTP/2 • Single
Page Application • Partial Rendering • Ajax

Web Performance Tools ~2016 • Lighthouse • Real User Monitoring
• Navigation Timing • Resource Timing

• Navigation Timing • Resource Timing -JHIUIPVTF ύϑΥʔϚϯε͸΋ͪΖΜͷ͜ͱɺϕετϓϥΫςΟεʹ࢖ΘΕ ΔςΫχοΫ͕ೖͬͯΔ͔Ͳ͏͔ɺϖʔδશମͷධՁΛ ఺਺Ͱදࣔͯ͘͠ΕΔ

• Navigation Timing • Resource Timing -JHIUIPVTF ύϑΥʔϚϯε͸΋ͪΖΜͷ͜ͱɺϕετϓϥΫςΟεʹ࢖ΘΕ ΔςΫχοΫ͕ೖͬͯΔ͔Ͳ͏͔ɺϖʔδશମͷධՁΛ ఺਺Ͱදࣔͯ͘͠ΕΔ 3FBM6TFS.POJUPSJOH ࣮ࡍͷϢʔβʔମݧΛ਺஋Խͯ͠ܭଌ͢Δɻ 4QFFE$VSWF /FX3FMJD#SPXTFS 6Q5SFOET

• Navigation Timing • Resource Timing -JHIUIPVTF ύϑΥʔϚϯε͸΋ͪΖΜͷ͜ͱɺϕετϓϥΫςΟεʹ࢖ΘΕ ΔςΫχοΫ͕ೖͬͯΔ͔Ͳ͏͔ɺϖʔδશମͷධՁΛ ఺਺Ͱදࣔͯ͘͠ΕΔ 3FBM6TFS.POJUPSJOH ࣮ࡍͷϢʔβʔମݧΛ਺஋Խͯ͠ܭଌ͢Δɻ 4QFFE$VSWF /FX3FMJD#SPXTFS 6Q5SFOET 9͔ΒͷมԽ ΑΓύϑΥʔϚϯεͷ౔ඨ͕ϦΞϧͳϢʔβʔΛର৅ͱ͢ΔΑ͏ ʹͳͬͨɻϖʔδϩʔυͷ͚࣌ؒͩͰ͸ͳ͘ɺΞϓϦέʔγϣϯ ͱͯ͠ͷશମͷ࣌ؒΛߟྀ͢ΔΑ͏ʹͳͬͨɻ

• Navigation Timing • Resource Timing -JHIUIPVTF ύϑΥʔϚϯε͸΋ͪΖΜͷ͜ͱɺϕετϓϥΫςΟεʹ࢖ΘΕ ΔςΫχοΫ͕ೖͬͯΔ͔Ͳ͏͔ɺϖʔδશମͷධՁΛ ఺਺Ͱදࣔͯ͘͠ΕΔ 3FBM6TFS.POJUPSJOH ࣮ࡍͷϢʔβʔମݧΛ਺஋Խͯ͠ܭଌ͢Δɻ 4QFFE$VSWF /FX3FMJD#SPXTFS 6Q5SFOET 9͔ΒͷมԽ ΑΓύϑΥʔϚϯεͷ౔ඨ͕ϦΞϧͳϢʔβʔΛର৅ͱ͢ΔΑ͏ ʹͳͬͨɻϖʔδϩʔυͷ͚࣌ؒͩͰ͸ͳ͘ɺΞϓϦέʔγϣϯ ͱͯ͠ͷશମͷ࣌ؒΛߟྀ͢ΔΑ͏ʹͳͬͨɻ ඞવతʹ΍Δ͜ͱ͕ܾ·ͬͯ͘Δ

Web Performance ~2016 • ΍Δ͜ͱ • ॳճͷϖʔδΞΫηε͚ͩ͡Όͳ͘ɺͦͷ ޙͷૢ࡞΋࠷దԽ͢Δ • ϨΠςϯγΛ࡟Δ

ॳճͷϖʔδΞΫηε͚ͩ͡Όͳ ͘ɺͦͷޙͷૢ࡞΋࠷దԽ͢Δ

ॳճͷϖʔδΞΫηε͚ͩ͡Όͳ ͘ɺͦͷޙͷૢ࡞΋࠷దԽ͢Δ 9Ͱ͸+40/Ͱ͸ͳ͘)5.-Ͱ΍Γ औΓ͍ͯͨ͠ɺશ෦ͷϖʔδΛຖճϨ ϯμϦϯά͢ΔͨΊɺແବ͕ଟ͍ɻ

ॳճͷϖʔδΞΫηε͚ͩ͡Όͳ ͘ɺͦͷޙͷૢ࡞΋࠷దԽ͢Δ 4JOHMF1BHF"QQMJDBUJPOʹ͢Δ

Single Page Application • Ajax ௨৴Λجຊͱͯ͠෦෼ϨϯμϦϯάΛ͢ Δ͜ͱͰߴ଎Խ͢Δํ๏

Single Page Application • Ajax ௨৴Λجຊͱͯ͠෦෼ϨϯμϦϯάΛ͢ Δ͜ͱͰߴ଎Խ͢Δํ๏ DMJDL

Single Page Application • Ajax ௨৴Λجຊͱͯ͠෦෼ϨϯμϦϯάΛ͢ Δ͜ͱͰߴ଎Խ͢Δํ๏ DMJDL (&5+40/

Single Page Application • Ajax ௨৴Λجຊͱͯ͠෦෼ϨϯμϦϯάΛ͢ Δ͜ͱͰߴ଎Խ͢Δํ๏ DMJDL (&5+40/ 1BSUJBM3FOEFS

Single Page Application • Ajax ௨৴Λجຊͱͯ͠෦෼ϨϯμϦϯάΛ͢ Δ͜ͱͰߴ଎Խ͢Δํ๏ DMJDL (&5+40/ 1BSUJBM3FOEFS
3JDIͳ8FC"QQMJDBUJPO͸େମ͜ ͏ͳ͖͍ͬͯͯΔɻ

ϨΠςϯγΛ࡟Δ • 1ͭͷHTTP઀ଓΛແବʹ͠ͳ͍ • ຖճHTTP઀ଓ͢Δͷ͸ωοτϫʔΫϨΠϠͰݟΔ ͱίετ͕ߴ͍ • 1ͭͷHTTP઀ଓͷதͰϦΫΤετΛଟॏԽ͢Δ • ઀ଓͬ͠ͺͳ͠ʹͳΔ͜ͱͰೖΕΒΕΔ࠷దԽ΋
͋Δʢޙड़ʣ

ϨΠςϯγΛ࡟Δ • 1ͭͷHTTP઀ଓΛແବʹ͠ͳ͍ • ຖճHTTP઀ଓ͢Δͷ͸ωοτϫʔΫϨΠϠͰݟΔ ͱίετ͕ߴ͍ • 1ͭͷHTTP઀ଓͷதͰϦΫΤετΛଟॏԽ͢Δ • ઀ଓͬ͠ͺͳ͠ʹͳΔ͜ͱͰೖΕΒΕΔ࠷దԽ΋
͋Δʢޙड़ʣ )551

HTTP2 • ଟॏԽ͞ΕͨϦΫΤετΛૹΔ • HTTP/1.1 ࣌୅ΑΓ΋1ͭͷωοτϫʔΫ઀ଓ Λ࢖ͬͨ࠷దԽ͕ߦΘΕΔΑ͏ʹɻ • Prioritization, Server
Push, Early Hints ଳҬ෯͕૿͑ͯ΋ϨΠςϯγ͕ݮΒͳ ͍ͱࠓޙͷߴ଎Խ͕Ͱ͖ͳ͍

http/1.1 problems

4FSWFS GET /main.js GET /main.css GET /image0.png

4FSWFS #SPXTFSDBOTFOESFRVFTUT POMZ

4FSWFS OFFEUPXBJUUPpOJTIQSFWJPVTSFRVFTU

4FSWFS "OEJGSFRVFTUJTTMPX CSPXTFS DBOVTFPOMZSFRVFTUT

4FSWFS "OEJGSFRVFTUJTTMPX CSPXTFS DBOVTFPOMZSFRVFTUT )FBEPG-JOF#MPDLJOH

http/2 solves HoL Blocking

Multiplexing requests on 1 TCP connection 4FSWFS 5$1$POOFDUJPOCVUNVMUJ SFRVFTUT

https://github.com/yosuke-furukawa/early-hints-demo

Next: HTTP3

HTTP3 • HTTP2ͩͱͯ͠΋HoL Blocking Λ͢΂ͯղফ ͍ͯ͠ͳ͍ • HTTP2ͷ1 connection ͷதͰTCPϨϕϧͰ
HoL Blocking͕ى͖Δ͜ͱ͸͋Δ

Multiplexing requests on 1 TCP connection 4FSWFS ΋͠΋ҰݸͷϦΫΤετͷதͰύέϩε͕ى͖ ͨ৔߹ɺ͜ͷύέϩεΛղফ͢Δ·Ͱ࣍ͷύ έοτΛૹड৴Ͱ͖ͳ͍

Multiplexing requests on UDP connection 4FSWFS ͡Ό͋͜͜Λ6%1ʹ͠Α͏

Multiplexing requests on UDP connection 4FSWFS 6%1্Ͱ৽͍͠ηΩϡΞͳϓϩτ ίϧΛ࡞ͬͯ͠·͓͏

Multiplexing requests on UDP connection 4FSWFS 6%1্Ͱ৽͍͠ηΩϡΞͳϓϩτ ίϧΛ࡞ͬͯ͠·͓͏ 26*$ͱݺͿ

HTTP3 26*$্Ͱ)551Λ࠶࣮૷͢Δɺ ͜ΕΛ)551ͱݺͿɻ

Summary ~2016 • Bible: • High Performance Browser Networking •
App: • Single Page Application • Performance Techniques: • ॳճͷϖʔδΞΫηε͚ͩ͡Όͳ͘ɺͦͷޙͷૢ࡞΋࠷దԽ͢Δ • SPA Λ࡞ΓɺϢʔβʔϞχλϦϯά͠ͳ͕Βૢ࡞ͱॲཧΛ ࠷దԽ͢Δ • ϨΠςϯγΛ࡟Δ • HTTP/2 Λ࢖͏ (ࠓޙ͸HTTP3ʹ)

όοΫΤϯυͷ ύϑΥʔϚϯε

όοΫΤϯυύϑΥʔϚϯε 4FSWFS 4FTTJPO$BDIF %#"1*

Database Performance • SQLνϡʔχϯά • N + 1 Query Λ΍ΊΔ
• Index ΛషΔ • εΩʔϚΛݟ௚͢ (ඇਖ਼نԽ) • DB ύϥϝʔλνϡʔχϯά • ΩϟογϡαΠζ • εϨουϓʔϧͷ਺

SQLνϡʔχϯά • N + 1 Query Λ΍ΊΔ • σʔλྔ(N) +
1ݸͷQuery͕ൃߦ͞Εͯ͠ ·͏ύϑΥʔϚϯεΠγϡʔ • ࠷ॳʹID͚ͩҾͬு͖͔ͬͯͯͦ͜Βৄࡉ ΛऔΓग़࣌͢౳͕ଟ͍ɻ

N+1 Query SELECT user_id FROM comments; SELECT * FROM user
WHERE id = 1; SELECT * FROM user WHERE id = 2; SELECT * FROM user WHERE id = 3; SELECT * FROM user WHERE id = 4; SELECT * FROM user WHERE id = 5; SELECT * FROM user WHERE id = 6; …

WHERE id = 1; SELECT * FROM user WHERE id = 2; SELECT * FROM user WHERE id = 3; SELECT * FROM user WHERE id = 4; SELECT * FROM user WHERE id = 5; SELECT * FROM user WHERE id = 6; … σʔλྔ /

WHERE id = 1; SELECT * FROM user WHERE id = 2; SELECT * FROM user WHERE id = 3; SELECT * FROM user WHERE id = 4; SELECT * FROM user WHERE id = 5; SELECT * FROM user WHERE id = 6; …    # JOIN 1 query  SELECT * FROM comments, users WHERE comments.user_id = users.id;   # 2 query  SELECT user_id FROM comments; SELECT * FROM users WHERE users.id IN [1, 2, 3, 4, 5, 6];

SQLνϡʔχϯά • N + 1 Query Λ΍ΊΔ • JOINͯ͠1ൃͰऔΔɹɻ SELECT
* FROM comments, users WHERE comments.user_id = users.id;

SQLνϡʔχϯά • IndexΛషΔ • ࡧҾͷ͜ͱɻ • ຊͷޙΖͰग़ͯདྷΔΞϨɻ

SQLνϡʔχϯά • IndexΛషΔ • ࡧҾͷछྨ͸৭ʑ͋Δ͕ɺ୅දతͳ΋ͷͱ ͯ͠B-Treeʹ͍ͭͯղઆ͢Δɻ JE OBNF BHF HFOEFS
BMJDF GFNBMF CPC NBMF DIBSMJF NBMF EBWJE NBMF

SQLνϡʔχϯά • IndexΛషΔ • ྫ୊ 1,2,3,4,5,6,7ͱ͋ΔϦετͷத͔Β5Λ୳࣌͢Ͳ͏͢Δ͔ʁ • Bad Algorithm: શ୳ࠪઌ಄͔Βॱʹݟ͍ͯͬͯൃݟͨ͠Β
OK => O(n) • Good Algorithm: ༧Ίιʔτͯ͋͠ΔͷͰɺ൒෼ʹ෼͚ͯ େখൺֱͯ͠ݟ͚ͭΔ => O(log2n)

• ̎෼୳ࡧ໦ SQLνϡʔχϯά

• B-Tree (1ͭͷNode͕ෳ਺ͷࢠϊʔυΛ࣋ͯΔ) SQLνϡʔχϯά

• B+-Tree (຤୺ͷϦʔϑϊʔυ͕ྡ઀͢ΔϦʔϑ ϊʔυʹͭͳ͕͍ͬͯΔ) • B-TreeΑΓ΋ൣғݕࡧʹڧ͍ SQLνϡʔχϯά

• IndexΛషΔɺͱ͍͏ͷ͸(ΞϧΰϦζϜ͸ෳ਺͕͋Δ͕ओ ʹ)B+-TreeͷࡧҾΛ࡞͓ͬͯ͘ɺͱ͍͏ߦҝɻ • RDBMS͸ݕࡧ໋͕ɺIndexΛ͓࣋ͬͯ͘ͱݕࡧʹ༗ར • ͔͠͠ɺݕࡧʹ͸༗ར͔ͩΒͱݴͬͯషΓ͗͢Δͱɺࠓ ౓͸B+-TreeͷόϥϯεΛऔΔ࣌ʹϊʔυͷҾͬӽ͕͠ൃ ੜ͢ΔͷͰInsert, Update,
Deleteͱ͍ͬͨߋ৽ॲཧʹෆ ར SQLνϡʔχϯά

• Covering Index • IndexΛҾ͚ͩ͘Ͱݕࡧ͕ऴྃ͢ΔIndexͷ ͜ͱ • ྫΛ্͛ͯղઆ SQLνϡʔχϯά

SQLνϡʔχϯά • Α͘ݕࡧ͢ΔΧϥϜʹ͸ΠϯσοΫεΛ෇͚Δɻ • JE OBNF BHF HFOEFS BMJDF
GFNBMF CPC NBMF DIBSMJF NBMF EBWJE NBMF SELECT * FROM age < 25 AND gender = "male"

SQLνϡʔχϯά • Α͘ݕࡧ͢ΔΧϥϜʹ͸ΠϯσοΫεΛ෇͚Δɻ • JE OBNF BHF HFOEFS BMJDF
GFNBMF CPC NBMF DIBSMJF NBMF EBWJE NBMF SELECT * FROM age < 25 AND gender = "male" ͜ͷ̎ͭʹΠϯσοΫεΛషΔ

SQLνϡʔχϯά • Ͳ͏షͬͨΒྑ͍͔ʁ • ALTER TABLE a ADD INDEX b
(age, gender); • ALTER TABLE a ADD INDEX b (gender, age);

SQLνϡʔχϯά • Ͳ͏షͬͨΒྑ͍͔ʁ • ALTER TABLE a ADD INDEX b
(age, gender); • ALTER TABLE a ADD INDEX b (gender, age); ࣮͸͕ͬͪ͜ਖ਼ղ

SQLνϡʔχϯά • ΠϯσοΫε͸ΧʔσΟφϦςΟ͕ߴ͍ॱʹషΔ΂͠ɻ • age ͸ଟ༷ɺ gender ͸2छྨ • age
Ͱݕࡧ͔ͯ͠ΒgenderͰݕࡧ͢Δ΄͏͕indexޮ͖ ΍͍͢ɻ • genderͰݕࡧ͔ͯ͠ΒageͰݕࡧ͢Δͱ࠷ॳʹ50%ʹ ߜ͔ͬͯΒΑΓখ͍͞ݕࡧʹͳΔ

SQLνϡʔχϯά • ඇਖ਼نԽΛݕ౼͢Δɻ • ਖ਼نԽ͢Δͷ͸ॏཁ͕ͩɺਖ਼نԽͯ͠ΔͱJOIN͕ සൃ͢Δɻ • ຖճJOIN͢ΔΑΓ͸Ή͠ΖҰݸͷςʔϒϧʹ͠ ͪΌ͏ͷ΋༗Γɻ

SQLνϡʔχϯά • ݕࡧʹΑͬͯ͸JOIN͢ΔΑΓ΋Ұݸͷςʔϒϧʹ ·ͱΊͨ΄͏͕ྑ͍͕࣌͋Δ(ඇਖ਼نԽ) JE OBNF BHF HFOEFS BMJDF
GFNBMF CPC NBMF DIBSMJF NBMF EBWJE NBMF VTFS@JE SPMF BENJO NFNCFS PXOFS OFXCJF JE OBNF BHF HFOEFS SPMF BMJDF GFNBMF BENJO CPC NBMF NFNCFS DIBSMJF NBMF PXOFS EBWJE NBMF OFXCJF

DBύϥϝʔλνϡʔχϯά • https://yakst.com/ja/posts/65 • innodb_buffer_pool_size • σʔλͱIndexΛϝϞϦ্ʹࡌͤΔαΠζ • innodb_buffer_pool_instance •
όοϑΝϓʔϧΛԿݸ͔࣋ͭ

DBύϥϝʔλνϡʔχϯά • ͜ͷล͸࣮͸DBʹΑͬͯνϡʔχϯάϙΠϯτ͕มΘ Δɻ • MySQLͳΒ͜ͷຊΦεεϝ • OracleͳΒύϑΥʔϚϯενϡʔχϯάΨΠυ͕Web ͰಡΊΔͷͰΦεεϝ •
PostgreSQL͸͋·Γ஌Βͳ͍

Use Proper Middleware • Ͳ͏ʹ΋ͳΒͳ͍࣌͸RDBMSΛ΍ΊΔ • RDBMSͰ஗͍ͱࢥ͏Α͏ͳॲཧ͸ࢥ͍੾ͬͯRDBMS ͡Όͳͯ͘΋ͬͱద੾ͳϛυϧ΢ΣΞΛ࢖͏ • ྫ:
શจݕࡧͳΒElasticsearch • ྫ: ηογϣϯ؅ཧɺΩϟογϡͳΒRedis, Memcached

Use Proper Middleware • શจݕࡧͷػೳΛ࣋ͬͯΔRDBMS΋͋Δ͕ɺ جຊ͸Elasticsearch౳ͷશจݕࡧΤϯδϯΛ ࡞ͬͯӡ༻͢Δࣄ͕ଟ͍ʢϦΫϧʔτͰ͸ʣ • Ϣʔβʔηογϣϯ౳ͷߴΞΫηε͞ΕΔྖ Ҭ͸Redisʹஔ͘͜ͱ͕ଟ͍(ϦΫϧʔτͰ͸)

ElasticSeach • શจݕࡧΤϯδϯ • RDBMS͸B+TreeΛجຊͱ͢ΔΠϯσΩγϯά Λ͢Δ͕ɺͪ͜Β͸సஔߦྻΛجຊͱͨ͠Π ϯσοΫεΛ࣋ͭ • จॻͷத͔ΒͲͷ୯ޠΛ͍࣋ͬͯΔ͔Λݟͭ ͚Δࡍʹ༗ޮͳΠϯσοΫε

Redis • Key Value Store ͱݺ͹ΕΔ୯७ͳߏ଄Λ࣋ͭσʔ λετΞͷҰछ • جຊతʹΠϯϝϞϦͰશͯϝϞϦதʹ৐ͤͯղܾ ͢Δ͜ͱ͕ଟ͍
• ൣғݕࡧ౳͸ͦ͜·ͰಘҙͰ͸ͳ͍͠ɺ·ͯ͠΍ τϥϯβΫγϣϯ౳ͷػೳ͸ͳ͍͕ɺΩʔΛࢦఆ ͯ͠όϦϡʔΛҰൃͰऔΔͱ͖ʹ͸Α͘࢖͏ɻ

όοΫΤϯυύϑΥʔϚϯε· ͱΊ • DBνϡʔχϯά • ద੾ͳϛυϧ΢ΣΞΛ࢖͓͏ • ΋ͬͱݴ͍͍ͨ͜ͱ͸৭ʑ͋Δ͕ɺ࢒Γ͸࣮ ફͰ΍Ζ͏

ਪଌ͢ΔͳܭଌͤΑ

ਪଌ͢ΔͳܭଌͤΑ • ૣ͗͢Δ࠷దԽʢࢥ͍ࠐΈͱצͰ΍Δ࠷ద Խʣ͸ॾѱͷࠜݯͰ͋Δ by Donald Knuth • ͪΌΜͱܭଌ͢Δඞཁ͕͋Δ •
ϘτϧωοΫΛݟ͚ͭͯղফ͢Δͷ͕࠷దԽ ͷجຊ

ਪଌ͢ΔͳܭଌͤΑ • ܭଌํ๏ • WebαʔόͷΞΫηεϩά • OSͷϦιʔεϩά • DBͷϓϩϑΝΠϥ •
ΞϓϦέʔγϣϯαʔόͷϓϩϑΝΠϥ

WebαʔόΞΫηεϩά • nginx, apacheͷΞΫηεϩάΛݟΔ • ͨͩ͠ී௨ʹݟͯ΋ζϥοͱग़͖ͯͯਏ͍ͷͰαϚϥ Πζͯ͠ݟΔ͔ݕࡧͰ͖ΔΑ͏ʹ͢Δඞཁ͕͋Δɻ • new relic
• kataribe • alp

OSͷϦιʔεϩά • ݪ࢝తʹ͸top • xxstatܥ͕ศར͕ͩɺશ෦ݟͯΔͷ͸ਏ͍ • ݸਓతͳΦεεϝ͸netdata, newrelic΋ྑ͍ ͔΋ɻ

DBͷϓϩϑΝΠϥ • SQLʹ͸EXPLAINͱ͍͏தͰDB͕Ͳ͏΍ͬͯͦͷΫ ΤϦΛ࣮ߦ͔ͨ͠ΛݟΔ͜ͱ͕Ͱ͖ΔίϚϯυ͕͋ ΔɺͦΕΛ࢖͏ • slow_query_logͳͲͷΫΤϦ͕஗͍͔ΛϞχλ͢Δ • pt_query_digest πʔϧ
• performance_schemaΛ࢖ͬͯϓϩϑΝΠϧ͢ΔͳͲ

ΞϓϦέʔγϣϯαʔόͷϓϩ ϑΝΠϥ • Golang pprof • Node.js see my blog
• http://yosuke-furukawa.hatenablog.com/ entry/2017/12/05/125517 • Java proﬁler

Performance Bootcamp

Performance Bootcamp

More Decks by Recruit Technologies

Other Decks in Technology

Featured

Transcript