I would recommend Model 2. Store each metric as a field on the same measurement, with a Device ID tag.
> Issues: > - Queries by value are slow (more than a minute). Example: select * from "data" where SYSTEM_MEMORY > 80 Queries that filter by field values are always slow. Field values are not indexed. Running a query unbounded in time forces the system to scan every single point to evaluate the condition. > - Continuous Query takes so much time: > ... BEGIN SELECT mean(SYSTEM_MEMORY) as SYSTEM_MEMORY_mean INTO .... FROM data GROUP BY time(5m), deviceId What does "so much time" mean? There doesn't appear to be anything inefficient in the query. You never talked about your data density. How many metrics are written every five minutes? On Tue, Sep 27, 2016 at 7:02 AM, Carlo Vargas <[email protected]> wrote: > BTW, for model 1 there will be 6 millions data points for database > "devices" (600K data points per database/measurement). > For model 3, there will be 600K data points per database (each database > has its own metric). > > > > > On Monday, September 26, 2016 at 8:52:19 PM UTC-4, Carlo Vargas wrote: >> >> >> Currently I am evaluating different Time Series data bases and I do have >> some questions regarding data modelling and query performance in InfluxDB. >> >> Context: We have 200 000 devices and 10 metrics per device (for instance: >> SYSTEM_MEMORY). >> >> Devices were processed 3 times, so we ended up with 600K data points. >> >> Here are the 3 models that were used: >> >> Model 1: One database named "devices", 10 measurements (one for each >> metric), and the tag deviceId. >> >> Issues: >> - Queries by value are not responding. Example of this >> query: select * from SYSTEM_MEMORY where value > 80 >> - It uses a lot of RAM, the server crashes when the above >> query is executed or when the following Continuous Query is also executed: >> ... BEGIN SELECT mean(value) as mean_value INTO >> devices."<current_policy>".:MEASUREMENT FROM devices."<new_policy>"./.*/ >> GROUP BY time(5m), deviceId >> >> >> Model 2: One database named "devices", one measurement named "data", >> deviceId tag and each metric as a field. >> >> Issues: >> - Queries by value are slow (more than a minute). >> Example: select * from "data" where SYSTEM_MEMORY > 80 >> - Continuous Query takes so much time: >> ... BEGIN SELECT mean(SYSTEM_MEMORY) as SYSTEM_MEMORY_mean >> INTO .... FROM data GROUP BY time(5m), deviceId >> >> >> Model 3: One database per metric, one measurement "data", and deviceId >> tag. >> >> Issues: >> - Queries by value takes around 25 seconds. Example: >> select * from "data" where value > 80 (this query is done in SYSTEM_MEMORY >> database) >> - Continuous Query needs to be created for each database >> and they are slow. >> - Adding data points is slower than previous two models. >> >> >> Any advice/suggestion would be really appreciated. >> >> Thanks in advance. >> > -- > Remember to include the InfluxDB version number with all issue reports > --- > You received this message because you are subscribed to the Google Groups > "InfluxDB" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/influxdb. > To view this discussion on the web visit https://groups.google.com/d/ > msgid/influxdb/4e0d07d0-fcda-4948-9bb0-ec8f93f703af%40googlegroups.com > <https://groups.google.com/d/msgid/influxdb/4e0d07d0-fcda-4948-9bb0-ec8f93f703af%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > For more options, visit https://groups.google.com/d/optout. > -- Sean Beckett Director of Support and Professional Services InfluxDB -- Remember to include the InfluxDB version number with all issue reports --- You received this message because you are subscribed to the Google Groups "InfluxDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/influxdb. To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/CALGqCvP5D-kGKcUyYkYr%2BRYuoFXOam3BrKf24hWafbhYcgDBuQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
