Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

soudai sone
February 22, 2017

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

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

soudai sone

February 22, 2017
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. 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
  2. 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
  3. L 8 n QS GJPS L U 1 0 '

    1 0 1 0 * - ; 1 ; A = - ;   S   S - ; - ; - ;   S   S   S
  4. ϩοΫ଴ͪ ΈΜͳҰൠతͳΤϯδχΞ͔ͩΒ ϩοΫͷࣄ͸஌ͬͯΔ͸ͣ ˣ ϩοΫͷઆ໌͸ׂѪ 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
  5. ϩοΫ଴ͪ ओͳϩοΫͷछྨ w ഉଞ F9DMVEFE ϩοΫ w ڞ༗ 4IBSFE ϩοΫ

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

    ϩοΫର৅ʹରͯ͠ɺࢀরҎ֎ͷॲཧΛېࢭ͢Δ ଞͷΞΫηε͸ࢀর 4&-&$5 Λ͢Δ͜ͱ͕ग़དྷΔ ಡΈࠐΈϩοΫͱݺ͹ΕΔࣄ΋͋Δ
  7. 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
  8. 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)