Connection Cleanup
Opened this issue · 2 comments
Problem
I have been using PHP ETL and the Parallel extension's functional API to ingest large CSV datasets. It has been reliable and fast, but there can be several hundred database connections left open until the ingest is complete. I poked around the source code for this ETL package, but there are no methods or documentation for removing connections.
For context, this is how the ingest script works:
- Stream contents of the CSV file into a temporary
$batch
array - Once 5K rows have been loaded into the batch, push a task into
$tasks
array - At end of file, load remaining batch into a task
- Each task runs ETL in a parallel process using a closure
- Initialize
new ETL
instance addConnection()
for each target databaseextract()
&transform()
the batch dataload()
the batch into the DBrun()
ETLunset($etl)
return
(also triedexit
)
- Initialize
- Await completion of all tasks
- Move to next dataset...
Though I can't share any actual code, those closure bullet points are essentially what happens.
Inside the ETL closure, I have tried exit
and return
after $etl->run()
completes, and I have tried unsetting the ETL instance in the closure. Still, the processes and DB connections remain open.
Documentation for the Parallel extension could be more robust.
Request
The Manager class would benefit from a removeConnection
or destroyConnection
method, where the conn would be removed from $connections
. Would that terminate the PDO connection?
I'm happy to open a PR if this would work. I would also take advice on using persistent connections with ETL and Parallel.
Hi @type1fool I don't know if you are still interested by this feature, but we added it in our fork : https://github.com/wizaplace/php-etl
The implementation is very basic: it removes any reference to the connection, so the garbage collector could remove it on its next pass.
Official PHP Doc:
The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.
Thank you, @ecourtial
I've changed jobs and I'm out of the PHP ecosystem now. Still, thanks for following up.