Re: DBI: For speed-critical forking server, is DB reconnection in each child required? And prepared statements?

James Marshall schrieb am 13.05.2012 um 11:29 (-0700):
> I'm writing a forking HTTP server that needs to access a database
> with each hit.  Speed is pretty important, so I was hoping to reuse
> database handles and prepared statement handles in the forked child
> processes.  Is this possible, or do I really need to reconnect to the
> database and prepare the statements in each child?

I used to think that you always have to reconnect and reprepare. Think
about what happens when two independent processes manipulate the handle
at the same time. But actually I think it depends on the implementation,
and this is more than I am confident to answer. There might be a driver
able to handle multiple concurrent clients within one connection.

I'm curious whether there is a general rule to this, and cases where
a driver allows a connection to be shared among multiple processes.

I'm including some pointers from my web search, mainly as a note to
myself … seems to indicate that you cannot share a connection, but
as you wrote, ultimately inconclusive.

DBI, fork, and clone.
http://www.perlmonks.org/?node_id=594175
=> You need to (1) clone the parent's $dbh, and (2) prevent
   the parent's $dbh from being destroyed in the child.

(1) Clone the parent's $dbh (in other words: reconnect)

https://metacpan.org/module/DBI#clone (only for $dbh, does a reconnect)

Perl Parallel::ForkManager with DBI database handlers
http://stackoverflow.com/a/3127247/269126

(2) Prevent the parent's $dbh from being destroyed in the child

https://metacpan.org/module/DBI#InactiveDestroy
https://metacpan.org/module/DBI#AutoInactiveDestroy

Why am I not able to query a database from a forked child in Perl?
http://stackoverflow.com/a/1194640/269126

> Info I found from googling, faqs, and list archives generally implies
> reconnection is required, but is inconclusive (at least what I found).
> 
> Alternately, is there any way to clone (deep or shallow) a prepared
> statement handle without repreparing, or a database handle without
> reconnecting?

The handle usually refers to something that cannot be cloned in Perl,
like some memory in the C driver, so cloning the handle won't do much.

> The only solution I've come up with is to have the server daemon
> manage a pool of connected database handles and prepared statement
> handles, which complicates the program a bit.  Any other solutions I'm
> missing?

Is connecting and preparing once for each connection and statement
really such a burden? The server process might run for 1000 requests
so one connect and 50 prepares might be okay …

Michael