Database Connection Pool Exhaustion/Locks on Large Queries
Description
From the field it was reported SSP was slow or not working at all. Upon investigation the database was reporting all SSP pool processes active and numerous locks created. In the SSP logs, there either is nothing found or
, or some other hibernate error in opening/creating a session.
This has been reported on and off since at least SSP 2.6 and no clear culprit could be found. Now due to two different schools having the same issue and providing helpful database information, the culprit is mainly large queries particularity in Directory Person Search.
This bug can be reproduced in Postgres or SQL Server especially with larger student counts in internal and external (2000+). Perform a person search particularly one that does a cross join say special service groups or map plans. Execute the query and in the browser dev tools capture the url sent to the server. Open multiple new tabs and run the search in all. If it completes re-run it again. As your doing that refresh or watch a database process monitor for the db server. You should see multiple active ssp connections/processes corresponding to the searches. Eventually the queries will never complete (the browser will time-out or you'll get an error response), but yet in the db monitor the process is never released and still active. Eventually, you'll run into a transaction lock error or hibernate error in creating/opening a session when starting new queries.
This issue so far has affected 3 schools with a large amount or users or students or both spanning 3 releases of SSP (SSP 2.6, 2.7, and 2.8).
I've definitely seen this at Sinclair during the person sync process. It does not recover after the failure.
Paul Spaude
June 21, 2017 at 4:57 PM
The commit on the parent for special service group fixes the sub-task for ssg. Tested that fix with 4000 internal and 2000 external students and all background jobs. So far no issues found and search time significantly improved.
From the field it was reported SSP was slow or not working at all. Upon investigation the database was reporting all SSP pool processes active and numerous locks created. In the SSP logs, there either is nothing found or
, or some other hibernate error in opening/creating a session.
This has been reported on and off since at least SSP 2.6 and no clear culprit could be found. Now due to two different schools having the same issue and providing helpful database information, the culprit is mainly large queries particularity in Directory Person Search.
This bug can be reproduced in Postgres or SQL Server especially with larger student counts in internal and external (2000+). Perform a person search particularly one that does a cross join say special service groups or map plans. Execute the query and in the browser dev tools capture the url sent to the server. Open multiple new tabs and run the search in all. If it completes re-run it again. As your doing that refresh or watch a database process monitor for the db server. You should see multiple active ssp connections/processes corresponding to the searches. Eventually the queries will never complete (the browser will time-out or you'll get an error response), but yet in the db monitor the process is never released and still active. Eventually, you'll run into a transaction lock error or hibernate error in creating/opening a session when starting new queries.
This issue so far has affected 3 schools with a large amount or users or students or both spanning 3 releases of SSP (SSP 2.6, 2.7, and 2.8).