一、案例描述
出于自身的好奇,和更好回答别人的提问————“oracle能够支持多少个TPS”?于是进行了如下测试。
二、问题
Oracle高并发Insert事务分区表与常规表性能差异多少?
三、设计
3.1 数据设计
设计具有相同结构的常规表和分区表,两条索引,具体如下:
- 常规表
create table qq_sale (so_no varchar(10),product_name varchar(10),qty number) tablespace cbs;
alter table qq_sale add constraint pk_so_no primary key(so_no) using index tablespace cbsinx;
create index idx_qq_sale_02 on qq_sale(last_updated_date) tablespace cbsinx;
create sequence seq_qq_sale maxvalue 99999999999999 cache 1000 noorder;
- 分区表,采用单号HASH分区,分20个区。
CREATE TABLE DBAHQQ.qq_sale_part
(
so_no VARCHAR2(10),
product_name VARCHAR2(10),
qty NUMBER,
last_updated_date DATE
)
NOCOMPRESS
RESULT_CACHE (MODE DEFAULT)
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
PARTITION BY HASH (so_no)
(
PARTITION qq_sale_part_P1
TABLESPACE CBS,
PARTITION qq_sale_part_P2
TABLESPACE CBS,
PARTITION qq_sale_part_P3
TABLESPACE CBS,
PARTITION qq_sale_part_P4
TABLESPACE CBS,
PARTITION qq_sale_part_P5
TABLESPACE CBS,
PARTITION qq_sale_part_P6
TABLESPACE CBS,
PARTITION qq_sale_part_P7
TABLESPACE CBS,
PARTITION qq_sale_part_P8
TABLESPACE CBS,
PARTITION qq_sale_part_P9
TABLESPACE CBS,
PARTITION qq_sale_part_P10
TABLESPACE CBS,
PARTITION qq_sale_part_P11
TABLESPACE CBS,
PARTITION qq_sale_part_P12
TABLESPACE CBS,
PARTITION qq_sale_part_P13
TABLESPACE CBS,
PARTITION qq_sale_part_P14
TABLESPACE CBS,
PARTITION qq_sale_part_P15
TABLESPACE CBS,
PARTITION qq_sale_part_P16
TABLESPACE CBS,
PARTITION qq_sale_part_P17
TABLESPACE CBS,
PARTITION qq_sale_part_P18
TABLESPACE CBS,
PARTITION qq_sale_part_P19
TABLESPACE CBS,
PARTITION qq_sale_part_P20
TABLESPACE CBS
)
NOCACHE
NOPARALLEL
NOMONITORING;
ALTER TABLE DBAHQQ.qq_sale_part ADD (
CONSTRAINT qq_sale_part_PK
PRIMARY KEY
(so_no)
ENABLE VALIDATE);
CREATE INDEX DBAHQQ.IDX_QQ_SALE_PART_02 ON DBAHQQ.QQ_SALE_PART
(LAST_UPDATED_DATE)
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
LOCAL (
PARTITION QQ_SALE_PART_P1
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P2
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P3
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P4
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P5
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P6
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P7
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P8
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P9
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P10
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P11
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P12
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P13
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P14
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P15
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P16
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P17
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P18
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P19
TABLESPACE CBSINX,
PARTITION QQ_SALE_PART_P20
TABLESPACE CBSINX
)
NOPARALLEL;
create index idx_qq_sale_02 on qq_sale_part(last_updated_date) tablespace cbsinx ;
3.2 测试场景设计
- 测试工具,采用python自编并发压测工具,代码如下:
- 场景描述:并发300个用户,每个用户完成10000次插入事务,不设置think_time,分别对常规表和分区表做进行压测。
- SQL语句: insert into qq_sale values (seq_qq_sale.nextval,'牙膏',1,sysdate());
3.3 压测结果
- 分区表结果
- 共完成300w个事务
- 时间: 0:04:14.432000
- TPS: 11800
- 主要等待事件(由高到底): log file sync、latch: enqueue hash chains 、library cache: mutex X 、 latch: ges resource hash list
- 常规表
- 共完成300w个事务
- 时间:0:08:31.432000
- TPS:5870
- 主要等待事件(由高到底):buffer busy waits 、enq: TX - index contention 、 latch: ges resource hash list 、log file sync
四、结论
- 单一事务,具有相同业务设计的分区表与常规表,使用分区表比常规表性能高1倍。
- 高并发事务数据库常见问题:
- 一是内存竞争问题,使用分区表把有效打散了表与索引数据块的分布,减少高并发时发生的内存冲突。
- 二是IO问题,采用高性能IO设备能够有效降低IO响应时间,如SSD, PCIE SSD等等设备。
- 三是应用设计分布,虽然在本次案例中没有针对RAC与单实例进行测试,但是这里有一个众所周知的约定,使用RAC多实例会出现GC竞争。设计的时候,需要根据分区表的平均分布在不同的实例上,按服务读取,必须要避免一个分区数据同时出现在两个实例上。
- 分区表设计索引时,必须使用local索引分区.