Database Stalls, From the Ordinary to the Obscure Preetam Jinka - PowerPoint PPT Presentation

Database Stalls, From the Ordinary to the Obscure Preetam Jinka (@PreetamJinka) Software Engineer Percona Live 2017

VividCortex’s database monitoring application is the best way to improve your database performance, efficiency, and uptime. Supporting MySQL, PostgreSQL, Redis, MongoDB, and Amazon Aurora, VividCortex uses patented algorithms to reveal key insights, helping users fix performance problems before they impact customers. Say hello and see a demo, Booth #205 . We’re hiring!

This talk isn’t about the math. Come to the O’Reilly booth after the talk to pick up a free copy of our book! 3

What is a stall? 4

Stalls Short periods when work isn’t being done ● We’re detecting stalls as short as 1 second ● We do this with zero configuration and no fixed thresholds ● The secret sauce: we have a model . ○ 5

We’re trying to catch small problems before they turn into bigger ones. 6

Little’s Law L = λ × W ● Concurrency = Throughput × Latency ● Little’s Law provides a model to relate throughput and concurrency ● In MySQL: Concurrency: threads_running ● There’s one thread per query. ○ From SHOW STATUS ○ Throughput: queries completed per second ● 7

MySQL Server Stall Example More queries in progress Fewer being completed 8

MySQL Server Stall Example All of the stalled queries are completing after the fault ends. 9

Where do stalls come from? Running out of credits on EBS volumes ● MySQL query cache ● Lock contention ● A bad network cable! ● Transparent huge pages (THP) ● “If a transparent huge page isn’t available, the application will stall to let memory compaction ○ run to free a page.” 10

But we don’t really care about any of those things. We’re focused on the work your database is doing. 11

Work-centric monitoring 12

Work-centric monitoring in one slide Focus on the work your systems are doing ● Find relationships between metrics (maybe using a model) ● Monitor what you want to optimize ● Focus on heavy hitters ● Automatically detect changes ● 13

How to respond to database stalls 14

Slowness is about spending time on something. Things spend time doing work or waiting . 15

Work Waiting CPU Lock contention ● ● Disk I/O Disk I/O ● ● Various storage engine metrics Memory compaction ● ● Slow queries ● Large scans ○ 16

Walkthrough 17

18

19

20

21

22

Be careful about causality. 23

Thread states 24

Back pressure 25

Back pressure is about systems receiving more work than they can process. 26

27

It’s much better to handle back pressure higher up the stack. 28

System Database APIs Clients 29

Low-level back pressure can cause unfair slowdowns higher up the stack.* *Totally untested hypothesis. :) 30

31

50 ms shift 32

50 ms shift ~1 sec queries stay ~1 sec queries ( 1x ) ~1 ms queries become ~50 ms queries ( 50x ) 33

Ways to deal with back pressure ● Rate limiting / throttling ● Use a queue to contain requests at a higher level ● Somehow prioritize some requests over others 34

Can you eliminate stalls? Probably not all. Most? Perhaps! 35

Questions? Twitter: @PreetamJinka Email: preetam@vividcortex.com Come find me at the O’Reilly booth! 36