TableAPI 提供了对表模型数据的操作接口。同时，在内部，TableAPI 定义了客户端和数据库服务端之间的一组通用的交互协议。
1、前言：
随着公司业务服务器慢慢云化，redis云化也提上了日程，但云redis是很贵的，当前业务规模的云redis每月费用达几万元，是否有一种数据库能替代redis的功能？于是就有了这篇测试文章， TableAPI文档地址
2、部署正式版OB4.1
配置文件
user:
username: admin
# password: your password if need
key_file: /data/home/admin/.ssh/id_rsa
port: 52898
oceanbase-ce:
servers:
# Please don't use hostname, only IP can be supported
- 192.168.28.53
global:
# The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
home_path: /data/home/admin/observer
# The directory for data storage. The default value is $home_path/store.
data_dir: /data/home/admin/data
# The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
redo_dir: /data/home/admin/redo
# Please set devname as the network adaptor's name whose ip is in the setting of severs.
# if set severs as "127.0.0.1", please set devname as "lo"
# if current ip is 192.168.1.10, and the ip's network adaptor's name is "eth0", please use "eth0"
devname: em2
mysql_port: 8881 # External port for OceanBase Database. The default value is 2881. DO NOT change this value after the cluster is started.
rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started.
zone: zone1
cluster_id: 1
# please set memory limit to a suitable value which is matching resource.
memory_limit: 18G # The maximum running memory for an observer
system_memory: 2G # The reserved system memory. system_memory is reserved for general tenants. The default value is 30G.
datafile_size: 60G # Size of the data file.
log_disk_size: 45G # The size of disk space used by the clog files.
cpu_count: 16
production_mode: false
enable_syslog_wf: false # Print system logs whose levels are higher than WARNING to a separate log file. The default value is true.
enable_syslog_recycle: true # Enable auto system log recycling or not. The default value is false.
max_syslog_file_count: 4 # The maximum number of reserved log files before enabling auto recycling. The default value is 0.
root_password: pwd@***安装和启动
[admin@node53 ~]$ obd cluster deploy obcluster -c mini-local-example.yaml
[admin@node53 ~]$ obd cluster start obcluster配置租户资源
[shaqf@cmagent021017 ~]$ mysql -h192.168.28.53 -uroot@sys -P8881 -c -A oceanbase -ppwd@***;
mysql> SELECT SVR_IP,ZONE,(CPU_CAPACITY-CPU_ASSIGNED) cpu_free,round((MEM_CAPACITY-MEM_ASSIGNED)/1024/1024/1024) mem_free_gb FROM GV$OB_SERVERS;
+---------------+-------+----------+-------------+
| SVR_IP | ZONE | cpu_free | mem_free_gb |
+---------------+-------+----------+-------------+
| 192.168.28.53 | zone1 | 15 | 12 |
+---------------+-------+----------+-------------+
1 row in set (0.00 sec)
mysql> CREATE RESOURCE UNIT unit001 MAX_CPU 10,MEMORY_SIZE '12G';
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE RESOURCE POOL pool001 UNIT='unit001',UNIT_NUM=1,ZONE_LIST=('zone1');
Query OK, 0 rows affected (0.17 sec)
mysql> CREATE TENANT IF NOT EXISTS m_bd
-> CHARSET='utf8mb4',
-> PRIMARY_ZONE='zone1',
-> RESOURCE_POOL_LIST=('pool001')
-> SET ob_tcp_invited_nodes='%';
Query OK, 0 rows affected (26.34 sec)
配置租户并创建数据库
[shaqf@cmagent021017 ~]$ mysql -h192.168.28.53 -uroot@m_bd -P8881 -c -A oceanbase;
mysql> alter user root identified by 'pwd@***' ;
Query OK, 0 rows affected (0.16 sec)
mysql> create database dw;
Query OK, 1 row affected (0.26 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dw |
| information_schema |
| mysql |
| oceanbase |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> 3、编译ob-configserver（需安装go环境）
[root@cmagent021017 codes]# git clone https://github.com/oceanbase/oceanbase.git
[root@cmagent021017 codes]# cd oceanbase/tools/ob-configserver/
[root@cmagent021017 ob-configserver]# make build
[root@cmagent021017 ob-configserver]# make build-release
[root@cmagent021017 ob-configserver]# cd rpm/
[root@cmagent021017 rpm]# bash ob-configserver-build.sh /data/codes/oceanbase/tools/ob-configserver ob-configserver
[root@cmagent021017 rpm]# ll
总用量 7656
-rw-r--r-- 1 root root 7831112 4月 20 14:58 ob-configserver-1.0.0-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 508 4月 20 14:43 ob-configserver-build.sh
-rw-r--r-- 1 root root 1100 4月 20 14:43 ob-configserver.spec
[root@cmagent021017 rpm]#安装、配置ob-configserver
[root@node53 ~]# rpm -ivh ob-configserver-1.0.0-1.el7.x86_64.rpm
[root@node53 ~]# su - admin
[admin@node53 ~]$ cd ob-configserver
[admin@node53 ob-configserver]$ pwd
/data/home/admin/ob-configserver
[admin@node53 ob-configserver]$ ll
总用量 16
drwxr-xr-x 2 admin admin 4096 4月 20 15:00 bin
drwxr-xr-x 2 admin admin 4096 4月 21 10:51 conf
drwxr-xr-x 2 admin admin 4096 4月 20 15:51 log
drwxr-xr-x 2 admin admin 4096 4月 20 14:57 run
[admin@node53 ob-configserver]$ vim conf/config.yaml
## log config
log:
level: info
filename: ./log/ob-configserver.log
maxsize: 30
maxage: 7
maxbackups: 10
localtime: true
compress: true
## server config
server:
address: "0.0.0.0:8081"
run_dir: run
## vip config, configserver will generate url with vip address and port and return it to the client
## if you don't hava a vip, use the server address and port is ok, but do not use some random value that can't be connected
vip:
address: "127.0.0.1"
port: 8080
## storage config
storage:
## database type, support sqlite3 or mysql
database_type: mysql
# database_type: sqlite3
## database connection config, should match database_type above
connection_url: "root@m_bd:pwd@***@tcp(192.168.28.53:8881)/dw?parseTime=true"
# connection_url: "/tmp/data.db?cache=shared&_fk=1"
# connection_url: "file:ent?mode=memory&cache=shared&_fk=1"
在OB里面配置ob-configserver并启动，验证
[shaqf@cmagent021017 ~]$ mysql -h192.168.28.53 -uroot@sys -P8881 -c -A oceanbase -ppwd@***;
mysql> alter system set obconfig_url = 'http://192.168.28.53:8081/services?Action=ObRootServiceInfo&ObCluster=obcluster';
Query OK, 0 rows affected (0.12 sec)
[admin@node53 ob-configserver]$ bin/ob-configserver -c conf/config.yaml
# 等待一分钟后验证：
[root@cmagent021017 rpm]# curl 'http://192.168.28.53:8081/services?Action=ObRootServiceInfo&ObCluster=obcluster&database=dw' | more
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 340 100 340 0 0 67487 0 --:--:-- --:--:-- --:--:-- 85000
{"Code":200,"Message":"successful","Success":true,"Data":{"ObClusterId":1,"ObRegionId":1,"ObCluster":"obcluster","ObRegion":"obcluster","ReadonlyRsList":[],"RsList":[{"address":"192.168.28.53:2882","r
ole":"LEADER","sql_port":8881}],"Type":"PRIMARY","timestamp":1682059676578840},"Trace":"2a10c31d4c07c7f7","Server":"121.46.28.53","Cost":2}
# 验证：
[shaqf@cmagent021017 ~]$ mysql -h192.168.28.53 -uroot@sys -P8881 -c -A oceanbase -ppwd@***;
mysql> use dw;
Database changed
mysql> show tables;
+--------------+
| Tables_in_dw |
+--------------+
| ob_clusters |
+--------------+
1 row in set (0.01 sec)
mysql> select * from ob_clusters;
+----+---------------------+---------------------+-----------+---------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | create_time | update_time | name | ob_cluster_id | type | rootservice_json |
+----+---------------------+---------------------+-----------+---------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 2023-04-21 06:47:57 | 2023-04-21 06:47:57 | obcluster | 1 | PRIMARY | {"ObClusterId":1,"ObRegionId":1,"ObCluster":"obcluster","ObRegion":"obcluster","ReadonlyRsList":[],"RsList":[{"address":"192.168.28.53:2882","role":"LEADER","sql_port":8881}],"Type":"PRIMARY","timestamp":1682059676578840} |
+----+---------------------+---------------------+-----------+---------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
创建kv数据表，其中timestamp列是为了维护数据时间，另外起任务删除过时数据：
mysql> CREATE TABLE kvdb (`key` VARCHAR(64) NOT NULL, `value` VARCHAR(1024), `timestamp` int NOT NULL,PRIMARY KEY (`key`),INDEX index_time (`timestamp`));
Query OK, 0 rows affected (0.40 sec)
mysql> insert into kvdb(`key`,`value`,`timestamp`)
-> values('key_001','{ "name":"小明", "age":30, "city":"New York"}',1681983180),
-> ('key_002','{ "name":"小红", "age":31, "city":"Beijing"}',1681983181),
-> ('key_003','{ "name":"小花", "age":33, "city":"Shanghai"}',1681983182);
Query OK, 3 rows affected (0.26 sec)
Records: 3 Duplicates: 0 Warnings: 0
4、压测
编写Java Table API 测试代码：
其中maven依赖：
<dependency>
<groupId>com.oceanbase</groupId>
<artifactId>obkv-table-client</artifactId>
<version>1.1.0</version>
</dependency>java测试代码：
public void test() throws Exception{
// import com.alipay.oceanbase.rpc.ObTableClient;
ObTableClient client = new ObTableClient();
client.setFullUserName("root@m_bd#obcluster");
client.setParamURL("http://192.168.28.53:8081/services?Action=ObRootServiceInfo&ObCluster=obcluster&database=dw");
client.setPassword("pwd@***");
client.setSysUserName("root@sys");
client.setSysPassword("pwd@***");
client.init();
Object[] key = {"key_002"};
String[] c = {"value","timestamp"};
client.addRowKeyElement("kvdb",new String[]{"key"});
Map data = client.get("kvdb",key,c);
logger.info(" data:{}", data);
}执行结果：
2023-04-21 15:44:35.056|INFO|https-jsse-nio-8089-exec-5|c.analyse.api.service.ExeTaskService|test|171| data:{value={ "name":"小红", "age":31, "city":"Beijing"}, timestamp=1681983181}
2亿条压测数据准备：
mysql> delimiter ;;
create procedure proc_insert_kv(in n int)
begin
declare i int;
set i=1;
while(i<=n)do
insert into dw.kvdb select MD5(i),'{"apid":"32344566423","chid":31,"gcid":1232,"aid":43256,"mid":212,"tid":32543,"pid":23423,"mn":2}',UNIX_TIMESTAMP(NOW());
set i=i+1;
end while;
end;;
Query OK, 0 rows affected (0.37 sec)
mysql> delimiter ;
mysql> call proc_insert_kv(20009000);
准备压测用的Java table api
@Test
public void test() throws Exception{
// import com.alipay.oceanbase.rpc.ObTableClient;
long startTime = System.currentTimeMillis();
ObTableClient client = new ObTableClient();
client.setFullUserName("root@m_bd#obcluster");
client.setParamURL("http://192.168.28.53:8081/services?Action=ObRootServiceInfo&ObCluster=obcluster&database=dw");
client.setPassword("pwd@***");
client.setSysUserName("root@sys");
client.setSysPassword("pwd@***");
client.init();
String[] c = {"value","timestamp"};
client.addRowKeyElement("kvdb",new String[]{"key"});
List<String> keyList = new ArrayList<>();
Random rand = new Random();
//两亿里面取两万个key
int size = 20000;
int max =200000000;
for (int i = 0; i < size; i++) {
keyList.add(md5(rand.nextInt(max)+""));
}
int j = 0;
long toDb = System.currentTimeMillis();
for (String keyStr : keyList) {
Object[] key = {keyStr};
Map data = client.get("kvdb",key,c);
if(data !=null && data.size()>0){
if(j%5000 == 0){
logger.info(" data:{}", data);
long end = System.currentTimeMillis();
logger.info("## data size: {}, deal time: {} ms, insert into db time: {} ms, qps:{}",j,toDb-startTime,end-toDb,j*1000/(end-toDb));
}
j = j+1;
}
}
long endTime = System.currentTimeMillis();
logger.info("#### data size: {}, deal time: {} ms, insert into db time: {} ms, qps:{}",j,toDb-startTime,endTime-toDb,j*1000/(endTime-toDb));
}测试输出：
2023-04-24 08:44:16.023|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|70| data:{value={"apid":"102550","chid":404,"gcid":356,"aid":263,"mid":62,"tid":430,"pid":19,"mn":86}, timestamp=1682240799}
2023-04-24 08:44:16.023|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|72|## data size: 0, deal time: 139 ms, insert into db time: 16 ms, qps:0
2023-04-24 08:44:20.435|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|70| data:{value={"apid":"126818","chid":622,"gcid":7637,"aid":256,"mid":162,"tid":783,"pid":853,"mn":94}, timestamp=1682240798}
2023-04-24 08:44:20.436|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|72|## data size: 5000, deal time: 139 ms, insert into db time: 4429 ms, qps:1128
2023-04-24 08:44:24.107|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|70| data:{value={"apid":"101834","chid":625,"gcid":5183,"aid":221,"mid":722,"tid":747,"pid":307,"mn":9}, timestamp=1682240799}
2023-04-24 08:44:24.108|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|72|## data size: 10000, deal time: 139 ms, insert into db time: 8101 ms, qps:1234
2023-04-24 08:44:27.942|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|70| data:{value={"apid":"152073","chid":366,"gcid":5862,"aid":325,"mid":335,"tid":425,"pid":57,"mn":61}, timestamp=1682240797}
2023-04-24 08:44:27.943|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|72|## data size: 15000, deal time: 139 ms, insert into db time: 11936 ms, qps:1256
2023-04-24 08:44:31.434|INFO|https-jsse-nio-8089-exec-10|c.analyse.api.service.ExeTaskService|test|78|#### data size: 20000, deal time: 139 ms, insert into db time: 15427 ms, qps:1296
2023-04-24 08:44:44.771|INFO|https-jsse-nio-8089-exec-2|c.a.api.controller.ExeTaskController|test|53|size 20000
5、结论
单线程吞吐达到预期。接下来将准备封装为多线程并替代redis的服务，验证业务逻辑。
