Pro Yearly is on sale from $80 to $50! »

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. VISUALIZING POSTGRES 2013-05-24 pgcon ottawa canada 1 2013-05-24 pgcon ottawa

    canada
  2. will @leinweber 2

  3. 3

  4. about the talk 4

  5. why visualize 5

  6. Anscombe's quartet 6

  7. 7

  8. 8

  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
  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
  11. capacity planning 11 should I upgrade? are there daily/weekly cycles?

    => increases robustness
  12. remove guessing and gut feelings 12

  13. history 13 "let's start with some history "inspiration, lessons, and

    they're just really neat
  14. playfiar william 14 w 1759-1823

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

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

  17. 17

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

  19. John Snow 19

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

    germ theory was not yet widely accepted
  21. 21

  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
  23. Charles Joseph Minard 23 w 1781-187 0

  24. 24 Napoleon graph of 1812 (made in 1869) needs explanation,

    but has wonderful density
  25. 25

  26. 26

  27. Edward Tufte 27

  28. 28

  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
  30. honesty 30 important not to mislead, even unintentionally (time zone)

  31. information consists of differences that make a difference 31

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

  33. charts 33 get a little more practical

  34. line 34

  35. 35

  36. an aside 36 wIJTUPHSBN

  37. horizon WJTCFSLFMFZFEVQBQFSTIPSJ[PO 37

  38. 38

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

  40. 40 small multiples

  41. postgres 41

  42. pg_stat_statements 42 low level 8.4 Takahiro Itagaki

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

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

  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
  49. designing a system 49

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

  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
  52. too much 52 you get X position Y position and

    a few colors aspect ratio should make lines 45º if possible
  53. 53 Cant line up the same time period Minimize Context

    switching
  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
  55. intents and needs NF ECB DPOUSBDUPS 55

  56. generation 56 Peter Eisentraut => logging yesterday

  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

  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
  59. datascope github.com/will/datascope 59

  60. overview 60 pg_stat_statements

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

  62. schema CREATE TABLE stats ( id serial primary key, created_at

    timestamptz default (now()), data json); CREATE INDEX ON stats (created_at); 62
  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
  64. worker def stat_statements TARGET_DB[:pg_stat_statements] .select(:query, :calls, :total_time) .exclude(query: '<insufficient privilege>')

    .all end 64
  65. thanks @leinweber 65