GPL-750 BUG - too high number of threads when generating the positive samples report

[emrojo]

Describe the bug
From mail:

Our monitoring system is again warning us that there are more than 300 concurrent connections to MLWH, and as before, this is due to the sudden appearance of a large number (241) connections They appeared at around 11:30, and they have been idle for the past 50 minutes.

RT Ticket Number
If applicable

To Reproduce
Steps to reproduce the behaviour:

1. Go to mlwh
2. Run: show status;
3. See number for 'threads_connected'.

Expected behaviour
The number of max threads is 500 and is shared with everybody using the MLWH database. We should not create 200 threads just for creating a report.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• Windows or Mac?
• Browser Chrome, Firefox, Safari or other?
• Browser version (use 'About' to look up)?

Additional context
Add any other context about the problem here.

[emrojo]

I've created a report from uat and checked the threads connected and it seems lighthouse uat does not have this issue. It went up from 2 to 3 threads connected, which it is quite smaller than the digits we get in production around 200 threads connected.

[JamesGlover]

In addition to the reports, we also open connections in the plates/new endpoint (create_plate_from_barcode) in plates_helper.update_mlwh_with_cog_uk_ids.
A preliminary scan of the code suggests that this connection is only opened once per http call, but I believe that the call gets made by lighthouse_ui once per plate in a box. So definitely a candidate for multiple connections.
A quick glance suggests we are calling connection.close() in a finally block.

[JamesGlover]

A few things I noted:

• We have a helper method for creating connection engines, but don't use it consistently
• We're manually opening and closing connections, it might be safer to do this via a context manager
• SQL alchemy provides connection pooling: https://docs.sqlalchemy.org/en/13/core/pooling.html I don't think we're accidentally creating 200 pools which we only use once, but it might reduce the number of concurrent connections we have open if opening and closing connections is a little slow. (But it sounds like we're just NOT closing connections)

[emrojo]

On mail today at 6:55, DBA confirmed all connections are staying below 400 (with a max set of 500). We are not requested to look into it further.