Taghistory query on Table cause server & client crash

Hello

I create table and bind it to date range component. I use ignition Tag history. After some test and selecting different time range the client and server crash and I get following error:

Blockquote
Traceback (most recent call last):
File “<event:propertyChange>”, line 1, in <module>
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException:
The database connection ‘MySQL’ is FAULTED.
See Gateway Status for details.
caused by GatewayException: The database connection ‘MySQL’ is FAULTED. See Gateway Status for details.
caused by FaultedDatabaseConnectionException: The database connection ‘MySQL’ is FAULTED. See Gateway Status for details.
Ignition v7.9.10 (b2018112821)
Java: Azul Systems, Inc. 9.0.7.2

This happen when I select range over 6 month and back to 1 month. I use 1 day as interval so the row count is not much more than 300 items.
I check RAM usage I see the RAM grow up to 2400MB.
Please guide me what cause this problem and how I can eliminate it.

You are overloading your database. If you are running it on the same machine as your gateway, consider moving it to another machine. And giving it more resources. It is also possible, though unlikely, that your indices on the history tables are broken/missing/corrupted, and therefore your queries cannot be completed within the connection’s query time limit.

Hi Phil
Thanks for your help.
I use i7 K series CPU with 16GB RAM.
So for 8 columns query from database it should be enough.
I also do same query in report module and get result without any problem very fast, so why table component show such behaviour?

How can I increase query time limit in ignition?

How is your polling mode and rate set up. Seems like I had a crash once when I accidently had the polling set to absolute and the rate at zero seconds.

I would maybe check the default memory allocation for both the database and your ignition gateway. I haven’t had issues with it going all the way to faulting the database, but I have seen significant slowdowns sometimes.

This problem significantly decrease in v8.0 beta.
when user select range more than a month I set sample size interval to a day to decrease return row count. for less than a month 1 hour and 30 min can be selected.

What is the InnoDBBufferPoolSize on your MYSQL?

Hi jpark
I check my.ini and see inno_buffer_pool_size=8M.
If it’s helping I can update my gateway back up for you for see the problem yourself.

You need to increase that. 8 megabytes is the default and not enough.

3 Likes

what is the best value for it?
I have 8 GB RAM and set gateway to 2GB and client to 2GB limit.

Amazing. Thank you. It seems the problem solved when I increase it to 512M.
Please tell me how should I set it properly for different usage.