暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片

「YashanDB个人版体验」探测100%自我研发的YashanDB | 10点总结

原创 大数据模型 2023-11-13
1141

前文

数据库一直是高科技领域的产品,YashanDB自称是100%研发。笔者非常好奇 ,国内大部分都是基于PG或者MySQL的基础上进行开发的,即使是群众中耳熟能详的TiDB、OceanBase以及openGauss。TiDB的存储引擎Tikv主要使用了rocksDB,而openGauss基于pg9.2.4开发,openGauss理解消化了pg9.2.4的核心代码,自我创新一套新的存储引擎、事务管理等。 OceanBase没有参与PG或MySQL,自称从零开始的数据库,里面的材质也是使用了sst作为持久化文件 。所以YashanDB的100%的自我研发,带 着这样的好奇之心调研实践了YashanDB。

调研中, YashanDB目前有个人版、标准版和企业版,支持x86、ARM、龙芯,支持单机布署、分布式布置、共享集群布置多种方式。居然同时支持Oracle模式和MySQL模式。下面以个人版的单机布署为入口,安装在X86机器,管中窥豹,领略一番。

安装

最理想的安装方式 yum install 软件包,yashandb目前没有到达这一步。

笔者尝试,首选是创建一个 yashan的普通用户,yashan用户无密钥ssh打通,打开一个WEB 服务,如下

./yasom --web --listen 192.168.153.128:9001

浏览器上输入http://192.168.153.128:9001

image.png
点击快署也可能快速完成安装布署 ,布署完成如下
image.png

设置SYS密码
yasboot cluster password set -n XXXX1234$ -c yashandb

命令登录yashandb
yasql sys/"xxxx1234$"@192.168.153.128:1688

卸装方面也简单,需要把两个目录删除,目录如下。

/home/yashan/.yasboot/

/home/yashan/yasdb_home

Oracle模式

开发者体验

//创建表空间
SQL>  create tablespace tbs_test datafile  '/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/data/tbs_test.dbf'  size 10m  autoextend  on;

Succeed.


//创建普通表 
SQL> CREATE TABLE t_yashan(
  stuNo CHAR(4),
  name  VARCHAR2(20),
  age   NUMBER(3),
  birthday DATE,
  sex VARCHAR2(5)
) tablespace  tbs_test; 

SQL> desc t_yashan;
NAME                                                             NULL?     DATATYPE
---------------------------------------------------------------- --------- ---------------------------------
STUNO                                                                      CHAR(4)
NAME                                                                       VARCHAR(20)
AGE                                                                        NUMBER(3)
BIRTHDAY                                                                   DATE
SEX                                                                        VARCHAR(5)




查看执行SQL的执行计划
 ALTER SESSION SET statistics_level=ALL;

explain  select *  from  t_yashan;

SQL> create index idx_stuNo_yashan on t_yashan(stuNo);

Succeed.


SQL> select    stuno  from t_yashan  where stuno=6;


Execution Plan
----------------------------------------------------------------
SQL hash value: 3536985664
Optimizer: ADOPT_C

+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | E - Rows | A - Rows | Cost(%CPU)  | A - Time | Loops    | Memory   | Disk     | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |          |             |          |          |          |          |                                |
|* 1 |  INDEX FAST FULL SCAN          | IDX_STUNO_YASHAN     | SYS        |       100|          |       92( 0)|          |          |          |          |                                |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+

Operation Information (identified by operation id):
---------------------------------------------------

   1 - Predicate : filter("T_YASHAN"."STUNO" = 6)




Statistics
----------------------------------------------------------------------------------------------------

14 rows fetched.


SQL> set autotrace   traceonly;

SQL> select   /*+ full(t_yashan)  */  stuno  from t_yashan  where stuno=6;


Execution Plan
----------------------------------------------------------------
SQL hash value: 1130584602
Optimizer: ADOPT_C

+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | E - Rows | A - Rows | Cost(%CPU)  | A - Time | Loops    | Memory   | Disk     | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |          |             |          |          |          |          |                                |
|* 1 |  TABLE ACCESS FULL             | T_YASHAN             | SYS        |       100|          |      152( 0)|          |          |          |          |                                |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+

Operation Information (identified by operation id):
---------------------------------------------------

   1 - Predicate : filter("T_YASHAN"."STUNO" = 6)




Statistics
----------------------------------------------------------------------------------------------------

14 rows fetched.

SQL> select /*+ no_index(t_yashan)  */ stuno  from t_yashan  where stuno=6;


Execution Plan
----------------------------------------------------------------
SQL hash value: 3678947624
Optimizer: ADOPT_C

+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | E - Rows | A - Rows | Cost(%CPU)  | A - Time | Loops    | Memory   | Disk     | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |          |             |          |          |          |          |                                |
|* 1 |  TABLE ACCESS FULL             | T_YASHAN             | SYS        |       100|          |      152( 0)|          |          |          |          |                                |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+

Operation Information (identified by operation id):
---------------------------------------------------

   1 - Predicate : filter("T_YASHAN"."STUNO" = 6)




Statistics
----------------------------------------------------------------------------------------------------

14 rows fetched.


SQL> create index idx_name_yashan on t_yashan(stuNo,name);

Succeed.

SQL> create index idx_age_yashan on t_yashan(age desc);

Succeed.


//查看表结构
SQL> SELECT dbms_metadata.get_ddl('TABLE', 'T_YASHAN') FROM dual;

DBMS_METADATA.GET_DD
----------------------------------------------------------------
CREATE TABLE "SYS"."T_YASHAN"
("STUNO" CHAR(4),
"NAME" VARCHAR(20),
"AGE" NUMBER(3, 0),
"BIRTHDAY" DATE,
"SEX" VARCHAR(5)
) PCTFREE 8 INITRANS 2 MAXTRANS 255
LOGGING
TABLESPACE "SYSTEM"
SEGMENT CREATION DEFERRED
ORGANIZATION HEAP

1 row fetched.

//创建普通表加分区
CREATE TABLE t_yashan_date(
  stuNo CHAR(4),
  name  VARCHAR2(20),
  age   NUMBER(3),
  birthday DATE,
  sex VARCHAR2(5),
  ttime  date
)partition by range(ttime)
(
partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')),
partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')),
partition p3 values less than(to_date('2022-01-01', 'YYYY-MM-DD'))
);

//查看表结构
SQL> SELECT dbms_metadata.get_ddl('TABLE', 'T_YASHAN_DATE') FROM dual;

DBMS_METADATA.GET_DD
----------------------------------------------------------------
CREATE TABLE "SYS"."T_YASHAN_DATE"
("STUNO" CHAR(4),
"NAME" VARCHAR(20),
"AGE" NUMBER(3, 0),
"BIRTHDAY" DATE,
"SEX" VARCHAR(5),
"TTIME" DATE
) PCTFREE 8 INITRANS 2 MAXTRANS 255
LOGGING
TABLESPACE "SYSTEM"
SEGMENT CREATION DEFERRED
PARTITION BY RANGE ("TTIME")
(PARTITION "P1" VALUES LESS THAN (to_date('2020-01-01', 'YYYY-MM-DD'))
PCTFREE 8 INITRANS 2 MAXTRANS 255
TABLESPACE "SYSTEM",
PARTITION "P2" VALUES LESS THAN (to_date('2021-01-01', 'YYYY-MM-DD'))
PCTFREE 8 INITRANS 2 MAXTRANS 255
TABLESPACE "SYSTEM",
PARTITION "P3" VALUES LESS THAN (to_date('2022-01-01', 'YYYY-MM-DD'))
PCTFREE 8 INITRANS 2 MAXTRANS 255
TABLESPACE "SYSTEM")
ORGANIZATION HEAP

1 row fetched.

//查询所有的表名

SELECT dbms_metadata.get_ddl('TABLE', table_name) FROM user_tables;

//增加列名
ALTER TABLE t_yashan ADD tel VARCHAR2(20);
//修改列名
ALTER TABLE t_yashan MODIFY tel VARCHAR2(30);
//删除列名
ALTER TABLE t_yashan DROP COLUMN tel;
//更改列名
ALTER TABLE t_yashan RENAME COLUMN name TO yashan_name;


CREATE USER henley IDENTIFIED BY henley;

ALTER USER henley IDENTIFIED BY "henley1234";
GRANT connect to henley;
GRANT resource to henley;

GRANT dba to henley;

image.png

功能视图

-- 序列
SQL> create sequence test_seq increment by 1 start with 50;
Succeed.

--同义词

SQL> create or replace synonym test_syn for user_tables;

Succeed.

--触发器
SQL> create table test_trigger(a int,b int);

Succeed.


--角色类型

select * from dba_roles; 


每日归档量查询

SELECT SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 AS "Size(M)",       TRUNC(completion_time)  FROM v$archived_log GROUP BY TRUNC(completion_time); 



--segment查看
SQL> select segment_name,segment_type,tablespace_name,blocks,extents,bytes/1024/1024   from user_segments  where  segment_name= 'T_YASHAN';


SEGMENT_NAME                                                     SEGMENT_TYPE          TABLESPACE_NAME                                                                 BLOCKS               EXTENTS BYTES/1024/1024
---------------------------------------------------------------- --------------------- ---------------------------------------------------------------- --------------------- --------------------- ---------------
T_YASHAN                                                         TABLE                 TBS_TEST                                                                          2560                    35              20

1 row fetched.




--线程查看
SQL> select name, thread_id,status from v$process;

NAME                                          THREAD_ID STATUS
--------------------------------- --------------------- ---------------------------------
TIMER                                              9182 Working
BUFFER_POOL                                        9183 Working
PRELOADER                                          9185 Working
PRELOADER                                          9186 Working
SMON                                               9187 Working
CKPT                                               9188 Working
DBWR                                               9189 Working
DBWR                                               9190 Working
SCHD_TIMER                                         9191 Working
TCP_LSNR                                           9192 Working
LISTENER_LOG                                       9194 Working
TCP_LSNR                                           9195 Working
TCP_LSNR                                           9196 Working
MMON                                               9197 Working
JOB_QUEUE                                          9198 Working
RD_ARCH                                            9214 Working
ARCH_DATA                                          9215 Working
HEALTH_MONITOR                                     9222 Working
PARAL_WORKER_0                                     9235 Working
PARAL_WORKER_1                                     9236 Working
LOGW                                               9238 Working
XFMR                                               9239 Working
XFMR_WORKER_0                                      9240 Working
XFMR_WORKER_1                                      9241 Working
XFMR_WORKER_2                                      9242 Working
XFMR_WORKER_3                                      9243 Working
XFMR_WORKER_4                                      9244 Working
XFMR_WORKER_5                                      9245 Working
XFMR_WORKER_6                                      9246 Working
XFMR_WORKER_7                                      9247 Working
XFMR_WORKER_8                                      9248 Working
XFMR_WORKER_9                                      9249 Working
XFMR_WORKER_10                                     9250 Working
XFMR_WORKER_11                                     9251 Working
XFMR_WORKER_12                                     9252 Working
XFMR_WORKER_13                                     9253 Working
XFMR_WORKER_14                                     9254 Working
XFMR_WORKER_15                                     9255 Working
XFMR_WORKER_16                                     9256 Working
XFMR_WORKER_17                                     9257 Working
XFMR_WORKER_18                                     9258 Working
XFMR_WORKER_19                                     9259 Working
XFMR_WORKER_20                                     9260 Working
XFMR_WORKER_21                                     9261 Working
XFMR_WORKER_22                                     9262 Working
XFMR_WORKER_23                                     9263 Working
XFMR_WORKER_24                                     9264 Working
XFMR_WORKER_25                                     9266 Working
XFMR_WORKER_26                                     9267 Working
XFMR_WORKER_27                                     9268 Working
XFMR_WORKER_28                                     9269 Working
XFMR_WORKER_29                                     9270 Working
XFMR_WORKER_30                                     9271 Working
XFMR_WORKER_31                                     9272 Working
WORKER                                            25340 Working

55 rows fetched.



-- 包与存储过程
SQL> create package my_package as
  procedure my_procedure (
    p_input_param in integer,
    p_output_param out integer
  );
end my_package;
/ 
Succeed.


create package body my_package as
  procedure my_procedure (
    p_input_param in integer,
    p_output_param out integer
  ) as
  begin
    p_output_param := p_input_param * 2;
  end my_procedure;
end my_package;
/  
Succeed.



SQL> set serveroutput on
SQL> declare
  v_output_param integer;
begin
  my_package.my_procedure(10, v_output_param);
  dbms_output.put_line('output parameter value: ' || v_output_param);
end;
PL/SQL Succeed.


--表空间
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
----------------------------------------------------------------
SYSTEM
SYSAUX
TEMP
SWAP
USERS
UNDO

--表统计

SQL> SELECT table_name,num_rows,blocks,avg_row_len,sample_size from  user_tab_statistics;

--列统计

select column_name,num_distinct,low_value,high_value,avg_col_len from  user_tab_col_statistics where table_name = 't_yashan';



业务测试

以前有一批Oracle业务表上面的SQL,大约300多个,现在把它们都移动yashandb上面进行测试

#!/bin/sh for file in `ls /home/yashan/oracle_sql/*` do echo $file >>batch.log yasboot sql -d sys/"gxxx1234$"@192.168.153.128:1688 -f $file>>batch.log done

由于语句太多,下面没有一一列举,把报错的摘录如下,出错的这些都需要改造

YAS-00004 feature "Window Function NTH_VALUE" has not been implemented yet YAS-00004 feature "Window Function Percent Rank" has not been implemented yet YAS-04243 invalid identifier "RATIO_TO_REPORT" YAS-02012 sequence does not exist YAS-02012 table or view does not exist YAS-04243 invalid identifier "UNISTR" YAS-04209 unexpected word by YAS-00207 end of text expected but b found YAS-04115 "RETURN" expected but missing YAS-04115 "IS|AS" expected but missing

系统架构

YashanDB相比mysql、pg的内存占用对比如何? 默认初始化的数据库,显然YashanDB占的内存更多。

image.png

从系统底层查看yashandb约有56个工作线程 ,具体线程名字结构与Oracle的名称差不多。

image.png

LGWR,DBWR,SMON,CKPT都是Oracle的后台进程名称,从线程名字可以知道 这些线程是干什么的。

进入到/home/yashan/yasdb_home/yashandb/23.1.1.100/lib目录,找出yas相关的so文件

image.png

nm -D libyas_infra.so.1.1.100,nm命令主要是用来列出目标文件中函数和全局变量

ldd -v libyas_infra.so.1.1.100

lsof -K | grep yashan

readelf -l libyas_infra.so.1.1.100用于显示ELF(Executable and Linkable Format)格式的可执行文件、共享库和目标文件的信息,包括头部信息、节区信息、符号表等等。

image.png

网络采集数据包,观察数据包里面有没有mysql协议或者pg相关的协议
tcpdump -i ens33 tcp and port 1688 -w test1688.cap
tcpdump: listening on ens33, link-type EN10MB (Ethernet), capture size 262144 bytes
152 packets captured
153 packets received by filter
0 packets dropped by kernel

image.png

image.png

结果没有发现mysql协议或者pg相关的协议

日志

[root@server128 yashandb]# find  /home/yashan/  -name "*log"
/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/log
/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/log/start.log
/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/log/listener/listener.log
/home/yashan/yasdb_home/yashandb/data/yashandb/db-1-1/log/alert/alert.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/log
/home/yashan/yasdb_home/yashandb/23.1.1.100/log/monit.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/log/yashandb/db-1-1/run/run.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/log/yashandb/db-1-1/slow/slow.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/ext/monit/log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasom-start.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasagent-panic.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasagent-start.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasom-panic.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasagent.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yashandb/log/yasom.log
/home/yashan/yasdb_home/yashandb/23.1.1.100/om/yasboot.log
/home/yashan/yashan/om/yasboot.log

start.log日志与实例启动的相关信息有关

image.png

listen.log监听日志可以记录用户名登录成功或失败

image.png

alert.log记录警告相关的信息。
monit.log记录yashandb程序跟踪的信。
run.log记录yashandb程序运行的信息。
slow.log记录yashandb慢日志相关的信息。

总结

明亡之后无华夏,崖山之后无中国,有人喜欢明朝那些事,没人知道崖山海战君民一心的悲壮,对yashandb的归纳总结如下。

  1. YashanDB个人版是一个单进程多线程的数据库,多线程 的工作结构类似Oracle,Oracle是多进程组织的,
  2. 目前YashanDB具有两种模式,oracle模式与mysql模式,明显当前YashanDB的精力支持oracle模式多一点,对oracle的DBA以及开发者友善,虽然很多视图和功能在开发中。mysql模式不如oracle模式。
  3. yashandb已经建立产品级的运维体系结构,例如产品 YAS-错误编码,无论是协助使用者排错还是使产品升级迭代,都大有脾益。
  4. 从线程 和占用内存以及目录结构来说,YashanDB没有站在PG或者MySQL的肩膀上进行开发的痕迹。
  5. 从内陷库文件来查看,YashanDB有很多自己定义的yas库函数,这些都是力证100%代码自主可控的证据。
  6. YashanDB的存储引擎默认创建的表是堆表组织形式,这里笔者嘴碎一下。ORACEL也是默认堆表,堆表更擅长写多读少的场景。官方称HEAP表类型,属于段页式结构,逻辑存储上与Oracle一致,因此保留Oracle原有的tablespace表空间、segment段、block数据块,extends数据区结构特色,更擅长TP,默认隔离水平是RC,DATA_BUFFER_SIZE是缓冲区容量设置。如果业务要用数据分析等需求,建议使用TAC表类型和LSC表类型。
  7. 通过show parameter查看,YashanDB一共有177个参数,而oracle12.2.0.1.0一共有500个参数,参数多少不能说明谁强大, 但是从侧明说明功能丰富程度和调整的选择空间。
  8. 从开发者体验来看,yashandb有自己的jdbc,社区指导有批量提交的api demo代码,已经能够满足应用开发者的需求进行WEB应用的开发。
  9. 从功能视图来看,日常oracle的常规运维命令,yashandb都能够支持。
  10. 从业务测试来看,YashanDB的小目标是与Oracle做到100%的兼容 ,现在来看没有Oracle所有的函数和视图,有些业务还是需要做一些YashanDB的适配。

最后

YashanDB没有选择开源,把它的存储引擎、SQL解析、并发控制和事务管理技术细节共享,但是我们可以管中窥豹,领略一番。从产品线的类型来看,从首选兼容ORACLE模式来看,从产品闭源的角度来看, YashanDB与达梦多处重合较相似。

数据库是长久的一份事业,数据库是一个世界的事业,2023年11月共283个数据库参与排名,在风云变幻的数据库市场,只有不断的研发和创新,才能提升产品竞争力。YashanDB作为少数不多有自主研究可控实力的厂商,要做大做强做精,认真务实是致胜法宝。

最后修改时间:2023-12-01 00:49:33
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论