Goracle limiting sessions
ankushgupta84 opened this issue · 15 comments
Is it possible that in certain circumstances, when one sets the db.SetMaxOpenConns(50) in database/SQL layer, there could be actually more than 50 sessions on database end since the goracle allows upto 1000 max sessions as default.
We see that we end up creating more number of sessions on DB end than the limit we had set with db/sql when we experience a spike of load and DB is slow and most calls are pre-empted by cancelling context due to timeouts.
When this happens the dbstats on the database/SQL side show that the number of idle connections are much less than what we see on the Oracle DBA end.
Our Max sessions set on Oracle DB are set to 200 and on DB/SQL end we have it set to 50. We simulated DB slowness by setting query timeouts to very low value to something like 5 sec. After a high load we see that we run out of connections on DB and db stats shows total connections to be ~10.
When this happens, our mitigation is usually killing the application. That immediately releases the connections.
I suspected that as one of the reasons. It's looks like that is being done right.
I am wondering if that were a problem. The number of connections that DB/SQL has should be inline with what Oracle DB has?
It does not have to be aligned.
But if you have too many sessions, you should limit the goracle session pool size to be less than what the DB allows, and check the used sessions.
BTW what are those sessions (on DB side) are used to? What does v$session says?
And another Oracle quirkness comes to my mind: prepared statements counts as used cursors.
I'll get that data again from the OracleDB after reproducing the issue. From what I remember, most sessions were idle sessions (~180). Had I waited for an hour (default idle timeout of goracle), the sessions would have timed out.
From what I understand that by design, goracle could have fewer sessions than DB/SQL layer as they may have timed out. Under what circumstances could goracle have more sessions than what DB/SQL would think.
Is there any documentation/past question asked I can read? If not, can you pl throw some light on session management and why the two layer's (DB/sql and goracle would have different counts for sessions)?
sql.DB is a connection pool.
Oracle also has a session pool, and we use it. See https://oracle.github.io/node-oracledb/doc/api.html#connpooling for detailed information.
Idle sessions means something is not closed. And the won't time out...
Our pattern is super simle
Opening connection:
db, err := sql.Open("goracle", connectionDescriptor)
Get calls:
ctx, cancel := context.WithTimeout(ctx, DeleteTimeout)
defer cancel()
valueRows, err := db.QueryContext(ctx, getQuery, key)
if err != nil {
return nil, failure(err, metricGetFailed, m.logger)
}
defer valueRows.Close()
Put calls:
ctx, cancel := context.WithTimeout(ctx, DeleteTimeout)
defer cancel()
_, err := db.ExecContext(ctx, m.putQuery, entry.Key, entry.Value)
if err != nil {
return failure(err, metricPutFailed, m.logger)
}
Delete calls:
ctx, cancel := context.WithTimeout(ctx, DeleteTimeout)
defer cancel()
_, err := db.ExecContext(ctx, m.deleteQuery, key)
if err != nil {
return failure(err, metricDeleteFailed, m.logger)
}
List calls:
ctx, cancel := context.WithTimeout(ctx, DeleteTimeout)
defer cancel()
valueRows, err := db.QueryContext(ctx, m.listQuery, prefix+"%")
if err != nil {
return nil, failure(err, metricListFailed, m.logger)
}
defer valueRows.Close()
When the context is cancelled for Get queries, is is possible that some layer (db/sql, goracle) fetched the results and non closure of values is resulting in leaks.
Maybe those strict Timeouts cause Break calls which does not really close the underlying connections?
Either lax those timeouts, or try ca9fd68 as it closes the connection if it's bad, really.
Thanks a lot for the fix, I ll try it out and see if that fixes the problem.
Meanwhile, how can I limit max sessions on the goracle layer:
We have connection string like:
connection_string = "user/pass@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ser_name)))"
and then use
db, err := sql.Open("goracle", connectionDescriptor)
db.SetMaxIdleConns(MaxIdleConnections)
db.SetMaxOpenConns(MaxOpenConnections)
db.SetConnMaxLifetime(MaxConnectionLifeTime)
I believe this sets the connection settings at the db/sql level and not at the goracle level which still uses 1000 as the max limit of connections.
Hello @tgulacsi. I see this commit in a master but don't see in a v2.20.1. Is it merge conflict? Please check https://github.com/go-goracle/goracle/blob/v2.20.1/conn.go#L123
It's not released yet, as I'm waiting for @ankushgupta84 to report back for the tests.