DBLE 性能调研

[wsm12138]

最近在调研 DBLE，想使用 TPC-C 模型来探索下性能如何。
辛苦提供些思路及配置，协助 DBLE 性能调优
以下是调研 DBLE 的配置:

DBLE

bootstrap.cnf

-agentlib:jdwp=transport=dt_socket,server=y,address=8088,suspend=n
-server
-XX:+AggressiveOpts
-Dfile.encoding=UTF-8
-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=1984
-Dcom.sun.management.jmxremote.authenticate=false
-Dcom.sun.management.jmxremote.ssl=false
-Dcom.sun.management.jmxremote.host=127.0.0.1
-Xmx16G
-Xms16G
-Xss1m
-XX:MaxDirectMemorySize=2G
-XX:MetaspaceSize=100M
-XX:-OmitStackTraceInFastThrow
-XX:+PrintHeapAtGC
-XX:+PrintGCDateStamps
-Xloggc:./logs/gc_%WRAPPER_TIME_YYYYMMDDHHIISS%_%p.log
-XX:+PrintGCTimeStamps
-XX:+PrintGCDetails
-XX:+PrintTenuringDistribution
-XX:+UseConcMarkSweepGC
-XX:+UseParNewGC
-XX:+CMSParallelRemarkEnabled
-XX:+UseCMSCompactAtFullCollection
-XX:CMSFullGCsBeforeCompaction=0
-XX:+CMSClassUnloadingEnabled
-XX:LargePageSizeInBytes=128M
-XX:+UseFastAccessorMethods
-XX:+UseCMSInitiatingOccupancyOnly
-XX:CMSInitiatingOccupancyFraction=70
-DhomePath=.
-DinstanceName=1
-DinstanceId=1
-DserverId=xxx1
-Dprocessors=10
-DbackendProcessors=10
-DprocessorExecutor=8
-DbackendProcessorExecutor=6
-DfakeMySQLVersion=5.7.11
-DserverBacklog=2048
-DuseOuterHa=true
-DmaxPacketSize=167772160
-DtxIsolation=2
-DusingAIO=0
-DuseThreadUsageStat=1
-DcheckTableConsistency=0
-DcheckTableConsistencyPeriod=60000
-DprocessorCheckPeriod=1000
-DsqlExecuteTimeout=3000
-DotherMemSize=4
-DorderMemSize=4
-DjoinMemSize=4
-DbufferPoolChunkSize=32767
-DbufferPoolPageSize=2097152
-DuseSqlStat=1
-DclearBigSQLResultSetMapMs=600000
-DrecordTxn=0
-DxaSessionCheckPeriod=1000
-DxaLogCleanPeriod=1000
-DnestLoopConnSize=4
-DnestLoopRowsSize=2000
-DenableSlowLog=1
-DflushSlowLogPeriod=1
-DflushSlowLogSize=1000
-DsqlSlowTime=100

sharding.xml

<?xml version="1.0"?>
<dble:sharding xmlns:dble="http://dble.cloud/" version="4.0">
<schema name="dble">
    <singleTable name="bmsql_config" shardingNode="dn1" />
    <shardingTable name="bmsql_warehouse" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="w_id" />
    <shardingTable name="bmsql_district" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="d_w_id" />
    <shardingTable name="bmsql_customer" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="c_w_id" />
    <shardingTable name="bmsql_item" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="i_id" />
    <shardingTable name="bmsql_history" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="h_w_id" />
    <shardingTable name="bmsql_oorder" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="o_w_id" />
    <shardingTable name="bmsql_stock" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="s_w_id" />
    <shardingTable name="bmsql_new_order" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="no_w_id" />
    <shardingTable name="bmsql_order_line" shardingNode="dn$1-4" function="hash-sysbench" shardingColumn="ol_w_id" />
</schema>
<shardingNode name="dn1" dbGroup="host_1" database="dble4" />
<shardingNode name="dn2" dbGroup="host_2" database="dble4" />
<shardingNode name="dn3" dbGroup="host_3" database="dble4" />
<shardingNode name="dn4" dbGroup="host_4" database="dble4" />

<function name="hash-sysbench" class="Hash">
        <property name="partitionCount">4</property>
        <property name="partitionLength">1</property>
    </function>

</dble:sharding>

BenchmarkSQL

props.dble

driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://ip:8066/dble?serverTimezone=UTC&useSSL=false&cachePrepStmts=true&prepStmtCacheSize=8000
user=test
password=111111

warehouses=1000

loadWorkers=1001

terminals=500

//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=2
//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

//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=user@dbhost
//osCollectorDevices=net_eth0 blk_sda

MySQL

my.cnf

server_id=13306
port =3306
basedir=/usr/local/mysql
datadir=/usr1/data
log-error=/usr1/data/mysql.err
character_set_server = utf8mb4
pid-file=/usr1/data/mysql.pid
socket=/tmp/mysql3306.sock
max_connections=50000
default_authentication_plugin=mysql_native_password

innodb_buffer_pool_size=128G
innodb-log-file-size=5000000000
innodb-log-files-in-group=5
innodb-flush-log-at-trx-commit=0
innodb-change-buffer-max-size=40
back_log=900
innodb_max_dirty_pages_pct=75
innodb_open_files=20480
innodb_buffer_pool_instances=8
innodb_page_cleaners=8
innodb_purge_threads=2
innodb_read_io_threads=8
innodb_write_io_threads=8
table_open_cache=102400
log_timestamps=system
thread_cache_size=16384
innodb_buffer_pool_instances = 16
innodb_lru_scan_depth=256
innodb_sort_buffer_size = 64M
innodb_buffer_pool_instances = 16
max_prepared_stmt_count=1048576
join_buffer_size = 128M
tmp_table_size = 64M
sort_buffer_size = 32M
read_rnd_buffer_size = 32M
read_buffer_size = 16M
transaction_isolation = READ-COMMITTED
innodb_directories = /usr3/
skip-log-bin
innodb_doublewrite=0
innodb_flush_method=nosync
innodb_flush_sync=OFF
innodb_checksum_algorithm=none
innodb_log_checksums=OFF

[LUAgam]

benchmarksql的调整过程可以参考：https://mp.weixin.qq.com/s/mOrOCyVtSs9UPuMooC3F0A
后端MySQL的参数配置需要自行调整，可通过benchmarksql压测mysql来得到最优配置