一.數據庫安裝準備
1.MogDB的單機安裝見下鏈接:
2.安裝成功后,登錄數據庫設置





3.設置數據庫參數
remote_read_mode = non_authentication
replication_type = 1
sync_config_strategy = none_node
recovery_max_workers = 20
max_connections = 3000
allow_concurrent_tuple_update = true
audit_enabled = off
cstore_buffers = 16MB
enable_alarm = off
enable_codegen = false
enable_data_replicate = off
full_page_writes = off
max_files_per_process = 100000
max_prepared_transactions = 2048
shared_buffers = 188GB #內存的20%
use_workload_manager = off
wal_buffers = 1GB
work_mem = 1MB
transaction_isolation = 'read committed'
default_transaction_isolation = 'read committed'
synchronous_commit = off
fsync = on
maintenance_work_mem = 2GB
vacuum_cost_limit = 10000
autovacuum = on
autovacuum_mode = vacuum
autovacuum_max_workers = 20
autovacuum_naptime = 5s
autovacuum_vacuum_cost_delay = 10
update_lockwait_timeout = 20min
enable_mergejoin = off
enable_nestloop = off
enable_hashjoin = off
enable_material = off
wal_log_hints = off
log_duration = off
checkpoint_timeout = 15min
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.02
enable_save_datachanged_timestamp = false
enable_double_write = on
enable_incremental_checkpoint = on
enable_opfusion = on
advance_xlog_file_num = 100
track_activities = off
enable_instr_track_wait = off
enable_instr_rt_percentile = off
track_counts = on
track_sql_count = off
enable_instr_cpu_timer = off
plog_merge_age = 0
session_timeout = 0
enable_instance_metric_persistent = off
enable_logical_io_statistics = off
enable_page_lsn_check = off
enable_user_metric_persistent = off
enable_xlog_prune = off
enable_resource_track = off
instr_unique_sql_count=0
wal_level = archive
hot_standby = off
hot_standby_feedback = off
client_min_messages = ERROR
log_min_messages = FATAL
enable_asp = off
enable_bbox_dump = off
bgwriter_flush_after = 32
wal_keep_segments = 1025
enable_bitmapscan = off
enable_seqscan = off
enable_beta_opfusion=on
enable_thread_pool = on
checkpoint_segments=8000
enable_stmt_track=false
bgwriter_delay = 5s
incremental_checkpoint_timeout = 5min
xloginsert_locks = 16
wal_file_init_num = 20
pagewriter_sleep = 10ms
4.操作系統參數
fs.aio-max-nr=1048576
fs.file-max= 76724600
kernel.sem = 50100 128256000 50100 2560
kernel.shmall = ?148242432? # page, 80% MEM
kernel.shmmax= 607201001472
kernel.shmmni = 4096
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304
net.core.somaxconn = 4096
net.ipv4.tcp_fin_timeout = 5
vm.overcommit_memory = 0
vm.swappiness = 1
net.ipv4.ip_local_port_range = 40000 65535
fs.nr_open = 20480000
vm.dirty_background_bytes=409600000
vm.nr_hugepages=51200
vm.mmap_min_addr=65536
5.設置core
echo "/home/core/core-%e-%p-%t" >/proc/sys/kernel/core_pattern
mkdir /home/core -p
chmod 777 /home/core
6.參數生效
sysctl -p 操作系統操作生效

gs_ctl restart -D /home/mogdb/data -Z single_node 重啟數據庫
二.benchmarkSQL5.0工作準備
相關軟件下載地址
benchmarksql-5.0下載地址: https://udomain.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
R語言下載地址: https://mirror.bjtu.edu.cn/cran/src/base/R-3/R-3.6.3.tar.gz
以下安裝R環境,是為了生成benchmarksql-5.0的監控信息。
#安裝依賴軟件包
yum install gcc glibc-headers gcc-c++ gcc-gfortran readline-devel libXt-devel pcre-devel libcurl libcurl-devel -y
yum install ncurses ncurses-devel autoconf automake zlib zlib-devel bzip2 bzip2-devel xz-devel -y
yum install java-1.8.0-openjdk ant -y
#安裝R語言(generateReport.sh腳本需要)
yum install pango-devel pango libpng-devel cairo cairo-devel
## 使R語言支持png圖片,否則報告生成有問題
tar -zxf R-3.6.3.tar.gz
cd R-3.6.3
./configure && make && make install
如果沒有benchmarksql-5.0的benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar 不存在,需配置ant、java環境進行編譯。

編譯完成后,現在jdbc的jar放到benchmarksql-5.0/lib/postgres下。在run下配置數據庫連接文件。

三.benchmarkSQL5.0在普通磁盤表的性能測試
1.導入數據:執行./runDatabaseBuild.sh mogdb.pg導入數據,執行前修改runDatabaseBuild.sh的權限chmod 755 run*.sh


2.500并發先預熱2分鐘。執行命令:./runBenchmark.sh mogdb.pg 執行tpcc。預熱完成后,修改配置文件runMins=30,執行測試30分鐘。

以下是運行30分鐘前的數據表大小

以下是運行30分鐘以后表大小

以下tpcc運行結果:

四.benchmarkSQL5.0在MOT內存表的性能測試
1.在測試內存表tpcc之前如下操作
MogDB=# drop database tpcc ;
DROP DATABASE
MogDB=# create database tpcc;
CREATE DATABASE
MogDB=# alter database tpcc owner to tpcc;
2.修改tpcc的表結構
操作以下操作替換原有的benchmarksql-5.0適合mot的操作


注意上圖報錯的是參數不存在,替換conf文件注意一下。
導入數據報錯如下是由于參數max_process_memory設置較小,根據機器的實際情況進行調整參數重啟數據庫。

運行30分鐘性能測試前表的情況

運行30分鐘以后表的占用情況

2.500并發先預熱2分鐘。執行命令:./runBenchmark.sh mogdb.pg 執行tpcc。預熱完成后,修改配置文件runMins=30,執行測試30分鐘。

五.測試結果
結果:經過以上測試發現,mot內存表的tpmC是普通表的tpmC 5-6倍。延遲比普通表小10倍左右。CPU的使用率比普通表大4倍。
mot的tpcc結果



普通表的tpcc測試結果:



數據庫參數參照:
https://blog.csdn.net/weixin_53596073/article/details/123643316?spm=1001.2014.3001.5502
benchmarksql-5.0參照:




