When it all Goes Wrong | Nordic PGDay 2019 | Will Leinweber

You're woken up in the middle of the night to your phone. Your app is down and you're on call to fix it. Eventually you track it down to "something with the db," but what exactly is wrong? And of course, you're sure that nothing changed recently…

Knowing what to fix, and even where to start looking, is a skill that takes a long time to develop. Especially since Postgres normally works very well for months at a time, not letting you get practice!

In this talk, I'll share not only the more common failure cases and how to fix them, but also a general approach to efficiently figuring out what's wrong in the first place.

Transcript

1. When it all Goes Wrong
   Nordic PGDay 2019 — March 19 — Copenhagen
   

   View Slide

2. @leinweber
   Will Leinweber
   @leinweber
   
   Citus Data (Microsoft)
   
   bitfission.com

   (warning autoplays midi)
   

   View Slide

3. @leinweber
   coming from
   citus cloud
   
   heroku postgres
   
   

   View Slide

4. @leinweber
   special thanks
   citus cloud

   — dan farina (@danfarina)
   
   heroku postgres

   — maciek sakrejda (@uhoh_itsmaciek)
   
   

   View Slide

5. @leinweber
   same sorts of problems
   from pages & alerts
   
   from support tickets
   

   View Slide

6. @leinweber
   this talk
   more app dev who uses postgres

   rather than dba
   

   View Slide

7. @leinweber
   the problem with Postgres
   it’s pretty good
   
   you don’t get experience with how it breaks
   

   View Slide

8. @leinweber
   what to do for a problem
   

   View Slide

9. @leinweber
   what to do for a problem
   

   View Slide

10. @leinweber
    complicated system
    network
    
    hardware
    
    o/s
    
    postgres
    

    View Slide

11. @leinweber
    using the database (too much)
    95% application
    
    4% auto vacuum
    
    1% everything else
    

    View Slide

12. @leinweber
    hard to convince
    all the graphs saying DB is slow
    
    and nothing has changed
    
    …must be the database!
    

    View Slide

13. @leinweber
    https://upload.wikimedia.org/wikipedia/commons/9/98/Survivorship-bias.png
    

    View Slide

14. @leinweber
    “but I didn’t change anything”
    no deploys!
    
    no database migrations!
    
    no scaling!
    

    View Slide

15. @leinweber
    “but I didn’t change anything”
    https://upload.wikimedia.org/wikipedia/commons/0/09/Redherring.gif
    

    View Slide

16. @leinweber
    “but I didn’t change anything”
    more traffic?
    
    change in access patterns?
    
    one big user logged in?
    

    View Slide

17. @leinweber
    run out of a resource
    

    View Slide

18. @leinweber
    snowball
    

    View Slide

19. @leinweber
    example
    manageable user 1s query => 2x expensive
    
    frequent, small queries 3ms => 12ms
    

    View Slide

20. @leinweber
    assumptions
    hardware
    maintenance
    app
    

    View Slide

21. @leinweber
    assumptions
    postgres should not crash
    
    …with overcommit off and no containers
    
    large extensions increase chance
    
    

    View Slide

22. @leinweber
    if not postgres, then what
    

    View Slide

23. @leinweber
    system resources
    cpu
    
    memory
    
    disk
    
    parallelism / backends
    
    locks
    

    View Slide

24. @leinweber
    cpu mem disk parallelism
    cpu mem disk parallelism
    

    View Slide

25. @leinweber
    cpu mem disk parallelism
    credentials wrong
    
    networking broken
    
    locking issue, check pg_locks
    
    idle in transaction
    

    View Slide

26. @leinweber
    cpu mem disk parallelism
    application submitting backlogged workload
    
    connection leak
    
    pool sizes set too large
    
    pg_lock issue + application backlog
    

    View Slide

27. @leinweber
    cpu mem disk parallelism
    workload skew causing thrashing
    
    unusual sequential scan workload
    
    failover or restart => no cache
    
    pg_prewarm
    

    View Slide

28. @leinweber
    cpu mem disk parallelism
    same as just disk,
    
    but also the application is piling on
    

    View Slide

29. @leinweber
    cpu mem disk parallelism
    large GROUP BYs
    
    high disk latency due to unusual page
    dispersion pattern in the workload
    

    View Slide

30. @leinweber
    cpu mem disk parallelism
    workload has high mem (GROUP BY)

    + app adding backlog
    
    lock contention slowing mem release
    

    View Slide

31. @leinweber
    cpu mem disk parallelism
    large GROUP BYs + paging in unusual data
    

    View Slide

32. @leinweber
    cpu mem disk parallelism
    Look for what is causing disk access
    

    View Slide

33. @leinweber
    cpu mem disk parallelism
    small, in-memory workload
    
    lots of seq scans on small table
    
    index scan w/ filter dropping lots
    

    View Slide

34. @leinweber
    cpu mem disk parallelism
    app backlog 

    + too much processing on small data
    
    simply a lot of work
    

    View Slide

35. @leinweber
    cpu mem disk parallelism
    large seq scans
    

    View Slide

36. @leinweber
    cpu mem disk parallelism
    loading cold data + application backlog
    

    View Slide

37. @leinweber
    cpu mem disk parallelism
    small # of backends doing a lot more work
    

    View Slide

38. @leinweber
    cpu mem disk parallelism
    entity, workload, entity*workload
    
    soft deletes and non-conditional indexes
    

    View Slide

39. @leinweber
    cpu mem disk parallelism
    reporting query
    

    View Slide

40. @leinweber
    cpu mem disk parallelism
    app backlog, but with CPU/mem problems
    

    View Slide

41. @leinweber
    tools of the trade
    

    View Slide

42. @leinweber
    tools of the trade
    C symbols
    

    View Slide

43. @leinweber
    tools of the trade: perf
    perf record -p && perf report
    

    View Slide

44. @leinweber
    tools of the trade: perf
    perf top
    

    View Slide

45. @leinweber
    tools of the trade: perf
    www.brendangregg.com/perf.html
    
    

    View Slide

46. @leinweber
    tools of the trade: gdb
    gdb -batch -ex 'bt' -p
    

    View Slide

47. @leinweber
    

    View Slide

48. @leinweber
    

    View Slide

49. @leinweber
    tools of the trade: iostat
    iostat -xm 10
    

    View Slide

50. @leinweber
    tools of the trade: iotop
    

    View Slide

51. @leinweber
    tools of the trade: htop
    

    View Slide

52. @leinweber
    Tools of the trade: bwm-ng
    

    View Slide

53. @leinweber
    tools of the trade: backends
    pgrep -lf postgres + grep + wc
    select * from pg_stat_activity
    

    View Slide

54. @leinweber
    tools of the trade: pg_s_s
    select * from pg_stat_statements
    

    View Slide

55. @leinweber
    tools of the trade: summary
    cpu mem disk parallelism network
    perf x
    gdb x
    iostat x
    iotop x
    htop x x
    bwm x
    pgrep x
    

    View Slide

56. @leinweber
    what to do
    

    View Slide

57. @leinweber
    what to do
    configuration change
    

    View Slide

58. @leinweber
    what to do
    db change
    

    View Slide

59. @leinweber
    what to do
    code change
    

    View Slide

60. @leinweber
    flirting with disaster
    Velocity NY 2013: Richard Cook

    "Resilience In Complex Adaptive Systems”
    
    Jens Rasmussen:

    Risk management in a dynamic society: a
    modeling problem
    

    View Slide

61. @leinweber
    flirting with disaster
    economic
    boundary
    

    View Slide

62. @leinweber
    flirting with disaster
    economic
    boundary
    workload
    boundary
    

    View Slide

63. @leinweber
    flirting with disaster
    economic
    boundary
    workload
    boundary
    performance
    boundary
    

    View Slide

64. @leinweber
    flirting with disaster
    economic
    boundary
    workload
    boundary
    performance
    boundary
    error
    margin
    

    View Slide

65. @leinweber
    flirting with disaster
    economic
    boundary
    workload
    boundary
    performance
    boundary
    

    View Slide

66. @leinweber
    flirting with disaster
    economic
    boundary
    workload
    boundary
    performance
    boundary
    error
    margin
    

    View Slide

67. @leinweber
    flirting with disaster
    economic
    boundary
    workload
    boundary
    performance
    boundary
    error
    margin
    

    View Slide

68. @leinweber
    flirting with disaster
    Velocity NY 2013: Richard Cook

    "Resilience In Complex Adaptive Systems”
    
    Jens Rasmussen: 

    Risk management in a dynamic society: a
    modeling problem
    

    View Slide

69. @leinweber
    thank you
    Will Leinweber
    @leinweber
    citusdata.com
    

    View Slide