文档中心MogDBMogDB StackUqbar
v1.1

文档:v1.1

支持的版本:

其他版本:

MogDB on x86性能测试报告

测试目的

本文主要讲述了对Mogdb1.1.0 版本on x86进行的单节点和一主多备(同步备、异步备)的测试。


测试环境

环境配置

服务器类型 Fit Server NFS5280M5
CPU 144cIntel® Xeon(R) Gold 6240 CPU@2.60GHz 64cIntel® Xeon(R) Gold 5218 CPU @2.30GHz
内存 768G 128G
硬盘 SAS SSD SAS SSD
网卡 10GE 10GE

测试工具

工具名称 用途
Benchmarksql5.0 BenchmarkSQL是对OLTP数据库主流测试标准TPC-C的开源JAVA实现,用于对数据库事务处理能力的评估测试。

测试步骤

MogDB数据库端操作

  1. 获取数据库安装包

  2. 安装数据库

  3. 创建tpcc测试用户和数据库

    create user [username] identified by ‘passwd’;
    grant [origin user] to [username];
    create database [dbname];
  4. 停止数据库修改数据库配置文件postgresql.conf数据库,在末尾添加配置参数(单节点测试追加参数如下)

    max_connections = 4096
    
    allow_concurrent_tuple_update = true
    
    audit_enabled = off
    
    checkpoint_segments = 1024
    
    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 = 350GB
    
    use_workload_manager = off
    
    wal_buffers = 1GB
    
    work_mem = 1MB
    
    log_min_messages  = FATAL
    
    transaction_isolation = 'read committed'
    
    default_transaction_isolation = 'read committed'
    
    synchronous_commit = on
    
    fsync  = on
    
    maintenance_work_mem = 2GB
    
    vacuum_cost_limit = 2000
    
    autovacuum = on
    
    autovacuum_mode = vacuum
    
    autovacuum_max_workers = 5
    
    autovacuum_naptime = 20s
    
    autovacuum_vacuum_cost_delay =10
    
    xloginsert_locks = 48
    
    update_lockwait_timeout =20min
    
    enable_mergejoin = off
    
    enable_nestloop = off
    
    enable_hashjoin = off
    
    enable_bitmapscan = on
    
    enable_material = off
    
    wal_log_hints = off
    
    log_duration = off
    
    checkpoint_timeout = 15min
    
    enable_save_datachanged_timestamp =FALSE
    
    enable_thread_pool = on
    
    thread_pool_attr =  '812,4,(cpubind:0-27,32-59,64-91,96-123)'
    
    enable_double_write = on
    
    enable_incremental_checkpoint = on
    
    enable_opfusion = on
    
    advance_xlog_file_num = 10
    
    numa_distribute_mode = 'all'
    
    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_user_metric_persistent =off
    
    enable_xlog_prune = off
    
    enable_resource_track = off
    
    instr_unique_sql_count = 0
    
    enable_beta_opfusion = on
    
    enable_beta_nestloop_fusion = on
    
    autovacuum_vacuum_scale_factor = 0.02
    
    autovacuum_analyze_scale_factor = 0.1
    
    client_encoding = UTF8
    
    lc_messages = en_US.UTF-8
    
    lc_monetary = en_US.UTF-8
    
    lc_numeric = en_US.UTF-8
    
    lc_time = en_US.UTF-8
    
    modify_initial_password = off
    
    ssl = off
    
    enable_memory_limit = off
    
    data_replicate_buffer_size = 16384
    
    max_wal_senders = 8
    
    log_line_prefix = '%m %u %d %h %p %S'
    
    vacuum_cost_limit = 10000
    
    max_process_memory = 12582912
    
    recovery_max_workers = 1
    
    recovery_parallelism = 1
    
    explain_perf_mode = normal
    
    remote_read_mode = non_authentication
    
    enable_page_lsn_check = off
    
    pagewriter_sleep = 100对方的

benchmarksql端操作

  1. 修改配置文件

    进入benchmarksql安装目录下,找到run目录下的配置文件[config file]

    db=postgres
    
    driver=org.postgresql.Driver
    
    conn=jdbc:postgresql://[ip:port]/tpcc?prepareThreshold=1&batchMode=on&fetchsize=10
    
    user=[user]
    
    password=[passwd]
    
    warehouses=1000
    
    loadWorkers=80
    
    terminals=812
    
    //To run specified transactions per terminal- runMins must equal zero
    
    runTxnsPerTerminal=0
    
    //To run for specified minutes- runTxnsPerTerminal must equal zero
    
    runMins=30
    
    //Number of total transactions per minute
    
    limitTxnsPerMin=0
    
    //Set to true to run in 4.x compatible mode. Set to false to use the
    
    //entire configured database evenly.
    
    terminalWarehouseFixed=false  #true
    
    //The following five values must add up to 100
    
    //The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
    
    newOrderWeight=45
    
    paymentWeight=43
    
    orderStatusWeight=4
    
    deliveryWeight=4
    
    stockLevelWeight=4
    
    // Directory name to create for collecting detailed result data.
    
    // Comment this out to suppress.
    
    //resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
    
    //osCollectorScript=./misc/os_collector_linux.py
    
    //osCollectorInterval=1
    
    //osCollectorSSHAddr=tpcc@127.0.0.1
    
    //osCollectorDevices=net_eth0 blk_sda blk_sdg blk_sdh blk_sdi blk_sdj
  2. 运行runDataBuild.sh生成数据

    ./runDatabaseBuild.sh [config file]
  3. 运行runBenchmark.sh测试数据库

    ./runBenchmark.sh [config file]

操作系统参数

vm.dirty_background_ratIO=5

vm.dirty_ratIO=10

kernel.sysrq=0

net.ipv4.ip_forward=0

net.ipv4.conf.all.send_redirects=0

net.ipv4.conf.default.send_redirects=0

net.ipv4.conf.all.accept_source_route=0

net.ipv4.conf.default.accept_source_route=0

net.ipv4.conf.all.accept_redirects=0

net.ipv4.conf.default.accept_redirects=0

net.ipv4.conf.all.secure_redirects=0

net.ipv4.conf.default.secure_redirects=0

net.ipv4.icmp_echo_ignore_broadcasts=1

net.ipv4.icmp_ignore_bogus_error_responses=1

net.ipv4.conf.all.rp_filter=1

net.ipv4.conf.default.rp_filter=1

net.ipv4.tcp_syncookies=1

kernel.dmesg_restrict=1

net.ipv6.conf.all.accept_redirects=0

net.ipv6.conf.default.accept_redirects=0

net.core.rmem_max = 21299200

net.core.rmem_default = 21299200

net.core.somaxconn = 65535

net.ipv4.tcp_tw_reuse = 1

net.sctp.sctp_mem = 94500000 915000000 927000000

net.ipv4.tcp_max_tw_buckets = 10000

net.ipv4.tcp_rmem = 8192 250000 16777216

kernel.sem = 250 6400000 1000 25600

net.core.wmem_default = 21299200

kernel.shmall = 1152921504606846720

net.core.wmem_max = 21299200

net.sctp.sctp_rmem = 8192 250000 16777216

net.core.netdev_max_backlog = 65535

kernel.shmmax = 18446744073709551615

net.sctp.sctp_wmem = 8192 250000 16777216

net.ipv4.tcp_keepalive_intvl = 30

net.ipv4.tcp_keepalive_time = 30

net.ipv4.tcp_wmem = 8192 250000 16777216

net.ipv4.tcp_max_syn_backlog = 65535

vm.oom_panic_on_oom=0

kernel.nmi_watchdog=0

kernel.nmi_watchdog=0

kernel.nmi_watchdog=0

kernel.nmi_watchdog=0

kernel.nmi_watchdog=0

net.ipv4.tcp_timestamps = 1

net.ipv4.tcp_tso_win_divisor = 30

net.sctp.path_max_retrans = 10

net.sctp.max_init_retransmits = 10

net.ipv4.tcp_retries1 = 5

net.ipv4.tcp_syn_retries = 5

net.ipv4.tcp_synack_retries = 5

kernel.core_uses_pid=1

kernel.core_pattern=/home/core/core-%e-%u-%s-%t-%p

kernel.nmi_watchdog=0

kernel.nmi_watchdog=0

kernel.nmi_watchdog=0

kernel.nmi_watchdog=0

kernel.core_pattern=/home/core/core-%e-%u-%s-%t-%h

net.core.netdev_max_backlog = 65535

net.core.rmem_default = 21299200

net.core.rmem_max = 21299200

net.core.somaxconn = 65535

net.core.wmem_default = 21299200

net.core.wmem_max = 21299200

net.ipv4.conf.all.accept_redirects = 0

net.ipv4.conf.all.rp_filter = 1

net.ipv4.conf.all.secure_redirects = 0

net.ipv4.conf.all.send_redirects = 0

net.ipv4.conf.default.accept_redirects = 0

net.ipv4.conf.default.accept_source_route = 0

net.ipv4.conf.default.rp_filter = 1

net.ipv4.conf.default.secure_redirects = 0

net.ipv4.conf.default.send_redirects = 0

net.ipv4.conf.enp135s0.accept_redirects = 0

net.ipv4.conf.enp135s0.accept_source_route = 0

net.ipv4.conf.enp135s0.forwarding = 1

net.ipv4.conf.enp135s0.rp_filter = 1

net.ipv4.conf.enp135s0.secure_redirects = 0

net.ipv4.conf.enp135s0.send_redirects = 0

net.ipv4.tcp_keepalive_intvl = 30

net.ipv4.tcp_keepalive_time = 30

net.ipv4.tcp_max_syn_backlog = 65535

net.ipv4.tcp_max_tw_buckets = 10000

net.ipv4.tcp_mem = 362715 483620 725430

#net.ipv4.tcp_mem = 94500000 915000000 927000000

net.ipv4.tcp_retries1 = 5

net.ipv4.tcp_rmem = 8192     250000  16777216

net.ipv4.tcp_syn_retries = 5

net.ipv4.tcp_tso_win_divisor = 30

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_wmem = 8192     250000  16777216

net.ipv4.udp_mem = 725430    967240  1450860

#net.ipv4.tcp_max_orphans = 3276800

#net.ipv4.tcp_fin_timeout = 60

#net.ipv4.ip_local_port_range = 26000 65535

net.ipv4.tcp_retries2 = 80

#net.ipv4.ip_local_reserved_ports = 20050-30007

vm.min_free_kbytes = 40140150

数据库参数

参数项 MogDB
listen_addresses 具体ip
port 26000
max_connectIOns 4096
wal_level hot_standby
archive_mode on
archive_command /bin/ture
max_wal_senders 16
wal_keep_segments 16
max_replicatIOn_slots 8
hot_standby on
logging_collector on
log_directory 安装工具指定
log_filename PostgreSQL-%Y-%m-%d_%H%M%S.log
log_min_duratIOn_statement 1800000
log_line_prefix %m%c%d%p%a%x%n%e
log_timezone PRC
datestyle iso,mdy
timezone PRC
default_text_search_config pg_catalog.english
applicatIOn_name dn_6001
max_prepared_transactIOns 2048
shared_buffers 350GB
wal_buffers 1GB
work_mem 64MB
log_min_messages FATAL
synchronous_commit on
fsync on
maintenance_work_mem 2GB
autovacuum on
autovacuum_max_workers 5
autovacuum_naptime 20s
autovacuum_vacuum_cost_delay 10
enable_mergejoin off
enable_nestloop off
enable_hashjoin off
enable_bitmapscan on
enable_material off
wal_log_hints off
log_duratIOn off
checkpoint_timeout 15min
track_activities off
track_counts on
autovacuum_vacuum_scale_factor 0.02
autovacuum_analyze_scale_factor 0.1
ssl off
local_bind_address 具体IP
max_inner_tool_connectIOns 10
password_encryptIOn_type 0
comm_tcp_mode on
comm_quota_size 1024kB
max_process_memory 700GB
bulk_write_ring_size 2GB
checkpoint_segments 1024
incremental_checkpoint_timeout 60s
archive_dest /log/archive
enable_slot_log off
data_replicate_buffer_size 128MB
walsender_max_send_size 8MB
enable_kill_query off
connectIOn_alARM_rate 0.9
alARM_report_interval 10
alARM_component /opt/huawei/snas/bin/snas_cm_cmd
lockwait_timeout 1200s
pgxc_node_name xxx
audit_directory 安装工具指定
explain_perf_mode pretty
job_queue_processes 10
default_storage_nodegroup installatIOn
expected_computing_nodegroup query
replicatIOn_type 1
recovery_max_workers 4
available_zone AZ1
allow_concurrent_tuple_update TRUE
audit_enabled off
cstore_buffers 16MB
enable_alARM off
enable_codegen FALSE
enable_data_replicate off
max_file_per_process 10000
use_workload_manager off
xloginsert_locks 48
update_lockwait_timeout 20min
enable_save_datachanged_timestamp FALSE
enable_thread_pool off
enable_double_write on
enable_incremental_checkpoint on
advance_xlog_file_num 10
enable_instr_track_wait off
enable_instr_rt_percentile off
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_user_metric_persistent off
enable_xlog_prune off
enable_resource_track off
instr_unique_sql_count 0
enable_beta_opfusIOn on
enable_bete_netsloop_fusIOn on
remote_read_mode non_authenticatIOn
enable_page_lsn_check off
pagewriter_sleep 2s
enable_opfusIOn on
max_redo_log_size 100GB
pagewrite_thread_num 1
bgwrite_thread_num 1
dirty_page_percent_max 1
candidate_buf_percent_target 01

测试项及结论

测试结果汇总

测试项 数据量 并发数 CPU平均使用率 IOPS IO延时 WAL数量 tpmC 测试时长
单节点 100G 500 29.39% 6.50K 1.94.ms 3974 520896.3 10分钟
一主一备 100G 500 30.4% 5.31K 453.2us 3944 519993.5 10分钟
一主两备 100G 500 26.35% 7.66K 531.9us 3535 480842.2 10分钟

单节点

  • tpmC

    tpmC

  • 系统数据

    系统数据


一主一备

  • tpmC

    tpmC

  • 系统数据

    系统数据


一主两备

  • tpmC

    tpmC

  • 系统数据

    系统数据

Copyright © 2011-2024 www.enmotech.com All rights reserved.