says that the output limit for a query is 1,000 lines and that “Short queries, as issued by Graph+, are of little consequence.”
Well, what happens when the Graph+ queries are of consequence?
It appears that we don’t get told; the graph is silently truncated.
I just asked Graph+ to graph two years worth of Group:hourly historical
numbers for two of my inputs because the auto Group:day numbers lost the
detail I needed. All I got from Graph+ was a tiny sliver of a graph and no
error message explaining that I had hit the 1,000-record limit.
I messed around and found I could get almost 42 days of hourly data
(42 * 24 = 1008) on the graph in Graph+, but no more, and when I asked
for more I got no error message about exceeding the 1,000 record limit.
Of course I could write a manual query with limit=none to fetch and
graph the data myself, but I’d really rather use the lovely Graph+
interface.
Two requests:
Please add a message when Graph+ hits the 1,000 limit and truncates the graph.
Please add a way for us to set the query record limit for Graph+,
with a link to the documentation explaining the consequences.
This post conflates Graph+ and Query. They are separate animals:
Query is an API that provides a way to extract data from the IoTaWatt. Queries are handled by the IoTaWatt firmware’s web-server and monopolize the processor for the time that it takes to retrieve the data from the datalog and send a formatted response.
Graph+ is an application that runs in your local browser to select and graphically display IoTaWatt data. It uses creates queries and sends them to the IoTaWatt in order to fetch the data.
Query will monopolize the ESP8266 processor in the IoTaWatt such that it will not be able to do anything else for the duration of the time required to service the query. That means it will not be able to sample any of the inputs. During normal operation, the IoTaWatt samples an AC cycle for 16.7ms and then does anything else it needs to do in 8.3ms, then samples another AC cycle…
When a typical query for 720 lines from Graph+ is handled, it usually takes a little over one second. During that time, AC cycles will not be sampled. That will amount to about 3 samples missed for each input. Each 5 second datalog entry, representing the average voltage and power for each channel for that 5 seconds, is usually the time weighted average of about 13 samples.
So there is a limit to how large a query can be without impacting the integrity of the data. I’ve set that limit at 1000 to avoid folks making arbitrarily large queries without appreciating the consequences of doing so. You will notice that the query API allows overriding that default with the &lines parameter. I’ve used that to download modest queries of a few thousand lines. At some point, typically around 5,000 lines, the monopolization of the CPU will cause watchdog timers to trigger, usually forcing a reboot of the unit.
So that’s what I mean by consequences.
As it happens, last week I developed a spreadsheet of a years worth of hourly data for a large industrial installation using Graph+ to format the queries. What I did was request a plot of the needed data for “last month” with hourly grouping. This generates up to 744 lines of data, under the limit as you point out above.
I then click “csv data” at the bottom and then click “copy” and paste that into the spreadsheet. I then move back in time one month (there are several ways to do that) and repeat the process. At the end I sort the spreadsheet data by time to get the sequence desired. The whole exercise took a few minutes.
As an alternative, you can setup an uploader and the IoTaWatt will export the data to one of several time series databases like influx or emoncms and subsequently issue queries of practically any size as well as look at the data with something like Grafana. The uploaders do not monopolize the CPU, even when sending a backlog of data.
Well, the truncation is pretty obvious.
An indication that the limit was exceeded is appended to the query result, but Graph+ does not look for that and simply plots what it got. If I were to do anything to remediate this issue, it would probably lean toward heading the problem off with a message before sending a query.
But the bottom line is that graph+ simply cannot plot 17,520 data points and is the wrong tool to use. I agree that it is a nice tool to develop the query, but when the query fails you can use the JS debugger to copy the query that it sent and paste it into another browser tab URL adding an increased &limit parameter.
As above, you can increase the limit for a query.
My approach to documentation is to keep it simple so that people will actually read it. I’m not inclined to bloat the query docs with details about something that hardly anyone will encounter.
I pull data via Query from my Iotawatt over to a database in smaller chunks based on the time gap between the latest in Iotawatt and the database copy. However, sometimes either Iotawatt or the database are offline for various reasons and the sync up can sometimes need 1000(s) of rows. I find that looping code doing smaller pulls works well. Mine is limited to a max of 360 rows per pull in each loop of the code - and with the database insert, there’s a slight delay between pulls.
My point / and just a suggestion in case it helps… If you need multi-thousand rows of data via Query, one approach is to pull smaller chunks in a loop with patience and Iotawatt will continue to show status at the same time. Good luck.