Visualizing Postgres

PGCon ottawa 2013-05-24

Transcript

1. VISUALIZING
   POSTGRES
   2013-05-24 pgcon ottawa canada
   1
   2013-05-24 pgcon ottawa canada
   

   View Slide

2. will @leinweber
   2
   

   View Slide

3. View Slide

4. about the
   talk
   4
   

   View Slide

5. why
   visualize
   5
   

   View Slide

6. Anscombe's quartet
   6
   

   View Slide

7. View Slide

8. View Slide

9. crisis
   9
   respond to page in the middle of the night
   whatever alert sent page isn't enough to solve the problem, or
   you would have automated it
   if you can see at a glance it's faster than grepping through logs
   => reduce downtime
   

   View Slide

10. anomalies
    10
    notice things you haven't seen before
    things you haven't planned for
    prompt investigation into odd behavior
    see the interactions between lots of things
    

    View Slide

11. capacity
    planning
    11
    should I upgrade?
    are there daily/weekly cycles?
    => increases robustness
    

    View Slide

12. remove guessing
    and gut feelings
    12
    

    View Slide

13. history
    13
    "let's start with some history
    "inspiration, lessons, and they're just really
    neat
    

    View Slide

14. playfiar
    william
    14
    w 1759-1823
    

    View Slide

15. 15
    title, frame, labeled axes, labels in the graph, caption,
    color
    - in 1786 the line graph and bar chart
    

    View Slide

16. View Slide

17. View Slide

18. 18
    - in 1801 the pie chart and circle graph
    

    View Slide

19. John Snow
    19
    

    View Slide

20. 20
    - 1854 Broad Street cholera outbreak (soho
    london)
    - germ theory was not yet widely accepted
    

    View Slide

21. View Slide

22. jenny downing
    http://www.flickr.com/photos/jenny-pics/3761648387/
    22
    There was one significant anomaly - none of the monks in the
    adjacent monastery contracted cholera. Investigation showed
    that this was not an anomaly, but further evidence, for they
    drank only beer, which they brewed themselves
    

    View Slide

23. Charles
    Joseph
    Minard
    23
    w 1781-187
    0
    

    View Slide

24. 24
    Napoleon graph of 1812 (made in 1869)
    needs explanation, but has wonderful density
    

    View Slide

25. View Slide

26. View Slide

27. Edward
    Tufte
    27
    

    View Slide

28. View Slide

29. data-ink ratio
    data-ink ratio
    data-ink ratio
    data-ink ratio
    data-ink ratio
    data-ink ratio
    data-ink ratio
    data-ink ratio
    data-ink ratio
    29
    

    View Slide

30. honesty
    30
    important not to mislead, even unintentionally (time zone)
    

    View Slide

31. information
    consists of
    differences
    that make a
    difference
    31
    

    View Slide

32. sparklines
    http://tinyurl.com/sparkline
    32
    small multiples
    intense data
    

    View Slide

33. charts
    33
    get a little more practical
    

    View Slide

34. line
    34
    

    View Slide

35. View Slide

36. an aside
    36
    wIJTUPHSBN
    

    View Slide

37. horizon
    WJTCFSLFMFZFEVQBQFSTIPSJ[PO
    37
    

    View Slide

38. View Slide

39. http://square.github.io/cubism/
    position—small
    color—large
    39
    compress vertically without loosing resolution
    

    View Slide

40. 40
    small multiples
    

    View Slide

41. postgres
    41
    

    View Slide

42. pg_stat_statements
    42
    low level
    8.4 Takahiro Itagaki
    

    View Slide

43. => select * from pg_stat_statements limit 1;
    userid | 16384
    dbid | 16385
    query | SELECT "clip_versions".* FROM "clip_versions"
    WHERE "clip_versions"."clip_id" = ? ORDER BY created_at desc
    calls | 103
    total_time | 37.603
    rows | 632
    shared_blks_hit | 504
    shared_blks_read | 210
    shared_blks_dirtied | 0
    shared_blks_written | 0
    43
    

    View Slide

44. select
    ((total_time::float/calls)::int
    / 50) * 50 as ms,
    lpad('', count(*)::int, '')
    || ' '
    || count(*) as freq
    from pg_stat_statements
    group by 1 order by 1; \watch 1
    44
    

    View Slide

45. View Slide

46. stat views
    pg_stat_activity
    pg_stat_database
    pg_stat_user_tables
    pg_stat_user_indexes
    pg_statio_user_tables
    pg_statio_user_indexes
    46
    transactions, rollbacks, bloat, cache hit
    

    View Slide

47. View Slide

48. pg-extras
    https://github.com/heroku/heroku-pg-extras
    cache hit ratio
    index usage
    running queries
    blocked queries
    locks
    index size
    unused indexes
    sequential scans
    bloat
    48
    

    View Slide

49. designing a
    system
    49
    

    View Slide

50. not easy
    50
    there aren't concrete rules to human perception
    

    View Slide

51. data is not
    information
    51
    just having a metric in graphite or libretto isn't enough
    -- other people won't have the same context
    -- you probably need to do conversions such as derivatives
    

    View Slide

52. too much
    52
    you get X position Y position and a few colors
    aspect ratio should make lines 45º if possible
    

    View Slide

53. 53
    Cant line up the same time period
    Minimize Context switching
    

    View Slide

54. color
    http://tinyurl.com/colorrules
    54
    Gray or muted background
    soft, nature colors for most, bright sparingly
    for similar categories: single hue, vary saturation
    different categories: vary hue, but keep saturation
    

    View Slide

55. intents and needs
    NF
    ECB
    DPOUSBDUPS
    55
    

    View Slide

56. generation
    56
    Peter Eisentraut => logging yesterday
    

    View Slide

57. log stream
    http://www.12factor.net/logs
    http://boundary.com/blog/2013/05/14/approximate-heavy-
    hitters-the-spacesaving-algorithm/
    http://www.slideshare.net/RedRooz/harmonic-mean-for-
    monitored-rate-data
    57
    

    View Slide

58. storage
    http://www.flickr.com/photos/lightmash/3183278318/
    58
    storing time series data is a talk in and of
    itself
    =>Ronald’s talk yesterday
    no way i can do it justice as part of this talk
    rollup, table rotation
    

    View Slide

59. datascope
    github.com/will/datascope
    59
    

    View Slide

60. overview
    60
    pg_stat_statements
    

    View Slide

61. overview
    XPSLFSMPPQPCTFSWF SFTFU
    TMFFQ
    TUPSFKTPOQH@TUBU@TUBUFNFOUT
    PUIFST
    DVTUPNDVCJTNEBUBTPVSDF
    61
    

    View Slide

62. schema
    CREATE TABLE stats (
    id serial primary key,
    created_at timestamptz default (now()),
    data json);
    CREATE INDEX ON stats (created_at);
    62
    

    View Slide

63. worker
    def capture_stats!
    s = stats
    DB[:stats] << {data: {
    connections: connections,
    stat_statements: stat_statements,
    cache_hit: cache_hit,
    locks: locks
    }.to_json }
    end
    63
    

    View Slide

64. worker
    def stat_statements
    TARGET_DB[:pg_stat_statements]
    .select(:query, :calls, :total_time)
    .exclude(query: '')
    .all
    end
    64
    

    View Slide

65. thanks
    @leinweber
    65
    

    View Slide