Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re: Sharing database handle

by d_i_r_t_y (Monk)
on Apr 21, 2002 at 03:10 UTC ( #160844=note: print w/replies, xml ) Need Help??


in reply to Sharing database handle

hi marcello,

I have an application which forks of child processes to do all the work. At the moment, every child process creates its own database connection, does its work and closes the database connection again. This causes a lot of connections to be made to the database.

how many children are you forking?!
seriously, we have done the same thing for our biological data since the amount of work preceding each insert/update justifies the parallel model.

i don't think there is a problem with many db connections as long as the work for each thread justifies the connection time overhead. on mysql, connections are cheap and fast and i wouldn't even think twice about doing it (though concurrency/locking with mysql is more or an issue...). on db2, where 1 connection costs about 750msec each (that's client/server on same machine!), you would want to have at least 750msec worth of work for each process to justify the connection overhead. that said, divide and conquer works a treat if you can break down your workload into mutually exclusive chunks.

btw, have to say it's sure satisfying to see those CPUs and disks crying for mercy when you have 8 concurrent processes hammering away inserts to the db...

matt

Replies are listed 'Best First'.
Re: Re: Sharing database handle
by Marcello (Hermit) on Apr 22, 2002 at 13:14 UTC
    Hi Matt,

    I'm forking about 1 process every second. And indeed, I use DBI to connect to a MySQL database. The child processes only perform simple SELECT and INSERT queries, so no heavy stuff.

    Judging from the reactions above, I guess it's best to keep it the way it is. I was just wondering if I could be more efficient.

    Grtz Marcello
      i'm forking about 1 process every second.

      continuously?! surely not?!!... perhaps you should post the code?

      if divide and conquer is not applicable then i have one other suggestion:

      1. open a local UNIX socket/pipe
      2. fork a predefined set of children (say, 8), have each of the children open a DBI handle to mysql
      3. set up the parent process to loop over your insert data spooling it to the socket that the children are all listening to/blocking on; meanwhile each of children are blocking in the $server->accept() call waiting for the parent to send them data
      4. upon reading a chunk of data, enter the insert routine and loop back to the accept
      5. continue until parent runs out of data

      an example of this approach (a pre-forking server, like apache) is in the perl cookback in the chapter on sockets or IPC, though i don't have the book in front of me at the moment, so can't give specific recipe.

      but you're probably right; unless you're loading >1Gb of data (which does or does not need transformation prior to insert), it's probably fast enough. ;-)

      d_i_r_t_y

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://160844]
help
Chatterbox?
[stevieb]: Corion I like the groundbreaking ones (guitar players). I have the ability to pick up on sounds that are groundbreaking or specific to a person, thanks to my years of doing recording/mixing/ sampling (hip-hop mind you, but years of it...
[stevieb]: ...has honed in my skills of recognizing sound
[stevieb]: All of the early members are coming out of the woodwork today :) Hey, planetscape
[Corion]: This cover version had so much promise but the singing is underwhelming :-/
[stevieb]: erix Thanks! I dislike remakes of songs usually, but my favourite remix of guitar/weeps is Jeff Healey. I'll take a listen to yours
[planetscape]: hi stevieb!
[stonecolddevin]: also hi Corion
[Corion]: stevieb: Yeah, but I don't follow guitar players enough... I never got Prince and I don't think I could recognize his play style. I can recognize Santana and Nile Rodgers, but that's about it, and not really deep knowledge I think ;)
[stevieb]: Jeff Healey cut of My guitar gently weeps
[stonecolddevin]: you don't recognize Prince. Prince tells you it's him.

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2017-06-22 21:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How many monitors do you use while coding?















    Results (531 votes). Check out past polls.