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

by soudai sone

Slide 1

Slide 1 text

WebαʔϏε͕੒௕͢Δͱ ϩοΫͰۤ࿑͢Δ࿩ ୈճதࠃ஍ํ%#ษڧձJOถࢠ

Slide 2

Slide 2 text

What is it? ϩοΫ͸ઈରʹඞཁͰ͋Δ

Slide 3

Slide 3 text

What is it? ͔͠͠αʔϏε͕੒௕͢Δͱ ϘτϧωοΫͷݪҼʹͳΓ΍͍͢

Slide 4

Slide 4 text

What is it? ͦΜͳWebαʔϏε͋Δ͋Δ

Slide 5

Slide 5 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ

Slide 6

Slide 6 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ

Slide 7

Slide 7 text

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Mackerel

Slide 10

Slide 10 text

Slide 11

Slide 11 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ

Slide 12

Slide 12 text

ϩοΫ଴ͪ ͬ͘͟Γͱͨ͠આ໌ ɹɹ!LLJEB͞Μͷࢿྉ͔ΒҾ༻

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

L 8 n QS GJPS L U 1 0 ' 1 0 1 0 * - ; 1 ; A = - ; S S - ; - ; - ; S S S

Slide 16

Slide 16 text

ϩοΫ଴ͪ ΈΜͳҰൠతͳΤϯδχΞ͔ͩΒ ϩοΫͷࣄ͸஌ͬͯΔ͸ͣ ˣ ϩοΫͷઆ໌͸ׂѪ 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

Slide 17

Slide 17 text

ϩοΫ଴ͪ ओͳϩοΫͷछྨ w ഉଞ F9DMVEFE ϩοΫ w ڞ༗ 4IBSFE ϩοΫ

Slide 18

Slide 18 text

ϩοΫ଴ͪ ओͳϩοΫͷछྨ w ഉଞ F9DMVEFE ϩοΫ w ڞ༗ 4IBSFE ϩοΫ ϩοΫର৅ʹରͯ͠ɺଞͷΞΫηεΛҰ੾ېࢭ͢Δ ଞͷΞΫηε͸ߋ৽ɾ࡟আɾࢀরશͯग़དྷͳ͍ ॻ͖ࠐΈϩοΫͱݺ͹ΕΔࣄ΋͋Δ

Slide 19

Slide 19 text

ϩοΫ଴ͪ ओͳϩοΫͷछྨ w ഉଞ F9DMVEFE ϩοΫ w ڞ༗ 4IBSFE ϩοΫ ϩοΫର৅ʹରͯ͠ɺࢀরҎ֎ͷॲཧΛېࢭ͢Δ ଞͷΞΫηε͸ࢀর 4&-&$5 Λ͢Δ͜ͱ͕ग़དྷΔ ಡΈࠐΈϩοΫͱݺ͹ΕΔࣄ΋͋Δ

Slide 20

Slide 20 text

ϩοΫ଴ͪ ϩοΫ͸҉໧తʹऔ͍ͬͯΔ

Slide 21

Slide 21 text

ϩοΫ଴ͪ .Z42-Ͱओʹڞ༗ϩοΫΛऔΔ w +0*/ͱ͔αϒΫΤϦͱ͔ w ߋ৽ܥͱ͔ w Ұ෦ͷ"-5&3ͱ͔

Slide 22

Slide 22 text

ϩοΫ଴ͪ 1PTUHSF42-ओʹڞ༗ϩοΫΛऔΔ w 4&-&$5Ͱ΋ϩοΫͱΔ w "-5&3จͱ͔%#ຖͰ݁ߏҧ͏ w ϨϓϦέʔγϣϯͷεϨʔϒଆ΋஫ҙ

Slide 23

Slide 23 text

ϩοΫ଴ͪ ͍ͭ͜Β͸ઃܭ࣌ʹҙࣝ͢ΔϩοΫ

Slide 24

Slide 24 text

ϩοΫ଴ͪ ࢓༷มߋʹΑΔ2VFSZͷมߋ

Slide 25

Slide 25 text

ϩοΫ଴ͪ ࢓༷มߋʹΑΔ2VFSZͷมߋ ˣ */%&9షΒͣʹ+0*/ */%&9ͷޮ͔ͳ͍8)&3&۟

Slide 26

Slide 26 text

ϩοΫ଴ͪ ࢓༷มߋʹΑΔߏ଄ͷมߋ

Slide 27

Slide 27 text

ϩοΫ଴ͪ ࢓༷มߋʹΑΔߏ଄ͷมߋ ˣ ࡟আϑϥάΈ͍ͨͳฅΧϥϜ

Slide 28

Slide 28 text

ϩοΫ଴ͪ ࣌ؒܦաͱڞʹ࢓ࠐ·ΕΔര஄

Slide 29

Slide 29 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ

Slide 30

Slide 30 text

ϦϑΝΫλϦϯά QFSGPSNBODF@TDIFNB

Slide 31

Slide 31 text

ϦϑΝΫλϦϯά QFSGPSNBODF@TDIFNB ˣ ͱΓ͋͑ͣ໎ͬͨΒ༗ޮԽ͠ͱ͚

Slide 32

Slide 32 text

ϦϑΝΫλϦϯά ੲ͸σϑΥϧτͰ ༗ޮԽ͡Όͳ͔ͬͨ

Slide 33

Slide 33 text

ϦϑΝΫλϦϯά ੲ͸σϑΥϧτͰ ༗ޮԽ͡Όͳ͔ͬͨ ࠷ۙ͸༗ޮʹͳͬͯΔ

Slide 34

Slide 34 text

ϦϑΝΫλϦϯά TZTTUBUFNFOU@BOBMZTJT

Slide 35

Slide 35 text

ϦϑΝΫλϦϯά TZTTUBUFNFOU@BOBMZTJT ˣ 2VFSZͷ࣮ߦճ਺ͱ͔ వΊͯ͘ΕΔ΍ͭ

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT

Slide 38

Slide 38 text

ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT ˣ lݱࡏzͷϩοΫ૊Έ߹Θͤ 2VFSZҰཡ

Slide 39

Slide 39 text

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)

Slide 40

Slide 40 text

ϦϑΝΫλϦϯά ZPLV͞Μ͕ தࠃ஍ํ%#ษڧJO޿ౡͰ࿩ͯ͠Δ

Slide 41

Slide 41 text

ϦϑΝΫλϦϯά ಈը΋ࢿྉ΋͋ΔͷͰੋඇ IUUQECTUVEZDIVHPLVHJUIVCJP FWFOUTFWFOUIUNM

Slide 42

Slide 42 text

ϦϑΝΫλϦϯά ஫ҙ఺

Slide 43

Slide 43 text

ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT ͸ྺ࢙Λ࣋ͨͳ͍

Slide 44

Slide 44 text

ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT ͸ྺ࢙Λ࣋ͨͳ͍ ਺෼ஔ͖ʹԿ͔ʹग़ྗ͠Α͏

Slide 45

Slide 45 text

ϦϑΝΫλϦϯά 4ʹు͍ͯ"84"UIFOBͰ෼ੳ

Slide 46

Slide 46 text

ϦϑΝΫλϦϯά ࠷ऴखஈ

Slide 47

Slide 47 text

ϦϑΝΫλϦϯά 4)08&/(*/&*//0%#45"564

Slide 48

Slide 48 text

ϦϑΝΫλϦϯά

Slide 49

Slide 49 text

ϦϑΝΫλϦϯά ਓྨ͕ಡΉʹ͸ͭΒ͍

Slide 50

Slide 50 text

ϦϑΝΫλϦϯά !NZpOEFS͞Μ͕ॻ͍ͯΔ IUUQTXXXTMJEFTIBSFOFUNZpOEFS TIPXJOOPECTUBUVT

Slide 51

Slide 51 text

ϦϑΝΫλϦϯά ࣍ճͷ.Z42-$BTVBM·Ͱʹ ಡΊΔΑ͏ͳ࢓૊Έ࡞Γ͍ͨʢ༧ఆʣ

Slide 52

Slide 52 text

ϦϑΝΫλϦϯά 1PTUHSF42-͸ʁ

Slide 53

Slide 53 text

ϦϑΝΫλϦϯά QH@TUBU@TUBUFNFOUT

Slide 54

Slide 54 text

ϦϑΝΫλϦϯά QH@TUBUTJOGP

Slide 55

Slide 55 text

ϦϑΝΫλϦϯά !LLJEB͞Μ͕ தࠃ஍ํ%#ษڧձJOԬࢁʢ͈́

Slide 56

Slide 56 text

ϦϑΝΫλϦϯά ࢀߟࢿྉ IUUQECTUVEZDIVHPLVHJUIVCJP FWFOUTFWFOUIUNM

Slide 57

Slide 57 text

ϦϑΝΫλϦϯά QH@TUBUT@SFQPSUFS

Slide 58

Slide 58 text

ϦϑΝΫλϦϯά !NBTVEBL[͞Μ͕ 1PTUHSF42-ษڧձͰʢ͈́

Slide 59

Slide 59 text

ϦϑΝΫλϦϯά ࢀߟࢿྉ IUUQRJJUBDPNNBTVEBL[ JUFNTCCBDBEE

Slide 60

Slide 60 text

ϦϑΝΫλϦϯά ͜ͷ΁ΜΛ࢖ͬͯ ໰୊ͷ2VFSZ܈Λඥղ͘

Slide 61

Slide 61 text

ϩοΫ଴ͪ ࣌ؒܦաͱڞʹ࢓ࠐ·ΕΔര஄ ݟ͑ͯ͘Δ

Slide 62

Slide 62 text

ϦϑΝΫλϦϯά ៉ྷͳϦϑΝΫλϦϯά w ࢀরͱߋ৽ͷಡΈࠐΈઌΛ෼͚Δ w ߋ৽ͷϘτϧωοΫΛ෼͚Δ w σʔλઃܭΛݟ௚͢ w ద੾ʹ*/%&9Λ׆༻͢Δ

Slide 63

Slide 63 text

ϦϑΝΫλϦϯά ҋͷਂ͍ϦϑΝΫλϦϯά w +0*/Λඇਖ਼نԽͰิ͏ w ֎෦Ωʔ੍໿Λ֎͢ w ͱΓ͋͑ͣΩϟογϡʹಥͬࠐΉ w ۃ౓ͷεέʔϧΞοϓ

Slide 64

Slide 64 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹϩοΫ଴ͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ

Slide 65

Slide 65 text

·ͱΊ ϩοΫ͸େ੾ͳ࢓૊ΈͷҰͭ

Slide 66

Slide 66 text

·ͱΊ 3%#ͷ੹຿ w σʔλΛద੾ʹकΔ w σʔλΛద੾ʹऔΓग़͢ w σʔλΛద੾ʹอଘ͢Δ

Slide 67

Slide 67 text

·ͱΊ ͔͠͠ϘτϧωοΫʹͳΓ΍͍͢

Slide 68

Slide 68 text

·ͱΊ Ͱ͸Ͳ͏΍ͬͯ෇͖߹͍͔ͬͯ͘

Slide 69

Slide 69 text

·ͱΊ wυΩϡϝϯτΛݟΔ wఆৗతʹ؂ࢹ͢Δ wద੾ͳվળΛଓ͚Δ

Slide 70

Slide 70 text

·ͱΊ ಛʹϩοΫ͸ઃܭ͕େࣄ

Slide 71

Slide 71 text

·ͱΊ %#ͷ໰୊͸๨Εͨࠒʹ΍ͬͯ͘Δ

Slide 72

Slide 72 text

·ͱΊ

Slide 73

Slide 73 text

ࢀߟࢿྉ ɾQPTUHSFTRMKQ4MBDL νϟοτϧʔϜ IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN ɾNZTRMDBTVBM4MBDL νϟοτϧʔϜ IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN

Slide 74

Slide 74 text

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ