Oceanbase系列之–关于oceanbase 2.2的几个最你造吗
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
今天来一篇关于oceanbase比较有意思的一点内容，那就是关于ob的几个最。
通过查询Oceanbase Oracle租户模式下的建脚本，可以看到一些有意思的属性，这么我们来分别测试一下。
1. Oceanbase 支持的block size范围是多少 ？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
obclient> CREATE TABLE t1_2k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 2048 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> CREATE TABLE t1_4k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 4096 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.03 sec) obclient> CREATE TABLE t1_8k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 8192 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.03 sec) obclient> obclient> CREATE TABLE t1_32k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 32768 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> CREATE TABLE t1_64k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 65536 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> CREATE TABLE t1_128k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 131072 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> CREATE TABLE t1_256k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 262144 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> CREATE TABLE t1_512k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 524288 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.05 sec) obclient> CREATE TABLE t1_1024k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 1048576 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> CREATE TABLE t1_2048k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 2097152 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; ERROR-00600: internal error code, arguments: -5258, Invalid block size, block size should between 16384 and 1048576 obclient> |
从上述测试来看，目前Oceanbase 2.2版本最大对于blocksize的支持范围是2k-1024k；远超Oracle 的支持范围，由此也可以初步判断oceanbase的单表支持应该是非常巨大的。
另外从上面测试来看，大家可以看到,虽然提示支持的范围是16k-1024k，但实际上我测试2k-8k都可以的。这或许是一个小bug ？
突然还有个小疑问，能否支持非标准block size呢？ 测一下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
obclient> CREATE TABLE t1_3k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 3072 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> insert into t1_3k select owner,object_name from dba_objects; Query OK, 269 rows affected (0.25 sec) Records: 269 Duplicates: 0 Warnings: 0 obclient> commit; Query OK, 0 rows affected (0.00 sec) obclient> select * from t1_3k where rownum < 5; +-------+-------------------------------------------------------+ | OWNER | OBJECT_NAME | +-------+-------------------------------------------------------+ | ROGER | KILLDB | | ROGER | __idx_1100611139453779_IDX_OWNER | | ROGER | TEST_RECOVER | | ROGER | __idx_1100611139453782_RECYCLE_OBIDX_1590579268121989 | +-------+-------------------------------------------------------+ 4 rows in set (0.00 sec) |
很有意思。。。还支持3k block size. 其实还有更好玩的。。。。
|
1 2 3 4 5 6 7 |
obclient> CREATE TABLE t1_5k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 5100 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> |
应该说支持任意block size的.前面测试最大支持1024k，那么最小是多少呢 ？
|
1 2 3 4 5 |
obclient> CREATE TABLE t1_1k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 1 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) |
Omg。。。 最小支持1个byte的block size。 当然这就玩玩，生产环境肯定不能这样设置。。。那没发跑业务。
那么问题又来了，ob支持最大的单表是多大？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
obclient> CREATE TABLE t1_big ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 524288 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 8109999999999999990 PCTFREE = 10; ERROR-00600: internal error code, arguments: -4147, Invalid config obclient> CREATE TABLE t1_big ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 524288 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 8053063680000000000 PCTFREE = 10; Query OK, 0 rows affected (0.05 sec) obclient> select 8053063680000000000/1024/1024/1024/1024/1024 "PB" from dual; +-------------------+ | PB | +-------------------+ | 7152.557373046875 | +-------------------+ 1 row in set (0.00 sec) |
我简单算了一下，单表最大支持大概是7 PB左右。 也足够海量了。。。。
表参数属性跟Oracle类似吗？ 大家知道Oracle table pctfree最大可以是99，那么ob呢 ？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
obclient> CREATE TABLE t1_big ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 32768 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 50; ERROR-02211: invalid value for PCTFREE or PCTUSED obclient> CREATE TABLE t1_big ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 32768 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 49; Query OK, 0 rows affected (0.04 sec) obclient> obclient> CREATE TABLE t1_big ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 32768 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTUSED= 40; ERROR-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '= 40' at line 4 |
从测试可以看出ob2.2版本的Oracle租户模式下，pctfree最大是49。 而且ob不支持pctused参数。
那么oceanbase最大支持几个副本集呢？ 测试一下便知：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
obclient> CREATE TABLE t1_2k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 6 BLOCK_SIZE = 2048 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 8388608 PCTFREE = 10; Query OK, 0 rows affected (0.04 sec) obclient> drop table t1_2k; Query OK, 0 rows affected (0.01 sec) obclient> CREATE TABLE t1_2k ( -> "OWNER" VARCHAR2(128) DEFAULT NULL NOT NULL, -> "OBJECT_NAME" VARCHAR2(128), -> ) COMPRESS FOR ARCHIVE REPLICA_NUM = 7 BLOCK_SIZE = 2048 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 8388608 PCTFREE = 10; ERROR-00600: internal error code, arguments: -4007, Not supported feature or function obclient> |
可见目前最大支持6个副本集。
这里简单总结一下，上面一些好玩的内容：
1. Oceanbase Oracle租户模式下，对于block size的支持范围是1 byte – 1024k ;并不官方说的16k-1024k；
另外也支持非标准block size大小。比如3k，5k，甚至5100 byte之类。当然生产环境肯定不能这么玩，要跟硬件IO size对齐。
2. Oceanbase 2.2版本最大支持的单表大小大概是7 PB左右
3. Oceanbase 目前最大支持6个副本，即1个主副本，5个副副本，类似Oracle一份数据，外加5份数据冗余。
4. 对于table的存储 pctfree参数，最大值49；这与Oracle有很大不同；而且不支持pctused参数。
Leave a Reply
You must be logged in to post a comment.