Using PSM to Monitor Database Performance
PSM captures most of the database queries initiated by your ThingWorx application. It has dashboards that highlight slow execution statements at the database level, connection pool usage, and captures the bind variables sent to the database.
PSM monitors transactions that are initiated by users. Therefore, PSM only tracks user-facing database transactions. If the database performance issue occurs in a scheduler, timer, or an asynchronous process, the corresponding queries are not collected by PSM.
For any period of slow performance, you can track the corresponding database transactions. Right-click any chart or dashboard to open the query view. For example, if you identify a CPU spike or slow PurePath, you can drill-down to the corresponding database queries occurring during that period.
In the following example, after you identify slow data table queries and their corresponding database queries, optimize the code in the application that performs the QueryDataTableEntries API:
PMS displays the connection pooling details in your application. The database dashboard shows the overall usage of the connection pool as shown in the following example:
ThingWorx uses a pool of connections. The default value for pool connections is set to 100. If all the connections in the pool are used simultaneously, the application may appear unresponsive. To resolve this problem, check if there are slow transactions holding a connection longer than necessary, before allocating additional connections.
ThingWorx uses the following connection pools:
• Built-in persistence provider connections that are managed by the C3P0 library.
• Other secondary JDBC connections, which are created from a database Thing, are tracked using Apache library.
In the PSM database overview dashboard, you can isolate periods of time when connection pool is saturated. You can check which queries are executing at the specified time. This helps you diagnose the connection pooling issues. You should resolve the underlying queries in your ThingWorx application that are holding a connection for longer duration of time.