$30 off During Our Annual Pro Sale. View Details »

Visualizing Postgres

will
May 24, 2013

Visualizing Postgres

PGCon ottawa 2013-05-24

will

May 24, 2013
Tweet

Other Decks in Programming

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. 3

    View Slide

  4. about the
    talk
    4

    View Slide

  5. why
    visualize
    5

    View Slide

  6. Anscombe's quartet
    6

    View Slide

  7. 7

    View Slide

  8. 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. 16

    View Slide

  17. 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. 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. 25

    View Slide

  26. 26

    View Slide

  27. Edward
    Tufte
    27

    View Slide

  28. 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. 35

    View Slide

  36. an aside
    36
    wIJTUPHSBN

    View Slide

  37. horizon
    WJTCFSLFMFZFEVQBQFSTIPSJ[PO
    37

    View Slide

  38. 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. 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. 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