Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Webサービスが成長するとロックで苦労する話
Search
soudai sone
February 22, 2017
Technology
6
3.8k
Webサービスが成長するとロックで苦労する話
MySQL Casual Talk #10と第19回 中国地方DB勉強会 in 米子の資料です
soudai sone
February 22, 2017
Tweet
Share
More Decks by soudai sone
See All by soudai sone
顧客が本当に必要だったもの - パフォーマンス改善編 / Make what is needed
soudai
24
6.8k
仕事を前に進めるためのコツ - 判断と決断と共有 / Aim for the goal
soudai
78
31k
アプリケーションが 正しく動作するということ - 自動テスト編 / Automated Testing
soudai
15
3k
Gitlab本から学んだこと - そーだいなるプレイバック / gitlab-book
soudai
8
1.7k
**強い**エンジニアのなり方 - フィードバックサイクルを勝ち取る / grow one day each day
soudai
116
110k
マルチテナントの実現におけるDB設計とRLS / Utilizing RSL in multi-tenancy
soudai
26
8.1k
キャッシュと向き合う、キャッシュと共に生きる / cache pattern
soudai
36
15k
RDBアンチパターンと戦う - 削除フラグ 完全攻略ガイド / delete flag
soudai
30
18k
コミュニティと共に生きる - キャリアの螺旋 / live with community
soudai
8
5.8k
Other Decks in Technology
See All in Technology
Commitment vs Harrisonism - Keynote for Scrum Niseko 2024
miholovesq
6
1.1k
わたしとトラックポイント / TrackPoint tips
masahirokawahara
1
240
フルカイテン株式会社 採用資料
fullkaiten
0
36k
【若手エンジニア応援LT会】AWSで繋がり、共に成長! ~コミュニティ活動と新人教育への挑戦~
kazushi_ohata
0
180
ガチ勢によるPipeCD運用大全〜滑らかなCI/CDを添えて〜 / ai-pipecd-encyclopedia
cyberagentdevelopers
PRO
3
200
来年もre:Invent2024 に行きたいあなたへ - “集中”と“つながり”で楽しむ -
ny7760
0
470
AWS CDKでデータリストアの運用、どのように設計する?~Aurora・EFSの実践事例を紹介~/aws-cdk-data-restore-aurora-efs
mhrtech
4
650
日経電子版におけるリアルタイムレコメンドシステム開発の事例紹介/nikkei-realtime-recommender-system
yng87
1
500
10分でわかるfreee エンジニア向け会社説明資料
freee
18
520k
Aurora_BlueGreenDeploymentsやってみた
tsukasa_ishimaru
1
120
2024-10-30-reInventStandby_StudyGroup_Intro
shinichirokawano
1
630
Amazon FSx for NetApp ONTAPを利用するにあたっての要件整理と設計のポイント
non97
1
160
Featured
See All Featured
KATA
mclloyd
29
13k
Mobile First: as difficult as doing things right
swwweet
222
8.9k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
246
1.3M
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
4
290
How STYLIGHT went responsive
nonsquared
95
5.2k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
31
2.7k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
46
2.1k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
159
15k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
228
52k
Designing Experiences People Love
moore
138
23k
What’s in a name? Adding method to the madness
productmarketing
PRO
22
3.1k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
26
1.4k
Transcript
WebαʔϏε͕͢Δͱ ϩοΫͰۤ࿑͢Δ ୈճதࠃํ%#ษڧձJOถࢠ
What is it? ϩοΫઈରʹඞཁͰ͋Δ
What is it? ͔͠͠αʔϏε͕͢Δͱ ϘτϧωοΫͷݪҼʹͳΓ͍͢
What is it? ͦΜͳWebαʔϏε͋Δ͋Δ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϩοΫͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϩοΫͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
Mackerel
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿηʔϧεΤϯδχΞ ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹதࠃࢧ෦ ࢧ෦ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϩοΫͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
ϩοΫͪ ͬ͘͟Γͱͨ͠આ໌ ɹɹ!LLJEB͞Μͷࢿྉ͔ΒҾ༻
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
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
L 8 n QS GJPS L U 1 0 '
1 0 1 0 * - ; 1 ; A = - ; S S - ; - ; - ; S S S
ϩοΫͪ ΈΜͳҰൠతͳΤϯδχΞ͔ͩΒ ϩοΫͷࣄͬͯΔͣ ˣ ϩοΫͷઆ໌ׂѪ 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
ϩοΫͪ ओͳϩοΫͷछྨ w ഉଞ F9DMVEFE ϩοΫ w ڞ༗ 4IBSFE ϩοΫ
ϩοΫͪ ओͳϩοΫͷछྨ w ഉଞ F9DMVEFE ϩοΫ w ڞ༗ 4IBSFE ϩοΫ
ϩοΫରʹରͯ͠ɺଞͷΞΫηεΛҰېࢭ͢Δ ଞͷΞΫηεߋ৽ɾআɾࢀরશͯग़དྷͳ͍ ॻ͖ࠐΈϩοΫͱݺΕΔࣄ͋Δ
ϩοΫͪ ओͳϩοΫͷछྨ w ഉଞ F9DMVEFE ϩοΫ w ڞ༗ 4IBSFE ϩοΫ
ϩοΫରʹରͯ͠ɺࢀরҎ֎ͷॲཧΛېࢭ͢Δ ଞͷΞΫηεࢀর 4&-&$5 Λ͢Δ͜ͱ͕ग़དྷΔ ಡΈࠐΈϩοΫͱݺΕΔࣄ͋Δ
ϩοΫͪ ϩοΫ҉తʹऔ͍ͬͯΔ
ϩοΫͪ .Z42-Ͱओʹڞ༗ϩοΫΛऔΔ w +0*/ͱ͔αϒΫΤϦͱ͔ w ߋ৽ܥͱ͔ w Ұ෦ͷ"-5&3ͱ͔
ϩοΫͪ 1PTUHSF42-ओʹڞ༗ϩοΫΛऔΔ w 4&-&$5ͰϩοΫͱΔ w "-5&3จͱ͔%#ຖͰ݁ߏҧ͏ w ϨϓϦέʔγϣϯͷεϨʔϒଆҙ
ϩοΫͪ ͍ͭ͜Βઃܭ࣌ʹҙࣝ͢ΔϩοΫ
ϩοΫͪ ༷มߋʹΑΔ2VFSZͷมߋ
ϩοΫͪ ༷มߋʹΑΔ2VFSZͷมߋ ˣ */%&9షΒͣʹ+0*/ */%&9ͷޮ͔ͳ͍8)&3&۟
ϩοΫͪ ༷มߋʹΑΔߏͷมߋ
ϩοΫͪ ༷มߋʹΑΔߏͷมߋ ˣ আϑϥάΈ͍ͨͳฅΧϥϜ
ϩοΫͪ ࣌ؒܦաͱڞʹࠐ·ΕΔര
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϩοΫͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
ϦϑΝΫλϦϯά QFSGPSNBODF@TDIFNB
ϦϑΝΫλϦϯά QFSGPSNBODF@TDIFNB ˣ ͱΓ͋͑ͣ໎ͬͨΒ༗ޮԽ͠ͱ͚
ϦϑΝΫλϦϯά ੲσϑΥϧτͰ ༗ޮԽ͡Όͳ͔ͬͨ
ϦϑΝΫλϦϯά ੲσϑΥϧτͰ ༗ޮԽ͡Όͳ͔ͬͨ ࠷ۙ༗ޮʹͳͬͯΔ
ϦϑΝΫλϦϯά TZTTUBUFNFOU@BOBMZTJT
ϦϑΝΫλϦϯά TZTTUBUFNFOU@BOBMZTJT ˣ 2VFSZͷ࣮ߦճͱ͔ వΊͯ͘ΕΔͭ
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
ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT
ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT ˣ lݱࡏzͷϩοΫΈ߹Θͤ 2VFSZҰཡ
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)
ϦϑΝΫλϦϯά ZPLV͞Μ͕ தࠃํ%#ษڧJOౡͰͯ͠Δ
ϦϑΝΫλϦϯά ಈըࢿྉ͋ΔͷͰੋඇ IUUQECTUVEZDIVHPLVHJUIVCJP FWFOUTFWFOUIUNM
ϦϑΝΫλϦϯά ҙ
ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT ྺ࢙Λ࣋ͨͳ͍
ϦϑΝΫλϦϯά TZTJOOPEC@MPDL@XBJUT ྺ࢙Λ࣋ͨͳ͍ ஔ͖ʹԿ͔ʹग़ྗ͠Α͏
ϦϑΝΫλϦϯά 4ʹు͍ͯ"84"UIFOBͰੳ
ϦϑΝΫλϦϯά ࠷ऴखஈ
ϦϑΝΫλϦϯά 4)08&/(*/&*//0%#45"564
ϦϑΝΫλϦϯά
ϦϑΝΫλϦϯά ਓྨ͕ಡΉʹͭΒ͍
ϦϑΝΫλϦϯά !NZpOEFS͞Μ͕ॻ͍ͯΔ IUUQTXXXTMJEFTIBSFOFUNZpOEFS TIPXJOOPECTUBUVT
ϦϑΝΫλϦϯά ࣍ճͷ.Z42-$BTVBM·Ͱʹ ಡΊΔΑ͏ͳΈ࡞Γ͍ͨʢ༧ఆʣ
ϦϑΝΫλϦϯά 1PTUHSF42-ʁ
ϦϑΝΫλϦϯά QH@TUBU@TUBUFNFOUT
ϦϑΝΫλϦϯά QH@TUBUTJOGP
ϦϑΝΫλϦϯά !LLJEB͞Μ͕ தࠃํ%#ษڧձJOԬࢁʢ͈́
ϦϑΝΫλϦϯά ࢀߟࢿྉ IUUQECTUVEZDIVHPLVHJUIVCJP FWFOUTFWFOUIUNM
ϦϑΝΫλϦϯά QH@TUBUT@SFQPSUFS
ϦϑΝΫλϦϯά !NBTVEBL[͞Μ͕ 1PTUHSF42-ษڧձͰʢ͈́
ϦϑΝΫλϦϯά ࢀߟࢿྉ IUUQRJJUBDPNNBTVEBL[ JUFNTCCBDBEE
ϦϑΝΫλϦϯά ͜ͷΜΛͬͯ ͷ2VFSZ܈Λඥղ͘
ϩοΫͪ ࣌ؒܦաͱڞʹࠐ·ΕΔര ݟ͑ͯ͘Δ
ϦϑΝΫλϦϯά ៉ྷͳϦϑΝΫλϦϯά w ࢀরͱߋ৽ͷಡΈࠐΈઌΛ͚Δ w ߋ৽ͷϘτϧωοΫΛ͚Δ w σʔλઃܭΛݟ͢ w దʹ*/%&9Λ׆༻͢Δ
ϦϑΝΫλϦϯά ҋͷਂ͍ϦϑΝΫλϦϯά w +0*/Λඇਖ਼نԽͰิ͏ w ֎෦Ωʔ੍Λ֎͢ w ͱΓ͋͑ͣΩϟογϡʹಥͬࠐΉ w ۃͷεέʔϧΞοϓ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹϩοΫͪ ̏ɹϦϑΝΫλϦϯά ̐ɹ·ͱΊ
·ͱΊ ϩοΫେͳΈͷҰͭ
·ͱΊ 3%#ͷ w σʔλΛదʹकΔ w σʔλΛదʹऔΓग़͢ w σʔλΛదʹอଘ͢Δ
·ͱΊ ͔͠͠ϘτϧωοΫʹͳΓ͍͢
·ͱΊ ͰͲ͏͖ͬͯ߹͍͔ͬͯ͘
·ͱΊ wυΩϡϝϯτΛݟΔ wఆৗతʹࢹ͢Δ wదͳվળΛଓ͚Δ
·ͱΊ ಛʹϩοΫઃܭ͕େࣄ
·ͱΊ %#ͷΕͨࠒʹͬͯ͘Δ
·ͱΊ
ࢀߟࢿྉ ɾQPTUHSFTRMKQ4MBDL νϟοτϧʔϜ IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN ɾNZTRMDBTVBM4MBDL νϟοτϧʔϜ IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN
͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ