Lengthy speech short: How do i make 100s of concurrently running processes talk to a database through one or couple of permanent periods?
The entire story:
I remember when i built several crushing engine that handles huge levels of large documents by forking off one child to another giving each a small amount of files to operate on. File securing, progress monitoring and result propagation take place in an Oracle database which all (sub-)processes access at various occasions utilizing an application-specific module which encapsulates DBI.
This labored well in the beginning, however with greater volumes of input data, the amount of database periods (one per child, and they may be very short-resided) constantly being opened up and closed has become an problem. I would now like to centralise database access to ensure that you will find just one or couple of fixed database periods which handle all database access for the (sub-)processes. The existence of the database abstraction module should result in the changes easy since the function calls within the worker instances can stay. My issue is which i cannot think about a appropriate method to enhance stated module to be able to establish communication between all of the processes and also the database connector(s).
I figured of message queueing, but could not develop a means of hooking up a sizable herd of requestors with one or couple of database fittings in ways to ensure that bidirectional communication can be done (for collecting the query result).
An asynchronous approach may help here for the reason that all demands are written towards the same queue and also the database connector maintenance the request will "call back" to submit the end result. But my thoughts fails me in producing a picture obvious enough to ensure that I'm able to fresh paint into code.
Threads rather than forking may have given me an simpler start, but this could now require massive changes towards the code base that I am not ready to do in order to an active system.
The greater I think about it, the greater the bottom idea appears like a pre-forked web server in my experience that it does not serve webpages but database queries. Any applying for grants things to search into, and where? Sample (pseudo) code to inspire me, links to possibly related articles, ready solutions on CPAN maybe?
I believe you need to think about adding a tier. I believe POE are designed for the center tier.
Take a look at DBD::Gofer. It's designed to become a separate process to pool and manage database connections.
You may take a look at SQLRELAY, that has another advantages too around the proxy aspect.
You might want to speak to your DBA about "Shared Server", quite simple to apply in Oracle >= 10. You are able to think about this like connection pooling around the server side. Then when you request for any connection, you aren't always developing a new dedicate server process, and wrecking it whenever you connect.
You might do connection pooling in your corner.