Example of SrvPool and SesPool usage about ora HOT 19 CLOSED

They are separate tools for the separate goals:

SesPool is great for reusing sessions where you open/close them frequently.
This would be enough, but Oracle does not allow concurrent execution on the very same connection (srv), so you have to use separate srvs for real concurrency (long execution of querys).

For convenience, there's NewEnvSrvSes, which returns a Ses, an Srv and the Env in one call;
For session pooling, use NewSesPool(srv)
If you need real concurrency, create a handful of Srvs (by hand or with env.NewSrvPool), and create a separate SesPool for each, and use them as needed.

If you know how to write this into the README in a conscious way, I'd be thankful!
If you know what kind of API would help you with these pooling issues, I'd glad to provide those functions!

from ora.

If I understand correctly, pooling sessions is just to avoid frequent closing and opening of sessions for sequential execution on the same connection. I am planning to use the pool behind a very high load (40K/s requests, not all db ofcourse) web server so I need every ounce of concurrency I can get. Which means I need SrvPool.

As for your API needs question, I think what most of the users of driver actually need is to use oracle specific features like in out variables, procedure calls an so on, on a database/sql kind of seamless pool. Basically the ideal thing would be to have a single db instance on which I can prepare, execute, create transactions and query the db without dealing with connection creation or closing.

I will try to implement a usable and hopefully general solution with the current tools in rana/ora and maybe I will have better opinion on how to edit the README after that.

Thanks

from ora.

I've tried earlier a "Pool" which managed the sessions and connections behind some easy api, but failed miserably: how to decide which SesPool (on which srv) shall the new ses be Get from?
Maybe some kind of "Executing" flag should be set: when an Exe is in progress (haven't returned yet), even the session creation blocks on that srv, where the executing ses is!

So careful locking is needed, to allocate a new srv if all existing srv is in "Executing" state, and that state flag must be managed well.

Maybe a Pool which returns a PooledSes for Get(), and that PooledSes has the same interface as Ses, except that if manages the "Executing" flag, and maybe just Put()s on Close() ?

But this fails also: when I Get() a PooledSes, and then another, the second will be on the same Srv, as it is not "Executing" (yet). Then if I Exe on the first, the second will block, also!
If I Get() these on separate Srv's, they won't block each other!

Maybe some lazy allocation could help, but this is where I left it as too complex...

from ora.

I know what you are describing it is very complicated to manage db side pooling.

When I was trying to implement a similar pool for goracle, I needed to adapt a lot of the pool code from database/sql. But that only pooled connections and not sessions (cursors in goracle). To be able to do that I kept a maxCursorPerConnection limit, and didn't remove a connection from the pool as busy if the number of cursors opened on it did not reach that limit. And when a single cursor was closed on that connection I returned it to the pool. A similar strategy, namely pooling only connections and creating a predefined number of sessions on those connections when they are requested, cuts back on some of that complexity.

The main difficulty I am having with the current pool implementation in rana is I cannot limit the maximum number of open connections, and if I somehow do there is no way of waiting for a connection to free up from a goroutine, it simply returns nil.

Would you like to discuss implementing a pool a more similar to database/sql, I can send you the goracle pool implementation that I am currently using and I am sure with your expertise we can tidy it up into a merge in the future. Please let me know if you are interested.

from ora.

There is also some good pool implementations from a postgres driver go-pg. This library is also interusable between database/sql and with its own pool.

from ora.

I've checked go-pg, but that's only a network connection pool. Session pooling is somewhere else - and for me that's the biggest problem: how to manage the srv and ses allocations.

from ora.

As I've thought about it, I'd need some advice for the following:
a Ses occupies its Srv for the duration of Exe, so a Get() must choose another Srv in this case.
If no more free Srvs in the pool, it must wait for one release by a Ses.

Maybe a buffered channel, with the capacity of the SrvPool's capacity?
Ses.Exe should take a token from that channel, and put back on finish?

from ora.

Interesting question, a buffered channel seems like a solution.
Maybe give more thought until it is crystal clear?
Or, write the buffered channel and refine as needed?

On Tue, May 31, 2016 at 9:30 PM, Tamás Gulácsi [email protected]
wrote:

As I've thought about it, I'd need some advice for the following:
a Ses occupies its Srv for the duration of Exe, so a Get() must choose
another Srv in this case.
If no more free Srvs in the pool, it must wait for one release by a Ses.

Maybe a buffered channel, with the capacity of the SrvPool's capacity?
Ses.Exe should take a token from that channel, and put back on finish?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#76 (comment), or mute
the thread
https://github.com/notifications/unsubscribe/AMGYwVnKdvBJfseOKr9INOGNKYfgQzjGks5qHQrSgaJpZM4H0_6D
.

from ora.

Both are viable options.
Yet, the details are a little hazy: ses.exe needs this accounting, but that'd mead it has to know about the pool it's part of. Or it should just tell this to its Srv?
Or shall this be a channel on Srv, which is nil (or big enough) by default, and set by the pool on the Srvs handled?

from ora.

Hmm, started some MVP, but stucked right at the beginning: is this possible at all?

If I pool.Get() a session, then it is allocated on an idle Srv. If I don't use this session right on, then a second pool.Get() should allocate this second session on the same Srv - why would it allocate another Srv?

So now I have two Ses from the same Srv. If I start a long running Exe on the first Ses, then it occupies the Srv, and blocks the second Ses, too!

Maybe the Get() could return a Ses-like interface, which allocates the underlying Ses on the first Exe. What about Prep?

The easiest seems to maintain a 1-1 relationship between Srv and Ses: every Ses is allocated on a new Srv, and no other Ses is allocated on that Srv. The Ses goes back to an idlePool, and on its eviction/close, its Srv goes back to the Srv idlePool.
This allows parallel execution, and hopefully reuse of Ses and Srv as far as possible.

from ora.

PTAL "pool" branch, pool.go, NewPool

from ora.

Hi @omerkirk can you take a look at Pool in pool.go? I want to merge the "pool" branch in the near future, and would like to have it reviewed before!

from ora.

Sorry, I've merged it already - wanted to say "I want to publish a new release (3.7) soon".

from ora.

Hi @tgulacsi I checked the pool.go file. From what I understand a singe pool object consists of both a session and connection pool. A get tries to get a session from the session pool, if not found tries to create a session on a connection from the connection pool else it creates a new session on a new connection.

You keep 1 session per 1 connection which makes it easier to keep track of idle connections when any given session is closed. The "sesSrvPB" struct is very useful, I didn't create a struct like that in my own dirty implementation and it made things crazy hard to keep track of.

I have just 2 issues with the current implementation please correct me if I am wrong.
1- It seems there is no simple way to limit the maximum number of connections open, which is one of the most requested features made to the db/sql package.

2- Can this 1-1 relationship be connected to configuration, for greater control over pool dynamics. And delegate your concerns above to the library user.

I would put the first issue as a deal breaker for my own applications, since they are very high load web applications and without an upper limit I am just going to crash the application. But the second issue is more like a feature request that can be dealt with based on feedback in the future releases.

On a side note I am assuming now both the SesPool and SrvPool that are in pool.go will be depreciated for Pool right? Since I couldn't figure out where would they be useful if you happen to release the Pool struct.

from ora.

1. absolutely yes: this just allows a reuse of sessions/connections, no limit on number of maximum concurrent connections. But I think this is easier from outside: for example go4.org/syncutil.Gate is a general solution.
2. this 1-1 relationship not just helps to track the idle connections, but allows the concurrent usage - as only one OCIStmtExecute can be in progress on a connection (not session, connection!), this is a must, if we want reliable concurrent usage.

I'd leave SesPool and SrvPool there (at least for middle-term), as they help implementing different (not 1-1) relationships, maybe better algorithms, which I don't see ATM.

For a web application, I'd use go4.org/syncutil.Gate at some higher level to limit db concurrency; or a simple buffered channel can limit the number of concurrent connections.

But you can persuade me to add an upper limit to the Pool :)

from ora.

(2) You are right 1-1 relationship is what most of the libraries does anyway. But for a read heavy application it would be great to have the choice to increase session per connection though.

(1) Let me try to persuade you then :)
1- I think it would be best to leave this in the driver then to let it be handled in different and sometimes wrong methods.

2- The implementation isn't simple but can be broken down.
a) pool.Get() - First 2 cases are the same. On the third case where there is a need to create new connection, check if number of open connections would be over the upper limit.
b) If not - open new connection
c) If it would be, create conn request object that contains a response channel where the client will be waiting for a connection
d) Add this request to an array or a list
e) when returning a connection to the pool check this list first
e1) if the request list not empty, pop a request send the connection from to channel
e2) if empty - return to the pool

Of course there is a need for a lot of accounting keeping track of the number of open connections, number of requests, number of pending open requests and of course lot of locking. So some part of the above steps need to be synhcronized with a separate goroutine.

The following code from db/sql package contains a good implementation for it. It is opinionated though and I am sure there are other ways to do it.
Pool code starts from line 710.

from ora.

This seems to be too complex for me - I can barely write the current idle
pool correctly (seel the latest commits)...

If you can factor out that pooling code, and provide proper tests, then we
can talk.

omerkirk [email protected] ezt írta (időpont: 2016. jún. 24., P,
11:41):

(2) You are right 1-1 relationship is what most of the libraries does
anyway. But for a read heavy application it would be great to have the
choice to increase session per connection though.

(1) Let me try to persuade you then :)
1- I think it would be best to leave this in the driver then to let it be
handled in different and sometimes wrong methods.

2- The implementation isn't simple but can be broken down.

a) pool.Get() - First 2 cases are the same. On the third case where there
is a need to create new connection, check if number of open connections
would be over the upper limit.
b) If not - open new connection
c) If it would be, create conn request object that contains a response
channel where the client will be waiting for a connection
d) Add this request to an array or a list
e) when returning a connection to the pool check this list first
e1) if the request list not empty, pop a request send the connection from
to channel
e2) if empty - return to the pool

Of course there is a need for a lot of accounting keeping track of the
number of open connections, number of requests, number of pending open
requests and of course lot of locking. So some part of the above steps need
to be synhcronized with a separate goroutine.

The following code from db/sql package contains a good implementation for
it. It is opinionated though and I am sure there are other ways to do it.
Pool code starts from line 710
https://golang.org/src/database/sql/sql.go.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#76 (comment), or mute
the thread
https://github.com/notifications/unsubscribe/AAPoSvr5O94JmkjLgNpgF2Xld2tp8Kx5ks5qO6Y5gaJpZM4H0_6D
.

from ora.

Hi omerkirk,

I punted on this feature too. The amount of complexity and time was too
much for the amount of benefit. Though I'm open to it, maybe 95%-99% of
users won't need the feature. So it may be up to you to build if you really
need it.

Best Regards,

Rana
On Jun 24, 2016 11:32 AM, "Tamás Gulácsi" [email protected] wrote:

This seems to be too complex for me - I can barely write the current idle
pool correctly (seel the latest commits)...

If you can factor out that pooling code, and provide proper tests, then we
can talk.

omerkirk [email protected] ezt írta (időpont: 2016. jún. 24., P,
11:41):

(2) You are right 1-1 relationship is what most of the libraries does
anyway. But for a read heavy application it would be great to have the
choice to increase session per connection though.

(1) Let me try to persuade you then :)
1- I think it would be best to leave this in the driver then to let it be
handled in different and sometimes wrong methods.

2- The implementation isn't simple but can be broken down.

a) pool.Get() - First 2 cases are the same. On the third case where there
is a need to create new connection, check if number of open connections
would be over the upper limit.
b) If not - open new connection
c) If it would be, create conn request object that contains a response
channel where the client will be waiting for a connection
d) Add this request to an array or a list
e) when returning a connection to the pool check this list first
e1) if the request list not empty, pop a request send the connection from
to channel
e2) if empty - return to the pool

Of course there is a need for a lot of accounting keeping track of the
number of open connections, number of requests, number of pending open
requests and of course lot of locking. So some part of the above steps
need
to be synhcronized with a separate goroutine.

The following code from db/sql package contains a good implementation for
it. It is opinionated though and I am sure there are other ways to do it.
Pool code starts from line 710
https://golang.org/src/database/sql/sql.go.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#76 (comment), or mute
the thread
<
https://github.com/notifications/unsubscribe/AAPoSvr5O94JmkjLgNpgF2Xld2tp8Kx5ks5qO6Y5gaJpZM4H0_6D

.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#76 (comment), or mute
the thread
https://github.com/notifications/unsubscribe/AMGYwee3mTos2juqNdJGJgbznJsqKGDTks5qPCLTgaJpZM4H0_6D
.

from ora.

NewPool is released as v3.7.0.
I acknowledge that this is just an idle pool, but a full pool is too much for me at the moment.

from ora.