Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大,国内顶级PostgreSQL数据库专家将悉数到场,并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:
- Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
- Postgres-XL的项目发起人Mason Sharp
- pgpool的作者石井达夫(Tatsuo Ishii)
- PG-Strom的作者海外浩平(Kaigai Kohei)
- Greenplum研发总监姚延栋
- 周正中(德哥), PostgreSQL中国用户会创始人之一
- 汪洋,平安科技数据库技术部经理
- ……
|
|
本文主要是针对Oracle的测试, PostgreSQL的测试和测试结果请参考上一篇:
Oracle版本为12c。硬件和软件环境与上一篇测试PostgreSQL的保持一致,同一台主机,测试时关闭其他任何有干扰的服务和软件。
调整Oracle参数,创建测试表,生成测试数据:
SQL> create tablespace tbs_digoal datafile '/data01/oradata/tbs_digoal' size 30G autoextend off;SQL> create tablespace tbs_digoal_idx datafile '/data02/oradata/tbs_digoal_idx' size 30G autoextend off;
SQL> create user digoal identified by digoal default tablespace tbs_digoal;SQL> grant resource,connect,dba to digoal;SQL> grant unlimited tablespace to digoal;
SQL> alter system set processes=2000 scope=spfile;SQL> alter system set SGA_TARGET=24G scope=spfile;SQL> alter system set PGA_AGGREGATE_TARGET=1G scope=spfile;SQL> alter system set db_keep_cache_size=10G scope=spfile;SQL> alter system set CURSOR_SHARING=force scope=spfile;SQL> alter system set commit_logging='batch' scope=spfile;SQL> alter system set commit_write='nowait' scope=spfile;
SQL> shutdown immediateSQL> startup
SQL> conn digoal/digoalSQL> create table tbl (id int,info varchar2(256),crt_time date) tablespace tbs_digoal;
插入测试数据:
declareid int := 1;beginloopexit when id>50000000;insert into digoal.tbl nologging select id,sysdate,sysdate from dual;id := id+1;end loop;commit;end;/
-- 或declaremaxrecords constant int := 50000000;beginfor id in 1..maxrecords loopinsert into digoal.tbl nologging select id,sysdate,sysdate from dual;end loop;dbms_output.put_line('成功导入数据!');commit;end;/
-- 或SQL> insert into digoal.tbl nologging select rownum,sysdate,sysdate from dual connect by level <=50000000;
创建主键,将数据加载到内存。
SQL> alter table digoal.tbl add constraint tbl_pkey primary key(id) using index tablespace tbs_digoal_idx;SQL> alter table digoal.tbl storage (buffer_pool KEEP);SQL> alter index digoal.tbl_pkey storage (buffer_pool KEEP);
修改profile
# vi /etc/profileexport ORACLE_BASE=/opt/oracle/productexport ORACLE_HOME=$ORACLE_BASE/12.0.2/db_1export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport PATH=$ORACLE_HOME/bin:$PATH
# . /etc/profile
安装python oracle驱动
# wget https://pypi.python.org/packages/source/c/cx_Oracle/cx_Oracle-5.1.3.tar.gz#md5=cd6ff16559cbc9c20087ec812c7092ab# tar -zxvf cx_Oracle-5.1.3.tar.gz# cd cx_Oracle-5.1.3# python setup.py install
python测试脚本:
# vi test.py
import threading
import time
import cx_Oracle
import random
dsn=cx_Oracle.makedsn('127.0.0.1', 1521, 'oradb')
xs=12000
tps=dict()
class n_t(threading.Thread): # The timer class is derived from the class threading.Thread
def __init__(self, num):
threading.Thread.__init__(self)
self.thread_num = num
def run(self): #Overwrite run() method, put what you want the thread do here
db=cx_Oracle.connect('digoal','digoal',dsn)
db.autocommit=1
cur=db.cursor()
pre=cur.prepare('update /*SQL' + str(self.thread_num) + '*/ tbl set info=sysdate,crt_time=sysdate where id=:id')
tps[self.thread_num] = dict()
f = open("/tmp/oracle_test." + str(self.thread_num), "w")
for x in range(1,3001):
start_t = time.time()
for i in range(0,xs):
cur.execute(pre,{'id':random.randrange(1,50000000)})
stop_t = time.time()
tps[self.thread_num][x] = round(xs/(stop_t-start_t),2)
res = "Round: " + str(x) + " TID: " + str(self.thread_num) + " Sec: " + str(round((stop_t-start_t),2)) + " tps: " + str(tps[self.thread_num][x])
print >> f, res
f.flush()
f.close()
def test():
t_names = []
for i in xrange(0,26):
t_names.append(n_t(i))
for t in t_names:
t.start()
return
if __name__ == '__main__':
test()
更新(26 conn)测试:
# nohup python ./test.py >/dev/null 2>&1 &
测试结果,约10秒输出一次tps:
# less /tmp/oracle_test.22Round: 1 TID: 22 Sec: 11.46 tps: 1187.12Round: 2 TID: 22 Sec: 11.34 tps: 1198.65.....
其他线程结果略。
测试结果合并:
# vi tps.pyimport fileinput
file = dict()line = dict()tps=0
for i in xrange(0,26):file[i] = open("/tmp/oracle_test." + str(i))while 1:for i in xrange(0,26):line[i] = file[i].readline().split(" ",8)[7]tps = tps + float(line[i])
print(str(tps))tps=0
if not line[0]:breakfor i in xrange(0,26):file[i].close()
# python tps.py33792.5729093.9233370.6733768.333750.533458.8630580.3233369.79......
图:
查询(36 conn)测试:
测试脚本:
# vi test.py
import threading
import time
import cx_Oracle
import random
dsn=cx_Oracle.makedsn('127.0.0.1', 1521, 'oradb')
xs=12000
tps=dict()
class n_t(threading.Thread): # The timer class is derived from the class threading.Thread
def __init__(self, num):
threading.Thread.__init__(self)
self.thread_num = num
def run(self): #Overwrite run() method, put what you want the thread do here
db=cx_Oracle.connect('digoal','digoal',dsn)
db.autocommit=1
cur=db.cursor()
pre=cur.prepare('select /*SQL' + str(self.thread_num) + '*/ * from tbl where id=:id')
tps[self.thread_num] = dict()
f = open("/tmp/oracle_test." + str(self.thread_num), "w")
for x in range(1,3001):
start_t = time.time()
for i in range(0,xs):
cur.execute(pre,{'id':random.randrange(1,50000000)})
stop_t = time.time()
tps[self.thread_num][x] = round(xs/(stop_t-start_t),2)
res = "Round: " + str(x) + " TID: " + str(self.thread_num) + " Sec: " + str(round((stop_t-start_t),2)) + " tps: " + str(tps[self.thread_num][x])
print >> f, res
f.flush()
f.close()
def test():
t_names = []
for i in xrange(0,36):
t_names.append(n_t(i))
for t in t_names:
t.start()
return
if __name__ == '__main__':
test()
测试结果:
# less oracle_test.0Round: 1 TID: 0 Sec: 13.37 tps: 897.85Round: 2 TID: 0 Sec: 11.72 tps: 1023.83Round: 3 TID: 0 Sec: 11.89 tps: 1009.26Round: 4 TID: 0 Sec: 11.82 tps: 1015.2Round: 5 TID: 0 Sec: 11.84 tps: 1013.37Round: 6 TID: 0 Sec: 11.91 tps: 1007.87Round: 7 TID: 0 Sec: 11.97 tps: 1002.33Round: 8 TID: 0 Sec: 11.96 tps: 1003.0Round: 9 TID: 0 Sec: 11.92 tps: 1007.11Round: 10 TID: 0 Sec: 11.75 tps: 1020.97Round: 11 TID: 0 Sec: 11.75 tps: 1021.17Round: 12 TID: 0 Sec: 11.95 tps: 1004.07Round: 13 TID: 0 Sec: 11.81 tps: 1015.96......
合并结果:
36637.7136436.3436512.4736614.6936585.2636553.5536578.8936540.2936483.5336594.2936438.5336518.6436480.38......
图:
插入(20 conn)测试:
SQL> truncate table digoal.tbl;SQL> create sequence digoal.seq cache 12000;SQL> alter table digoal.tbl modify crt_time default sysdate;SQL> alter table digoal.tbl modify info default sysdate;SQL> alter table digoal.tbl storage (buffer_pool default);
SQL> alter index digoal.tbl_pkey storage (buffer_pool default);
测试脚本:
# vi test.pyimport threadingimport timeimport cx_Oracleimport random
dsn=cx_Oracle.makedsn('127.0.0.1', 1521, 'oradb')
xs=12000tps=dict()
class n_t(threading.Thread): # The timer class is derived from the class threading.Threaddef __init__(self, num):threading.Thread.__init__(self)self.thread_num = num
def run(self): #Overwrite run() method, put what you want the thread do heredb=cx_Oracle.connect('digoal','digoal',dsn)db.autocommit=1cur=db.cursor()pre=cur.prepare('insert /*SQL' + str(self.thread_num) +'*/ into tbl (id) values(seq.nextval)')
tps[self.thread_num] = dict()
f = open("/tmp/oracle_test." + str(self.thread_num), "w")
for x in range(1,3001):start_t = time.time()for i in range(0,xs):cur.execute(pre)stop_t = time.time()tps[self.thread_num][x] = round(xs/(stop_t-start_t),2)res = "Round: " + str(x) + " TID: " + str(self.thread_num) + " Sec: " + str(round((stop_t-start_t),2)) + " tps: " + str(tps[self.thread_num][x])print >> f, resf.flush()
f.close()
def test():t_names = []for i in xrange(0,20):t_names.append(n_t(i))
for t in t_names:t.start()return
if __name__ == '__main__':test()
测试结果:
# less oracle_test.19Round: 1 TID: 19 Sec: 22.98 tps: 522.25Round: 2 TID: 19 Sec: 27.12 tps: 442.54Round: 3 TID: 19 Sec: 23.8 tps: 504.2Round: 4 TID: 19 Sec: 26.47 tps: 453.35Round: 5 TID: 19 Sec: 23.63 tps: 507.83Round: 6 TID: 19 Sec: 23.78 tps: 504.72Round: 7 TID: 19 Sec: 26.02 tps: 461.13Round: 8 TID: 19 Sec: 23.9 tps: 502.02Round: 9 TID: 19 Sec: 26.22 tps: 457.67Round: 10 TID: 19 Sec: 23.48 tps: 511.14Round: 11 TID: 19 Sec: 26.89 tps: 446.31Round: 12 TID: 19 Sec: 22.46 tps: 534.37......
合并结果:
10195.188863.8410067.919167.069753.8510024.519028.9210006.428869.4910251.399202.5310048.210062.34......
图:
Oracle插入时需先写UNDO,再写redo,所以比较慢.
[其他]
1. 一开始测试脚本报错,
ORA-24550
KPEDBG_HDL_PUSH_FCPTRMAX。
解决办法 :
# cd /opt/oracle/product/12.0.2/db_1/network/admin/# cp samples/sqlnet.ora ./# vi sqlnet.ora追加 : DIAG_ADR_ENABLED=FALSEDIAG_SIGHANDLER_ENABLED=FALSEDIAG_DDE_ENABLED=FALSE
2. 测试时遇到过大量的CURSOR pin S的等待事件,通过添加注释,改写SQL解决。
例如:
pre=cur.prepare('insert /*SQL' + str(self.thread_num) +'*/ into tbl (id) values(seq.nextval)')
[参考]