[Date Prev][Date Next]
[Chronological]
[Thread]
[Top]
RE: back-sql improvements (was: Slapd frontend performance issues )
Eric & Dmitry,
Disclaimer :-) LOL : These routines are provided so that you can see the
parameters we used to set up
connections etc... There is no guarantee as to the suitability of this code
for blah, blah, blah
Note that at least one of these calls was missing from the Oracle provided C
(Scott, Tiger) examples (makes you wonder)...
//-------------------------------------------------------------------------
// ConnectToServer
//
// Description: Connects to Oracle
// Parameters: OCI handles
// Returns: error code of last error
//-------------------------------------------------------------------------
int ConnectToServer( OCISession **authp,OCIEnv **envhp, OCIError
**errhp,OCIStmt **stmthp,OCIStmt **stmthp1,
OCIServer **srvhp,OCISvcCtx **svchp, cDBConnection
*pConnection)
{
int nRC=DB_SUCCESS;
if (pConnection)
{ // Clear connection down bit (could be a re-try) :-)
pConnection->nStateBits=(pConnection->nStateBits)&(~nConnectionDown);
}
if( OCIInitialize((ub4) OCI_THREADED, (dvoid *)0,(dvoid * (*)(dvoid *,
size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *,
size_t))0,
(void (*)(dvoid *, dvoid *)) 0 ))
{ nRC= DB_SQL_INITIALIZE_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
HandleError(nRC,(char *)__FILE__,__LINE__,(char *)__DATE__,(char
*)__TIME__);
goto Cleanup;
}
if( OCIEnvInit( (OCIEnv **) envhp, OCI_DEFAULT, (size_t) 0, (dvoid **) 0
))
{ nRC= DB_SQL_ENV_INIT_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
HandleError(nRC,(char *)__FILE__,__LINE__,(char *)__DATE__,(char
*)__TIME__ );
goto Cleanup;
}
if( OCIHandleAlloc( (dvoid *) *envhp, (dvoid **) errhp, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0))
{ nRC= DB_SQL_ALLOC_HANDLE_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
HandleError(nRC,(char *)__FILE__,__LINE__,(char *)__DATE__,(char
*)__TIME__);
goto Cleanup;
}
// Server contexts
if( ChkErr(*errhp,__LINE__, OCIHandleAlloc( (dvoid *) *envhp, (dvoid **)
srvhp, OCI_HTYPE_SERVER,
(size_t) 0, (dvoid **) 0)))
{ nRC= DB_SQL_ALLOC_HANDLE_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
if( ChkErr(*errhp,__LINE__, OCIHandleAlloc( (dvoid *) *envhp, (dvoid **)
svchp, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0)))
{ nRC= DB_SQL_ALLOC_HANDLE_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
if( ChkErr(*errhp,__LINE__, OCIServerAttach( *srvhp, *errhp, (text
*)pConnection->pszDataSourceName,
strlen(pConnection->pszDataSourceName), OCI_DEFAULT)))
{ nRC= DB_SQL_ATTACH_SERVER_FAILED;
goto Cleanup;
}
// set attribute server context in the service context
if( ChkErr(*errhp,__LINE__, OCIAttrSet( (dvoid *) *svchp,
OCI_HTYPE_SVCCTX, (dvoid *) *srvhp, (ub4) 0,
OCI_ATTR_SERVER, (OCIError *)
*errhp)))
{ nRC= DB_SQL_SET_ATTRIB_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
if( ChkErr(*errhp,__LINE__, OCIHandleAlloc((dvoid *) *envhp, (dvoid
**)authp, (ub4) OCI_HTYPE_SESSION,
(size_t) 0, (dvoid **) 0)))
{ nRC= DB_SQL_ALLOC_HANDLE_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
if( ChkErr(*errhp,__LINE__, OCIAttrSet((dvoid *) *authp, (ub4)
OCI_HTYPE_SESSION,
(dvoid *)pConnection->pszUserID, (ub4)
strlen((char *)pConnection->pszUserID),
(ub4) OCI_ATTR_USERNAME, *errhp)))
{ nRC= DB_SQL_SET_ATTRIB_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
if( ChkErr(*errhp,__LINE__, OCIAttrSet((dvoid *) *authp, (ub4)
OCI_HTYPE_SESSION,
(dvoid *) pConnection->pszPassword,
(ub4) strlen((char *)pConnection->pszPassword)
,
(ub4) OCI_ATTR_PASSWORD, *errhp)))
{ nRC= DB_SQL_SET_ATTRIB_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
if( ChkErr(*errhp,__LINE__, OCISessionBegin ( *svchp, *errhp, *authp,
OCI_CRED_RDBMS,
(ub4) OCI_DEFAULT)))
{ nRC= DB_SQL_BEGIN_SESSION_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
if( ChkErr(*errhp,__LINE__, OCIAttrSet((dvoid *) *svchp, (ub4)
OCI_HTYPE_SVCCTX,
(dvoid *) *authp, (ub4) 0, (ub4)
OCI_ATTR_SESSION, *errhp)))
{ nRC= DB_SQL_SET_ATTRIB_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
// Allocate statements
if( ChkErr(*errhp,__LINE__, OCIHandleAlloc( (dvoid *) *envhp, (dvoid **)
stmthp,
OCI_HTYPE_STMT, (size_t) 0,
(dvoid **) 0)))
{ nRC= DB_SQL_ALLOC_STMT_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
if( ChkErr(*errhp, __LINE__, OCIHandleAlloc( (dvoid *) *envhp, (dvoid **)
stmthp1,
OCI_HTYPE_STMT, (size_t) 0,
(dvoid **) 0)))
{ nRC= DB_SQL_ALLOC_STMT_FAILED;
pConnection->nStateBits=(pConnection->nStateBits)|(nConnectionDown);
goto Cleanup;
}
Cleanup:
return nRC;
}
Here is the code I've used to disconnect:
//-------------------------------------------------------------------------
// Cleanup
//
// Description: Disconnect from Oracle
// Parameters: OCI handles
// Returns: void
//-------------------------------------------------------------------------
void Cleanup(OCIEnv *envhp, OCIError *errhp,OCIStmt *stmthp,OCIStmt
*stmthp1,
OCIServer *srvhp,OCISvcCtx *svchp, OCISession *authp)
{
if ((svchp)&&(errhp)&&(authp))
ChkErr(errhp, __LINE__, OCISessionEnd (svchp, errhp, authp,
OCI_DEFAULT));
if (stmthp)
ChkErr(errhp, __LINE__, OCIHandleFree((dvoid *) stmthp,
OCI_HTYPE_STMT));
if (stmthp1)
ChkErr(errhp, __LINE__, OCIHandleFree((dvoid *) stmthp1,
OCI_HTYPE_STMT));
if (errhp)
ChkErr(errhp, __LINE__, OCIServerDetach( srvhp, errhp, OCI_DEFAULT ));
if (srvhp)
ChkErr(errhp, __LINE__, OCIHandleFree((dvoid *) srvhp,
OCI_HTYPE_SERVER));
if (svchp)
ChkErr(errhp, __LINE__, OCIHandleFree((dvoid *) svchp,
OCI_HTYPE_SVCCTX));
if (errhp)
ChkErr(errhp, __LINE__, OCIHandleFree((dvoid *) errhp,
OCI_HTYPE_ERROR));
if (envhp)
ChkErr(errhp, __LINE__, OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV ));
if (authp)
ChkErr(errhp, __LINE__, OCIHandleFree((dvoid *) authp,
OCI_HTYPE_SESSION));
return;
}
Here is a routine that does numeric queries:
//-----------------------------------------------------------------------
// DbPerformSQLNumericFetch
//
// Description: DBAL function that performs a single numeric fetch request
to the SQL database.
// This is a 32 bit signed fetch. Valid data range is
-2147483647 -> 2147483647
// Parameters: SQL strings to perform fetch, column to bind, pointer to
fetched value
// and connection pointer
//
// Returns: return code of last error
//-----------------------------------------------------------------------
int DbPerformSQLNumericFetch(char * pszSQLCount,char * pszSQLCmd, int
nColumn, int *pnFetchData,
cDBConnection *pConnection)
{
int nRC=DB_SUCCESS;
long nRowCount=0;
OCISession *authp = (OCISession *) 0;
OCIEnv *envhp;
OCIServer *srvhp;
OCIError *errhp;
OCISvcCtx *svchp;
OCIStmt *stmthp, *stmthp1;
OCIDefine *defnp = (OCIDefine *) 0;
if (pConnection->nOptionBits&nPermanentConnection)
{ // If this is a permanent connection then use existing connection
authp = pConnection->pOCI->authp;
envhp = pConnection->pOCI->envhp;
srvhp = pConnection->pOCI->srvhp;
errhp = pConnection->pOCI->errhp;
svchp = pConnection->pOCI->svchp;
stmthp = pConnection->pOCI->stmthp;
stmthp1= pConnection->pOCI->stmthp1;
defnp = pConnection->pOCI->defnp;
}
else
{ // else connect to DB server, initialize handles, statements, do error
handling...
nRC=ConnectToServer(&authp,&envhp,&errhp,&stmthp,&stmthp1,&srvhp,&svchp,pCon
nection);
if (nRC!=DB_SUCCESS)
{
goto Cleanup;
}
}
if(pszSQLCount)
{
// Prepare the count statement
if( ChkErr(errhp, __LINE__, OCIStmtPrepare(stmthp1, errhp, (unsigned
char *) pszSQLCount,
(ub4) strlen((char *)
pszSQLCount),
(ub4) OCI_NTV_SYNTAX, (ub4)
OCI_DEFAULT)) )
{ nRC= DB_SQL_PREPARE_STMT_FAILED;
goto Cleanup;
}
// Bind the input variable
if( ChkErr(errhp, __LINE__, OCIDefineByPos(stmthp1, &defnp, errhp,1 ,
(dvoid *) &nRowCount,
(sword) sizeof(sword),
SQLT_INT, (dvoid *) 0, (ub2 *)0,
(ub2 *)0, OCI_DEFAULT)))
{ nRC= DB_SQL_BIND_COL_FAILED;
goto Cleanup;
}
// execute and fetch
nRC = ChkErr(errhp,__LINE__,
OCIStmtExecute(svchp, stmthp1, errhp, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot
*) NULL,OCI_DEFAULT), pszSQLCount);
if (nRC == DB_SQL_NO_DATA)
{
nRC=DB_SQL_FETCH_FAILED;
goto Cleanup;
}
// verify that if we have a command that they are operating on unique
entries
if ( (nRowCount > 1) && (pszSQLCmd) )
{ // verify that nRowCount is correct
nRC= DB_SQL_DUPLICATE_ENTRY_FOUND;
HandleError(nRC, (char *)__FILE__,(char *) __DATE__,(char *)
__TIME__,(char *) "Duplicate rows in database\n")
;
goto Cleanup;
}
if ((nRowCount == 0)&&(pszSQLCmd))
{ // if nRowCount == 0 and we have a command then return error
nRC= DB_SQL_DATA_ENTRY_NOT_FOUND;
goto Cleanup;
}
}
if (pszSQLCmd)
{
// prepare the statement
if( ChkErr(errhp, __LINE__, OCIStmtPrepare(stmthp, errhp, (unsigned
char *) pszSQLCmd,
(ub4) strlen((char *)
pszSQLCmd),
(ub4) OCI_NTV_SYNTAX, (ub4)
OCI_DEFAULT)) )
{ nRC= DB_SQL_PREPARE_STMT_FAILED;
goto Cleanup;
}
// bind the input variable
if( ChkErr(errhp, __LINE__, OCIDefineByPos(stmthp, &defnp, errhp,
nColumn, (dvoid *) pnFetchData,
(sword) sizeof(sword),
SQLT_INT, (dvoid *) 0, (ub2 *)0,
(ub2 *)0, OCI_DEFAULT)))
{ nRC= DB_SQL_BIND_COL_FAILED;
goto Cleanup;
}
// execute and fetch
nRC = ChkErr(errhp,__LINE__,
OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot
*) NULL, OCI_DEFAULT), pszSQLCmd);
if (nRC == DB_SQL_NO_DATA)
{
nRC=DB_SQL_FETCH_FAILED;
}
}
else
{ // if there is no command return the row count
*pnFetchData=nRowCount;
}
Cleanup:
if (nRC!=DB_SUCCESS)
{
HandleError(nRC,(char *)__FILE__,(char *)__DATE__,(char *)__TIME__ );
}
if (
(!(pConnection->nOptionBits&nPermanentConnection))&&(!(pConnection->nStateBi
ts&nConnectionDown)) )
{ // if this connection is not permanent and is not down then cleanup
(free) connection
Cleanup(envhp,errhp, stmthp, stmthp1, srvhp, svchp, authp);
}
return (nRC);
}
I would also try performing different queries, for example some very
simple numeric queries to see if the problem is still there. I looked in
google.com and this could
be related to varchar2 type queries or other things like using a version
7.x.x client w/ a version 8.x.x
server. The only semaphore code you should need is in the routines that
provide a cDBConnection to the client from
the connection pool.
You could have some kind of relationship in the connection pool to the ldap
handle of the client that way
when the client does an ldap disconnect you can release the OCI connection
as well from the pool.
At that point the connection is marked as free in the pool and a Mutex
should
be used. The connection pool could be in the format of an array of linked
lists. Hope you
don't mind the MS like Hungarian notation (an old habit that is hard for me
to break...:-).
Regards,
Tomas
-----Original Message-----
From: Eric T. Blue [mailto:openldap@star-cs.com]
Sent: Monday, April 30, 2001 6:43 PM
To: Arredondo, Tomas; Dmitry Kovalev
Cc: openldap-devel@OpenLDAP.org
Subject: RE: back-sql improvements (was: Slapd frontend performance
issues)
Tomas,
Thanks for your suggestions. This is what I figured (obviously only
1
thread using the connection at a time), but I get strange results if I do
not place mutexes on the OCI code. For some reason, after about 1,000
queries (with 2 client running in parallel) the server gets put in a locked
state. After looking through the debug log, the OCI error received is
ORA-03106. Unfortunately, here is Oracle's comment on this result code:
ORA-03106 fatal two-task communication protocol error
Cause: The communication path between Oracle and the user task has stopped.
This is an internal error message not usually issued.
Action: Contact Oracle Customer Support.
If I'm locking out the connection while it is in use, I'm not sure why this
would cause an issue.
-----Original Message-----
From: owner-openldap-devel@OpenLDAP.org
[mailto:owner-openldap-devel@OpenLDAP.org]On Behalf Of Arredondo, Tomas
Sent: Monday, April 30, 2001 10:16 AM
To: 'Eric T. Blue'; Dmitry Kovalev
Cc: openldap-devel@OpenLDAP.org
Subject: RE: back-sql improvements (was: Slapd frontend performance issues)
Eric,
What I think may be the problem is that it looks like the code that does the
work (eg. ...... DO OCI specific fcns and return results to client) is
mutexed out. Meaning that only one user (thread or process or whatever)
gets to it at one time.
A solution is to lock and unlock the mutex around the conn_inuse flag check
and set code. But not around the DO OCI specific functions code because
that is where a good portion of the time is spent.
If you can make an association between the ldap user making this request and
one of the connections from your pool that you pick for him then you can
lock the code around your mutexes but not around the DO OCI specific fcns
code. Hope this helps.
Thanks,
Tomas
-----Original Message-----
From: Eric T. Blue [mailto:openldap@star-cs.com]
Sent: Friday, April 27, 2001 5:41 PM
To: Arredondo, Tomas; Dmitry Kovalev
Cc: openldap-devel@OpenLDAP.org
Subject: RE: back-sql improvements (was: Slapd frontend performance
issues)
Dmitry & Tomas,
I've spent a little time over the last couple weeks, and have a
barebones
OCI backend working. The performance looks very good thus far(~ 500
searches / sec), but I'd still like to do the connection pool. Currently, I
initiate 1 persistent OCI connection on db_init and subsequently perform
searches using the same cursor/handle (obviously locking via a mutex). For
some reason, and it may be my lack of understanding of the underlying
OpenLDAP code, utilizing multiple OCI connections doesn't seem to contribute
towards speed in any way. Here's the basic setup:
The speed is fairly fast, since there is only support for equality searches
right now. Basically, a filter of uid=test translates to "select col1,col2
.. colz from table where (uid='username')".
============= SNIPPETS START HERE =============
// back-oci.h
#define CONNECT_POOL_SIZE 10
typedef struct {
int conn_inuse;
int conn_ok;
Lda_Def lda;
Cda_Def cda;
ub4 hda[HDA_SIZE/(sizeof(ub4))];
}backoci_db_conn;
backoci_db_conn *dbc[CONNECT_POOL_SIZE];
ldap_pvt_thread_mutex_t dbconn_mutex;
// init.c
I do a loop to init the cursor, data area, etc.
sprintf(connect_string,"%s/%s@%s",si->dbuser,si->dbpasswd,si->dbname);
for (i=0; i <= CONNECT_POOL_SIZE; i++) {
dbc[i]=(backoci_db_conn*)ch_calloc(1,sizeof(backoci_db_conn));
dbc[i]->conn_inuse = 0;
/* Authenticate to Oracle */
if (olog(&dbc[i]->lda, (ub1 *)dbc[i]->hda, connect_string, -1,
0, -1, (text *) 0, -1, (ub4)OCI_LM_DEF)) {
Debug(LDAP_DEBUG_TRACE,"backoci_db_open(): Oracle connection
failed, exiting\n",0,0,0);
return 1;
}
/* Open a cursor, exit on error (unrecoverable). */
if (oopen(&dbc[i]->cda, &dbc[i]->lda, (text *) 0, -1, -1, (text *)
0, -1)) {
Debug(LDAP_DEBUG_TRACE,"backoci_db_open(): Oracle - can't
open cursor\n",0,0,0);
ologof(&dbc[i]->lda);
return 1;
}
}
ldap_pvt_thread_mutex_init(&dbconn_mutex);
// search.c
for (i=0; i <= CONNECT_POOL_SIZE; i++) {
if (dbc[i]->conn_inuse == 0) {
freeconn = i;
break;
}
}
dbc[freeconn]->conn_inuse = 1;
ldap_pvt_thread_mutex_lock(&dbconn_mutex);
...... DO OCI specific fcns and return results to client
dbc[freeconn]->conn_inuse = 0;
ldap_pvt_thread_mutex_unlock(&dbconn_mutex);
============= SNIPPETS STOP HERE =============
Is this a problem with thread serialization, or the mutex? My logic may be
slightly flawed here, but I'm sure the solution is simple. I'd appreciate
any input or suggestions.
-----Original Message-----
From: owner-openldap-devel@OpenLDAP.org
[mailto:owner-openldap-devel@OpenLDAP.org]On Behalf Of Arredondo, Tomas
Sent: Tuesday, April 10, 2001 10:05 AM
To: 'mitya@seismic.ru'
Cc: openldap-devel@OpenLDAP.org
Subject: RE: back-sql improvements (was: Slapd frontend performance issues)
Hi Dmitry,
Comments inline.
Regards,
Tomas
-----Original Message-----
From: Dmitry Kovalev [mailto:mitya@seismic.geol.msu.ru]
Sent: Tuesday, April 10, 2001 6:52 AM
To: Arredondo, Tomas
Cc: openldap-devel@openldap.org
Subject: back-sql improvements (was: Slapd frontend performance issues)
Hello Tomas!
As this thread eventually spinned down to back-sql, I thought it would be
reasonable to rename it...
I think two separate threads are appropriate - one about back-sql - specific
issues, and another - about slapd frontend profiling.
Also, if you have no objections - let us transfer posts that have more than
one
recipient to the list.
As I understand, it is what Kurt suggested - use -devel list for
development.
Kurt, if you think that too much back-sql specifics is bad for this list - I
could arrange a temporal list for this, and post only results to
openldap-devel.
"Arredondo, Tomas" wrote:
> I think that in an optimal environment it should exceed 100 reads/second.
did you test the "packaged" back-sql, or you made some optimizations? what
was
the data you tested on?
[TA] The OCI data access library has been tested to go about 100 reads/sec
with a different application. I have not integrated this with back-sql or
OpenLDAP and so I could
not tell you how fast an OCI version of back-sql could be. I would think it
should be pretty
close unless there is a massive amount of processing going on in back-sql.
>
> There are two pieces to the code that I could help with. One is a set of
> routines that can make db calls to read and write data using connections.
>
> The other is a set of connection related classes that can manage
connection
> handles from
>
> OCI and other formats such as ODBC concurrently.
>
> These connection classes are things we can call 'dbo', 'sets' and
> 'connections'.
[description skipped]
as I undestand, it could be used
1) to use direct APIs (lightweight wrapper)
2) to "aggregate" data from different RDBMSes in single slapd
[TA] Right, the 'dbo', 'sets' and 'connections' are a potential way to
abstract different connections
to different RDBMSes in a single slapd. These are currently used with OCI
but I tested them
previously with ODBC (a couple of years ago). OCI and ODBC are from a
connection point of
view very similar so a lightweight wrapper could use this abstraction to so
that depending on the
connection type the underlying direct API would change but to the caller it
would be transparent
except for the connection type used to create the 'key' (OCI, ODBC,...).
Am I right? It is very interesting, anyway - as soon as we manage to
summarize
what each of the contributers can offer, we can develop a little "plan" of
incorporating new code in appropriate order...
[TA] Sounds great. I'm flexible, any plan is better than none! :-)
WBW, Dmitry