Performance Test Report for MogDB on x86 Servers

Test Objective

This document describes the test of MogDB 1.1.0 on x86 servers in scenarios where MogDB is deployed on a single node, one primary node and one standby node, or one primary node and two standby nodes (one synchronous standby and one asynchronous standby).

Test Environment

Environment Configuration

Test Tool

Test Procedure

MogDB Database Operation

1. Obtain the database installation package.

2. Install the database.

3. Create TPCC test user and database.

   create user [username] identified by ‘passwd’;
   grant [origin user] to [username];
   create database [dbname];

4. Disable the database and modify the postgresql.conf database configuration file by adding configuration parameters at the end of the file.

   For example, add the following parameters in the single node test:

   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 Operation

1. Modify the configuration file.

   Open the BenchmarkSQL installation directory and find the [config file] configuration file in the run directory.

   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. Run runDataBuild.sh to generate data.

   ./runDatabaseBuild.sh [config file]

3. Run runBenchmark.sh to test the database.

   ./runBenchmark.sh [config file]

OS Parameters

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

Database Parameters

Test Items and Conclusions

Test Result Summary

Single Node

• tpmC

  

  System data

  

One Primary Node and One Standby Node

• tpmC

  

• System data

  

One Primary Node and Two Standby Nodes

• tpmC

  

• System data