Insert Times MariaDB Azure. Increase in query duration over time

General Discussion
1

Hey there I have a connection to a mariaDB in azure. I have a bunch of TX groups that write against really long tables. Like 70gb tables. We are having trouble and our IT only blames ignition.

One interesting thing when the inserts times get high… like 1.5 seconds queries. I can reset the DB connection by disabling then re-enabling. The insert times go back down to like 20ms but about 6-12 hours later they creep back up to 1.5seconds and fill up the S&F cache. Is anyone have any clue about this behavior. This is the connection string.

connectTimeout=120000;socketTimeout=120000;rewriteBatchedStatementes=true;allowPublicKeyRetrieval=true;useSSL=true;enabledTLSProtocols=TLSv1.2;serverTimezone=America/Los_Angeles

2

Hello wildbilly,

It sounds like the longer query times may potentially correspond with S&F filling up. Is the database connection faulting causing S&F to fill?

This may be best to troubleshoot with support if you’d like to look into it further as there’s a lot to unpack.

You can reach our support department at the link below.
https://support.inductiveautomation.com/hc/en-us

1 Like
3

We have reached out to support. We tried a number of things. Like creating additional S&F engines. The DB connection isn’t faulting. Throughput is low because of slow tables. Then the avg query time increases over a time span(6-12 hours). Then disabling and reenabling the connection lowers the query speed. And will clear out the cache. But, the slow queries will come back.

I am wondering if other users experience similar situations with DB cloud providers. Or how I might prove or explain why this is occurring. I have additional logging turned on for both the S&F engines and the DB connection. And nothing is ever in the logs