Sharing a DBI::Mysql database connection with your children

Discussion in 'Perl Misc' started by Andrew DeFaria, Mar 31, 2008.

  1. 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 <http://defaria.com>
    Instead of talking to your plants, if you yelled a them would they still
    grow, only to be troubled and insecure?
    Andrew DeFaria, Mar 31, 2008
    #1
    1. Advertising

  2. 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
    Peter J. Holzer, Mar 31, 2008
    #2
    1. Advertising

  3. Andrew DeFaria

    Guest

    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
    > would make the process faster because I wouldn't need to open the
    > database every time a new client wanted service.


    On my system it takes less than one millisecond to open a connection
    to a MySQL server (located on a different system.) Do you find that
    connection time problematic? This is about the same amount of time
    it takes to fork in the first place.


    > 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.


    Yes, true.

    > Well
    > that's the very time consuming thing


    I find that hard to believe.

    > 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?


    I don't. I rather doubt other people do, either. Maybe your server
    should handle requests internally rather than forking.


    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    The costs of publication of this article were defrayed in part by the
    payment of page charges. This article must therefore be hereby marked
    advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
    this fact.
    , Mar 31, 2008
    #3
  4. Andrew DeFaria

    Dr.Ruud Guest

    Andrew DeFaria schreef:

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


    See connect_cached and Ima::DBI.

    --
    Affijn, Ruud

    "Gewoon is een tijger."
    Dr.Ruud, Mar 31, 2008
    #4
  5. Andrew DeFaria

    szr Guest

    wrote:
    > 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 would make the process faster because I wouldn't need
    >> to open the database every time a new client wanted service.

    >
    > On my system it takes less than one millisecond to open a connection
    > to a MySQL server (located on a different system.) Do you find that
    > connection time problematic? This is about the same amount of time
    > it takes to fork in the first place.


    Normally this would be true, if the MySQL server is on the same network,
    but if it's not, connection time depends entirely on the latency between
    the OP's running system and the MySQL server, such is the case if it is
    across the internet; it will surely take longer than a millisecond. But
    then again, that would hardly be an ideal setup, but it's not unheard
    of.

    --
    szr
    szr, Mar 31, 2008
    #5
  6. wrote:
    > On my system it takes less than one millisecond to open a connection
    > to a MySQL server (located on a different system.) Do you find that
    > connection time problematic? This is about the same amount of time it
    > takes to fork in the first place.

    I'm sorry. I didn't officially measure it. I remember another DBMS being
    extremely slow so I assume it. Will you ever forgive me?
    --
    Andrew DeFaria <http://defaria.com>
    Disk Full - Press F1 to belch.
    Andrew DeFaria, Apr 1, 2008
    #6
  7. Andrew DeFaria <> wrote in
    news:47f1c1d6$0$89385$:

    > I'm sorry. I didn't officially measure it. I remember another DBMS
    > being extremely slow so I assume it. Will you ever forgive me?
    > --
    > Andrew DeFaria <http://defaria.com>
    > Disk Full - Press F1 to belch.
    >
    > Attachment decoded: untitled-2.txt
    > --------------060102070906040004010008
    > <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    > <html>
    > <head>


    I may consider it if you stop posting HTML attachments.

    Sinan

    --
    A. Sinan Unur <>
    (remove .invalid and reverse each component for email address)

    comp.lang.perl.misc guidelines on the WWW:
    http://www.rehabitation.com/clpmisc/
    A. Sinan Unur, Apr 1, 2008
    #7
  8. A. Sinan Unur <> wrote:
    > Andrew DeFaria <> wrote in
    > news:47f1c1d6$0$89385$:
    >
    >> I'm sorry. I didn't officially measure it. I remember another DBMS
    >> being extremely slow so I assume it. Will you ever forgive me?
    >> --
    >> Andrew DeFaria <http://defaria.com>
    >> Disk Full - Press F1 to belch.
    >>
    >> Attachment decoded: untitled-2.txt
    >> --------------060102070906040004010008
    >> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    >> <html>
    >> <head>

    >
    > I may consider it if you stop posting HTML attachments.



    Please don't tell him how to post.

    http://groups.google.com/group/comp.lang.perl.misc/msg/1a331dca40c4d90a


    Nobody else here ignores that netiquette, simply spend your
    time answering their questions instead.


    --
    Tad McClellan
    email: perl -le "print scalar reverse qq/moc.noitatibaher\100cmdat/"
    Tad J McClellan, Apr 2, 2008
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Atif Jalal

    Database connection sharing

    Atif Jalal, Aug 8, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    1,891
    Steve C. Orr, MCSD
    Aug 8, 2003
  2. Paul Vudmaska

    dbi:mysql mysql has gone away

    Paul Vudmaska, Apr 21, 2004, in forum: Ruby
    Replies:
    0
    Views:
    111
    Paul Vudmaska
    Apr 21, 2004
  3. Jerome Hauss
    Replies:
    0
    Views:
    167
    Jerome Hauss
    Oct 13, 2004
  4. roadrunner
    Replies:
    1
    Views:
    1,081
    Ben Morrow
    Oct 13, 2007
  5. John
    Replies:
    10
    Views:
    241
    J. Gleixner
    Mar 26, 2009
Loading...

Share This Page