A crucial component in many web applications is the use of a database. The chances of using JDBC directly or indirectly to access a relational database (through JPA or other ORM frameworks) on these applications are quite high. A common problem when dealing with relational databases is dealing with SQL queries or statements that take too much time to resolve, thus causing your application to be considered slow in producing results to the user. It's usually better to detect these issues preemptively, before sub-par performance damages your application, and Payara Server has you covered!
You can configure whether or not Payara Server detects and logs all slow SQL queries or statements when they are being executed through a JDBC Connection Pool. An SQL query or statement is considered slow if its execution time exceeds a configured threshold in seconds or milliseconds. To illustrate this with an example, let's say that you are working on a crucial application where every query needs to be executed in less than one second. You can configure the server to log all queries that don't fulfill this criteria using this simple command:
When the server detects queries that fail to run under a second, it will log the SQL queries or statements, its execution time and the stack trace to track where in the application's code the query or statement was executed:
JDBC Monitoring Improvements
As part of Payara Server 18.104.22.168 release, the following monitoring statistics for JDBC connection pools have been added to enhance the user experience for SQL fine tuning, in addition to the logging events mentioned earlier:
A list of the top 10 most used SQL queries or statements run through a JDBC connection pool.
A list of the top 10 slowest SQL queries or statements run through a JDBC connection pool. This statistic can be collected even if the JDBC connection pool hasn't been configured to log slow SQL statements.
Remember that to see these statistics, you need to enable the monitoring of JDBC connection pools for the server to a
HIGH level first:
Once this is done, you can query these statistics using any of the means available (REST interface, JMX monitoring tools, etc.). The following is a sample to get the most frequently used SQL queries via the monitoring REST API. Running the following command:
Will provide the following result:
You will notice that the format of the statistic is a simple string value of the list of the SQL queries accompanied by the number of times each query has been executed. Every record is separated by the
%%%%EOL%%%(end-of-line) separator string:
And for the slow SQL queries statistic, each query will be accompanied by its worst execution time:
Remember to use these statistics if you're having trouble with your JDBC connections! Since they will always be available when the server's monitoring is enabled, you can constantly check what are the most used SQL queries and cross-check them with the slowest ones to determine which queries and statements need to be fine-tuned immediately. In the future, we will include additional SQL related statistics that can direct users to detect performance issues for their applications.
If you have any suggestions, feel free to let us know!