Visualizing Postgres

PGCon ottawa 2013-05-24

Transcript

1. VISUALIZING POSTGRES 2013-05-24 pgcon ottawa canada 1 2013-05-24 pgcon ottawa canada
2. will @leinweber 2
4. about the talk 4
5. why visualize 5
6. Anscombe's quartet 6
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
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
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
27. Edward Tufte 27
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
36. an aside 36 wIJTUPHSBN
37. horizon WJTCFSLFMFZFEVQBQFSTIPSJ[PO 37
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
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
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