Hold on, you’re getting 1.4 million rows of data a day and you want this to go on for 30 years without deleting anything? Let me ask - is your database on the same server as your Ignition gateway?
1 Like
And using SQL Express.
I would highly recomend that you look into a full blown Database so that you can use Archiving, if for some regulatory reason you must keep this amount of data.
Otherwise take real close look at how often you are really interested in recording these tags. For instance changing the Historical Deadband. Are you really interested in a 0.1 change of these tags or is something larger really what you are concerned with? Do they really need to be collected every 2 seconds if they haven't changed?
1 Like
Ah I forgot SQL Express was mentioned. Yes that is something you should look at changing sooner rather than later - once you hit your 10 gigabyte limit you’re going to hit a brick wall.
But if you’re using that now, then you have less than 10 gigabytes of data now, so INSERTS should not be taking 30 seconds as @lrose said. You probably want to look into your database configuration - how much RAM is allocated to it, etc.
I got more details from the admin, it's a VM hosting both the sql and ignition servers on the same device. Intel i7-4790 @ 3.6Ghz, 4GB ram, 200GB hdd space. This server is due for an upgrade in the next few weeks, but it's what I've got for now. When I monitor the system during a long sql query, there is no appreciable spike in cpu, ram, or hdd activity. Could there be something causing long delays due to the VM nature? I don't know much about VM's I'm afraid.
The timeouts are getting to the point where the application crashes with a blank error window.
You really need more RAM. 4GB is probably the minimum for just ignition, let alone SQL (express or otherwise) and the operating system.
On top of that it is recommended that you run Ignition and SQL on separate servers.
Look at it this way, in your current setup, the OS, Ignition Gateway, and SQL Express are all competing for the same resources. As your gateway runs it will allocate more resources, up to whatever limit you have set for it. By default I believe that is 4GB of ram. It doesn’t necessarily release that memory. Mean while SQL is standing by using what little memory it can capture to try and run.
If your IT will not give you another server, then I would recommend you settle for no less than 32GB of Ram. I would also up the amount you allow ignition to use to 8GB.
Ideally though you would have two separate servers one for your gateway and one for SQL(move away from SQL Express, when you hit the 10GB it will really be over)
I would ask for each server to have 16GB Ram(minimum) and at least 2 cores.
1 Like
More!
Seriously, Ignition parallelizes pretty well and can make effective use of dozens of cores. Most databases can benefit from more cores, too. You might consider AMD Epyc server CPUs if Intel's bang for the buck is an issue. (Lack of bang for the buck, that is.)
3 Likes
Thanks everyone for your suggestions, I’ll be passing it along to the IT admin. We might be able to move the ignition server onto it’s own physical device, and leave the database on the VM on the new server. Would this be a more preferred arrangement? If the use of Virtual Machines is completely discouraged, we’ll have to explore other options, possibly repurposing the current server hardware. The admin initially set up with SQL Express due to cost and licensing concerns, but we may have to revisit that sooner than expected.
In the meantime, is there somewhere that I can restrict how much memory Ignition uses? Since my project isn’t very large, I was thinking of limiting the ram it uses, to try to leave more available to the database. I see a few charts on the System > Overview screen, RAM looks to be around 250MB of 2048, cpu time is in the single digits percent. This is also the case while running the queries which seem to be so problematic.
Thanks again everyone!
Holy mackerel! I followed that thread and found my SQL server memory was some insane number, maybe 15 digits long, I have reduced it to 2GB for now, see if that makes some change.
Further reduced, Ignition is limited to 1024MB and SQL to 2048MB, process monitor shows RAM running around 90% used now, though chart updates are in the 20-40 second range now.
I haven’t been able to figure out how to get Workstation to “Explain” a query. When I copied the query to the Workstation, it gave a syntax error at the ?, I think I need to supply the exact parameters (Dates, and tag_id) for the query, is there a way to get this from the Ignition program?