Unix Programming - persistant database connection with child processes

Author

persistant database connection with child processes

Martin John Brindle

2004-01-23, 5:08 pm

Hi,

I'm looking at writing a program in pro*c, which reads messages off a Q,
forks a child, then processes the request by connecting to an oracle
database. The trouble is, if I connect to the database in each child, i will
be hammering the database with connect/disconnect commands, which doesn't
sound very efficient. Therefore, I was thinking of putting an initial
persistant connection before the fork, which would be better, but I'm now
thinking that this would cause a problem with traffic all going throught the
one port. I take it pro*c uses sockets to connect to the database, how else
can it work?

Does anyone see my predicament, or am I thinking too hard and looking for a
problem? Essentially, should I use a persistant connection, and how should I
go about it?

Thanks for any suggestions,

Martin

Felipe Cerqueira (skylazart)

2004-01-23, 5:08 pm

why dont u create the two processes, the first one getting the messages
and sending using pipe or SysV IPC to the second process that will receive
the data and do all work with oracle?

- skylazart
http://core.cx/skylazart
mailto: skylazart!core.cx

On Sun, 14 Dec 2003, Martin John Brindle wrote:

quote:

> Hi,
>
> I'm looking at writing a program in pro*c, which reads messages off a Q,
> forks a child, then processes the request by connecting to an oracle
> database. The trouble is, if I connect to the database in each child, i will
> be hammering the database with connect/disconnect commands, which doesn't
> sound very efficient. Therefore, I was thinking of putting an initial
> persistant connection before the fork, which would be better, but I'm now
> thinking that this would cause a problem with traffic all going throught the
> one port. I take it pro*c uses sockets to connect to the database, how else
> can it work?
>
> Does anyone see my predicament, or am I thinking too hard and looking for a
> problem? Essentially, should I use a persistant connection, and how should I
> go about it?
>
> Thanks for any suggestions,
>
> Martin
>
>
>

Martin John Brindle

2004-01-23, 5:08 pm

The parent process will talk to the children via shared memory, and the
objective of the parent is just to receive messages and spawn children to
handle them. But if I connect before the fork, the children will each get a
copy of the one port to communicate through won't they? As opposed to having
their own connection with their own port?

Either way, I think I'm either gonna blow the buffer on the socket, or blow
the number of connections to the database!

Martin

"Felipe Cerqueira (skylazart)" <skylazart@core.cx> wrote in message
news:Pine.LNX.4.58.0312141555110.25942@tasty.sna.cx...[QUOTE][color=darkred]
>
> why dont u create the two processes, the first one getting the messages
> and sending using pipe or SysV IPC to the second process that will receive
> the data and do all work with oracle?
>
>
> - skylazart
> http://core.cx/skylazart
> mailto: skylazart!core.cx
>
>
> On Sun, 14 Dec 2003, Martin John Brindle wrote:
>
will[QUOTE][color=darkred]
doesn't[QUOTE][color=darkred]
now[QUOTE][color=darkred]
the[QUOTE][color=darkred]
else[QUOTE][color=darkred]
for a[QUOTE][color=darkred]
should I[QUOTE][color=darkred]

Felipe Cerqueira (skylazart)

2004-01-23, 5:08 pm

u need to think if you really need one child for each message.. (its too
expensive for the operation system)...

I was using OCI to do something like you need... I know that I cant use
the same OCI handler opened at parent in all others children .. it could
trigger a race condition problem.

So, my solution was, a parent receiving the msgs and sending to a SysV
IPC. Each child, with its own OCI connection, will consume this IPC Queue
and do the work with oracle DB. So, to increase performance, all I need is
to increase the number of children. (I know I have a limit, but its
another history)

If u dont need one child for each msg, I think its a nice way to design
your appl.

- skylazart
http://core.cx/skylazart
mailto: skylazart!core.cx

On Sun, 14 Dec 2003, Martin John Brindle wrote:

quote:

> The parent process will talk to the children via shared memory, and the
> objective of the parent is just to receive messages and spawn children to
> handle them. But if I connect before the fork, the children will each get a
> copy of the one port to communicate through won't they? As opposed to having
> their own connection with their own port?
>
> Either way, I think I'm either gonna blow the buffer on the socket, or blow
> the number of connections to the database!
>
> Martin
>
>
>
> "Felipe Cerqueira (skylazart)" <skylazart@core.cx> wrote in message
> news:Pine.LNX.4.58.0312141555110.25942@tasty.sna.cx...
> will
> doesn't
> now
> the
> else
> for a
> should I
>
>
>

Alexey A. Kiritchun

2004-01-23, 5:08 pm

Martin John Brindle writes:

quote:

> Hi,
>
> I'm looking at writing a program in pro*c, which reads messages off a Q,
> forks a child, then processes the request by connecting to an oracle
> database. The trouble is, if I connect to the database in each child, i will
> be hammering the database with connect/disconnect commands, which doesn't
> sound very efficient. Therefore, I was thinking of putting an initial
> persistant connection before the fork, which would be better, but I'm now
> thinking that this would cause a problem with traffic all going throught the
> one port. I take it pro*c uses sockets to connect to the database, how else
> can it work?

You could set up a pool of database handles in parent, and supply childs
with handles from this pool. Handle being used is marked as such until
the child exits, then returned to "unused" pool.

You could also grow the pool dynamically, adding new connection handles
when it is empty (or even somewhat beforehand, when it is 'nearly empty').

I have written a couple of programs in this way (using MySQL), and it
works. OTOH, I have heard that OCI's behind-the-scene work causes
problems with this setup (on_exit routines or smth like this).

--
Alexey 'Kaa the Snake' Kiritchun
mailto:kaa@nightmail.ru

Joseph Dionne

2004-01-23, 5:08 pm

One approach I have used is to start threads for the clients to the DB,
Oracle in this case, that establish the DB connection, then assign
application clients, Q messages in this case, to each these threads in turn.

This process reduces the over head of connecting to and logging into the DB.

Martin John Brindle wrote:

quote:

> Hi,
>
> I'm looking at writing a program in pro*c, which reads messages off a Q,
> forks a child, then processes the request by connecting to an oracle
> database. The trouble is, if I connect to the database in each child, i will
> be hammering the database with connect/disconnect commands, which doesn't
> sound very efficient. Therefore, I was thinking of putting an initial
> persistant connection before the fork, which would be better, but I'm now
> thinking that this would cause a problem with traffic all going throught the
> one port. I take it pro*c uses sockets to connect to the database, how else
> can it work?
>
> Does anyone see my predicament, or am I thinking too hard and looking for a
> problem? Essentially, should I use a persistant connection, and how should I
> go about it?
>
> Thanks for any suggestions,
>
> Martin
>
>

Martin John Brindle

2004-01-23, 5:08 pm

How would running multiple threads differ from multiple children, or am I
missing something?

I've also stumbled upon an issue of Oracle retuning a pointer to strings.
Looks like this can't be done, unless anyone can think of something clever?
What I wanted to do hear was have an array which I malloc up to the number
of records I'm pulling back, and then pass the array. This works for int
datatypes, but not for char datatypes, as it seems the size needs to be
known in the DECLARE sections?

Martin

"Joseph Dionne" <jdionne@hotmail.com> wrote in message
news:p6jDb.26737$Dt6.612633@twister.tampabay.rr.com...

quote:

> One approach I have used is to start threads for the clients to the DB,
> Oracle in this case, that establish the DB connection, then assign
> application clients, Q messages in this case, to each these threads in

turn.

quote:

>
> This process reduces the over head of connecting to and logging into the

DB.

quote:

>
> Martin John Brindle wrote:
will[QUOTE][color=darkred]
doesn't[QUOTE][color=darkred]
now[QUOTE][color=darkred]
the[QUOTE][color=darkred]
else[QUOTE][color=darkred]
for a[QUOTE][color=darkred]
should I[QUOTE][color=darkred]
>

Joseph Dionne

2004-01-23, 5:08 pm

If the "children" will establish and maintain the DB connection and
simply run transactions, being passed work from their parent, there is
no difference. However, if the parent is spawning new children that
must establish DB connection, a time consuming process, you have the
problem you noted.

I typically use threads because of their ease of use. A command line
option defines how many DB connections to make. The "parent" thread
simply queues up DB transactions, assigns the request to each thread
that is available, i.e. not already processing a request, and holds
future request in it's queue until a DB "client" thread is ready for
work. This is a typical producter/comsumer model.

Any IPC method use to pass queued transactions from a parent process to
a child process that has already established a DB connection will work
just the same as threads.

Threads are nothing more the another way of implementing parent/child
processes (in my view of the world at least).

Martin John Brindle wrote:

quote:

> How would running multiple threads differ from multiple children, or am I
> missing something?
>
> I've also stumbled upon an issue of Oracle retuning a pointer to strings.
> Looks like this can't be done, unless anyone can think of something clever?
> What I wanted to do hear was have an array which I malloc up to the number
> of records I'm pulling back, and then pass the array. This works for int
> datatypes, but not for char datatypes, as it seems the size needs to be
> known in the DECLARE sections?
>
> Martin
>
>
>
> "Joseph Dionne" <jdionne@hotmail.com> wrote in message
> news:p6jDb.26737$Dt6.612633@twister.tampabay.rr.com...
>
>
> turn.
>
>
> DB.
>
>
> will
>
>
> doesn't
>
>
> now
>
>
> the
>
>
> else
>
>
> for a
>
>
> should I
>
>
>

Michel Bardiaux

2004-01-23, 5:10 pm

Alexey A. Kiritchun wrote:

quote:

>
>
> You could set up a pool of database handles in parent, and supply
> childs with handles from this pool. Handle being used is marked as
> such until the child exits, then returned to "unused" pool.
>
> You could also grow the pool dynamically, adding new connection
> handles when it is empty (or even somewhat beforehand, when it is
> 'nearly empty').
>
> I have written a couple of programs in this way (using MySQL), and it
> works. OTOH, I have heard that OCI's behind-the-scene work causes
> problems with this setup (on_exit routines or smth like this).
>

Absolutely (atexit actually). You *can* work around it by calling _exit
or kill(getpid(), 9); but you can still get in a mess if the *parent*
calls exit.

As a rule, I have found that shared OCI handles are a catastrophe
waiting to happen. Better avoid them.

--
Michel Bardiaux
Peaktime Belgium S.A. Bd. du Souverain, 191 B-1160 Bruxelles
Tel : +32 2 790.29.41

Michel Bardiaux

2004-01-23, 5:10 pm

Alexey A. Kiritchun wrote:

quote:

>
>
> You could set up a pool of database handles in parent, and supply
> childs with handles from this pool. Handle being used is marked as
> such until the child exits, then returned to "unused" pool.
>
> You could also grow the pool dynamically, adding new connection
> handles when it is empty (or even somewhat beforehand, when it is
> 'nearly empty').
>
> I have written a couple of programs in this way (using MySQL), and it
> works. OTOH, I have heard that OCI's behind-the-scene work causes
> problems with this setup (on_exit routines or smth like this).
>