MySQL Casual Talks Vol.3 〜カジュアルだよこわくないよ〜

MySQL Casual Talks Vol.3

Transcript

1. σʔληϯλʔҠߦʹ൐͍ɺ MySQLΛΧδϡΞϧʹ ΞοϓάϨʔυ͓ͨ͠࿩ MySQL Casual Talks Vol.3 ʙΧδϡΞϧͩΑ͜Θ͘ͳ͍Αʙ 2012/04/19 Naoya Nakazawa@n0ts ೔ຊΦϥΫϧ ੨ࢁηϯλʔ Thursday, April 19, 12
2. ࣗݾ঺հ Thursday, April 19, 12
3. n Naoya Nakazawa n @n0ts n ೔ຊޠϒϩά n http://www.sssg.org/blogs/naoya/ n ແॴଐ n έʔϒϦϯά͔ΒΞϓϦ·Ͱ Thursday, April 19, 12
4. αʔό/ΠϯϑϥΤϯδχΞཆ੒ಡຊ ؅ཧ/؂ࢹฤ ઈࢍൃചதʂʂʂ Thursday, April 19, 12
5. ࠓ೔ͷ͓࿩ Thursday, April 19, 12
6. n େਓͷࣄ৘Ͱɺ͋ΔαʔϏεΛσʔληϯ λʔҠߦ͢Δ͜ͱʹͳΓ·ͨ͠ n ͦͷ͍ͭͰʹ MySQL ΛΞοϓάϨʔυ͠ ͨͷͰɺͦͷ͓࿩Λ͠·͢ Thursday, April 19, 12
7. ͦͷલʹ Thursday, April 19, 12
8. લճͷMySQL Casual Talks Vol.2 Thursday, April 19, 12
9. http://d.hatena.ne.jp/oranie/20110810/1312969238 ʮMySQL 5.0 -> 5.5 ΁ͷΞοϓάϨʔυ ϓϥεͳΜ͔ʯ @oranie Thursday, April 19, 12
10. αʔϏεΛఀࢭͰ͖Δ৔߹Ͱͷɺ MySQL 5.0͔Β5.5ʹ ΞοϓάϨʔυ͢ΔͨΊͷํ๏ Thursday, April 19, 12
11. ͸ͯϒ͓ئ͍͠·͢ʂʂʂ Thursday, April 19, 12
12. ࠓ೔͸ɺαʔϏε͕ࢭΊΔ ͜ͱ͕Ͱ͖ͳ͍৔߹ ͷMySQLΞοϓάϨʔυʹ͍ͭͯͷ ͓࿩Λ͠·͢ Thursday, April 19, 12
13. αʔϏεن໛ n ͱ͋Δ޿ࠂܥαʔϏε n ඵؒ͋ͨΓͷϐʔΫ࣌ͷϦΫΤετ਺ n X000 req/sec n ෺ཧαʔό1ϥοΫఔ౓ n ௒খن໛αʔϏε Thursday, April 19, 12
14. αʔόߏ੒ ʢ؆қ൛ʣ Thursday, April 19, 12
15. LVS Application Server DB1 MySQL 5.0.51b DB2 MySQL 5.0.71 LVS Master repl Slave Master Slave repl Write Read Write Read Thursday, April 19, 12
16. MySQL 5.0.51b? Thursday, April 19, 12
17. https://twitter.com/#!/nobu666/status/178350176719736832 5.0.53! InnoDB Deep Talk #1 2012/03/10 Thursday, April 19, 12
18. σʔληϯλʔҠߦ Thursday, April 19, 12
19. Thursday, April 19, 12
20. Thursday, April 19, 12
21. Ҡߦલͷαʔόߏ੒ Thursday, April 19, 12
22. چDC ৽DC Application Server LVS LVS repl repl DB1 DB2 Application Server LVS LVS repl repl DB1 DB2 Thursday, April 19, 12
23. repl DB1 MySQL 5.0.51b چDC ৽DC Master Slave repl Master Slave DB1 MySQL 5.1.58 ×SSL Replication SSH Port Forwarding repl DB2 MySQL 5.0.71 Master Slave repl Master Slave DB2 MySQL 5.1.58 Thursday, April 19, 12
24. n MySQL 5.1.58͔Β5.0.51bʹ઀ଓ͢Δͱɺ࣍ͷΤ ϥʔ͕ൃੜͨ͠ n ERROR 2026 (HY000): SSL connection error n MasterͷόʔδϣϯΛ5.1.58ʹ͋ΘͤΔͱ͏·͘ ͍ͬͨͷͰɺόʔδϣϯͷ໰୊͔ɺ5.0ܥΛSSLର ԠϏϧυ͍ͯ͠ͳ͔ͬͨ͜ͱ͕ݪҼ͔΋͠Εͳ͍ SSLϨϓϦέʔγϣϯͷࣦഊ Thursday, April 19, 12
25. SSHϙʔτϑΥϫʔυ n ৽DC DB1 Master্ͰɺچDC DB1 MasterʹSSHτϯωϧΛ۷Δ n ssh -f <چDC DB1 Master> -L 13306:localhost:3306 -N -4 -g n LVSܦ༝ͷͨΊɺLVS͕੾ΓସΘΔͱSSHτϯωϧ͕੾ΕΔͨΊɺ MonitͰఆظతʹSSHτϯωϧ͕ด͍ͯ͡Δ͔֬ೝ͢ΔΑ͏ʹͨ͠ n pgrepͯ͠ͳ͚Ε͹SSHτϯωϧΛ۷Δ؆୯ͳγΣϧεΫϦϓτ n ৽DC DB1 MasterΛɺҰ࣌తʹSlaveʹ͢Δ n mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=13306 ...; Thursday, April 19, 12
26. ৽DCͷMySQL n MySQL SlaveͷΈϋʔυ΢ΣΞRAID0 + SSD x 4 n XFS, deadlineεέδϡʔϥ n ެࣜͷMySQL 5.1.58 RPM n InnoDB pluginΛར༻ n InnoDBͷઃఆΛશൠతʹݟ௚͔ͨͬͨ͠ͷͰɺچDC্Ͱmysqldumpͨ͠ σʔλΛྲྀ͠ࠐΜͰɺ৽DC DB1 MasterΛεϨʔϒͱͨ͠ n DB2ͷํ͸ɺ׬શҠߦޙʹఀࢭͯ͠Ҡߦͨ͠ n Ұ෦ͷςʔϒϧʹInnoDB Data CompressionΛಋೖ Thursday, April 19, 12
27. DCҠߦ -STEP 1- Thursday, April 19, 12
28. n DNSϥ΢ϯυϩϏϯʹ͢ΔʢϨδετϥ ͷ࢓༷ͰTTLΛมߋͰ͖ͳ͔ͬͨʣ n ৽DB1ͷΞϓϦέʔγϣϯͷॻ͖ࠐΈઌ ͸ɺSSHτϯωϧܦ༝ͰچDCͷDB1 Masterʹରͯ͠ߦ͏ Thursday, April 19, 12
29. چDC ৽DC Application Server LVS LVS repl repl DB1 DB2 Application Server LVS LVS repl repl DB1 DB2 Traffic Write Thursday, April 19, 12
30. DCҠߦ -STEP 2- Thursday, April 19, 12
31. n DNSϥ΢ϯυϩϏϯΛ΍Ίͯɺ৽DCͷΈ ੾Γସ͑Δ n ϦΫΤετ͕׬શʹ৽DCͷΈʹͳΔ·Ͱ ͠͹Β͘଴ͭ Thursday, April 19, 12
32. DCҠߦ -STEP 3- Thursday, April 19, 12
33. 1. ϦΫΤετͷ࠷΋গͳ͍࣌ؒଳʹɺΞϓϦέʔ γϣϯ͔Βσʔλϕʔε΁ͷॻ͖ࠐΈΛҰ࣌తʹ ఀࢭ͢ΔʢόονͳͲ΋͋Θͤͯʣ 2. ৽DC DB1ͷSlaveΛMaster΁ঢ֨ͤ͞Δ 3. ঢ֨ޙɺΞϓϦέʔγϣϯ͔Βσʔλϕʔεͷॻ ͖ࠐΈΛ࠶։ͤ͞Δ Thursday, April 19, 12
34. چDC ৽DC Application Server LVS LVS repl repl DB1 DB2 Application Server LVS LVS repl repl DB1 DB2 Traffic Thursday, April 19, 12
35. ·ͱΊ Thursday, April 19, 12
36. n DCҠߦதΛؚΊͯαʔϏεͷఀࢭ͸ͳ͠ n Ҡߦத΋αʔϏε্ͷେ͖ͳ஗Ԇ͸ͳ͠ n SlaveΛMaster΁ঢ֨ͤ͞Δ਺෼͚ؒͩɺॻ͖ࠐΈ ͸Ұ࣌ఀࢭ͚ͨ͠ΕͲαʔϏεͷఀࢭ͸ͳ͠ n ͋ΘͤͯɺMySQL 5.0ܥ͔Β5.1ܥʹ҆શʹ͔ͭ໰ ୊ͳ͘ΞοϓάϨʔυͰ͖ͨ Thursday, April 19, 12
37. Thanks! Thursday, April 19, 12