Webサービスが成長するとロックで苦労する話

MySQL Casual Talk #10と第19回 中国地方DB勉強会 in 米子の資料です

Transcript

1. WebαʔϏε͕੒௕͢Δͱ ϩοΫͰۤ࿑͢Δ࿩ ୈճ
   தࠃ஍ํ%#ษڧձ
   JO
   ถࢠ
2. What is it? ϩοΫ͸ઈରʹඞཁͰ͋Δ
3. What is it? ͔͠͠αʔϏε͕੒௕͢Δͱ ϘτϧωοΫͷݪҼʹͳΓ΍͍͢
4. What is it? ͦΜͳWebαʔϏε͋Δ͋Δ
5. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
6. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
7. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
8. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
9. Mackerel
10. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
11. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
12. ϩοΫ଴ͪ ͬ͘͟Γͱͨ͠આ໌
    ɹɹ!LLJEB͞Μͷࢿྉ͔ΒҾ༻
13. ste rt G n tn 1 ' 1 1 tn L a p L J 6 P L P o gQ6 L GUL J t P S S S Q u L S L S L PG
14. gJL C r K n a osEK 1 pEK 41 ' 2 5 7 A B G NSOMR n OUPJ eQ g * 0 = 1 = ; 1 = ; 0  
    I 'I  
    I 'I  
    I 'I  
    I 'I 1 N g  
    I 'I 41 ' N ' 0 TN K
15. L 8 n QS GJPS L U 1 0 ' 1 0 1 0 * - ; 1 ; A = - ;  
    S  
    S - ; - ; - ;  
    S  
    S  
    S
16. ϩοΫ଴ͪ ΈΜͳҰൠతͳΤϯδχΞ͔ͩΒ
    ϩοΫͷࣄ͸஌ͬͯΔ͸ͣ
    ˣ
    ϩοΫͷઆ໌͸ׂѪ G nPMJL G Q TP TU 1 0 ' 0 S C S G * 1 * * =; * =; * SF -* * P MLN * * =;  
    M9I M O  
    M9I M O * * =; * * =; * =; * SF -* * P MLN R < > H IODTUCAEBG  
    M9I M O  
    M9I M O
17. ϩοΫ଴ͪ ओͳϩοΫͷछྨ
    w ഉଞ F9DMVEFE ϩοΫ
    w ڞ༗ 4IBSFE ϩοΫ
18. ϩοΫ଴ͪ ओͳϩοΫͷछྨ
    w ഉଞ F9DMVEFE ϩοΫ
    w ڞ༗ 4IBSFE ϩοΫ ϩοΫର৅ʹରͯ͠ɺଞͷΞΫηεΛҰ੾ېࢭ͢Δ
    ଞͷΞΫηε͸ߋ৽ɾ࡟আɾࢀরશͯग़དྷͳ͍
    ॻ͖ࠐΈϩοΫͱݺ͹ΕΔࣄ΋͋Δ
19. ϩοΫ଴ͪ ओͳϩοΫͷछྨ
    w ഉଞ F9DMVEFE ϩοΫ
    w ڞ༗ 4IBSFE ϩοΫ ϩοΫର৅ʹରͯ͠ɺࢀরҎ֎ͷॲཧΛېࢭ͢Δ
    ଞͷΞΫηε͸ࢀর 4&-&$5 Λ͢Δ͜ͱ͕ग़དྷΔ
    ಡΈࠐΈϩοΫͱݺ͹ΕΔࣄ΋͋Δ
20. ϩοΫ଴ͪ ϩοΫ͸҉໧తʹऔ͍ͬͯΔ
21. ϩοΫ଴ͪ .Z42-Ͱओʹڞ༗ϩοΫΛऔΔ
    w +0*/ͱ͔αϒΫΤϦͱ͔
    w ߋ৽ܥͱ͔
    w Ұ෦ͷ"-5&3ͱ͔
22. ϩοΫ଴ͪ 1PTUHSF42-ओʹڞ༗ϩοΫΛऔΔ
    w 4&-&$5Ͱ΋ϩοΫͱΔ
    w "-5&3จͱ͔%#ຖͰ݁ߏҧ͏
    w ϨϓϦέʔγϣϯͷεϨʔϒଆ΋஫ҙ
23. ϩοΫ଴ͪ ͍ͭ͜Β͸ઃܭ࣌ʹҙࣝ͢ΔϩοΫ
24. ϩοΫ଴ͪ ࢓༷มߋʹΑΔ2VFSZͷมߋ
    
25. ϩοΫ଴ͪ ࢓༷มߋʹΑΔ2VFSZͷมߋ
    ˣ
    */%&9షΒͣʹ+0*/
    */%&9ͷޮ͔ͳ͍8)&3&۟
26. ϩοΫ଴ͪ ࢓༷มߋʹΑΔߏ଄ͷมߋ
    
27. ϩοΫ଴ͪ ࢓༷มߋʹΑΔߏ଄ͷมߋ
    ˣ
    ࡟আϑϥάΈ͍ͨͳฅΧϥϜ
    
28. ϩοΫ଴ͪ ࣌ؒܦաͱڞʹ࢓ࠐ·ΕΔര஄
29. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
30. ϦϑΝΫλϦϯά QFSGPSNBODF@TDIFNB
31. ϦϑΝΫλϦϯά QFSGPSNBODF@TDIFNB
    ˣ
    ͱΓ͋͑ͣ໎ͬͨΒ༗ޮԽ͠ͱ͚
32. ϦϑΝΫλϦϯά ੲ͸σϑΥϧτͰ
    ༗ޮԽ͡Όͳ͔ͬͨ
33. ϦϑΝΫλϦϯά ੲ͸σϑΥϧτͰ
    ༗ޮԽ͡Όͳ͔ͬͨ ࠷ۙ͸༗ޮʹͳͬͯΔ
34. ϦϑΝΫλϦϯά TZTTUBUFNFOU@BOBMZTJT
35. ϦϑΝΫλϦϯά TZTTUBUFNFOU@BOBMZTJT
    ˣ
    2VFSZͷ࣮ߦճ਺ͱ͔
    వΊͯ͘ΕΔ΍ͭ
36. TZTTUBUFNFOU@BOBMZTJT mysql> SELECT * FROM statement_analysis\G *************************** 2. row *************************** query: SELECT `c` FROM `sbtest1` WHERE `id` = ? db: sbtest full_scan: exec_count: 23890 err_count: 0 warn_count: 0 total_latency: 2.36 s max_latency: 521.15 us avg_latency: 98.74 us lock_latency: 778.65 ms rows_sent: 23890 rows_sent_avg: 1 rows_examined: 23890 rows_examined_avg: 1 rows_affected: 0 rows_affected_avg: 0 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 0 sort_merge_passes: 0 digest: 80295d1d2720d4515b05d648e8caa82f first_seen: 2016-07-12 17:04:40 last_seen: 2016-07-12 17:04:53
37. ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT
    
38. ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT
    ˣ
    lݱࡏzͷϩοΫ૊Έ߹Θͤ
    2VFSZҰཡ
39. TZTJOOPEC@MPDL@XBJUT mysql57> SELECT * FROM innodb_lock_waits\G *************************** 1. row *************************** wait_started: 2016-07-12 17:49:06 wait_age: 00:00:13 wait_age_secs: 13 locked_table: `d1`.`user` locked_index: PRIMARY locked_type: RECORD waiting_trx_id: 8063 waiting_trx_started: 2016-07-12 17:49:06 waiting_trx_age: 00:00:13 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 320 waiting_query: SELECT * FROM user LIMIT 3 FOR UPDATE waiting_lock_id: 8063:146:3:2 waiting_lock_mode: X blocking_trx_id: 8062 blocking_pid: 321 blocking_query: NULL blocking_lock_id: 8062:146:3:2 blocking_lock_mode: X blocking_trx_started: 2016-07-12 17:49:06 blocking_trx_age: 00:00:13 blocking_trx_rows_locked: 3 blocking_trx_rows_modified: 0 sql_kill_blocking_query: KILL QUERY 321 sql_kill_blocking_connection: KILL 321 1 row in set (0.01 sec)
40. ϦϑΝΫλϦϯά ZPLV͞Μ͕
    தࠃ஍ํ%#ษڧ
    JO
    ޿ౡͰ࿩ͯ͠Δ
41. ϦϑΝΫλϦϯά ಈը΋ࢿྉ΋͋ΔͷͰੋඇ
    IUUQECTUVEZDIVHPLVHJUIVCJP FWFOUTFWFOUIUNM
42. ϦϑΝΫλϦϯά ஫ҙ఺
43. ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT
    ͸ྺ࢙Λ࣋ͨͳ͍
44. ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT
    ͸ྺ࢙Λ࣋ͨͳ͍ ਺෼ஔ͖ʹԿ͔ʹग़ྗ͠Α͏
45. ϦϑΝΫλϦϯά 4ʹు͍ͯ"84
    "UIFOBͰ෼ੳ
46. ϦϑΝΫλϦϯά ࠷ऴखஈ
    
47. ϦϑΝΫλϦϯά 4)08
    &/(*/&
    *//0%#
    45"564
48. ϦϑΝΫλϦϯά
49. ϦϑΝΫλϦϯά ਓྨ͕ಡΉʹ͸ͭΒ͍
50. ϦϑΝΫλϦϯά !NZpOEFS͞Μ͕ॻ͍ͯΔ
    IUUQTXXXTMJEFTIBSFOFUNZpOEFS TIPXJOOPECTUBUVT
51. ϦϑΝΫλϦϯά ࣍ճͷ.Z42-
    $BTVBM·Ͱʹ
    ಡΊΔΑ͏ͳ࢓૊Έ࡞Γ͍ͨʢ༧ఆʣ
52. ϦϑΝΫλϦϯά 1PTUHSF42-͸ʁ
53. ϦϑΝΫλϦϯά QH@TUBU@TUBUFNFOUT
54. ϦϑΝΫλϦϯά QH@TUBUTJOGP
55. ϦϑΝΫλϦϯά !LLJEB͞Μ͕
    தࠃ஍ํ%#ษڧձ
    JO
    Ԭࢁʢ͈́
56. ϦϑΝΫλϦϯά ࢀߟࢿྉ
    IUUQECTUVEZDIVHPLVHJUIVCJP FWFOUTFWFOUIUNM
57. ϦϑΝΫλϦϯά QH@TUBUT@SFQPSUFS
58. ϦϑΝΫλϦϯά !NBTVEBL[͞Μ͕
    1PTUHSF42-ษڧձͰʢ͈́
59. ϦϑΝΫλϦϯά ࢀߟࢿྉ
    IUUQRJJUBDPNNBTVEBL[ JUFNTCCBDBEE
60. ϦϑΝΫλϦϯά ͜ͷ΁ΜΛ࢖ͬͯ
    ໰୊ͷ2VFSZ܈Λඥղ͘
61. ϩοΫ଴ͪ ࣌ؒܦաͱڞʹ࢓ࠐ·ΕΔര஄ ݟ͑ͯ͘Δ
62. ϦϑΝΫλϦϯά ៉ྷͳϦϑΝΫλϦϯά
    w ࢀরͱߋ৽ͷಡΈࠐΈઌΛ෼͚Δ
    w ߋ৽ͷϘτϧωοΫΛ෼͚Δ
    w σʔλઃܭΛݟ௚͢
    w ద੾ʹ*/%&9Λ׆༻͢Δ
63. ϦϑΝΫλϦϯά ҋͷਂ͍ϦϑΝΫλϦϯά
    w +0*/Λඇਖ਼نԽͰิ͏
    w ֎෦Ωʔ੍໿Λ֎͢
    w ͱΓ͋͑ͣΩϟογϡʹಥͬࠐΉ
    w ۃ౓ͷεέʔϧΞοϓ
64. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
65. ·ͱΊ ϩοΫ͸େ੾ͳ࢓૊ΈͷҰͭ
66. ·ͱΊ 3%#ͷ੹຿
    w σʔλΛద੾ʹकΔ
    w σʔλΛద੾ʹऔΓग़͢
    w σʔλΛద੾ʹอଘ͢Δ
67. ·ͱΊ ͔͠͠ϘτϧωοΫʹͳΓ΍͍͢
68. ·ͱΊ Ͱ͸Ͳ͏΍ͬͯ෇͖߹͍͔ͬͯ͘
69. ·ͱΊ wυΩϡϝϯτΛݟΔ
    wఆৗతʹ؂ࢹ͢Δ
    wద੾ͳվળΛଓ͚Δ
70. ·ͱΊ ಛʹϩοΫ͸ઃܭ͕େࣄ
71. ·ͱΊ %#ͷ໰୊͸๨Εͨࠒʹ΍ͬͯ͘Δ
72. ·ͱΊ
73. ࢀߟࢿྉ ɾQPTUHSFTRMKQ
    4MBDL νϟοτϧʔϜ
    IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN
    ɾNZTRMDBTVBM
    4MBDL νϟοτϧʔϜ
    IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN
    
74. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ