MogDB on x86性能测试

测试目的

本文主要讲述了对MogDB 2.0.0 版本on x86进行的单节点和一主多备（同步备、异步备）的测试。

测试环境

环境配置

测试工具

测试步骤

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

数据库参数

测试项及结论

测试结果汇总

单节点

• tpmC

  

• 系统数据

  

一主一备

• tpmC

  

• 系统数据

  

一主两备

• tpmC

  

• 系统数据