This article is intended to to provide some information on how Yellowfin creates/uses/drops connections from the connection pool, which can help with identifying server requirements or troubleshooting connection issues.
At a very basic level, here how it works;
- Connections are created on start-up (based on min no. set).
- Connections are only removed from the connection pool at data source refresh time.
- Connections are only held in use, when actually in-use. The number of active users running reports, and how long the data sets take to return, along with any background tasks will affect when a connection is freed up.
- Additional connections will only be made when needed.
- You cannot create more connections that set via the max no.
- If a connection is in-use longer than the MaxTime set, it will be forcibly closed.
- Secondary connection pool can be configured for background tasks only.
Now to provide a bit more context on how it all ties in with usage.
Yellowfin starts and creates the initial pool
When Yellowfin is started, connections are made to both the Yellowfin DB and datasources. The number of connections created in the pool are based on the minimum number of connections set.
Yellowfin DB has a minimum default of 2. To modify this, check the web.xml settings.
Data sources have a minimum default of 1. To modify this, check the data source connection settings.
These connections are now in the pool, and will not be removed from the pool.
These connections are verified every minute via a background task to ensure the connections are valid. If so, they remain, if not, they are removed from the pool.
The connections are there for Yellowfin to use, and they will only be used when free, so once Yellowfin is started, and you're sitting on the login page (and you don't have any background tasks running), the 2 connections to the Yellowfin DB, and 5 connections to your datasources are all just sitting there, waiting to be used.
Additional connections will ONLY be created if needed, and are still within the maximum connection count.
Yellowfin DB has a maximum default of 25. To modify this, check the web.xml settings.
Data sources have a maximum default of 5. To modify this, check the data source connection settings.
If a connection to the data source cannot be made during this start up process, it will be flagged as unavailable, until manually resolved via the data source connection settings page.
Here's some additional details surrounding JDBCTimeout and the connection pool.
Running reports or schedules
When running reports, there is generally 2 separate connections that need to be used (assuming the report data is returned from a single data source). 1 connection is made to the Yellowfin DB to get the report definitions, check user access etc.. and the other connection is made to the data source to actually retrieve the data. Both of these connections would already exist in the connection pool, so they will just be re-used. However, if there are no available connections, a new connection will be made.
The connection to the Yellowfin DB should be only be briefly used before released, while the datasource connection will be in use until the data has been returned from the driver, at which point it is then released.
The number of active users running reports, and how long the data sets take to return, along with any background tasks will affect when a connection is freed up.
If a connection is still free at the end of the refresh time, it will be removed from the connection pool. However, it will never shrink below the min no. of connections set.
The secondary connection pool
This has it's own set of min/max/timeout settings, and will ONLY affect background tasks.
This needs to be manually enabled on each data source.
Connections marked as unavailable cannot be used
If a connection to the database is not available during initial connection pool creation (e.g on Startup), or via refreshing the connection using the 'test connection' (or refresh WS call), it will mark the data source as unavailable, and further connection requests will fail, even if the database comes back online.
The only way to then make this data source available is to go back to the data source connection page and hit the 'test connection' button (or use the refresh WS call above).
If you would like to stop the marking of the data source as unavailable, please insert the DB flag referenced here.
If you have any questions, or running into issues relating to connections, please raise a ticket with our support team.
Regards,
The Yellowfin Support Team.