大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 YashanDB 23.1 个人版脚本安装及简单查询测试，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！
为啥会写这篇文章呢，起初是因为上篇文章测试的是崖山企业版本以及和 Oracle 12c 的性能对比，总体感觉还不错，但这几天看到有几位大佬测试的结果是 Oracle 11.2.0.4 比 YashanDB 性能要强很多，感觉和我之前测试的有出入，所以想在测一测，在体验体验，看看问题在哪是不是我出现了啥错误，而且这次把 Oracle 12c 多租户环境换成非容器 19c 单机版本来试试。
1.下载个人版 YashanDB
我这里下载 x86 架构软件包 YashanDB https://download.yashandb.com/download
2.操作系统基本信息
[root@jieke-19c ~]# lscpuArchitecture: x86_64CPU op-mode(s): 32-bit, 64-bitByte Order: Little EndianCPU(s): 8On-line CPU(s) list: 0-7Thread(s) per core: 1Core(s) per socket: 4Socket(s): 2NUMA node(s): 1Vendor ID: GenuineIntelCPU family: 6Model: 79Model name: Intel(R) Xeon(R) CPU E7-4809 v4 @ 2.10GHzStepping: 1CPU MHz: 2094.952BogoMIPS: 4189.90Hypervisor vendor: VMwareVirtualization type: fullL1d cache: 32KL1i cache: 32KL2 cache: 256KL3 cache: 20480KNUMA node0 CPU(s): 0-7Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon nopl xtopology tsc_reliable nonstop_tsc eagerfpu pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch ssbd ibrs ibpb stibp fsgsbase tsc_adjust bmi1 avx2 smep bmi2 invpcid rdseed adx smap xsaveopt arat spec_ctrl intel_stibp flush_l1d arch_capabilities[root@Ops-19cOGG ~]# cat /proc/cpuinfo |grep processor |wc -l8[root@jieke-19c ~]# free -h total used free shared buff/cache availableMem: 15G 1.1G 6.1G 6.3G 8.3G 7.9GSwap: 7.9G 16M 7.9G[root@jieke-19c ~]# df -hFilesystem Size Used Avail Use% Mounted on/dev/mapper/rootvg-lvroot 392G 314G 78G 81% /devtmpfs 7.8G 0 7.8G 0% /devtmpfs 7.8G 56K 7.8G 1% /dev/shmtmpfs 7.8G 81M 7.7G 2% /runtmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup/dev/sda1 1014M 190M 825M 19% /boottmpfs 1.6G 0 1.6G 0% /run/user/54321tmpfs 1.6G 0 1.6G 0% /run/user/0[root@jieke-19c ~]# cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.6 (Maipo)
3.系统参数调整
调整资源限制值
[root@jieke-19c ~]# echo "yashan soft nofile 65536yashan hard nofile 65536yashan soft nproc 65536yashan hard nproc 65536yashan soft rss unlimitedyashan hard rss unlimitedyashan soft stack 8192yashan hard stack 8192" >> /etc/security/limits.conf
4.创建用户和组
[root@jieke-19c ~]# groupadd YASDBA[root@jieke-19c ~]# useradd yashan -G YASDBA[root@jieke-19c ~]# echo "yashan"|passwd --stdin yashan
5.上传软件包并解压
[root@jieke-19c ~]# su - yashan[yashan@jieke-19c ~]$ mkdir soft [yashan@jieke-19c ~]$ cd soft/[yashan@jieke-19c soft]$ lltotal 171280-rw-r--r-- 1 root root 175386922 Nov 20 11:32 yashandb-personal-23.1.1.100-linux-x86_64.tar.gz[yashan@jieke-19c soft]$ tar -zxvf yashandb-personal-23.1.1.100-linux-x86_64.tar.gz [yashan@jieke-19c soft]$ lltotal 171312drwxrwxr-x 6 yashan yashan 70 Nov 9 18:16 admindrwxrwxr-x 2 yashan yashan 188 Nov 9 18:16 bindrwxrwxr-x 2 yashan yashan 152 Nov 9 18:16 confdrwxrwxr-x 5 yashan yashan 60 Nov 9 18:16 ext-rw-rw-r-- 1 yashan yashan 11632 Nov 9 18:16 gitmoduleversion.datdrwxrwxr-x 2 yashan yashan 65 Nov 9 18:16 includedrwxrwxr-x 3 yashan yashan 17 Nov 9 18:16 javadrwxrwxr-x 2 yashan yashan 4096 Nov 9 18:16 lib-rw-r----- 1 yashan yashan 14989 Nov 9 18:16 LICENSEdrwxrwxr-x 3 yashan yashan 21 Nov 9 18:16 plug-indrwxrwxr-x 2 yashan yashan 170 Nov 9 18:16 scripts-rw-r--r-- 1 root root 175386922 Nov 20 11:32 yashandb-personal-23.1.1.100-linux-x86_64.tar.gz
6.使用自带脚本快速安装并初始化库
本次只是简单体验,不进行单独目录规划及参数规划，scripts 目录下的 install.ini 文件则是一个初始化参数文件，可修改相关参数后初始化使用，我们直接使用 install.sh 脚本安装数据库软件，initDB.sh 初始化数据库实例。
[yashan@jieke-19c soft]$ cd scripts/[yashan@jieke-19c scripts]$ lltotal 32-rwxrwxr-x 1 yashan yashan 3296 Nov 9 18:16 createResourceCgroup.sh-rwxrwxr-x 1 yashan yashan 2934 Nov 9 18:16 initDB.sh-rwxrwxr-x 1 yashan yashan 2104 Nov 9 18:16 initStandby.sh-rw-rw-r-- 1 yashan yashan 411 Nov 9 18:16 install.ini-rwxrwxr-x 1 yashan yashan 1412 Nov 9 18:16 install.sh-rwxrwxr-x 1 yashan yashan 1157 Nov 9 18:16 startup.sh-rwxrwxr-x 1 yashan yashan 1157 Nov 9 18:16 stop.sh-rwxrwxr-x 1 yashan yashan 667 Nov 9 18:16 yascheckStart.sh[yashan@jieke-19c scripts]$ more install.ini[install]YASDB_HOME=~/yashandb/yasdb_homeYASDB_DATA=~/yashandb/yasdb_dataREDO_FILE_SIZE=100MREDO_FILE_NUM=4INSTALL_SIMPLE_SCHEMA_SALES=NNLS_CHARACTERSET=UTF8[instance]LISTEN_ADDR=0.0.0.0:1688DB_BLOCK_SIZE=8KDATA_BUFFER_SIZE=256MSHARE_POOL_SIZE=256MWORK_AREA_POOL_SIZE=32MLARGE_POOL_SIZE=32MREDO_BUFFER_SIZE=8MUNDO_RETENTION=300OPEN_CURSORS=310MAX_SESSIONS=1024RUN_LOG_LEVEL=INFONODE_ID=1-1:1[yashan@jieke-19c scripts]$ more install.sh#!/bin/bash#install.shFILE_PATH=$(dirname "$(readlink -f "$0")")PACKAGE_PATH=$(dirname "${FILE_PATH}")YASDB_TEMP_FILE="${FILE_PATH}/.temp.ini"YASDB_INSTALL_FILE="${FILE_PATH}/install.ini"# shellcheck disable=SC2002cat "${YASDB_INSTALL_FILE}" | grep "=" > "${YASDB_TEMP_FILE}"# shellcheck disable=SC1090source "${YASDB_TEMP_FILE}"if [ -d "$YASDB_HOME" ]; thenecho -e "install failed!\n$YASDB_HOME already exists."exit 1fiif [ -f "$YASDB_DATA"/config/yasdb.ini ]; thenecho -e "install failed!\n$YASDB_DATA used, yasdb.ini is already exists"exit 1fi##创建数据库软件目录mkdir -p "$YASDB_HOME"cp -ra "$PACKAGE_PATH"/{admin,bin,conf,gitmoduleversion.dat,include,java,lib,plug-in,scripts} "$YASDB_HOME"mkdir -p "$YASDB_HOME"/clienttouch "$YASDB_HOME"/client/yasc_service.ini##创建数据库数据目录mkdir -p "$YASDB_DATA"/{config,data,dbfiles,instance,archive,local_fs,log/{run,audit,trace,alarm,alert,listener},diag/{metadata,hm,blackbox}}##配置数据库环境变量sed -i '/'"source ${YASDB_HOME//\//\\/}\/conf\/yasdb.bashrc"'/d' ~/.bashrcYASDB_ENV_FILE="${YASDB_HOME}/conf/yasdb.bashrc"cat >"${YASDB_ENV_FILE}" <<EOFexport YASDB_HOME=$YASDB_HOMEexport YASDB_DATA=$YASDB_DATAexport PATH=\$YASDB_HOME/bin:\$PATHexport LD_LIBRARY_PATH=\$YASDB_HOME/lib:\$LD_LIBRARY_PATHEOFcat >>~/.bashrc <<EOF[ -f $YASDB_ENV_FILE ] && source $YASDB_ENV_FILEEOF[yashan@jieke-19c ~]$ more /home/yashan/soft/scripts/initDB.sh#!/bin/bash#initDB.shFILE_PATH=$(dirname "$(readlink -f "$0")")YASDB_TEMP_FILE="${FILE_PATH}/.temp.ini"INSTALL_INI_FILE="${FILE_PATH}/install.ini"YASDB_PASSWORD="yasdb_123"# shellcheck disable=SC1090source "${YASDB_TEMP_FILE}"YASDB_ENV_FILE="${YASDB_HOME}/conf/yasdb.bashrc"YASDB_HOME_BIN_PATH="${YASDB_HOME}/bin"YASDB_BIN="${YASDB_HOME_BIN_PATH}/yasdb"YASQL_BIN="${YASDB_HOME_BIN_PATH}/yasql"YASPWD_BIN="${YASDB_HOME_BIN_PATH}/yaspwd"# shellcheck disable=SC1090source "${YASDB_ENV_FILE}"if [ ! -d "$YASDB_HOME" ] || [ ! -d "$YASDB_DATA" ]; thenecho -e "Software installation \"./install.sh\" is not performed."exit 1fiif [ -f "$YASDB_DATA"/config/yasdb.ini ]; thenecho -e "init failed!\n$YASDB_DATA used, yasdb.ini is already exists"echo -e "do not reinit in $YASDB_DATA, if you want start yasdb, run \"./startup.sh\""exit 1fie_i=$(sed -n '$=' "$INSTALL_INI_FILE")s_i=$(sed -n -e '/\<instance\>/=' "$INSTALL_INI_FILE")n_i=$((s_i + 1))sed -n "${n_i},${e_i} p" "$INSTALL_INI_FILE" >>"$YASDB_DATA"/config/yasdb.ini##创建密码文件if [ ! -f "$YASDB_HOME/admin/yasdb.pwd" ]; then"$YASPWD_BIN" file="$YASDB_HOME"/admin/yasdb.pwd password="$YASDB_PASSWORD"elserm -f "$YASDB_HOME"/admin/yasdb.pwd"$YASPWD_BIN" file="$YASDB_HOME"/admin/yasdb.pwd password="$YASDB_PASSWORD"ficp "$YASDB_HOME"/admin/yasdb.pwd "$YASDB_DATA"/instance/yasdb.pwdREDOFILE="("for ((i = 0; i < "$REDO_FILE_NUM"; i++)); doif [ $i == $((REDO_FILE_NUM - 1)) ]; thenREDOFILE=${REDOFILE}"'redo${i}'"" size $REDO_FILE_SIZE)"elseREDOFILE=${REDOFILE}"'redo${i}'"" size $REDO_FILE_SIZE,"fidone##创建数据库START_LOG_FILE="$YASDB_DATA/log/start.log"rm -rf "${START_LOG_FILE}""${YASDB_BIN}" nomount -D "$YASDB_DATA" >"$START_LOG_FILE" 2>&1 &i=0while ((i < 5))dosleep 2# shellcheck disable=SC2002 disable=SC2126alive=$(cat "$START_LOG_FILE" | grep "Instance started" | wc -l)if [ "$alive" -ne 0 ]; thenecho "process started!"breakfii=$((i+1))doneif [ "$i" -eq "5" ];thenecho "start process failed. read $START_LOG_FILE"cat "$START_LOG_FILE"exit 1fi"${YASQL_BIN}" sys/$YASDB_PASSWORD >>"$START_LOG_FILE" <<EOFcreate database yasdb CHARACTER SET $NLS_CHARACTERSET logfile $REDOFILE;exit;EOFi=0while ((i < 60))dosleep 1alive=$($YASQL_BIN sys/$YASDB_PASSWORD -c "select open_mode from v\$database" | grep -c READ_WRITE)if [ "$alive" -eq 1 ]; thenecho "Database open succeed !"breakfii=$((i+1))doneif [ "$i" -eq "60" ];thenecho "Failed ! please check logfile $START_LOG_FILE ."exit 1fi##创建样例数据:salesif [ "$INSTALL_SIMPLE_SCHEMA_SALES" == 'Y' ] || [ "$INSTALL_SIMPLE_SCHEMA_SALES" == 'y' ]; then"${YASQL_BIN}" sys/$YASDB_PASSWORD -f "$YASDB_HOME"/admin/simple_schema/sales.sql >>"$START_LOG_FILE"fiexit 0####密码以二进制形式存储，不可读。根据上面 initDB.sh 文件中的 YASDB_PASSWORD 可知密码为 yasdb_123[yashan@jieke-19c ~]$ strings $YASDB_HOME/admin/yasdb.pwdS:5C8774C730A775DF857AC562BBA45B20B07C7BB8123D75F03C9FA007645B69CF5168AD585B10A5F795E0
执行 install.sh 没有输出记录，但是在当前用户家目录下生成了 yashandb 子目录，此目录下包含 yasdb_home 和 yasdb_data 目录。
[yashan@jieke-19c scripts]$ sh install.sh[yashan@jieke-19c scripts]$ cd[yashan@jieke-19c ~]$ lltotal 0drwxrwxr-x 11 yashan yashan 220 Nov 22 16:27 softdrwxrwxr-x 4 yashan yashan 42 Nov 22 17:42 yashandb[yashan@jieke-19c ~]$ cd yashandb/[yashan@jieke-19c yashandb]$ lltotal 0drwxrwxr-x 10 yashan yashan 117 Nov 22 17:42 yasdb_datadrwxrwxr-x 11 yashan yashan 152 Nov 22 17:42 yasdb_home[yashan@jieke-19c yashandb]$ du -sh *0 yasdb_data432M yasdb_home[yashan@jieke-19c yashandb]$ pwd/home/yashan/yashandb--初始化数据库实例，类似于 Oracle 的 dbca[yashan@jieke-19c scripts]$ cd /home/yashan/soft/scripts[yashan@jieke-19c scripts]$ ./initDB.shprocess started!Database open succeed !--初始化之后会生成 yasdb_data 的文件，看着目录结构和 O 记很像很像。[yashan@jieke-19c yashandb]$ lltotal 0drwxrwxr-x 11 yashan yashan 128 Nov 27 11:01 yasdb_datadrwxrwxr-x 11 yashan yashan 152 Nov 22 17:42 yasdb_home[yashan@jieke-19c yashandb]$ tree -L 3 yasdb_datayasdb_data+-- archive+-- config¦ +-- yasdb.ini+-- data+-- dbfiles¦ +-- ctrl1¦ +-- ctrl2¦ +-- ctrl3¦ +-- dwf¦ +-- redo0¦ +-- redo1¦ +-- redo2¦ +-- redo3¦ +-- swap¦ +-- sysaux¦ +-- system¦ +-- temp¦ +-- undo¦ +-- users+-- diag¦ +-- adr.pid¦ +-- blackbox¦ +-- hm¦ +-- metadata¦ ¦ +-- hm_finding¦ ¦ +-- hm_run¦ ¦ +-- incident¦ ¦ +-- problem¦ +-- trace+-- instance¦ +-- yasdb.ipc¦ +-- yasdb.pid¦ +-- yasdb.pwd+-- local_fs¦ +-- users+-- log¦ +-- alarm¦ +-- alert¦ ¦ +-- alert.log¦ +-- audit¦ +-- listener¦ ¦ +-- listener.log¦ +-- run¦ ¦ +-- run.log¦ +-- slow¦ ¦ +-- slow.log¦ +-- start.log¦ +-- trace+-- tmp21 directories, 28 files--生效环境变量[yashan@jieke-19c scripts]$ cd[yashan@jieke-19c ~]$ . ~/.bashrc
7.登录数据库
通过上述方式脚本安装的数据库，据上面脚本介绍 sys 默认密码是 yasdb_123。
[yashan@jieke-19c ~]$ yasql / as sysdbaYashanDB SQL Personal Edition Release 23.1.1.100 x86_64Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> select status,version from v$instance;STATUS VERSION------------- ----------------------------------------------------------------OPEN Personal Edition Release 23.1.1.100 x86_641 row fetched.SQL> exit[yashan@jieke-19c ~]$ yasql sys/yasdb_123 --居然不用 sysdba 方式登录YashanDB SQL Personal Edition Release 23.1.1.100 x86_64Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> exit[yashan@jieke-19c ~]$ yasql sys/yasdb_12 --错误密码无法登录YashanDB SQL Personal Edition Release 23.1.1.100 x86_64YAS-02143 invalid username/password, login deniedplease input user name:-- 关闭数据库/重启数据库SQL> shutdown immediate;Succeed.SQL> exit[yashan@jieke-19c ~]$ nohup yasdb open &[1] 980[yashan@jieke-19c ~]$ ps -ef | grep yasdbyashan 980 31966 21 11:00 pts/0 00:00:10 yasdb openyashan 1171 31966 0 11:01 pts/0 00:00:00 grep --color=auto yasdb[yashan@jieke-19c ~]$ yasql / as sysdbaYashanDB SQL Personal Edition Release 23.1.1.100 x86_64Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> select status from v$instance;STATUS-------------OPEN1 row fetched.SQL> SELECT NAME,STATUS,EXTENT_BLOCKS,ALLOCATION_TYPE,MEMORY_MAPPED,ENCRYPTED,COMPRESSED FROM V$TABLESPACE;NAME STATUS EXTENT_BLOCKS ALLOCATION_TYPE MEMORY_MAPPED ENCRYPTED COMPRESSED---------------------------------------------------------------- ----------------- ------------- --------------- ------------- --------- ----------SYSTEM ONLINE 8 AUTO FALSE FALSE FALSESYSAUX ONLINE 8 AUTO FALSE FALSE FALSETEMP ONLINE 8 UNIFORM FALSE FALSE FALSESWAP ONLINE 8 UNIFORM FALSE FALSE FALSEUSERS ONLINE 8 AUTO FALSE FALSE FALSEUNDO ONLINE 1 UNIFORM FALSE FALSE FALSE6 rows fetched.
8.查看崖山数据库线程
[yashan@jieke-19c bin]$ pwd/home/yashan/yashandb/yasdb_home/bin[yashan@jieke-19c bin]$ ls -l /home/yashan/yashandb/yasdb_home/bintotal 59076-rwxr-xr-x 1 yashan yashan 101320 Nov 9 18:16 exp-rwxr-xr-x 1 yashan yashan 79944 Nov 9 18:16 imp-rwxrwxr-x 1 yashan yashan 19865208 Nov 9 18:16 yasagent-rwxrwxr-x 1 yashan yashan 5986112 Nov 9 18:16 yasbak-rwxrwxr-x 1 yashan yashan 9381440 Nov 9 18:16 yasboot-rwxr-xr-x 1 yashan yashan 181096 Nov 9 18:16 yasdb-rwxr-xr-x 1 yashan yashan 779640 Nov 9 18:16 yasldr-rwxrwxr-x 1 yashan yashan 21896816 Nov 9 18:16 yasom-rwxr-xr-x 1 yashan yashan 66072 Nov 9 18:16 yaspwd-rwxr-xr-x 1 yashan yashan 645680 Nov 9 18:16 yasql-rwxr-xr-x 1 yashan yashan 773256 Nov 9 18:16 yasrman-rwxr-xr-x 1 yashan yashan 120136 Nov 9 18:16 yaswrap-rwxr-xr-x 1 yashan yashan 590416 Nov 9 18:16 yex_server--查看数据库进程 980 对应的线程 top -Hp 980[yashan@jieke-19c bin]$ ps -ef | grep yasdbyashan 980 1 14 11:00 pts/0 00:04:39 yasdb openyashan 4716 2449 0 11:33 pts/0 00:00:00 grep --color=auto yasdb[yashan@jieke-19c bin]$ ps -Tp 980PID SPID TTY TIME CMD980 980 pts/0 00:00:03 yasdb980 981 pts/0 00:00:33 TIMER980 982 pts/0 00:00:04 BUFFER_POOL980 983 pts/0 00:00:00 PRELOADER980 984 pts/0 00:00:00 PRELOADER980 985 pts/0 00:00:00 SMON980 986 pts/0 00:00:05 CKPT980 988 pts/0 00:00:00 DBWR980 989 pts/0 00:00:00 DBWR980 990 pts/0 00:00:00 SCHD_TIMER980 991 pts/0 00:00:04 LISTENER_LOG980 992 pts/0 00:00:00 TCP_LSNR980 993 pts/0 00:00:00 TCP_LSNR980 1013 pts/0 00:00:00 HEALTH_MONITOR980 1033 pts/0 00:00:00 HOT_CACHE_RECYC980 1034 pts/0 00:00:00 LOGW980 1041 pts/0 00:00:07 XFMR980 1042 pts/0 00:01:31 XFMR_WORKER_0980 1043 pts/0 00:00:03 MMON980 1044 pts/0 00:00:00 JOB_QUEUE980 1045 pts/0 00:00:00 XFMR_WORKER_1980 1046 pts/0 00:00:00 XFMR_WORKER_2980 1047 pts/0 00:00:00 XFMR_WORKER_3980 1048 pts/0 00:00:00 XFMR_WORKER_4980 1049 pts/0 00:00:00 XFMR_WORKER_5980 1050 pts/0 00:00:00 XFMR_WORKER_6980 1051 pts/0 00:00:00 XFMR_WORKER_7980 1052 pts/0 00:00:00 XFMR_WORKER_8980 1053 pts/0 00:00:00 XFMR_WORKER_9980 1054 pts/0 00:00:00 XFMR_WORKER_10980 1055 pts/0 00:00:00 XFMR_WORKER_11980 1056 pts/0 00:00:00 XFMR_WORKER_12980 1057 pts/0 00:00:00 XFMR_WORKER_13980 1058 pts/0 00:00:00 XFMR_WORKER_14980 1059 pts/0 00:00:00 XFMR_WORKER_15980 1060 pts/0 00:00:00 XFMR_WORKER_16980 1061 pts/0 00:00:00 XFMR_WORKER_17980 1062 pts/0 00:00:00 XFMR_WORKER_18980 1063 pts/0 00:00:00 XFMR_WORKER_19980 1066 pts/0 00:00:00 XFMR_WORKER_20980 1067 pts/0 00:00:00 XFMR_WORKER_21980 1068 pts/0 00:00:00 XFMR_WORKER_22980 1069 pts/0 00:00:00 XFMR_WORKER_23980 1070 pts/0 00:00:00 XFMR_WORKER_24980 1071 pts/0 00:00:00 XFMR_WORKER_25980 1072 pts/0 00:00:00 XFMR_WORKER_26980 1073 pts/0 00:00:00 XFMR_WORKER_27980 1074 pts/0 00:00:00 XFMR_WORKER_28980 1075 pts/0 00:00:00 XFMR_WORKER_29980 1076 pts/0 00:00:00 XFMR_WORKER_30980 1077 pts/0 00:00:00 XFMR_WORKER_31-- v$process 视图也可以看到上述线程。SQL> select * from v$process;
可以看到 YashanDB 是单进程多线程架构，Oracle 中的很多进程，SMON、MMON、CKPT、DBWR 等进程在崖山数据库中都以线程的形式存在。
9.修改参数及数据插入对比
19.12 单机文件系统，配置为 8c16g 的同一台虚拟机，且均开启了归档日志: Release 19.0.0.0.0 - Production on Tue Nov 28 11:10:48 2023Version 19.12.0.0.0Copyright (c) 1982, 2021, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.12.0.0.0show parameter sga_NAME TYPE VALUE----------- ------------------------------sga_max_size big integer 6560Msga_min_size big integer 0sga_target big integer 6560Munified_audit_sga_queue_size integer 1048576!free -htotal used free shared buff/cache availableMem: 15G 1.7G 5.4G 6.2G 8.4G 7.4GSwap: 7.9G 16M 7.9GYashanDB 的参数为上面 SGA 的大小，并重启数据库--查看参数show parameter DATA_BUFFER_SIZE --256Mshow parameter VM_BUFFER_SIZE --32M--修改参数并重启数据库alter system set DATA_BUFFER_SIZE=6560M scope=spfile;alter system set VM_BUFFER_SIZE=6560M scope=spfile;shutdown immediate;exitnohup yasdb open &
Oracle 19c 导出操作
grant connect,resource,dba to test;conn test/testcreate table T_base_table as select * from dba_objects;select count(*) from T_base_table;COUNT(*)----------131341Elapsed: 00:00:00.54--获取此基表 DDL 建表语句，并将其简单修改下TEST@test> set long 9999TEST@test> SELECT DBMS_METADATA.GET_DDL('TABLE','T_BASE_TABLE','TEST') DDL_SQL FROM DUAL;CREATE TABLE "TEST"."T_BASE_TABLE"( "OWNER" VARCHAR2(128) ,"OBJECT_NAME" VARCHAR2(128) ,"SUBOBJECT_NAME" VARCHAR2(128) ,"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT_TYPE" VARCHAR2(23) ,"CREATED" DATE,"LAST_DDL_TIME" DATE,"TIMESTAMP" VARCHAR2(19) ,"STATUS" VARCHAR2(7) ,"TEMPORARY" VARCHAR2(1) ,"GENERATED" VARCHAR2(1) ,"SECONDARY" VARCHAR2(1) ,"NAMESPACE" NUMBER,"EDITION_NAME" VARCHAR2(128) ,"SHARING" VARCHAR2(18) ,"EDITIONABLE" VARCHAR2(1) ,"ORACLE_MAINTAINED" VARCHAR2(1) ,"APPLICATION" VARCHAR2(1) ,"DEFAULT_COLLATION" VARCHAR2(100) ,"DUPLICATED" VARCHAR2(1) ,"SHARDED" VARCHAR2(1) ,"CREATED_APPID" NUMBER,"CREATED_VSNID" NUMBER,"MODIFIED_APPID" NUMBER,"MODIFIED_VSNID" NUMBER) TABLESPACE "TEST";--使用 sqluldr2 导出 CSV 文件，将其导入到崖山数据库--当使用 table 参数时，在目录下会生成对应的 ctl 控制文件，如下语句会生成 t_base_table_sqlldr.ctl 文件。./sqluldr2_linux64_10204.bin test/test query="select * from t_base_table" table=t_base_table file=/home/oracle/tmp/sqluldr2/t_base_table.csv0 rows exported at 2023-11-28 15:38:18, size 0 MB.131341 rows exported at 2023-11-28 15:38:19, size 19 MB.output file /home/oracle/tmp/sqluldr2/t_base_table.csv closed at 131341 rows, size 19 MB.cp t_base_table* /tmp/
YashanDB 导入数据
Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;TABLESPACE_NAME----------------------------------------------------------------SYSTEMSYSAUXTEMPSWAPUSERSUNDO6 rows fetched.--YashanDB 没有类似 oracle 的 col name for a30 这样的格式化语句，显示看的很难受。SQL> select file_id,file_name from dba_data_files;FILE_ID FILE_NAME------------ ----------------------------------------------------------------0 /home/yashan/yashandb/yasdb_data/dbfiles/system1 /home/yashan/yashandb/yasdb_data/dbfiles/sysaux2 /home/yashan/yashandb/yasdb_data/dbfiles/temp3 /home/yashan/yashandb/yasdb_data/dbfiles/swap4 /home/yashan/yashandb/yasdb_data/dbfiles/users5 /home/yashan/yashandb/yasdb_data/dbfiles/undo6 rows fetched.--创建表空间和用户SQL> CREATE TABLESPACE test datafile '/home/yashan/yashandb/yasdb_data/dbfiles/test' size 1g;Succeed.SQL> CREATE USER test IDENTIFIED BY test;SQL> grant dba to test;SQL> grant connect to test;SQL> grant resource to test;SQL> alter user test default tablespace test;SQL> select username,account_status,default_tablespace from dba_users;
yasldr 是 YashanDB 提供的数据快速导入工具，用于将 CSV 格式的数据文件快速导入至崖山数据库中，支持通过数据库连接向远端节点进行数据导入。
-- yasldr 查看帮助信息[yashan@jieke-19c ~]$ yasldr -HYashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bdYashanDB LOADER HELP:To specify parameters, should use keywords:Command Format:YASLDR USERNAME/PASSWORD@IP:PORT KEYWORD=value"USERNAME/PASSWORD@URL" must be the first on the command line, "@URL" default 127.0.0.1:1688KEYWORD DESCRIPTION (DEFAULT)---------------------------------------------------------------------------------------------------BATCH_SIZE the number of lines per batch, default 4032, range [1, 65535]CONTROL_FILE a file containing the LOAD statementCONTROL_TEXT the LOAD statement, only be used when CONTROL_FILE is not specifiedMODE import mode, including BASIC/BATCH, default BATCHPACKET_SIZE client expected packet size, default 128KB, range [65536, 524288]CONN_POOL_SIZE client connection pool size, default 5, range [1, 32]Example:YASLDR USERNAME/PASSWORD@IP:PORT CONTROL_FILE=LOAD.CTL--使用 yasldr -V 命令可查看版本信息。[yashan@jieke-19c ~]$ yasldr -VYashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd--命令格式$ YASLDR USERNAME/PASSWORD@IP:PORT {LOAD Options} {LOAD Statement}--官方手册参考链接：https://doc.yashandb.com/yashandb/23.1/zh/%E5%B7%A5%E5%85%B7%E6%89%8B%E5%86%8C/yasldr/yasldr%E4%BD%BF%E7%94%A8%E6%8C%87%E5%AF%BC.html--参考导出的 ctl 文件和官方文档给出的示例编写 ctl 控制文件vim t_base_table.ctlLOAD DATA INFILE '/tmp/t_base_table.csv' FIELDS TERMINATED BY ',' optionally enclosed by '"'INTO TABLE t_base_table("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED","APPLICATION","DEFAULT_COLLATION","DUPLICATED","SHARDED","CREATED_APPID","CREATED_VSNID","MODIFIED_APPID","MODIFIED_VSNID")--导入数据[yashan@jieke-19c tmp]$ yasldr test/test@127.0.0.1:1688 batch_size=4032 mode=batch packet_size=131072 control_file=/tmp/t_base_table.ctlYashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd131341 rows successfully loaded.Check /tmp/t_base_table.log for more info.[YASLDR] execute succeeded#查询数据库表中的数据条数是否和 Oracle 中条数一致[yashan@jieke-19c tmp]$ yasql test/test@127.0.0.1:1688 -c "select count(*) from t_base_table"COUNT(*)---------------------1313411 row fetched.[yashan@jieke-19c tmp]$ yasql / as sysdbaSQL> select sysdate from dual;SYSDATE--------------------------------2023-11-28SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YYY----------------------------------------------------------------2023-11-28 17:34:47SQL> select rowid,object_id from test.t_base_table where rownum<=3;ROWID OBJECT_ID-------------------------------------------- -----------2272:6:0:132:0 162272:6:0:132:1 202272:6:0:132:2 83 rows fetched.SQL> select object_id,data_object_id from dba_objects where object_name='T_BASE_TABLE';OBJECT_ID DATA_OBJECT_ID--------------------- ---------------------2272 2272SQL> select file_id from dba_data_files where tablespace_name='TEST';FILE_ID------------6
不难看出崖山数据库的 ROWID 结构为五组数字用冒号分割来表示：daba_object_id:tablespace_file_id:file_id:block_number:row#。
简单查询对比
接下来我们进行一个简单的插入和表关联对比，为了避免影响，同一时刻只有一个数据库是启动状态的，现在我们在 Oracle 中进行插入数据，关闭崖山数据库。
Oracle 19c
#### oracle 19calter database datafile 7 resize 10g;conn test/testcreate table T_BIG_TABLE as select * from T_BASE_TABLE;beginfor i in 1..8 loopinsert into T_BIG_TABLE select * from T_BIG_TABLE;end loop;commit;end;/Elapsed: 00:03:21.09select count(*) from T_BIG_TABLE;COUNT(*)----------33623296Elapsed: 00:01:12.27create table T_SMALL_TABLE as select * from T_BASE_TABLE;beginfor i in 1..4 loopinsert into T_SMALL_TABLE select * from T_SMALL_TABLE;end loop;commit;end;/Elapsed: 00:00:04.50select count(*) from T_SMALL_TABLE;COUNT(*)----------2101456Elapsed: 00:00:00.24select /*+ USE_HASH(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;COUNT(*)----------537956352Elapsed: 00:01:16.32create index idx_t_big_table_id on t_big_table(object_id);Index created.Elapsed: 00:02:33.97create index idx_t_small_table_id on t_small_table(object_id);Index created.Elapsed: 00:00:07.62select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;COUNT(*)----------537956352Elapsed: 00:02:55.9022:16:26 TEST@test> set autot traceonly22:16:32 TEST@test> select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;Elapsed: 00:02:41.73Execution Plan----------------------------------------------------------Plan hash value: 188159531--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 10 | 263K (1)| 00:00:11 || 1 | SORT AGGREGATE | | 1 | 10 | | || 2 | NESTED LOOPS | | 131K| 1282K| 263K (1)| 00:00:11 || 3 | TABLE ACCESS FULL| T_BIG_TABLE | 131K| 641K| 344 (2)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IDX_T_SMALL_TABLE_ID | 1 | 5 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")Hint Report (identified by operation id / Query Block Name / Object Alias):Total hints for statement: 1 (U - Unused (1))---------------------------------------------------------------------------3 - SEL$1 / A@SEL$1U - USE_NL(A B)Statistics----------------------------------------------------------0 recursive calls0 db block gets38649498 consistent gets444251 physical reads0 redo size554 bytes sent via SQL*Net to client438 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed
现在关闭 Oracle 数据库，启动 YashanDB 执行上述操作，我们先扩一下 test 表空间,然后使用相同 SQL 插入相同数据量的数据进行简单查询。
YashanDB 个人版
#### YashanDB 个人版YashanDB SQL Personal Edition Release 23.1.1.100 x86_64Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL>SQL> alter database datafile '/home/yashan/yashandb/yasdb_data/dbfiles/test' resize 10g;conn test/testcreate table T_BIG_TABLE as select * from T_BASE_TABLE;beginfor i in 1..8 loopinsert into T_BIG_TABLE select * from T_BIG_TABLE;end loop;commit;end;/Elapsed: 00:02:51.247select count(*) from T_BIG_TABLE;COUNT(*)----------33623296Elapsed: 00:00:03.837select object_id,data_object_id from dba_objects where object_name='T_SMALL_TABLE';OBJECT_ID DATA_OBJECT_ID--------------------- ---------------------2274 2288create table T_SMALL_TABLE as select * from T_BASE_TABLE;beginfor i in 1..4 loopinsert into T_SMALL_TABLE select * from T_SMALL_TABLE;end loop;commit;end;/Elapsed: 00:00:07.631select count(*) from T_SMALL_TABLE;COUNT(*)----------2101456Elapsed: 00:00:00.260select /*+ USE_HASH(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;COUNT(*)----------537956352Elapsed: 00:03:23.322--创建索引SQL> create index idx_t_big_table_id on t_big_table(object_id);Elapsed: 00:01:44.781SQL> create index idx_t_small_table_id on t_small_table(object_id);Elapsed: 00:00:04.731SQL> select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;COUNT(*)---------------------537956352Elapsed: 00:03:39.800SQL> explain select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;PLAN_DESCRIPTION----------------------------------------------------------------SQL hash value: 1167597945Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | AGGREGATE | | | 1| 152( 0)| || 2 | NESTED LOOPS INNER | | | 100000| 150( 0)| || 3 | INDEX FAST FULL SCAN | IDX_T_BIG_TABLE_ID | TEST | 100000| 91( 0)| ||* 4 | INDEX RANGE SCAN | IDX_T_SMALL_TABLE_ID | TEST | 1| 6( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):---------------------------------------------------4 - Predicate : access("B"."OBJECT_ID" = "A"."OBJECT_ID")17 rows fetched.Elapsed: 00:00:00.001
汇总对比结果
| 统计对比（单位为秒） | Oracle 19.12 | YashanDB23.1.1.100 | 备注 |
|---|---|---|---|
| 插入3362W T_BIG_TABLE | 201.09s | 171.247s | Y |
| Count(*) T_BIG_TABLE | 72.27s | 3.837s | Y |
| 插入 210W T_SMALL_TABLE | 4.50s | 7.631s | N |
| Count(*) T_SMALL_TABLE | 0.24s | 0.260s | N |
| 两表关联 Hash join 查询 | 76.32s | 203.322s | N |
| 创建索引 T_BIG_TABLE | 153.97s | 104.781s | Y |
| 创建索引 T_SMALL_TABLE | 7.62s | 4.731s | Y |
| 两表关联 NESTED LOOPS 查询 | 175.55s | 219.800s | N |
这个结果和上次使用企业版体验的结果有所差别，小表的插入和查询还是 Oracle 19c 更快一些，当然可能的原因是上次使用的是配置比较低的个人虚拟机安装的 Oracle 12c 版本，本次使用的是公司的虚拟机环境，个人感觉是比较公平公正的测试了，如果有其他意见或建议欢迎交流。总体来看，YashanDB 在大表插入及查询以及创建索引的过程要比 Oracle 19c 更快一些，但是在 Hash join 和 Nest LOOP 关联查询中，性能偏低一些。
全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
欢迎关注我公众号【JiekeXu DBA之路】，第一时间一起学习新知识！以下三个地址可以找到我，其他地址均属于盗版侵权爬取我的文章，而且代码格式、图片等均有错乱，不方便阅读，欢迎来我公众号或者墨天轮地址关注我，第一时间收获最新消息。
—————————————————————————— 公众号：JiekeXu DBA之路 CSDN ：https://blog.csdn.net/JiekeXu 墨天轮：https://www.modb.pro/u/4347 腾讯云：https://cloud.tencent.com/developer/user/5645107 ——————————————————————————
分享几个数据库备份脚本
Oracle 表碎片检查及整理方案
OGG|Oracle GoldenGate 基础2022 年公众号历史文章合集整理
Oracle 19c RAC 遇到的几个问题
OGG|Oracle 数据迁移后比对一致性OGG|Oracle GoldenGate 微服务架构
Oracle 查询表空间使用率超慢问题一则
Oracle 11g升级到19c需要关注的几个问题
国产数据库|TiDB 5.4 单机快速安装初体验
Oracle ADG 备库停启维护流程及增量恢复
Linux 环境搭建 MySQL8.0.28 主从同步环境
从国产数据库调研报告中你都能了解哪些信息及我的总结建议
