为啥会写这篇文章呢，起初是因为上篇文章测试的是崖山企业版本 以及和 Oracle 12c 的性能对比，总体感觉还不错，但这个月看到有几位大佬测试的结果是 Oracle 11.2.0.4 比 YashanDB 性能要强很多，感觉和我之前测试的有出入，所以想在测一测，在体验体验，看看问题在哪是不是我出现了啥错误，而且这次把 Oracle 12c 多租户环境换成非容器 19c 单机版来试试。
1.下载个人版 YashanDB
我这里下载 x86 架构软件包 YashanDB https://download.yashandb.com/download
2.操作系统基本信息
[root@jieke-19c ~]# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 8 On-line CPU(s) list: 0-7 Thread(s) per core: 1 Core(s) per socket: 4 Socket(s): 2 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E7-4809 v4 @ 2.10GHz Stepping: 1 CPU MHz: 2094.952 BogoMIPS: 4189.90 Hypervisor vendor: VMware Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 20480K NUMA node0 CPU(s): 0-7 Flags: 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 -l 8 [root@jieke-19c ~]# free -h total used free shared buff/cache available Mem: 15G 1.1G 6.1G 6.3G 8.3G 7.9G Swap: 7.9G 16M 7.9G [root@jieke-19c ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/rootvg-lvroot 392G 314G 78G 81% / devtmpfs 7.8G 0 7.8G 0% /dev tmpfs 7.8G 56K 7.8G 1% /dev/shm tmpfs 7.8G 81M 7.7G 2% /run tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup /dev/sda1 1014M 190M 825M 19% /boot tmpfs 1.6G 0 1.6G 0% /run/user/54321 tmpfs 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 65536 yashan hard nofile 65536 yashan soft nproc 65536 yashan hard nproc 65536 yashan soft rss unlimited yashan hard rss unlimited yashan soft stack 8192 yashan 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]$ ll total 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]$ ll total 171312 drwxrwxr-x 6 yashan yashan 70 Nov 9 18:16 admin drwxrwxr-x 2 yashan yashan 188 Nov 9 18:16 bin drwxrwxr-x 2 yashan yashan 152 Nov 9 18:16 conf drwxrwxr-x 5 yashan yashan 60 Nov 9 18:16 ext -rw-rw-r-- 1 yashan yashan 11632 Nov 9 18:16 gitmoduleversion.dat drwxrwxr-x 2 yashan yashan 65 Nov 9 18:16 include drwxrwxr-x 3 yashan yashan 17 Nov 9 18:16 java drwxrwxr-x 2 yashan yashan 4096 Nov 9 18:16 lib -rw-r----- 1 yashan yashan 14989 Nov 9 18:16 LICENSE drwxrwxr-x 3 yashan yashan 21 Nov 9 18:16 plug-in drwxrwxr-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]$ ll
total 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_home
YASDB_DATA=~/yashandb/yasdb_data
REDO_FILE_SIZE=100M
REDO_FILE_NUM=4
INSTALL_SIMPLE_SCHEMA_SALES=N
NLS_CHARACTERSET=UTF8
[instance]
LISTEN_ADDR=0.0.0.0:1688
DB_BLOCK_SIZE=8K
DATA_BUFFER_SIZE=256M
SHARE_POOL_SIZE=256M
WORK_AREA_POOL_SIZE=32M
LARGE_POOL_SIZE=32M
REDO_BUFFER_SIZE=8M
UNDO_RETENTION=300
OPEN_CURSORS=310
MAX_SESSIONS=1024
RUN_LOG_LEVEL=INFO
NODE_ID=1-1:1
[yashan@jieke-19c scripts]$ more install.sh
#!/bin/bash
#install.sh
FILE_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=SC2002
cat "${YASDB_INSTALL_FILE}" | grep "=" > "${YASDB_TEMP_FILE}"
# shellcheck disable=SC1090
source "${YASDB_TEMP_FILE}"
if [ -d "$YASDB_HOME" ]; then
echo -e "install failed!\n$YASDB_HOME already exists."
exit 1
fi
if [ -f "$YASDB_DATA"/config/yasdb.ini ]; then
echo -e "install failed!\n$YASDB_DATA used, yasdb.ini is already exists"
exit 1
fi
##创建数据库软件目录
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"/client
touch "$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' ~/.bashrc
YASDB_ENV_FILE="${YASDB_HOME}/conf/yasdb.bashrc"
cat >"${YASDB_ENV_FILE}" <<EOF
export YASDB_HOME=$YASDB_HOME
export YASDB_DATA=$YASDB_DATA
export PATH=\$YASDB_HOME/bin:\$PATH
export LD_LIBRARY_PATH=\$YASDB_HOME/lib:\$LD_LIBRARY_PATH
EOF
cat >>~/.bashrc <<EOF
[ -f $YASDB_ENV_FILE ] && source $YASDB_ENV_FILE
EOF
[yashan@jieke-19c ~]$ more /home/yashan/soft/scripts/initDB.sh
#!/bin/bash
#initDB.sh
FILE_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=SC1090
source "${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=SC1090
source "${YASDB_ENV_FILE}"
if [ ! -d "$YASDB_HOME" ] || [ ! -d "$YASDB_DATA" ]; then
echo -e "Software installation \"./install.sh\" is not performed."
exit 1
fi
if [ -f "$YASDB_DATA"/config/yasdb.ini ]; then
echo -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 1
fi
e_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"
else
rm -f "$YASDB_HOME"/admin/yasdb.pwd
"$YASPWD_BIN" file="$YASDB_HOME"/admin/yasdb.pwd password="$YASDB_PASSWORD"
fi
cp "$YASDB_HOME"/admin/yasdb.pwd "$YASDB_DATA"/instance/yasdb.pwd
REDOFILE="("
for ((i = 0; i < "$REDO_FILE_NUM"; i++)); do
if [ $i == $((REDO_FILE_NUM - 1)) ]; then
REDOFILE=${REDOFILE}"'redo${i}'"" size $REDO_FILE_SIZE)"
else
REDOFILE=${REDOFILE}"'redo${i}'"" size $REDO_FILE_SIZE,"
fi
done
##创建数据库
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=0
while ((i < 5))
do
sleep 2
# shellcheck disable=SC2002 disable=SC2126
alive=$(cat "$START_LOG_FILE" | grep "Instance started" | wc -l)
if [ "$alive" -ne 0 ]; then
echo "process started!"
break
fi
i=$((i+1))
done
if [ "$i" -eq "5" ];then
echo "start process failed. read $START_LOG_FILE"
cat "$START_LOG_FILE"
exit 1
fi
"${YASQL_BIN}" sys/$YASDB_PASSWORD >>"$START_LOG_FILE" <<EOF
create database yasdb CHARACTER SET $NLS_CHARACTERSET logfile $REDOFILE;
exit;
EOF
i=0
while ((i < 60))
do
sleep 1
alive=$($YASQL_BIN sys/$YASDB_PASSWORD -c "select open_mode from v\$database" | grep -c READ_WRITE)
if [ "$alive" -eq 1 ]; then
echo "Database open succeed !"
break
fi
i=$((i+1))
done
if [ "$i" -eq "60" ];then
echo "Failed ! please check logfile $START_LOG_FILE ."
exit 1
fi
##创建样例数据:sales
if [ "$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"
fi
exit 0
####密码以二进制形式存储，不可读。根据上面 initDB.sh 文件中的 YASDB_PASSWORD 可知密码为 yasdb_123
[yashan@jieke-19c ~]$ strings $YASDB_HOME/admin/yasdb.pwd
S:5C8774C730A775DF857AC562BBA45B20B07C7BB8123D75F03C9FA007645B69CF5168AD585B10A5F795E0
执行 install.sh 没有输出记录，但是在当前用户家目录下生成了 yashandb 子目录，此目录下包含 yasdb_home 和 yasdb_data 目录。
[yashan@jieke-19c scripts]$ sh install.sh [yashan@jieke-19c scripts]$ cd [yashan@jieke-19c ~]$ ll total 0 drwxrwxr-x 11 yashan yashan 220 Nov 22 16:27 soft drwxrwxr-x 4 yashan yashan 42 Nov 22 17:42 yashandb [yashan@jieke-19c ~]$ cd yashandb/ [yashan@jieke-19c yashandb]$ ll total 0 drwxrwxr-x 10 yashan yashan 117 Nov 22 17:42 yasdb_data drwxrwxr-x 11 yashan yashan 152 Nov 22 17:42 yasdb_home [yashan@jieke-19c yashandb]$ du -sh * 0 yasdb_data 432M 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.sh process started! Database open succeed ! --初始化之后会生成 yasdb_data 的文件，看着目录结构和 O 记很像很像。 [yashan@jieke-19c yashandb]$ ll total 0 drwxrwxr-x 11 yashan yashan 128 Nov 27 11:01 yasdb_data drwxrwxr-x 11 yashan yashan 152 Nov 22 17:42 yasdb_home [yashan@jieke-19c yashandb]$ tree -L 3 yasdb_data yasdb_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 +-- tmp 21 directories, 28 files --生效环境变量 [yashan@jieke-19c scripts]$ cd [yashan@jieke-19c ~]$ . ~/.bashrc
7.登录数据库
通过上述方式脚本安装的数据库，据上面脚本介绍 sys 默认密码是 yasdb_123。
[yashan@jieke-19c ~]$ yasql / as sysdba
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> select status,version from v$instance;
STATUS VERSION
------------- ----------------------------------------------------------------
OPEN Personal Edition Release 23.1.1.100 x86_64
1 row fetched.
SQL> exit
[yashan@jieke-19c ~]$ yasql sys/yasdb_123 --居然不用 sysdba 方式登录
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> exit
[yashan@jieke-19c ~]$ yasql sys/yasdb_12 --错误密码无法登录
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
YAS-02143 invalid username/password, login denied
please input user name:
-- 关闭数据库/重启数据库
SQL> shutdown immediate;
Succeed.
SQL> exit
[yashan@jieke-19c ~]$ nohup yasdb open &
[1] 980
[yashan@jieke-19c ~]$ ps -ef | grep yasdb
yashan 980 31966 21 11:00 pts/0 00:00:10 yasdb open
yashan 1171 31966 0 11:01 pts/0 00:00:00 grep --color=auto yasdb
[yashan@jieke-19c ~]$ yasql / as sysdba
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> select status from v$instance;
STATUS
-------------
OPEN
1 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 FALSE
SYSAUX ONLINE 8 AUTO FALSE FALSE FALSE
TEMP ONLINE 8 UNIFORM FALSE FALSE FALSE
SWAP ONLINE 8 UNIFORM FALSE FALSE FALSE
USERS ONLINE 8 AUTO FALSE FALSE FALSE
UNDO ONLINE 1 UNIFORM FALSE FALSE FALSE
6 rows fetched.
--开启归档模式
[yashan@jieke-19c archive]$ yasql / as sysdba
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> SELECT database_name,log_mode,open_mode FROM V$DATABASE;
DATABASE_NAME LOG_MODE OPEN_MODE
---------------------------------------------------------------- ----------------- -----------------
yasdb NOARCHIVELOG READ_WRITE
1 row fetched.
SQL> SHUTDOWN IMMEDIATE;
Succeed.
SQL> exit
[yashan@jieke-19c ~]$ nohup yasdb mount &
[1] 410
[yashan@jieke-19c ~]$ yasql / as sysdba
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> alter database archivelog;
Succeed.
SQL> alter database open;
Succeed.
SQL> SELECT database_name,log_mode,open_mode FROM V$DATABASE;
DATABASE_NAME LOG_MODE OPEN_MODE
---------------------------------------------------------------- ----------------- -----------------
yasdb ARCHIVELOG READ_WRITE
1 row fetched.
SQL> alter system switch logfile;
Succeed.
SQL>
8.查看崖山数据库线程
[yashan@jieke-19c bin]$ pwd
/home/yashan/yashandb/yasdb_home/bin
[yashan@jieke-19c bin]$ ls -l /home/yashan/yashandb/yasdb_home/bin
total 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 yasdb
yashan 980 1 14 11:00 pts/0 00:04:39 yasdb open
yashan 4716 2449 0 11:33 pts/0 00:00:00 grep --color=auto yasdb
[yashan@jieke-19c bin]$ ps -Tp 980
PID SPID TTY TIME CMD
980 980 pts/0 00:00:03 yasdb
980 981 pts/0 00:00:33 TIMER
980 982 pts/0 00:00:04 BUFFER_POOL
980 983 pts/0 00:00:00 PRELOADER
980 984 pts/0 00:00:00 PRELOADER
980 985 pts/0 00:00:00 SMON
980 986 pts/0 00:00:05 CKPT
980 988 pts/0 00:00:00 DBWR
980 989 pts/0 00:00:00 DBWR
980 990 pts/0 00:00:00 SCHD_TIMER
980 991 pts/0 00:00:04 LISTENER_LOG
980 992 pts/0 00:00:00 TCP_LSNR
980 993 pts/0 00:00:00 TCP_LSNR
980 1013 pts/0 00:00:00 HEALTH_MONITOR
980 1033 pts/0 00:00:00 HOT_CACHE_RECYC
980 1034 pts/0 00:00:00 LOGW
980 1041 pts/0 00:00:07 XFMR
980 1042 pts/0 00:01:31 XFMR_WORKER_0
980 1043 pts/0 00:00:03 MMON
980 1044 pts/0 00:00:00 JOB_QUEUE
980 1045 pts/0 00:00:00 XFMR_WORKER_1
980 1046 pts/0 00:00:00 XFMR_WORKER_2
980 1047 pts/0 00:00:00 XFMR_WORKER_3
980 1048 pts/0 00:00:00 XFMR_WORKER_4
980 1049 pts/0 00:00:00 XFMR_WORKER_5
980 1050 pts/0 00:00:00 XFMR_WORKER_6
980 1051 pts/0 00:00:00 XFMR_WORKER_7
980 1052 pts/0 00:00:00 XFMR_WORKER_8
980 1053 pts/0 00:00:00 XFMR_WORKER_9
980 1054 pts/0 00:00:00 XFMR_WORKER_10
980 1055 pts/0 00:00:00 XFMR_WORKER_11
980 1056 pts/0 00:00:00 XFMR_WORKER_12
980 1057 pts/0 00:00:00 XFMR_WORKER_13
980 1058 pts/0 00:00:00 XFMR_WORKER_14
980 1059 pts/0 00:00:00 XFMR_WORKER_15
980 1060 pts/0 00:00:00 XFMR_WORKER_16
980 1061 pts/0 00:00:00 XFMR_WORKER_17
980 1062 pts/0 00:00:00 XFMR_WORKER_18
980 1063 pts/0 00:00:00 XFMR_WORKER_19
980 1066 pts/0 00:00:00 XFMR_WORKER_20
980 1067 pts/0 00:00:00 XFMR_WORKER_21
980 1068 pts/0 00:00:00 XFMR_WORKER_22
980 1069 pts/0 00:00:00 XFMR_WORKER_23
980 1070 pts/0 00:00:00 XFMR_WORKER_24
980 1071 pts/0 00:00:00 XFMR_WORKER_25
980 1072 pts/0 00:00:00 XFMR_WORKER_26
980 1073 pts/0 00:00:00 XFMR_WORKER_27
980 1074 pts/0 00:00:00 XFMR_WORKER_28
980 1075 pts/0 00:00:00 XFMR_WORKER_29
980 1076 pts/0 00:00:00 XFMR_WORKER_30
980 1077 pts/0 00:00:00 XFMR_WORKER_31
-- v$process 视图也可以看到上述线程。
SQL> select * from v$process;
可以看到 YashanDB 是单进程多线程架构，Oracle 中的很多进程，SMON、MMON、CKPT、DBWR 等进程在崖山数据库中都以线程的形式存在。
9.修改参数数据插入对比
--Oracle 19.12 单机文件系统，配置为 8c16g 的同一台虚拟机,且均开启了归档日志
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 28 11:10:48 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SYS@test> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 6560M
sga_min_size big integer 0
sga_target big integer 6560M
unified_audit_sga_queue_size integer 1048576
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 5034
Next log sequence to archive 5036
Current log sequence 5036
SYS@test> !free -h
total used free shared buff/cache available
Mem: 15G 1.7G 5.4G 6.2G 8.4G 7.4G
Swap: 7.9G 16M 7.9G
--同样我们需要设置 YashanDB 的参数为上面 SGA 的大小，并重启数据库
--查看参数
show parameter DATA_BUFFER_SIZE --256M
show 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;
exit
nohup yasdb open &
Oracle 19c 导出操作
grant connect,resource,dba to test;
conn test/test
create table T_base_table as select * from dba_objects;
select count(*) from T_base_table;
COUNT(*)
----------
131341
Elapsed: 00:00:00.54
--获取此基表 DDL 建表语句，并将其简单修改下
TEST@test> set long 9999
TEST@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.csv
0 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 Linux
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
TABLESPACE_NAME
----------------------------------------------------------------
SYSTEM
SYSAUX
TEMP
SWAP
USERS
UNDO
6 rows fetched.
SQL> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
------------ ----------------------------------------------------------------
0 /home/yashan/yashandb/yasdb_data/dbfiles/system
1 /home/yashan/yashandb/yasdb_data/dbfiles/sysaux
2 /home/yashan/yashandb/yasdb_data/dbfiles/temp
3 /home/yashan/yashandb/yasdb_data/dbfiles/swap
4 /home/yashan/yashandb/yasdb_data/dbfiles/users
5 /home/yashan/yashandb/yasdb_data/dbfiles/undo
6 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 -H
YashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd
YashanDB 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:1688
KEYWORD DESCRIPTION (DEFAULT)
---------------------------------------------------------------------------------------------------
BATCH_SIZE the number of lines per batch, default 4032, range [1, 65535]
CONTROL_FILE a file containing the LOAD statement
CONTROL_TEXT the LOAD statement, only be used when CONTROL_FILE is not specified
MODE import mode, including BASIC/BATCH, default BATCH
PACKET_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 -V
YashanDB 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.ctl
LOAD 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.ctl
YashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd
131341 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(*)
---------------------
131341
1 row fetched.
[yashan@jieke-19c tmp]$ yasql / as sysdba
SQL> select sysdate from dual;
SYSDATE
--------------------------------
2023-11-28
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYY
----------------------------------------------------------------
2023-11-28 17:34:47
SQL> select rowid,object_id from test.t_base_table where rownum<=3;
ROWID OBJECT_ID
-------------------------------------------- -----------
2272:6:0:132:0 16
2272:6:0:132:1 20
2272:6:0:132:2 8
3 rows fetched.
SQL> select object_id,data_object_id from dba_objects where object_name='T_BASE_TABLE';
OBJECT_ID DATA_OBJECT_ID
--------------------- ---------------------
2272 2272
SQL> 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 19c
alter database datafile 7 resize 10g;
conn test/test
create table T_BIG_TABLE as select * from T_BASE_TABLE;
begin
for i in 1..8 loop
insert into T_BIG_TABLE select * from T_BIG_TABLE;
end loop;
commit;
end;
/
Elapsed: 00:03:21.09
select count(*) from T_BIG_TABLE;
COUNT(*)
----------
33623296
Elapsed: 00:01:12.27
create table T_SMALL_TABLE as select * from T_BASE_TABLE;
begin
for i in 1..4 loop
insert into T_SMALL_TABLE select * from T_SMALL_TABLE;
end loop;
commit;
end;
/
Elapsed: 00:00:04.50
select count(*) from T_SMALL_TABLE;
COUNT(*)
----------
2101456
Elapsed: 00:00:00.24
select /*+ USE_HASH(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
COUNT(*)
----------
537956352
Elapsed: 00:01:16.32
create index idx_t_big_table_id on t_big_table(object_id);
Index created.
Elapsed: 00:02:33.97
create index idx_t_small_table_id on t_small_table(object_id);
Index created.
Elapsed: 00:00:07.62
select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
COUNT(*)
----------
537956352
Elapsed: 00:02:55.90
22:16:26 TEST@test> set autot traceonly
22: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.73
Execution 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$1
U - USE_NL(A B)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38649498 consistent gets
444251 physical reads
0 redo size
554 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
现在关闭 Oracle 数据库，启动 YashanDB 执行上述操作，我们先扩一下 test 表空间,然后使用相同 SQL 插入相同数据量的数据进行简单查询。
YashanDB 个人版
#### YashanDB 个人版
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL>
SQL> alter database datafile '/home/yashan/yashandb/yasdb_data/dbfiles/test' resize 10g;
conn test/test
create table T_BIG_TABLE as select * from T_BASE_TABLE;
begin
for i in 1..8 loop
insert into T_BIG_TABLE select * from T_BIG_TABLE;
end loop;
commit;
end;
/
Elapsed: 00:05:06.402
select count(*) from T_BIG_TABLE;
COUNT(*)
----------
33623296
Elapsed: 00:00:03.358
select object_id,data_object_id from dba_objects where object_name='T_SMALL_TABLE';
OBJECT_ID DATA_OBJECT_ID
--------------------- ---------------------
2274 2288
create table T_SMALL_TABLE as select * from T_BASE_TABLE;
begin
for i in 1..4 loop
insert into T_SMALL_TABLE select * from T_SMALL_TABLE;
end loop;
commit;
end;
/
Elapsed: 00:00:12.605
select count(*) from T_SMALL_TABLE;
COUNT(*)
----------
2101456
Elapsed: 00:00:00.244
select /*+ USE_HASH(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
COUNT(*)
----------
537956352
Elapsed: 00:03:33.602
--创建索引
SQL> create index idx_t_big_table_id on t_big_table(object_id);
Elapsed: 00:01:46.005
SQL> create index idx_t_small_table_id on t_small_table(object_id);
Elapsed: 00:00:04.892
SQL> select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
COUNT(*)
---------------------
537956352
Elapsed: 00:03:48.926
SQL> 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: 1167597945
Optimizer: 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 | 306.402s | N |
| Count(*) T_BIG_TABLE | 72.27s | 3.358s | Y |
| 插入 210W T_SMALL_TABLE | 4.50s | 12.605s | N |
| Count(*) T_SMALL_TABLE | 0.24s | 0.244s | N |
| 两表关联 Hash join 查询 | 76.32s | 213.602s | N |
| 创建索引 T_BIG_TABLE | 153.97s | 106.005s | Y |
| 创建索引 T_SMALL_TABLE | 7.62s | 4.892s | Y |
| 两表关联 NESTED LOOPS 查询 | 175.55s | 228.926s | N |
下图是 YashanDB 没有开启归档模式的统计表格。
这个结果和上次使用企业版体验的结果有所差别，小表的插入和查询及大表的插入还是 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
————————————————————————————
最后修改时间：2023-12-01 15:21:41
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
