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.7k
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
**強い**エンジニアのなり方 - フィードバックサイクルを勝ち取る / grow one day each day
soudai
65
19k
マルチテナントの実現におけるDB設計とRLS / Utilizing RSL in multi-tenancy
soudai
26
6.3k
キャッシュと向き合う、キャッシュと共に生きる / cache pattern
soudai
31
11k
RDBアンチパターンと戦う - 削除フラグ 完全攻略ガイド / delete flag
soudai
25
16k
コミュニティと共に生きる - キャリアの螺旋 / live with community
soudai
7
4.4k
今年1年間の”アウトプット”を振り返る / soudai's output - 2023
soudai
12
1.9k
「GitLabに学ぶ 世界最先端のリモート組織のつくりかた」の輪読会のススメ - そーだいなる輪読会キックオフ / soudai-kickoff
soudai
0
280
Webアプリケーションのパフォーマンス・チューニングの勘所 / web tuningperformance
soudai
40
12k
データベーススペシャリストというキャリアと生存戦略 ~10年後も変わらないこと、変わること / career-spiral
soudai
21
7.2k
Other Decks in Technology
See All in Technology
ServiceNow Knowledge 24の歩き方 EYストラテジー・アンド・コンサルティング
manarobot
0
180
FrontDoorとWebAppsを組み合わせた際のリダイレクト処理の注意点
kenichirokimura
1
480
KubeConにproposalを送りたい人へのアドバイス
sat
PRO
3
210
On Your Data を超えていく!
hirotomotaguchi
2
650
ChatworkのSRE部って実は 半分くらいPlatform Engineering部かもしれない
saramune
0
150
反実仮想機械学習とは何か
usaito
PRO
8
3k
オーナーシップを持つ領域を明確にする
konifar
13
3.1k
Hands-on Gemini, the Google DeepMind LLM
meteatamel
1
110
Databricks における 『MLOps』
databricksjapan
2
160
ChatGPT for IT Service Management (IT Pro)
dahatake
7
1.5k
レガシーをぶっ壊せ。AEONで始めるDevRelの話 / Qiita Night 2024-2-22
aeonpeople
3
1.2k
Postman v10リリース後を振り返る / Looking back at Postman v10 after release
yokawasa
1
150
Featured
See All Featured
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
20
1.9k
A Modern Web Designer's Workflow
chriscoyier
689
190k
Building Effective Engineering Teams - LeadDev
addyosmani
28
1.8k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
14
1.5k
Testing 201, or: Great Expectations
jmmastey
28
6.3k
Ruby is Unlike a Banana
tanoku
96
10k
Writing Fast Ruby
sferik
621
60k
The Pragmatic Product Professional
lauravandoore
25
5.8k
The Illustrated Children's Guide to Kubernetes
chrisshort
31
46k
Building Adaptive Systems
keathley
31
1.9k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
2
3.4k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
116
18k
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
͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ