Visualizing Postgres

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 XPSLFS
    MPPQ
    PCTFSWF
    SFTFU
    TMFFQ TUPSF
    KTPO
    QH@TUBU@TUBUFNFOUT
    PUIFST DVTUPN
    DVCJTN
    EBUB
    TPVSDF 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