曾经对postgres和mysql用的比较熟,sqlite是一个小型文件数据库,没啥可提的。oracle还未曾用过
最近正好要做一个数据库性能测试,研究了下oracle的c、c++的API,找到一个不错的开发库,ocilib.
在网上看了点文章,知道ocilib是基于oci的c开发库,其性与oci一致,而且入库时批量入库的方法能够达到很高性能,别人测试每秒能上万条,我这边测试了下,绑定CPU,两个应用,也就是说启动两个应用同时入库,运行在不通的CPU上,每分钟能到70多万条,也超过了上万条每秒。而且入库表的字段为29个。
下面说说ocilib的编译安装。
首先下载ocilib的源码包,我用的是ocilib-3.6.0-gnu.tar.gz
解压缩后,进入ocilib-3.6.0目录;在configure之前需要导出oracle的相关环境变量,什么意思捏?
也就是说,你当前要安装ocilib的机器上必须有oci的环境,包括lib和include目录,lib包含了静态库和so动态库。include包含了头文件。
以我的机器为例:oracle的安装目录为:/opt/oracle
那么首先要导出两个环境变量:
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2.0
然后进行configure
./configure --prefix=/usr/local/ocilib3.6
--with-oracle-lib-path=/opt/oracle/product/10.2.0/lib
我把它安装在了/usr/local/ocilib3.6目录下。
等configure完成后
make
make install
就安装完成了。
然后在/etc/ld.so.conf中添加一行ocilib的lib路径:
/usr/local/ocilib3.6/lib
执行ldconfig命令。
使用ocilib进行开发。
先说说Makefile的写法,看一个例子就知道了,毋须多说:
INC=-I/usr/local/ocilib3.6/include -D__LINUX__ -m64
LIBS=-L/opt/oracle/product/10.2.0/lib -lclntsh
-L/usr/local/ocilib3.6/lib -locilib
default:
g++ -c $(INC) export.cpp -g
g++ -c $(INC) utils.cpp -g
g++ -c $(INC) IniHelper.cpp -g
g++ -o export export.o utils.o
IniHelper.o $(LIBS) clean:
rm -fr *.o export
下来看一个使用select语句的例子:
export.c
#include
static OCI_Connection *cn = NULL;
static OCI_Statement *st = NULL;
static
OCI_Error *err = NULL;
static OCI_Resultset *rs =
NULL;
int init_db(void)
{
#ifdef __LINUX__
int code =
1;
if
(!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT |
OCI_ENV_CONTEXT))
{
cout << "OCI_Initialize"
<< endl;
return -1;
}
cn =
OCI_ConnectionCreate(db, user, pass,
OCI_SESSION_DEFAULT);
if (!cn)
{
cout << "OCI_ConnectionCreate"
<< endl;
return -1;
}
st =
OCI_StatementCreate(cn);
if
(!st)
{
cout << "OCI_StatementCreate"
<< endl;
return -1;
}
#endif
return
1;
}
int do_task(time_t start, time_t
end)
{
#ifdef __LINUX__
int code =
1;
char
sql[512] = {0};
sprintf
(sql,
"select evt.eventtime, evt.eventtypeid, evt.src_ip, evt.src_port,
evt.dst_ip, evt.dst_port, evt.devid, area.area_code, sr.name,
sr.reverse from sign_event_data evt, area_code_t area, sign_rule sr
where area.controller_id = evt.devid and sr.rule_id =
evt.eventtypeid and evt.eventtime
>= %d and evt.eventtime < %d", start,
end);
//OCI_ExecuteStmtFmt(st, MT("select * from
sign_event_data where inout_id
> %i"), code);
OCI_ExecuteStmtFmt(st, MT(sql));
if
(debug)
printf ("Run
SQL:%sn", sql);
rs =
OCI_GetResultset(st);
//
time
//
typeid
//src
ip
//src
port
//dst
ip
//dst
port
//devid
//arear
code
//event
name #define
intv(a,b) OCI_GetInt(a,b)
#define
strv(a, b) OCI_GetString(a,b)
int index =
0;
while (OCI_FetchNext(rs))
{
unsigned int time =
intv(rs,1);
unsigned int type =
intv(rs,2);
unsigned int
sip =
intv(rs,3);
unsigned int sport = intv(rs,4);
unsigned int
dip =
intv(rs,5);
unsigned int dport = intv(rs,6);
unsigned int devid = intv(rs,7);
string area = strv(rs,8);
string evtname = strv(rs,9);
unsigned int revs =
intv(rs,10);
}
注意一点,ocilib的连接中,只需要填写数据库,用户名,密码,没有主机这一项,因为数据库包含了主机信息,因为你可以这样写:db=192.168.0.1:1521/ORCL
再给一个insert的例子:
#define MAX_ITE 50
int recid[MAX_ITE];
int devid[MAX_ITE];
int inoutid[MAX_ITE];
int tupleid[MAX_ITE];
int eventid[MAX_ITE];
char srcip[MAX_ITE][18];
int thread_func(void* data)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Error *err;
int
i;
time_t tm ;
if
(!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT |
OCI_ENV_CONTEXT))
return EXIT_FAILURE;
cn
= OCI_ConnectionCreate("orcl", "venus", "venus",
OCI_SESSION_DEFAULT);
printf
("connect db success...n");
st =
OCI_StatementCreate(cn);
printf ("create statment success...n");
OCI_Prepare(st,
"insert into
netids_eventlog( RECID, DEV_ID, INOUT_ID, TUPLE_ID, EVENTTYPEID,
SRCIP, SIP_COUNTRY, SIP_CITY, SIP_ISP,"
" SIP_DEFINE, DSTIP, DIP_COUNTRY, DIP_CITY,
DIP_ISP, DIP_DEFINE, SRCPORT, DSTPORT, EVENTCOUNT,"
" STORE_FLAG, DEFINE_FLAG, SOURCE_ISP_ID, RETURNINFO, CAP_FILE,
IPLOCATE,"
"
CAPFILENAMELEN, PARAMLEN, PARAM, FIVETUPLESET, EVENTTIME)"
" values (recid.nextval, :dev_id, :inout_id, :tuple_id,
:eventtypeid, :srcip, :sip_country, :sip_city, :sip_isp,
:sip_define, :dstip, :dip_country, :dip_city, :dip_isp,
:dip_define, :srcport,"
" :dstport,:eventcount, :store_flag, :define_flag,:source_isp_id,
:returninfo, "
":cap_file, :iplocate, :capfilenamelen, :paramlen, :param,
:fivetupleset, to_date(:eventtime, 'yyyy-mm-dd
hh24:mi:ss'))");
OCI_BindArraySetSize(st, MAX_ITE);
//devid
OCI_BindArrayOfInts(st, ":dev_id", (int*) devid, 0);
//2 inout id
OCI_BindArrayOfInts(st, ":inout_id", (int*) inoutid, 0);
//3 tupleid
OCI_BindArrayOfInts(st, ":TUPLE_ID", (int*) tupleid, 0);
//4 event id
OCI_BindArrayOfInts(st, ":EVENTTYPEID", (int*) eventid,
0);
OCI_BindArrayOfStrings(st, ":SRCIP", (char*) srcip, 17,
0);
for( i = 0; i < MAX_ITE;
i++)
{
//age[i] = 20 + i;
//sprintf (sname[i], "person %d", i);
recid[i] = i +1;
devid[i] = 123;
inoutid[i] = 1000;
tupleid[i] = 1;
eventid[i] = 2;
sprintf (srcip[i], "192.168.1.1");
}
if (!OCI_Execute(st))
{
printf("Number of DML array errors : %dn",
OCI_GetBatchErrorCount(st));
err = OCI_GetBatchError(st);
while (err)
{
printf("Error at row %d : %sn",
OCI_ErrorGetRow(err), OCI_ErrorGetString(err));
err = OCI_GetBatchError(st);
}
}
//printf("row processed : %dn",
OCI_GetAffectedRows(st));
OCI_Commit(cn);
OCI_Cleanup();
切记字符串插入时,如果你的字符串长度为N,在绑定数组时一定要写N-1的长度