[rtg] Oracle driver
Brian T. O'Neill
btoneill at misplaced.net
Wed Jun 7 12:45:20 EDT 2006
We have a hope that Oracle will be able to handle large datasets better.
I have multigig tables and 1G+ index files....
bash-2.05# du -sh .
1.0T .
ls -la *.MYD | sort -n +4 | tail -10
-rw-rw---- 1 mysql mysql 2788637245 Jun 7 11:40 ifInErrors_37.MYD
-rw-rw---- 1 mysql mysql 2788979909 Jun 7 11:40 ifOutDiscards_37.MYD
-rw-rw---- 1 mysql mysql 2813725406 Jun 7 11:40 ifInNUcastPkts_37.MYD
-rw-rw---- 1 mysql mysql 2817745357 Jun 7 11:40 ifInUcastPkts_37.MYD
-rw-rw---- 1 mysql mysql 2828126951 Jun 7 11:40 ifOutUcastPkts_37.MYD
-rw-rw---- 1 mysql mysql 2830794197 Jun 7 11:40 ifHCInMulticastPkts_37.MYD
-rw-rw---- 1 mysql mysql 2839564174 Jun 7 11:40 ifInOctets_37.MYD
-rw-rw---- 1 mysql mysql 2840566124 Jun 7 11:40 ifOutNUcastPkts_37.MYD
-rw-rw---- 1 mysql mysql 2840570387 Jun 7 11:40 ifHCOutMulticastPkts_37.MYD
-rw-rw---- 1 mysql mysql 2840668958 Jun 7 11:40 ifOutOctets_37.MYD
ls -la *.MYI | sort -n +4 | tail -10
-rw-rw---- 1 mysql mysql 1176815616 Jun 7 11:40 ifOutDiscards_37.MYI
-rw-rw---- 1 mysql mysql 1179376640 Jun 7 11:40 ifInDiscards_37.MYI
-rw-rw---- 1 mysql mysql 1189960704 Jun 7 11:40 ifInNUcastPkts_37.MYI
-rw-rw---- 1 mysql mysql 1191654400 Jun 7 11:40 ifInUcastPkts_37.MYI
-rw-rw---- 1 mysql mysql 1193325568 Jun 7 11:40 ifOutUcastPkts_37.MYI
-rw-rw---- 1 mysql mysql 1194449920 Jun 7 11:40 ifHCInMulticastPkts_37.MYI
-rw-rw---- 1 mysql mysql 1198147584 Jun 7 11:40 ifInOctets_37.MYI
-rw-rw---- 1 mysql mysql 1198570496 Jun 7 11:40 ifOutNUcastPkts_37.MYI
-rw-rw---- 1 mysql mysql 1198614528 Jun 7 11:40 ifOutOctets_37.MYI
-rw-rw---- 1 mysql mysql 1201298432 Jun 7 11:40 ifHCOutMulticastPkts_37.MYI
Quoting Leech, Jonathan (jleech at virtela.net) from :
> Brian,
>
> I am not sure of any performance difference. It should be comparable to writing to a seperate MySQL or Postgres instance. We are doing it for ease of integration with the rest of our systems - reporting infrastructure, backups, etc.
>
> -Jonathan
>
> -----Original Message-----
> From: Brian T. O'Neill [mailto:btoneill at misplaced.net]
> Sent: Tuesday, June 06, 2006 10:13 PM
> To: Leech, Jonathan
> Cc: rtg at fireflynetworks.net
> Subject: Re: [rtg] Oracle driver
>
>
> Any idea on performance difference between Oracle and MySQL for this
> type of data? We've been debating trying Oracle but haven't had time to
> write the driver. Having a corporate license to run as many instances of
> Oracle at no additional cost is a nice plus to giving it a shot for on
> our side.
>
> Brian
>
> Quoting Leech, Jonathan (jleech at virtela.net) from :
> > Here is the working code for an Oracle database, using OCI. It creates a connection pool with the same number of connections as RTG threads, but can be easily modified to use fewer connections.
> >
> > librtgoci.c
> > ----------------------
> > /*
> > * RTG Oracle OCI database driver
> > */
> >
> > #include "rtg.h"
> > #include <oci.h>
> > #include <sys/param.h>
> >
> > // thread-specific global variable
> > pthread_key_t key;
> >
> > // only call the thread-specific variable setup once
> > pthread_once_t once = PTHREAD_ONCE_INIT;
> >
> > // holds the config info for all the threads
> > config_t *set;
> >
> > // Oracle handles
> > static OCIError *errhp;
> > static OCIEnv *envhp;
> > static OCICPool *poolhp;
> >
> > // stores the Oracle connection pool name and length
> > static OraText *poolName;
> > static sb4 poolNameLen;
> >
> > /* Max,Min, and increment connections */
> > static ub4 conMin = 1;
> > static ub4 conIncr = 1;
> >
> > // log any Oracle errors
> > void checkerr(errhp, status)
> > OCIError *errhp;
> > sword status;
> > {
> > text errbuf[512];
> > sb4 errcode = 0;
> >
> > switch (status)
> > {
> > case OCI_SUCCESS:
> > break;
> > case OCI_SUCCESS_WITH_INFO:
> > debug(LOW, "Error - OCI_SUCCESS_WITH_INFO\n");
> > break;
> > case OCI_NEED_DATA:
> > debug(LOW, "Error - OCI_NEED_DATA\n");
> > break;
> > case OCI_NO_DATA:
> > debug(LOW, "Error - OCI_NODATA\n");
> > break;
> > case OCI_ERROR:
> > (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
> > errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
> > debug(LOW, "Error - %.*s\n", 512, errbuf);
> > break;
> > case OCI_INVALID_HANDLE:
> > debug(LOW, "Error - OCI_INVALID_HANDLE\n");
> > break;
> > case OCI_STILL_EXECUTING:
> > debug(LOW, "Error - OCI_STILL_EXECUTE\n");
> > break;
> > case OCI_CONTINUE:
> > debug(LOW, "Error - OCI_CONTINUE\n");
> > break;
> > default:
> > break;
> > }
> > }
> >
> > /* variable cleanup function */
> > void killkey(void *target) {
> > free(target);
> >
> > // destroy the connection pool
> > checkerr(errhp, (sword)OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
> > checkerr(errhp, OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_CPOOL));
> > checkerr(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
> > }
> >
> > /* called when library loads */
> > void __attribute__ ((constructor)) dl_init(void) {
> > }
> >
> > /* this gets called once */
> > void my_makekey() {
> > /* this shouldn't fail, and we're too early on to report errors */
> > pthread_key_create(&key, killkey);
> >
> > // create the connection pool
> > OCIEnvCreate (&envhp, OCI_THREADED, (dvoid *)0, NULL, NULL, NULL, 0, (dvoid *)0);
> >
> > (void)OCIHandleAlloc((dvoid *)envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **) 0);
> >
> > (void)OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_CPOOL, (size_t) 0, (dvoid **) 0);
> >
> > checkerr(errhp, OCIConnectionPoolCreate(envhp,
> > errhp, poolhp, &poolName, &poolNameLen,
> > set->dbdb, (sb4)strlen((const signed char *)set->dbdb),
> > conMin, set->threads, conIncr,
> > set->dbuser, (sb4)strlen((const signed char *)set->dbuser),
> > set->dbpass, (sb4)strlen((const signed char *)set->dbpass),
> > OCI_DEFAULT)
> > );
> > OCIThreadProcessInit();
> > checkerr(errhp, OCIThreadInit(envhp, errhp));
> > }
> >
> > int __db_test() {
> > return TRUE;
> > }
> >
> > /*
> > * check the status of the connection
> > * we don't try and reconnect because this is sometimes used to confirm a disconnect
> > */
> > int __db_status() {
> > sword lstat = OCI_SUCCESS;
> > char buf[100];
> >
> > OCISvcCtx *svchp = pthread_getspecific(key);
> > checkerr(errhp, lstat = OCIServerVersion(svchp, errhp, buf, 100, OCI_HTYPE_SVCCTX));
> > if (lstat == OCI_SUCCESS) {
> > return TRUE;
> > } else {
> > return FALSE;
> > }
> > }
> >
> > int __db_connect(config_t *config) {
> > // initialize the key and connection pool once
> > set = config;
> > pthread_once(&once, my_makekey);
> >
> > // connect to Oracle
> > OCISvcCtx *svchp = (OCISvcCtx *)0;
> > checkerr(errhp, OCILogon2(envhp, errhp, &svchp,
> > (CONST OraText *)config->dbuser, (ub4)strlen((const signed char *)config->dbuser),
> > (CONST OraText *)config->dbpass, (ub4)strlen((const signed char *)config->dbpass),
> > (CONST OraText *)poolName, (ub4)poolNameLen, OCI_CPOOL)
> > );
> >
> > // store the Oracle session in thread-specific storage
> > pthread_setspecific(key, svchp);
> >
> > return __db_status();
> > }
> >
> > int __db_disconnect() {
> > OCISvcCtx *svchp = pthread_getspecific(key);
> > checkerr(errhp, OCILogoff((dvoid *) svchp, errhp));
> > return TRUE;
> > }
> >
> > int __db_insert(char *table, int iid, unsigned long long insert_val, double insert_rate) {
> > char *query;
> > OCISvcCtx *svchp = pthread_getspecific(key);
> > OCIStmt *stmthp = (OCIStmt *)0;
> > sword lstat;
> >
> > asprintf(&query, "INSERT INTO %s (id,dtime,counter) VALUES (%i,sysdate,%llu)", table, iid, insert_val);
> >
> > debug(HIGH, "Query = \"%s\"\n", query);
> >
> > OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
> >
> > checkerr(errhp, lstat = OCIStmtPrepare (stmthp, errhp, (CONST OraText *)query, (ub4)strlen((const signed char *)query), OCI_NTV_SYNTAX, OCI_DEFAULT));
> >
> > checkerr(errhp, lstat = OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0, (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT));
> >
> > checkerr(errhp, lstat = OCITransCommit(svchp, errhp, (ub4)0));
> >
> > checkerr(errhp, lstat = OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT));
> >
> > free(query);
> >
> > if (lstat == OCI_SUCCESS) {
> > return TRUE;
> > } else {
> > return FALSE;
> > }
> > }
>
> > _______________________________________________
> > RTG mailing list
> > RTG at fireflynetworks.net
> > http://fireflynetworks.net/mailman/listinfo/rtg
>
>
> --
> btoneill at misplaced.net
>
> ****************************************************************************
> UNIX is simple and coherent, but it takes a genius (or at any rate a
> programmer) to understand and appreciate the simplicity." - Dennis Ritchie
> ****************************************************************************
>
> _______________________________________________
> RTG mailing list
> RTG at fireflynetworks.net
> http://fireflynetworks.net/mailman/listinfo/rtg
--
btoneill at misplaced.net
****************************************************************************
UNIX is simple and coherent, but it takes a genius (or at any rate a
programmer) to understand and appreciate the simplicity." - Dennis Ritchie
****************************************************************************
More information about the RTG
mailing list