Hi,
Can Ignition works with NoSQL databases ? (MongoDB, Redis, …)
Do you know what type of NoSQL environment is the best for IoT / datalogging / smartenergy applications ?
Thanks,
2 Likes
Hi, Were you able to make ignition work with nosql? Any updates on this?
Thank you.
1 Like
No response for now 
In theory, any NoSQL DB that provides a JDBC (4) driver could be used by Ignition.
In practice, I wouldn’t recommend it.
NoSQL DBs like Mongo are a very powerful tool, but using them is opting into a totally different set of guarantees and behaviors than traditional RDBMS. Nothing in Ignition is going to take real advantage of anything offered by these DBs, and you’re likely to not get significant performance gains (if that’s what you’re actually after).
You may find Kymera’s InfluxDB module interesting:
https://inductiveautomation.com/moduleshowcase/module/kymera-systems-inc-influxdb-history-provider
1 Like
Has the performance gain of combining Ignition with NoSQL evolved?
I have a couple tables with 3 million rows.
I indexed on the columns (t_stamp, Line, Code).
I think getting the data takes a few seconds anyway because there are so many rows.
I appreciate help with what can be leveraged.
3 million rows is nothing for a properly specced RDBMS. NoSQL is not a magic "make every query faster" button; it's a totally different technology with totally different tradeoffs than a traditional RDBMS.
5 Likes
In addition, I don't think NoSQL will necessarily give you a speed boost on this sort of query, and SQL database to a NoSQL database over a few seconds seems like a lot of work. I would first look at your EXPLAIN statement and see where other improvements can be made before doing something drastic like changing databases.
2 Likes
If you are getting all of the rows like your prior examples, a few seconds is blistering fast--dominated by transfer, not selection. What are your expectations?
3 Likes
Will a timeseries data base like influxdb be more suitable? I guess Kyle Chase has a module for that for Ignition!
I want all my queries to load in under a hundredth of a second haha.
Maybe Netflix and new IPhones spoiled me with their high core counts and customer partitions.
It isn't core counts and partitions that matter for transfer. Millions of rows is multiple millions of bytes. Typical office LANs will move ~ 100 Megabytes per second. Typical WiFi is slower. Legacy LANs (100 Mbit or less) are much slower. Faster CPUs and databases do jack [expletive] for pure transfer limitations.
Edit: Really butchered the scale on that. I'm surprised no one called me out. But the point still stands.
2 Likes
You mean that you are impressed by Netflix partial query with paging loading few records at a time, as it loads data as needed, and not all data just in case.
1 Like
Thanks, the transfer is a bottleneck I wasn't actively considering.
Thanks, I didn't really think about the low number of rows Netflix has to query.
I conflated NoSQL and compute leasing on accident too.
I think I need to find ways to make my data smaller since it seems like my true bottleneck is the size of the data instead of the speed of my database or query at the moment.
In another topic I suggested maintenance event scripts to perform data manipulation minute-by-minute to relieve the load of using window functions. That same idea can be used to pre-compute minute-by-minute grouped aggregates into another table. Then your display queries could use those pre-computed groupings instead of the raw data.
Your database may also support "Materialized Views" which is another way of doing this.
2 Likes
In my time trial testing, the processing took much less time than the initial grabbing of the data.
My table is indexed on (t_stamp, Line).
Using a where clause
3.10 seconds to get 1227445 rows.
Just the t_stamp column
3.86 seconds to get 1227445 rows.
2 normal columns
4.45 seconds to get 1227445 rows.
3 normal columns
5.77 seconds to get 1227445 rows.
3 normal columns, one joined column
7.86 seconds to get 1227445 rows.
3 normal columns, one joined column, lead t_stamp column
I add 1-2 seconds per column with an initial 3 seconds it seems.
The main table has 5 columns. 2 bigints, 1 int, an nvarchar that I am checking on size, and a datetime.
If I drop the bigint columns, should performance improve?
I don't select them, so I don't know if they will have any impact.
If you're really concerned over these few seconds which I have to admit, I would not be, a few seconds for over a million rows is to be expected, I would either go with doing a Materialized View as suggested before. Is this something that a client is complaining about or are you just trying to see how fast you can get this? As Phil pointed out you're going to be have transfer of the data as a limiting factor to your speed such that even if your query speed is impossibly fast 0.0 seconds you are still going to have to wait for the data to get from the db to Ignition.
Even if you made a gateway dataset tag with these and then you referenced that in your clients and that gateway tag was just perfectly computed computations and not needing to run SQL because it was able to just add the rows directly, I think you'd run into how fast the gateway can get data to your clients.
I don't know I have to say this smells of premature optimization. I don't really see anything wrong with those query times and that amount of data.
Can you share the EXPLAIN statement for the query? That would show if there is something else database side to make it faster, either fixing the query structure itself or an index etc.
1 Like
I really think the question you should be asking here is why the users need over a million rows.
Personally, if a user came to me and complained that the query was slow and they were getting 1.2M rows + in 8 seconds, I think I would point out just how fast that is. 150,000 rows per sec. (plus). I'd say your system is doing just fine.
5 Likes
I appreciate the help and sharing perspectives on it.
I very much appreciate learning that 8 seconds for 1.2 million rows is an acceptable speed for SQL.
Nobody is complaining about the data or the speed.
I have limited access to the database and what is appropriate for me to share.
I will request to see the explain statement. I have not seen it myself.
Human response times are typically 150ms to 200ms.
Many budgetary decision are made in terms of fiscal years which are a year long.
I personally look at these two things, and then WISH to be able to query a year of data before a person can recognize a delay.
Regarding premature optimization, I think this is a good time to try to work on my speeds.
I have many reports posted, and the current report I made is collecting data for release to be used tomorrow.
I am not sure when else I would work on it.
The main table has 5 columns. 2 bigints, 1 int, an nvarchar that I am checking on size, and a datetime.
If I drop the bigint columns, should performance improve?
I don't select them, so I don't know if they will have any impact.
The only way to really tell is to get a copy of the database put it on a development server and then try it out.
I will say that some delay is actually a good thing and in the web dev world there's so much of "fake delays" added via sleep or similar because when things happen to fast, people don't believe it. I think it's called "the labor illusion" that the computer is doing work, and if it happens to fast people doubt the work was done or done correctly, for whatever reason. Faking a progress bar is basically the standard now.
2 Likes
How does one get an explain from their SQL in Ignition?
I had thought it required a kind of access to the database that I don't have in the designer.
Is that correct?