How can I monitor DB Thing other than Persistence Provider

[bmdeshmukh18]

Hello Community,

I am trying to find a way to monitor or check the amount of triggers the DB thing is creating and whether that is contributing to the performance of the ThingWorx due to load getting created on the SQL server from where I am getting data.

I am aware of the C3P0 thread count which can be used to monitor the DB connections, but that is specific to the default persistence provider.

I am fetching data from multiple DBs using MS SQL DB Thing connectors.
I want to know if there is any way we can monitor the queue size and active connections.

So, in the DB Thing Configuration tab, the JDBC connection settings has an option of Maximum Number of Connections which is set to 5 and there's also an option to enable monitoring. But I am trying to understand how I can do the monitoring even if it is enabled.

Also, how to monitor the active connections out of the maximum 5 and the requests or the queue size to the DB thing at a regular interval. Please refer to the below reference image to what I am trying to point.

In addition to above, can anyone point towards the Db connection limit(not specific to persistence provider), that is set or allowed between ThingWorx and any MS SQL server. Is this setting done at ThingWorx level or Tomcat or the JVM level and how can I verify the same.

NOTE: I am aware that all the SQL servers I'm using are under load, but I am trying to find which ThingWorx SQL services are creating the load.

[Tudor]

Hello @bmdeshmukh18,

Database Things in ThingWorx don't utilize a queue mechanism to sequence the commands they send to the database.  All commands are sent directly from the context of the thread initiating the query with no queue or sequencing mechanism in between.  There is an Apache connection pool, but this mechanism tries to execute statements as quickly as possible within the context of each thread and does not have an actual 'queue' where it sequences SQL statements.

When the connection limit is reached in the pooling mechanism (define on the Thing), threads are going to wait for a DB connection to become available before sending the next command in sequence.  This often leads to significant backups in the connection pool mechanism -- the default of 5 connections can be too small and for production usage we sometimes need to increase this 10x provided the DB has resources.

Because there's no actual queue mechanism like we have for Events, for performance investigation we recommend capturing a 10 minute stack trace at 30s increments from the Support Subsystem or a 10 minute JFR recording so we can see which threads take significant time to return results from the DB.  The 'monitoring' section is not to monitor active queries going to the DB, but strictly to monitor that we still have an active connection to the DB (the isConnected property will be updated with the correct status based on this output).

If you're able to capture diagnostic output, please open a case with our support team and we can investigate further.  Please do mention this post in your case so we can reference the information more easily.

[slangley]

Hi @bmdeshmukh18 

If the response received on this post answered your question, please mark it as the Accepted Solution for the benefit of others in the Community.  If you still have questions, please let us know.

Regards.

--Sharon