Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PTSM - #2 - QuasarDB et véhicules connectés

D0a6969000c3715087b3a00abd646d20?s=47 ParisTimeSeries
November 05, 2019
990

PTSM - #2 - QuasarDB et véhicules connectés

D0a6969000c3715087b3a00abd646d20?s=128

ParisTimeSeries

November 05, 2019
Tweet

Transcript

  1. QuasarDB Use Case 5 novembre 2019

  2. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable 2 AGAETIS Offices in Paris, Lyon, Clermont-Ferrand and Toulouse 25 employees Company created 13 years ago Subsidiary company of Novencia Group for Innovation and Consultancy services
  3. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • ~ 300 vehicles, bus, trucks, tractors ◦ Tyre pressure, temperature, ... ◦ Latitude, longitude, altitude, used satellites, ... ◦ Engaged gear, speed, … ◦ Acceleration x,y,z,... • Frequency ◦ -GPS: 1Hz ◦ -Accelerometer: 25 Hz ◦ - CAN: 350 Hz ◦ - TPMS: 1Hz ◦ -Altimeter: 1Hz ◦ - Shock : 1KHz (!) ◦ - ... • 4,094,330,208 Rows per month (Precisely!) • 60 msg/sec (Protocol buffer) • Target ~ 250,000 new devices for Q4 2019 USE CASE CONTEXT
  4. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • High availability & resilient system • QoS 2 for devices • Aggregate data • Monitoring (Infra & fleet) • Expose data to Data Scientists (API) TECHNICAL NEEDS
  5. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    AS-IS ARCHITECTURE
  6. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Storage ◦ Use a single DB & remove TTL ◦ Use a full time series DB ◦ Compression • Grafana integration ◦ Datasource plugin for MongoDB • Spring XD EOL ◦ Use Kafka broker ◦ Use Kafka streams to enrich data in real-time • Security implementation ◦ ACL per device • Historical data (only for migration purpose) ◦ Load data from HDFS to DB ◦ Performance issue PROBLEMS TO SOLVE
  7. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    Studied database engines: 1. InfluxDB 2. MongoDB 3. QuasarDB BENCHMARKED SOLUTIONS
  8. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Limitations ◦ HTTP API ◦ UDP ◦ Fields & Tags INFLUXDB TEST & LIMITATIONS
  9. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Limitations ◦ Time Series DB ◦ Fields name length ◦ Aggregate data per minute ◦ Upsert (Order & minute) ◦ Flatten data ◦ Aggregation are needed most of the time (avg, count, …) ◦ Time precision MONGODB TEST & LIMITATIONS
  10. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Querying ◦ Filtering: { "id.i":354676050163640, "id.t":{ "$date":"2018-09-01T01:46:00.000Z" } } ◦ Upsert: { "$set":{ "ttl":{ "$date":"2018-09-01T00:00:00.000Z"}}, "$push":{ "d":{ "$each":[ { "ms":746, "t":315.83, "p":99031.75, "a":211.21174330329822}, { ... }], "$sort":{"ms":1} } } MONTGODB TEST - QUERIES SAMPLES
  11. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Use of skeletons • One table per device and channel (Best compression) • Inserts only • Tag tables QUASARDB TEST - USED MODELISATION FEATURES
  12. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Query by tags • ACL associated to tags • Retrieved data are ready to use • Python pandas integration • Range queries QUASARDB TEST - USED QUERY FEATURES
  13. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    BENCHMARK SUMMARY Storage HDFS QuasarDB MongoDB Size Size Ratio Size Ratio 1.47 Go 2.80 Go 1.9 5.60 Go 3.81 Time Precision Nanoseconds Milliseconds Loading (1 day ~ 1.47 Go) 1h 30 m 2h 25m Querying with index scan QuasarDB is 2.3 times faster without index scan QuasarDB is 18 times faster
  14. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    TARGET ARCHITECTURE
  15. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Prometheus Exporter ◦ https://github.com/Agaetis-IT/qdb-prometheus-exporter • Plugin Grafana • Kafka Connect ◦ https://github.com/Agaetis-IT/kafka-connect-qdb TOOLS & QUASARDB INTEGRATION IMPROVEMENTS
  16. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Kafka ◦ Connect vs consumer/producer ◦ Partitioning ◦ Expose JVM metrics from all consumers/producers/streams • Fork real time data ◦ No down time • Load historical data ◦ Cluster stress ◦ Use HDFS for historical raw data storage ABOUT MIGRATION
  17. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    MONITORING EXAMPLES -MQTT Producer
  18. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    MONITORING EXAMPLES - Raw Stream
  19. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    MONITORING EXAMPLES - GPS data integration
  20. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    MONITORING EXAMPLES - QuasarDB Cluster Monitoring
  21. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Common Table Expression usage • New types usage: Char(N) for instance • User Defined Functions • Compression improvements NEXT STEPS
  22. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable

    • Leon Mergen(QuasarDB): leon@quasardb.net • François Travais(Agaetis): francois.travais@agaetis.fr THANKS
  23. Mickaël Gervais Tech Lead & Associé mgervais@agaetis.fr