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

  

• 系统数据