obkv测试与调研，看能否替代Redis
TableAPI 提供了对表模型数据的操作接口。同时，在内部，TableAPI 定义了客户端和数据库服务端之间的一组通用的交互协议。
1、前言：
随着公司业务服务器慢慢云化，redis云化也提上了日程，但云redis是很贵的，当前业务规模的云redis每月费用达几万元，是否有一种数据库能替代redis的功能？于是就有了这篇测试文章， TableAPI文档地址
2、部署正式版OB4.1
配置文件
user:
username: admin
1. password: your password if need
key_file: /data/home/admin/.ssh/id_rsa
port: 52898
oceanbase-ce:
servers:
1. Please don't use hostname, only IP can be supported
- 192.168.28.53
global:
1. The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
home_path: /data/home/admin/observer
1. The directory for data storage. The default value is $home_path/store.
data_dir: /data/home/admin/data
1. The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
redo_dir: /data/home/admin/redo
1. Please set devname as the network adaptor's name whose ip is in the setting of severs.
1. if set severs as "127.0.0.1", please set devname as "lo"
1. 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
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
1. 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"
1. connection_url: "/tmp/data.db?cache=shared&_fk=1"
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
1. 等待一分钟后验证：
[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}
1. 验证：
[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的服务，验证业务逻辑。
