Tag Historian/SQL Crashing


I seem unable to reliable pull history tags without my client crashing. Details as follows...

  • Windows Server 2012 R2 (Xeon E3-1220 3ghz 4Cores, 8gb Ram)
  • MySQL server 8 - 8.0.11
  • Java Version 1.8.0_171-b11
  • Ignition 7.9.7

I have approx 10 Modbus IP devices, and 10 serial Modbus behind a single rs-232/Ethernet Gateway.

I'm trying to view tag history, eg. 2months of 4 tags (ie. Power/amps/volts from a single device)

If I try to pull back more than 1-2 tags, the entire client freezes and crashes.

  • Server CPU maxes out with program called "JAVA(TM) Platform SE binary
  • MySQL never uses more than 10% CPU
  • Server RAM remains stable
  • Ignition Databases shows pool connections dont max, but throughput is only 1-2 queries/sec.

Finally, the entire client crashes and I have to start again.

Any suggestions where to start?


You probably haven't given Ignition itself enough RAM. Look for the settings in ignition.conf.

(You tagged this as ignition81 but your post says Ignition 7.9. Which is this really?)

Your screenshot shows "Searching for active Gateway", which strongly suggests that it isn't a client problem, but a gateway problem (the main Ignition service).

You should look at the wrapper.log file in your install folder. If you can, extract from the time of one restart to the next, and post it here. (Use the "preformatted text" button in the forum's comment editor to preserve the text layout.)

I could only see RAM allocation in "designer memory" under "Web-launch settings" increasing this, doesn't have an effect, while the Java Program still seems to max out memory at 1200mb. Is there another place to allocate RAM?

If I recall the trending, and it freezes, I close the client. I'm still left with another Java(TM Platform SE Binary (the ignition server?) running that is still pulling 1200mb of RAM? vieing system performance via webpage status, it states the system is only using 177mb

Apologies for the tag, not sure where that came from, I've removed it.

In my first comment, I suggest checking the settings in ignition.conf. That is where the controls for gateway memory usage live. You currently allow only 1GB, as shown on your memory trend. And it shows congestion (probably when you make the large query). 1GB is a fairly small allowance for an Ignition gateway.

Meanwhile, you mention that the gateway machine has 8GB. If you are running a 4GB designer and any kind of database on the same machine, you are simply overloading.

(Other than for training, you should never be running a gateway, database, and designer on the same machine.)

Thanks, Apologies I've found what you meant by ignition.config via the user manual.
I've made that change, but still experiencing long wait/hangups, but still not hitting system restrictions. Curious the 'used' RAM in ignition is never close to what the system says its using.

I can't speak to running gateway/database/designer on the same machine, this is how it was set up by installers 5 years ago. But as it is a small project - two graphics pages, and 20 modbus devices, it should be able to handle pulling a month of trend data from a couple tags? for all of this testing, I'm only running the client, and the webpage to view status of system.

That's because Java doesn't "give back" unused RAM. It hangs onto it so it doesn't have to ask again. As you can see from the normal trends, java uses memory in a sawtooth pattern. If it had to ask the OS for it all the time, performance would be much worse.

Ideally, ignition.conf is set to use the same memory at startup as at max, so that there's no fighting over it after the system has been running a while. The OS view of java's memory usage is the total it has ever claimed.

Awesome, that makes perfect sense. I'll edit that again so startup and max matches as there's no other memory requirements on this machine - that i know of!

The bottle neck really seems to be in the queries, I've increased max number of connections to 12, but I've never seen the throughput increase more than 1.6queries/sec. I don't know what a good baseline is but reading some other threads on the forum this seems extremely low?

It is low, but this is a small system. Also, you're requesting the data in one go, so why would there ever be more than one query?

How long does it take before the client crashes? does any data update? Are you trying to pull this data into a table?

2 months of data is fairly vague. That could be 15 records or 15million. How many records is it? This is important because the client has to store this data in it's memory, which is a separate allocation, if you're running it on the same machine as the gateway, with the database and the designer at the same time, there may not be much left. Databases are notorious memory hogs.

1 Like

Yeap, good points.. This screenshot is 3 days, 1hour samples of 3 tags, loads in under 5 seconds. If I then change it to 2 months timeline, it dies completely.

The end goal here is, what do I need to do to remedy, somewhere between a setup change and full system upgrade :slight_smile:

The chart might show three days aggregated into 1 hour chunks, but I doubt that those tags are actually on a 1 hour historical sample rate. What is the rate of the historical scan class?

2 months at a 1 hour scan is around 1500 records (x3) so roughly 4500 records (if that is the true scan class). I find it hard to believe that the client is crashing with so few records.

Yea agreed again. They look like a minute? Unfortunately that level of resolution is often required (or nice to have in fault events). I would have thought defining that as 1 hour, would only pull that number of samples back. If I try pull it back as 1min is definitely, definitely worse ha.

It's not always about how many records the query is pulling. Do you have partitioning set up on the machine? How much storage is available?

If it is required that is one thing, but if it's just nice to have then its worth the discussion of exactly what you need to have. You really want most tags set up to record history on change. Meaning that the max time between records is unlimited. As long as your dead band is set correctly you will still record changes that are interesting. As it is set now you record a record every 1 Minute independent of if the value has changed or not. Perhaps that is needed, in my experience it is mostly not.

60 Days x 24 Hours X 60 Min X 3 tags is a minimum of 259,200 (if the value is changing inside of a 1 minute interval then you will have more) records in the database. That is not an unsubstantial number.

Hopefully you have some type of data pruning configured for the historian. Assuming the worst configuration of no data pruning and no partitioning the database will have >= 7.8M records for just those three tags in a single table. Assuming a fairly low 4 bytes per row of data, that's roughly 32GB or storage, again for just those three tags.

The performance issues come in if no indexes have been added to the tables (they aren't there by default in 7.9) and so the database engine will have to trudge through all of that data to gather the information being requested, and that can kill a server that doesn't have enough memory available.

Does the database have data pruning and partitioning configured? Has proper indexing been set up on the tables?

All of that being said, 2 months of data for a client running on this machine that already has a lot of memory hungry applications running might just be too much.

1 Like

Thanks for you input so far Irose. I'll have a look into sampling based on change vs time sampling. Thats all pretty standard consideration no matter your system.

I can't answer any questions about the setup in regard to data pruning/partitioning/indexing, I'll do some reading up on it but might have to pass that onto a contractor with specific experience.

In regard to the samples, I would have thought defining 1 hour samples in the Tag historian would only pull those back, rather than ALL the samples in that time period.
ie. 60 Days x 24 Hours x 3 tags = 4320samples. That doesn't seem outrageous. but the minute samples are still stored in the SQL database if you needed to zoom in on any particular areas/times?

No, the historian must grab all the data in the time period, then it can apply the selected aggregation mode.

And if the indices on t_stamp are missing, the DB might have to scan the entire table to find the right row data.

1 Like

Yikes. Where am I looking for t_stamp ?

In the SQLT data partitions in your database. Look for table information that indicates whether there is an index present for that column (varies by DB brand).