The reference api application uses a connection pool for database connections. There is a cache that is used per sandbox that stores Hikari connection pool datasource objects. These objects are used for getting a database connection from the pool. The cache has a limit of 15 entries and each datasource entry can provide a maximum of 10 database connections. This setting is designed to provide a maximum of 3 (one for each of DSTU2, STU3 and R4) x 15 (datasource entries) x 10 (maximum connection pool size) = 450 database connections.
However it has been observed that the database connections steadily increase and easily cross over 450. In addition to database connections to the sandbox schemas, there are connections to schemas oic, sandman, hspc_8_hspc8, hspc_8_hspc9 and hspc_8_hspc10. However the connections from R4 sandboxes account for the majority of connections.
During the recent HL7 Connectathon 25 from 9/9 through 9/11, it was observed that the number of connections went over 640. After the R4 reference api was task was restarted, the count went down to 210. After restarting stu3 reference api, the count dropped to 160. And after restarting dstu2, it further fell to 80.
I have made a change to have a fixed connection pool and not one that is periodically closed. Will test and see if it works. Will also get the solution verified by others, since it is a major change.
Changing the wait_timeout setting in MySql from 8 hours to 30 seconds reduced the number of database connections and they stayed down. They went back up when the setting was changed back to 8 hours. See attachment.
Database connections trend for various wait_timeout values has been attached.
The wait_timeout was reduced to 10 minutes on 10/05 and the number of database connections is at an average of 320 with a swing between 420 and 200.
Moving it to done status. This may not be a long term solution with wait_timeout setting. We may open another long term issue to have a more permanent solution.