Taming InfluxDB

Story about taming InfluxDB from its very beginning in Teachbase

Transcript

1. Taming InfluxDB Makar Ermokhin Teachbase
4. About Teachbase
5. + =
6. !6
7. !7
8. !8
9. !9
10. Timeline 2013 — InfluxDB initial release 2014 — Teachbase started using InfluxDB (2015 — Prometheus public release) Present days — InfluxDB 2.0 alpha !10
11. Challenge
12. Collect users «active time» in the app Read stats grouped by time (day, month) Filter stats by resource (course, quiz, etc.) Generate charts! Challenge !12
13. Solutions PostgreSQL Something else !13
14. PostgreSQL Too much writing Difficult to group by time without gaps !14
15. PostgreSQL: grouping by time SELECT DATE_TRUNC('minute', created_at) AS time, COUNT(DISTINCT(visits.user_id)) AS users_count FROM visits WHERE visits.created_at > '2019-09-25 21:46:35 +0300'"::timestamp AND visits.created_at < '2019-09-25 23:46:35 +0300'"::timestamp GROUP BY 1 !15
16. PostgreSQL: grouping by time !16 Gaps!
17. PostgreSQL: grouping without gaps WITH minutes AS ( SELECT generate_series( DATE_TRUNC('minute', now()) - '6 hours'"::interval, DATE_TRUNC('minute', now()), '1 minute'"::interval ) as minute ) SELECT minutes.minute AS time, COUNT(DISTINCT(visits.user_id)) AS users_count FROM minutes LEFT JOIN users ON DATE_TRUNC('minute', visits.created_at) = minutes.minute GROUP BY 1 !17
18. Time Series Database
19. Key Features Downsampling data Comparison with the previous record Joining time series !19
20. Downsampling data SELECT COUNT(DISTINCT(user_id)) AS users_count FROM visits WHERE time > '2019-09-25T03:00:00Z' AND time < '2019-09-26T03:00:00Z' GROUP BY time(1h) !20
21. Relational DBMS: comparison with the previous record "-- price changing "-- of course if we have window functions SELECT price - lag(price) OVER my_window FROM stock_prices WINDOW my_window AS (ORDER BY created_at ASC) WHERE series_id = 'some' !21
22. TSDB: comparison with the previous record SELECT derivative(price) FROM stock_prices ORDER BY time ASC WHERE series_id = 'some' !22
23. SELECT one_host.cpu_load AS cpu_load_first, other_host.cpu_load AS cpu_load_second FROM one_host INNER JOIN other_host ON ABS((one_host.time - other_host.time)"::float) < 0.01 !23 Relational DBMS: joining by time
24. TSDB: joining series !24 SELECT one_host.cpu_load AS cpu_load_first, other_host.cpu_load AS cpu_load_second FROM one_host INNER JOIN other_host
25. Examples Graphite InfluxDB Prometheus TimescaleDB !25
26. Back to 2014
27. Graphite Released in 2006 No SQL-like support Accessible over sockets !27
28. Graphite: query language &target=integralByInterval( company.sales.perMinute, "1d" )&from=midnight-10days !28
29. InfluxDB SQL-like query language "=> easier to learn HTTP API "=> easier to build clients New and shiny (in 2014) !29
30. InfluxDB
31. InfluxDB Measurement Tags Fields Timestamp !31
32. InfluxDB fields Key-value pairs that stores your data Values can be strings, floats, integers, or Booleans Not indexed !32
33. InfluxDB tags Key-value pairs that used for filtering Keys and values are strings Indexed !33
34. Query example SELECT mean(time_spent) FROM visits WHERE time > '2019-09-23 03:00:00' AND time < '2019-09-24 03:00:00’ AND account_id = 1985 GROUP BY time(1h), user_id, account_id !34
35. Query example SELECT mean(time_spent) FROM visits WHERE time > '2019-09-23 03:00:00' AND time < '2019-09-24 03:00:00’ AND account_id = 1985 GROUP BY time(1h), user_id, account_id Select aggregated stats !35
36. Query example SELECT mean(time_spent) FROM visits WHERE time > '2019-09-23 03:00:00' AND time < '2019-09-24 03:00:00’ AND account_id = 1985 GROUP BY time(1h), user_id, account_id Specify measurement !36
37. Query example SELECT mean(time_spent) FROM visits WHERE time > '2019-09-23 03:00:00' AND time < '2019-09-24 03:00:00’ AND account_id = 1985 GROUP BY time(1h), user_id, account_id Filtering by timestamp and tag !37
38. Query example SELECT mean(time_spent) FROM visits WHERE time > '2019-09-23 03:00:00' AND time < '2019-09-24 03:00:00’ AND account_id = 1985 GROUP BY time(1h), user_id, account_id Group by time interval and tag !38
39. Continuous Queries Continuous queries (CQ) are InfluxQL queries that run automatically and periodically on realtime data and store query results in a specified measurement. !39
40. CQ example CREATE CONTINUOUS QUERY cq_course_visits_by_day ON teachbase BEGIN SELECT sum(time_spent) AS total_spent, count(distinct(user_id)) AS user_count, count(distinct(account_id)) AS account_count INTO course_visits_by_day FROM visits GROUP BY time(1d), course_id END !40
41. User activity tracking
42. Writing activity Open WebSocket connection when user visits page Send context (tags) via socket Commit time into Influx when user left page or once in X seconds !42
43. WebSockets For accurate and reliable tracking WebSockets service implemented on Erlang (because there was no AnyCable) !43
44. Reading activity !44
45. Rails integration !45
46. class VisitMetrics < Influxer"::Metrics set_series :visits tags :course_session_id, :course_id, :account_id, :user_id attributes :time_spent, :field_user_id, :field_account_id validates :user_id, :account_id, :time_spent, presence: true before_write :tags_to_fields scope :by_course, "->(id) { where(course_id: id) } scope :today, "-> { where(time: Time.current.all_day) } private def tags_to_fields self.field_account_id = account_id self.field_user_id = user_id end end !46
47. Query example VisitMetrics .where(time: Date.yesterday.all_day) .time(:hour) .mean(:time_spent, :mean_time_spent) !47
48. !48
49. class CourseSession < ApplicationRecord has_metrics :visits, foreign_key: :course_session_id end course_session = CourseSession.find(100500) course_session.visits # "=> Influxer"::Relation course_session.visits.today # "=> only today !49 Associations
50. Caching course_session = CourseSession.find(100500) course_session.visits.past(:hour) # performing query and caching result for 1 minute # some magic VisitMetrics.where(course_session_id: 100500).past(:hour) # fetch from cache course_session.visits # performing and caching with default expiring time !50
51. Writing VisitMetrics.write( course_session_id: 100, course_id: 87, account_id: 50, user_id: 100500, time_spent: 360 ) !51
52. Influxer == ActiveRecord for InfluxDB
53. Not only charts !53
54. Aggregated visits Frequently requested Used in billing "=> There is a reason to dump them into PostgreSQL !54
55. Dumping to PostgreSQL Once per day Just fetch data from Influx and write it into Postgres !55
56. Summary Using influxer Dumping frequently requested data into PostgreSQL !56
57. Early Adopter Problems
58. InfluxDB out of memory CQ causes Influx to consume all memory Influx unable to restart Days of analytics lost !58
59. InfluxDB out of memory !59 InfluxDB crashed
60. Solutions Create dumps Increase memory Upgrade to InfluxDB > 1.0 (unavailable until 2016) !60
61. Present days
62. InfluxDB for monitoring !62
63. TICK stack Telegraf — collects metrics InfluxDB — stores metrics Chronograf — shows metrics Kapacitor — processes metrics (sends alerts) !63
64. TICK stack Easy to setup Flexible metrics & views Many plugins for Telegraf !64
65. Telegraf plugins !65
66. TICK stack Easy to setup Flexible metrics & views Many plugins for Telegraf There is no Ruby plugins !66
67. Future plans
68. InfluxDB protocols For now, InfluxDB supports following protocols: CollectD Graphite OpenTSDB Prometheus UDP !68
69. Yabeda !69 gem 'yabeda-prometheus'
70. In theory, Yabeda + TICK = Awesome metrics
71. Summary Use InfluxDB for user data Use TICK for monitoring Be a happy developer !71
72. What if 2014 was now
73. Available solutions !73 InfluxDB Prometheus TimescaleDB Clickhouse (not a TSDB, but very new and shiny)
74. Criteria Learning curve Integration complexity Infrastructure overhead Popularity !74
75. Prometheus: learning curve Web UI out of box ✅ No CLI by default ❌ Custom, non-SQL-like, query language ❌ !75
76. Prometheus: integration complexity HTTP API ✅ Official Ruby client ✅ No obvious way to insert data ⁉ No SQL-like querying support ❌ !76
77. Prometheus: infrastructure overhead Need additional tool to write data ❌ No cloud version ❌ !77
78. Prometheus: popularity Db-engines score: 3.45 Db-engines overall rank: #76 Db-engines TSDB rank: #3 and growing !78
79. TimescaleDB: learning curve Full SQL support ✅ PostgreSQL extension ✅ Not obvious gapfilling ❌ !79
80. TimescaleDB: gapfilling !80 Where is filling with NULL? What if we want to fill with other values?
81. TimescaleDB: integration complexity !81 ActiveRecord! ✅
82. TimescaleDB: infrastructure overhead If you use PostgreSQL you really don’t need any new infrastructure ✅ Otherwise you need only PostgreSQL (and maybe migrate your DB to PostgreSQL) ✅ Cloud version ✅ !82
83. TimescaleDB: popularity !83 Db-engines score: 1.40 Db-engines overall rank: #126 Db-engines TSDB rank: #8 PostgreSQL score/overall rank: 482.25/#4
84. TimescaleDB: other ACID transactions ✅ You able to store time series and other data in one DB ✅ !84
85. Clickhouse: learning curve SQL support ✅ !85
86. Clickhouse: integration complexity HTTP API ✅ Ruby client ✅ No ActiveRecord-like client ❌ Batch insert strong preferred ❌ !86
87. Clickhouse: infrastructure overhead One tool ✅ No cloud version ❌ !87
88. Clickhouse: popularity !88 Db-engines score: 3.05 Db-engines overall rank: #83 Db-engines relational DB rank: #43
89. Clickhouse: other !89 Clickhouse is not for key-value requests Didn’t found built-in way for gapfilling Clickhouse is good for «wide» tables Clickhouse is good in big data
90. InfluxDB: learning curve SQL-like querying support ✅ !90
91. InfluxDB: integration complexity HTTP API ✅ Ruby client ✅ ActiveRecord-like client (Influxer) ✅ !91
92. InfluxDB: infrastructure overhead One tool ✅ Cloud version ✅ !92
93. InfluxDB: popularity !93 Db-engines score: 18.65 Db-engines overall rank: #33 Db-engines TSDB rank: #1 (with big space after)
94. Summary Prometheus is good for monitoring TimescaleDB is good when you want store everything in one DB Clickhouse is good for BigData InfluxDB is still good for us !94
95. Questions?