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

Transcript

1. QuasarDB Use Case
   5 novembre 2019
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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

   View Slide

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
   

   View Slide

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

   View Slide

8. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable
   ● Limitations
   ○ HTTP API
   ○ UDP
   ○ Fields & Tags
   INFLUXDB TEST & LIMITATIONS
   

   View Slide

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
   

   View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

23. Mickaël Gervais
    Tech Lead & Associé
    mgervais@agaetis.fr
    

    View Slide