Visualizing Postgres

7e042c238dffcc90a7535ccb682e47be?s=47 will
May 24, 2013

Visualizing Postgres

PGCon ottawa 2013-05-24

7e042c238dffcc90a7535ccb682e47be?s=128

will

May 24, 2013
Tweet

Transcript

  1. 3.

    3

  2. 7.

    7

  3. 8.

    8

  4. 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
  5. 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
  6. 15.

    15 title, frame, labeled axes, labels in the graph, caption,

    color - in 1786 the line graph and bar chart
  7. 16.

    16

  8. 17.

    17

  9. 20.

    20 - 1854 Broad Street cholera outbreak (soho london) -

    germ theory was not yet widely accepted
  10. 21.

    21

  11. 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
  12. 25.

    25

  13. 26.

    26

  14. 28.

    28

  15. 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
  16. 34.
  17. 35.

    35

  18. 38.

    38

  19. 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
  20. 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
  21. 45.

    45

  22. 47.

    47

  23. 48.
  24. 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
  25. 52.

    too much 52 you get X position Y position and

    a few colors aspect ratio should make lines 45º if possible
  26. 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
  27. 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
  28. 62.

    schema CREATE TABLE stats ( id serial primary key, created_at

    timestamptz default (now()), data json); CREATE INDEX ON stats (created_at); 62
  29. 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