Sharing a DBI::Mysql database connection with your children

This is a multi-part message in MIME format.
--------------050001070300010900070500
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have a process I was thinking of making into a multithreaded daemon
that deals with a MySQL database. The thought is that the daemon would
open the database once, then listen for clients. As clients connected
the daemon would fork off a copy of itself and handle the requests. This
would make the process faster because I wouldn't need to open the
database every time a new client wanted service.

However, I've found that once I fork the database handle (obtained
through DBI) is no longer valid. Reading around a little bit I noticed
people saying to reopen or reconnect to the database in the child. Well
that's the very time consuming thing I was trying to avoid!

Does anybody know how to open a MySQL database such that that database
connection can be used in forked children?
--
Andrew DeFaria
Instead of talking to your plants, if you yelled a them would they still
grow, only to be troubled and insecure?

--------------050001070300010900070500
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit






I have a process I was thinking of making into a multithreaded daemon
that deals with a MySQL database. The thought is that the daemon would
open the database once, then listen for clients. As clients connected
the daemon would fork off a copy of itself and handle the requests.
This would make the process faster because I wouldn't need to open the
database every time a new client wanted service.



However, I've found that once I fork the database handle (obtained
through DBI) is no longer valid. Reading around a little bit I noticed
people saying to reopen or reconnect to the database in the child. Well
that's the very time consuming thing I was trying to avoid!



Does anybody know how to open a MySQL database such that that database
connection can be used in forked children?

Re: Sharing a DBI::Mysql database connection with your children

On 2008-03-31 03:45, Andrew DeFaria wrote:
> I have a process I was thinking of making into a multithreaded daemon
^^^^^^^^^^^^^
> that deals with a MySQL database. The thought is that the daemon would
> open the database once, then listen for clients. As clients connected
> the daemon would fork off a copy of itself and handle the requests. This
^^^^
Do you want to use threads or fork?

> would make the process faster because I wouldn't need to open the
> database every time a new client wanted service.

This cannot be done. Not only will the database server get a mixture of
requests from different database clients on the same connection (this
could be solved), but it also has to send back all replies via the same
connection: Which client will receive the response? There is no way to
determine that.

(There is at least one RDBMS where the client automatically opens a new
connection when it detects a pid change - presumably the new connection
will be pre-authenticated and faster to establish).

Your best bet is probably to use a pre-forked approach like some web
servers. Run a number of your your daemons in parallel, all listening on
the same port. A client connecting to that port will get any of them.
If all are busy, the client has to wait, or a controlling process can
start more worker processes.

hp

Re: Sharing a DBI::Mysql database connection with your children

Andrew DeFaria wrote:

> However, I've found that once I fork the database handle (obtained
> through DBI) is no longer valid. Reading around a little bit I noticed
> people saying to reopen or reconnect to the database in the child. Well
> that's the very time consuming thing I was trying to avoid!

In my experience a connect to MySQL isn't time consuming.

Frank
--
Dipl.-Inform. Frank Seitz; http://www.fseitz.de/
Anwendungen für Ihr Internet und Intranet
Tel: 04103/180301; Fax: -02; Industriestr. 31, 22880 Wedel