When e.g. there is a db failover the connection validator causes the whole connection pool to be flushed and new connections will be created to the new database. This is the reason I’m using the fail-all-connections=true setting.
But the issue I’m having is that when a lot of new connections are created at the same time the connections are so slow that the connection validator keeps triggering the db pool flush.
I did some tests and it seems that it is caused by SSL. When I switch off SSL the connections are fast.
Questions:
Is it appropriate to use the ‘fail-all-connections=true’ setting for a db failover scenario
Is it possible in payara to monitor the time it takes to setup a new connection to a database
Is it possible to configure payara to re-use the SSL channel so that every connection does not have to setup a new one?
I’m really not sure whether you can do anything about this in Payara Server. Your configuration of Payara Server looks OK and appropriate.
Creating a connection is a responsibility of the JDBC driver. If the driver creates an SSL channel for every new connection, then you can have a look at the configuration options of the driver whether it supports using a single channel for all connections.
As a workaround, you could create a SSH tunnel to your database and point the JDBC data source to the local port created by the tunnel, without using SSL on the driver. All the communication with the DB would then go through a single SSH encrypted connection. But I’m not sure about performance impacts and you would have to ensure that the SSH connection remains open all the time, or at least that it recreates if it’s dropped.
You can monitor an average, lowest and highest waiting time for a new connection. You can access this information via the REST interface (Payara Server Administration and Monitoring API :: Payara Community Documentation) or JMX after you enable the JDBC connection pool monitoring component with HIGH level. This is what you can get via a browser for the default JDBC pool:
I looked a the different monitoring options and the closest one seems averageconnwaittime.
But this is the wait time when requesting a connection from the pool.
Is this the same as the physical connection time to the database?
