polardb mysql 比较_PolarDB-X 1.0和RDS性能对比之吞吐量对比（二）

最新推荐文章于 2024-03-28 14:32:03 发布

weixin_39623082

最新推荐文章于 2024-03-28 14:32:03 发布

阅读量873

点赞数

文章标签： polardb mysql 比较

本文链接：https://blog.csdn.net/weixin_39623082/article/details/113477554

版权

测试环境：

PolarDB-X 标准版：16C64G(1台)

RDS MySQL5.7： 16C64G(4台)

ECS 压测机： 8C16G(1台)

sysbench 0.5

通过阿里云官方文档，找到oltp_drds.lua，如下：

pathtest = string.match(test, "(.*/)")

if pathtest then

dofile(pathtest .. "common.lua")

else

require("common")

end

function get_range_end(start)

return start + oltp_range_size - 1

end

function thread_init(thread_id)

set_vars()

if (((db_driver == "mysql") or (db_driver == "attachsql")) and mysql_table_engine == "myisam") then

local i

local tables = {}

for i=1, oltp_tables_count do

tables[i] = string.format("sbtest%i WRITE", i)

end

begin_query = "LOCK TABLES " .. table.concat(tables, " ,")

commit_query = "UNLOCK TABLES"

else

begin_query = "BEGIN"

commit_query = "COMMIT"

end

function event(thread_id)

local rs

local i

local table_name

local range_start

local c_val

local pad_val

local query

table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)

if not oltp_skip_trx then

db_query(begin_query)

end

if not oltp_write_only then

for i=1, oltp_point_selects do

rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))

end

if oltp_range_selects then

for i=1, oltp_simple_ranges do

range_start = sb_rand(1, oltp_table_size)

rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. get_range_end(range_start))

end

for i=1, oltp_sum_ranges do

range_start = sb_rand(1, oltp_table_size)

rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. get_range_end(range_start))

end

for i=1, oltp_order_ranges do

range_start = sb_rand(1, oltp_table_size)

rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. get_range_end(range_start) .. " ORDER BY c")

end

for i=1, oltp_distinct_ranges do

range_start = sb_rand(1, oltp_table_size)

rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. get_range_end(range_start) .. " ORDER BY c")

end

if not oltp_read_only then

for i=1, oltp_index_updates do

rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))

end

for i=1, oltp_non_index_updates do

c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")

query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)

rs = db_query(query)

if rs then

print(query)

end

for i=1, oltp_delete_inserts do

i = sb_rand(1, oltp_table_size)

rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)

c_val = sb_rand_str([[

###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])

pad_val = sb_rand_str([[

###########-###########-###########-###########-###########]])

rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))

end

end -- oltp_read_only

if not oltp_skip_trx then

db_query(commit_query)

end

接下来进行三轮测试，线程数分别为1000、2000和5000

第一轮测试：每个测试都是1000个线程。

测试一：

直连RDS，4张表，每个表1亿条记录，不分库不分表。

/usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=hlj-prt-mdb.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-db=sysbenchtest --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --num-threads=1000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 1000, tps: 0.00, reads: 19008.31, writes: 4757.46, response time: 1453.76ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 1000, tps: 0.00, reads: 15811.06, writes: 4523.35, response time: 1881.71ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 1000, tps: 0.00, reads: 16982.31, writes: 4862.33, response time: 2066.54ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 1000, tps: 0.00, reads: 17089.70, writes: 4815.01, response time: 2436.37ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 1000, tps: 0.00, reads: 17524.32, writes: 5071.33, response time: 2253.27ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 1000, tps: 0.00, reads: 17377.98, writes: 4916.66, response time: 2581.27ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 1000, tps: 0.00, reads: 17707.68, writes: 5092.34, response time: 2613.15ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 1000, tps: 0.00, reads: 17744.99, writes: 5068.66, response time: 2279.73ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 1000, tps: 0.00, reads: 18033.69, writes: 5126.67, response time: 2358.16ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 1000, tps: 0.00, reads: 18720.99, writes: 5368.66, response time: 2474.59ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 1000, tps: 0.00, reads: 17656.00, writes: 5054.33, response time: 2150.47ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 1000, tps: 0.00, reads: 19473.00, writes: 5571.67, response time: 2128.69ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 1000, tps: 0.00, reads: 19023.66, writes: 5391.00, response time: 2297.54ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 1000, tps: 0.00, reads: 19932.01, writes: 5676.34, response time: 2294.11ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 1000, tps: 0.00, reads: 19776.10, writes: 5724.70, response time: 1990.64ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 1000, tps: 0.00, reads: 21788.88, writes: 6174.97, response time: 1995.41ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 1000, tps: 0.00, reads: 20492.34, writes: 5845.33, response time: 2452.47ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 1000, tps: 0.00, reads: 21967.34, writes: 6295.34, response time: 2302.36ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 1000, tps: 0.00, reads: 22879.32, writes: 6522.33, response time: 2000.20ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 1000, tps: 0.00, reads: 23728.91, writes: 6824.31, response time: 2006.80ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 329652

other: 0

total: 1483434

transactions: 0 (0.00 per sec.)

read/write requests: 1483434 (24431.99 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.7169s

total number of events: 82413

total time taken by event execution: 60324.5293s

response time:

min: 28.02ms

avg: 731.98ms

max: 8960.15ms

approx. 95 percentile: 2217.81ms

Threads fairness:

events (avg/stddev): 82.4130/8.80

execution time (avg/stddev): 60.3245/0.21

平均QPS为24431，平均响应时间为731ms。

测试二：

PolarDB-X+RDS x 1 分库分表，4张表，每个表1亿条记录。

/usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp_drds.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser_123 --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=drdsbggamlqgcm2o.drds.aliyuncs.com --mysql-port=3306 --mysql-db=polardb_test --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4' --num-threads=1000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 1000, tps: 0.00, reads: 13393.86, writes: 3047.91, response time: 1593.21ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 1000, tps: 0.00, reads: 12526.71, writes: 3604.68, response time: 1691.50ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 1000, tps: 0.00, reads: 12914.27, writes: 3705.31, response time: 1627.43ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 1000, tps: 0.00, reads: 13467.38, writes: 3839.68, response time: 1610.47ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 1000, tps: 0.00, reads: 13495.69, writes: 3810.01, response time: 1695.56ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 1000, tps: 0.00, reads: 13687.63, writes: 3987.99, response time: 1570.48ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 1000, tps: 0.00, reads: 13227.35, writes: 3765.67, response time: 1493.01ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 1000, tps: 0.00, reads: 13168.69, writes: 3711.67, response time: 1657.91ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 1000, tps: 0.00, reads: 13564.97, writes: 3958.66, response time: 1591.78ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 1000, tps: 0.00, reads: 14030.35, writes: 3972.67, response time: 1430.02ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 1000, tps: 0.00, reads: 14764.35, writes: 4214.67, response time: 1252.42ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 1000, tps: 0.00, reads: 14663.63, writes: 4250.66, response time: 1281.24ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 1000, tps: 0.00, reads: 15126.03, writes: 4289.34, response time: 1212.58ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 1000, tps: 0.00, reads: 15176.33, writes: 4269.33, response time: 1189.57ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 1000, tps: 0.00, reads: 15593.65, writes: 4399.00, response time: 1202.10ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 1000, tps: 0.00, reads: 16670.33, writes: 4820.33, response time: 1082.53ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 1000, tps: 0.00, reads: 17699.68, writes: 5006.67, response time: 1077.36ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 1000, tps: 0.00, reads: 17738.33, writes: 5136.66, response time: 978.36ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 1000, tps: 0.00, reads: 18391.33, writes: 5247.67, response time: 977.48ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 1000, tps: 0.00, reads: 18851.67, writes: 5368.67, response time: 968.75ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 257008

other: 0

total: 1156536

transactions: 0 (0.00 per sec.)

read/write requests: 1156536 (19126.43 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.4679s

total number of events: 64252

total time taken by event execution: 60274.6103s

response time:

min: 190.35ms

avg: 938.10ms

max: 28872.80ms

approx. 95 percentile: 1421.06ms

Threads fairness:

events (avg/stddev): 64.2520/3.15

execution time (avg/stddev): 60.2746/0.14

平均QPS为19126，平均响应时间为938ms。

测试三：

PolarDB-X+RDS x 2 分库分表，4张表，每个表1亿条记录。

/usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp_drds.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser_123 --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=drdsbggamlqgcm2o.drds.aliyuncs.com --mysql-port=3306 --mysql-db=polardb1 --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4' --num-threads=1000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 1000, tps: 0.00, reads: 28019.89, writes: 7169.06, response time: 652.35ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 1000, tps: 0.00, reads: 27098.72, writes: 7750.35, response time: 696.96ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 1000, tps: 0.00, reads: 29117.97, writes: 8397.66, response time: 611.14ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 1000, tps: 0.00, reads: 28496.66, writes: 8215.66, response time: 641.50ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 1000, tps: 0.00, reads: 28862.72, writes: 8093.35, response time: 667.16ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 1000, tps: 0.00, reads: 29154.66, writes: 8358.00, response time: 629.52ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 1000, tps: 0.00, reads: 28560.34, writes: 8116.67, response time: 672.57ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 1000, tps: 0.00, reads: 28415.34, writes: 8159.67, response time: 624.26ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 1000, tps: 0.00, reads: 28917.99, writes: 8313.33, response time: 674.99ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 1000, tps: 0.00, reads: 28030.35, writes: 8008.00, response time: 663.57ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 1000, tps: 0.00, reads: 28821.28, writes: 8318.65, response time: 606.94ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 1000, tps: 0.00, reads: 27747.70, writes: 7794.34, response time: 668.36ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 1000, tps: 0.00, reads: 28850.99, writes: 8325.66, response time: 630.84ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 1000, tps: 0.00, reads: 27551.35, writes: 7800.67, response time: 690.94ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 1000, tps: 0.00, reads: 27826.22, writes: 7986.30, response time: 670.56ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 1000, tps: 0.00, reads: 28426.10, writes: 8062.36, response time: 698.00ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 1000, tps: 0.00, reads: 27804.00, writes: 7960.67, response time: 675.40ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 1000, tps: 0.00, reads: 28487.64, writes: 8165.99, response time: 639.39ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 1000, tps: 0.00, reads: 26126.37, writes: 7396.01, response time: 730.28ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 1000, tps: 0.00, reads: 27593.01, writes: 7965.34, response time: 684.35ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 484796

other: 0

total: 2181582

transactions: 0 (0.00 per sec.)

read/write requests: 2181582 (36160.85 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.3299s

total number of events: 121199

total time taken by event execution: 60189.5542s

response time:

min: 195.49ms

avg: 496.62ms

max: 960.44ms

approx. 95 percentile: 663.77ms

Threads fairness:

events (avg/stddev): 121.1990/6.58

execution time (avg/stddev): 60.1896/0.09

平均QPS为36160，平均响应时间为496ms。

测试四：

PolarDB-X+RDS x 4 分库分表，4张表，每个表1亿条记录。

/usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp_drds.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser_123 --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=drdsbggamlqgcm2o.drds.aliyuncs.com --mysql-port=3306 --mysql-db=polardb2 --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4' --num-threads=1000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 1000, tps: 0.00, reads: 29787.76, writes: 7699.07, response time: 591.87ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 1000, tps: 0.00, reads: 27974.10, writes: 8002.36, response time: 694.05ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 1000, tps: 0.00, reads: 28439.01, writes: 8110.00, response time: 628.01ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 1000, tps: 0.00, reads: 28933.00, writes: 8250.00, response time: 655.09ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 1000, tps: 0.00, reads: 28384.65, writes: 8084.66, response time: 647.87ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 1000, tps: 0.00, reads: 27132.94, writes: 7674.98, response time: 705.57ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 1000, tps: 0.00, reads: 27655.07, writes: 8082.69, response time: 668.96ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 1000, tps: 0.00, reads: 28205.34, writes: 7969.33, response time: 636.72ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 1000, tps: 0.00, reads: 27698.00, writes: 7938.00, response time: 654.69ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 1000, tps: 0.00, reads: 27834.66, writes: 8027.33, response time: 673.98ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 1000, tps: 0.00, reads: 28422.33, writes: 8095.33, response time: 683.74ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 1000, tps: 0.00, reads: 28710.31, writes: 8224.66, response time: 607.85ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 1000, tps: 0.00, reads: 27460.03, writes: 7902.34, response time: 666.56ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 1000, tps: 0.00, reads: 28705.67, writes: 8126.67, response time: 627.07ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 1000, tps: 0.00, reads: 28635.67, writes: 8189.33, response time: 641.31ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 1000, tps: 0.00, reads: 28336.60, writes: 8122.65, response time: 639.97ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 1000, tps: 0.00, reads: 22608.39, writes: 6467.02, response time: 1729.39ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 1000, tps: 0.00, reads: 28560.33, writes: 8084.00, response time: 628.95ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 1000, tps: 0.00, reads: 26484.99, writes: 7647.66, response time: 689.70ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 1000, tps: 0.00, reads: 28253.33, writes: 8075.00, response time: 677.62ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 480192

other: 0

total: 2160864

transactions: 0 (0.00 per sec.)

read/write requests: 2160864 (35770.16 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.4097s

total number of events: 120048

total time taken by event execution: 60258.8000s

response time:

min: 214.12ms

avg: 501.96ms

max: 1969.11ms

approx. 95 percentile: 660.60ms

Threads fairness:

events (avg/stddev): 120.0480/6.20

execution time (avg/stddev): 60.2588/0.09

平均QPS为35770，平均响应时间为501ms。

第二轮测试

：每个测试都是2000个线程。

测试一：

直连RDS，4张表，每个表1亿条记录，不分库不分表。

[root@sysbench sysbench]# /usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=hlj-prt-mdb.mysql.rds.aliyuncs.com --mysql-port=3306 --mysql-db=sysbenchtest --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --num-threads=2000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 2000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 2000, tps: 0.00, reads: 8007.68, writes: 858.19, response time: 2998.02ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 2000, tps: 0.00, reads: 8609.35, writes: 2545.01, response time: 5621.31ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 2000, tps: 0.00, reads: 10409.60, writes: 2964.65, response time: 6904.76ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 2000, tps: 0.00, reads: 12536.07, writes: 3581.69, response time: 6180.85ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 2000, tps: 0.00, reads: 14334.96, writes: 4141.32, response time: 6687.11ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 2000, tps: 0.00, reads: 16145.33, writes: 4590.33, response time: 7893.31ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 2000, tps: 0.00, reads: 17691.40, writes: 5039.02, response time: 8292.95ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 2000, tps: 0.00, reads: 19727.60, writes: 5610.31, response time: 9488.77ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 2000, tps: 0.00, reads: 20652.39, writes: 5952.35, response time: 7287.00ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 2000, tps: 0.00, reads: 22149.25, writes: 6345.31, response time: 5902.39ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 2000, tps: 0.00, reads: 22610.99, writes: 6439.66, response time: 5181.76ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 2000, tps: 0.00, reads: 24004.08, writes: 6853.36, response time: 4685.94ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 2000, tps: 0.00, reads: 25188.27, writes: 7184.98, response time: 7086.94ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 2000, tps: 0.00, reads: 27350.96, writes: 7837.32, response time: 4274.51ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 2000, tps: 0.00, reads: 28234.75, writes: 8092.02, response time: 3187.76ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 2000, tps: 0.00, reads: 29231.93, writes: 8259.31, response time: 3214.59ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 2000, tps: 0.00, reads: 30905.05, writes: 8923.35, response time: 3191.57ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 2000, tps: 0.00, reads: 30901.34, writes: 8850.33, response time: 3680.32ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 2000, tps: 0.00, reads: 32390.76, writes: 9181.69, response time: 3189.66ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 2000, tps: 0.00, reads: 33255.55, writes: 9598.30, response time: 3490.40ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 375392

other: 0

total: 1689264

transactions: 0 (0.00 per sec.)

read/write requests: 1689264 (27792.44 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.7814s

total number of events: 93848

total time taken by event execution: 120641.2549s

response time:

min: 29.25ms

avg: 1285.50ms

max: 20599.73ms

approx. 95 percentile: 4986.97ms

Threads fairness:

events (avg/stddev): 46.9240/9.74

execution time (avg/stddev): 60.3206/0.21

平均QPS为27792，平均响应时间为1285ms。

测试二：

PolarDB-X+RDS x 1 分库分表，4张表，每个表1亿条记录。

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 2000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 2000, tps: 0.00, reads: 16997.58, writes: 2834.15, response time: 2133.16ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 2000, tps: 0.00, reads: 13520.05, writes: 4542.02, response time: 2398.74ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 2000, tps: 0.00, reads: 15070.67, writes: 4073.33, response time: 2505.90ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 2000, tps: 0.00, reads: 14738.00, writes: 4346.67, response time: 2396.59ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 2000, tps: 0.00, reads: 15485.00, writes: 4265.67, response time: 2389.43ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 2000, tps: 0.00, reads: 15969.94, writes: 4566.32, response time: 2297.54ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 2000, tps: 0.00, reads: 16184.72, writes: 4704.35, response time: 2198.64ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 2000, tps: 0.00, reads: 16609.99, writes: 4681.66, response time: 2129.97ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 2000, tps: 0.00, reads: 16883.01, writes: 4835.34, response time: 2119.79ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 2000, tps: 0.00, reads: 17044.33, writes: 4724.67, response time: 2098.96ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 2000, tps: 0.00, reads: 17702.67, writes: 5104.33, response time: 2066.54ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 2000, tps: 0.00, reads: 17928.66, writes: 5342.66, response time: 2089.55ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 2000, tps: 0.00, reads: 18806.68, writes: 5324.00, response time: 1886.22ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 2000, tps: 0.00, reads: 19190.33, writes: 5544.33, response time: 1826.22ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 2000, tps: 0.00, reads: 19577.32, writes: 5413.33, response time: 1735.61ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 2000, tps: 0.00, reads: 19919.67, writes: 5699.67, response time: 1696.57ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 2000, tps: 0.00, reads: 19865.67, writes: 5769.00, response time: 1713.41ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 2000, tps: 0.00, reads: 20882.67, writes: 5826.67, response time: 1622.08ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 2000, tps: 0.00, reads: 20928.98, writes: 6115.66, response time: 1538.83ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 1999, tps: 0.00, reads: 21576.04, writes: 6128.68, response time: 1581.33ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 306988

other: 0

total: 1381446

transactions: 0 (0.00 per sec.)

read/write requests: 1381446 (22735.69 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.7611s

total number of events: 76747

total time taken by event execution: 120915.1885s

response time:

min: 674.37ms

avg: 1575.50ms

max: 3622.02ms

approx. 95 percentile: 2138.27ms

Threads fairness:

events (avg/stddev): 38.3735/1.37

execution time (avg/stddev): 60.4576/0.23

平均QPS为22735，平均响应时间为1575ms。

测试三：

PolarDB-X+RDS x 2 分库分表，4张表，每个表1亿条记录。

/usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp_drds.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser_123 --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=drdsbggamlqgcm2o.drds.aliyuncs.com --mysql-port=3306 --mysql-db=polardb1 --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4' --num-threads=2000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 2000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 2000, tps: 0.00, reads: 30375.81, writes: 6945.86, response time: 1149.66ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 2000, tps: 0.00, reads: 28717.72, writes: 8305.02, response time: 1239.00ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 2000, tps: 0.00, reads: 28909.29, writes: 8445.42, response time: 1170.50ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 2000, tps: 0.00, reads: 28400.29, writes: 7842.90, response time: 1244.95ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 2000, tps: 0.00, reads: 28851.04, writes: 8276.77, response time: 1191.00ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 2000, tps: 0.00, reads: 26889.41, writes: 7823.93, response time: 1374.62ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 2000, tps: 0.00, reads: 28306.94, writes: 8181.41, response time: 1280.09ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 2000, tps: 0.00, reads: 28484.62, writes: 8062.65, response time: 1171.90ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 2000, tps: 0.00, reads: 28837.68, writes: 8160.00, response time: 1237.52ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 2000, tps: 0.00, reads: 28533.97, writes: 8333.32, response time: 1184.24ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 2000, tps: 0.00, reads: 28663.43, writes: 7941.60, response time: 1228.66ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 2000, tps: 0.00, reads: 28487.64, writes: 8394.76, response time: 1236.04ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 2000, tps: 0.00, reads: 28917.60, writes: 8059.32, response time: 1187.08ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 2000, tps: 0.00, reads: 27693.45, writes: 7869.94, response time: 1229.76ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 2000, tps: 0.00, reads: 28192.95, writes: 8138.75, response time: 1255.05ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 2000, tps: 0.00, reads: 27821.84, writes: 8024.52, response time: 1208.23ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 2000, tps: 0.00, reads: 27382.88, writes: 7783.73, response time: 1249.80ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 2000, tps: 0.00, reads: 27203.25, writes: 7878.07, response time: 1271.69ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 2000, tps: 0.00, reads: 28515.99, writes: 8270.33, response time: 1230.87ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 2000, tps: 0.00, reads: 28312.69, writes: 7835.67, response time: 1258.81ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 489056

other: 0

total: 2200752

transactions: 0 (0.00 per sec.)

read/write requests: 2200752 (36334.97 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.5684s

total number of events: 122264

total time taken by event execution: 120643.6805s

response time:

min: 413.80ms

avg: 986.75ms

max: 1746.30ms

approx. 95 percentile: 1231.23ms

Threads fairness:

events (avg/stddev): 61.1320/3.24

execution time (avg/stddev): 60.3218/0.16

平均QPS为36335，平均响应时间为986ms。

测试四：

PolarDB-X+RDS x 4 分库分表，4张表，每个表1亿条记录。

/usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp_drds.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser_123 --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=drdsbggamlqgcm2o.drds.aliyuncs.com --mysql-port=3306 --mysql-db=polardb2 --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4' --num-threads=2000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 2000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 2000, tps: 0.00, reads: 28987.55, writes: 6756.18, response time: 1195.64ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 2000, tps: 0.00, reads: 28278.10, writes: 8094.36, response time: 1242.34ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 2000, tps: 0.00, reads: 27627.01, writes: 8036.67, response time: 1214.76ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 2000, tps: 0.00, reads: 27821.65, writes: 7719.66, response time: 1297.06ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 2000, tps: 0.00, reads: 27398.31, writes: 7951.66, response time: 1320.17ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 2000, tps: 0.00, reads: 24377.69, writes: 6901.67, response time: 1389.51ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 2000, tps: 0.00, reads: 26543.34, writes: 7583.67, response time: 1269.79ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 2000, tps: 0.00, reads: 25436.64, writes: 7304.32, response time: 1318.59ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 2000, tps: 0.00, reads: 26810.66, writes: 7467.67, response time: 1308.76ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 2000, tps: 0.00, reads: 26911.08, writes: 7885.02, response time: 1252.05ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 2000, tps: 0.00, reads: 26195.43, writes: 7450.60, response time: 1298.23ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 2000, tps: 0.00, reads: 26893.50, writes: 7595.71, response time: 1316.23ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 2000, tps: 0.00, reads: 27461.69, writes: 7886.67, response time: 1302.12ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 2000, tps: 0.00, reads: 27118.30, writes: 7805.32, response time: 1262.96ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 2000, tps: 0.00, reads: 26545.27, writes: 7712.69, response time: 1310.72ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 2000, tps: 0.00, reads: 27248.07, writes: 7733.64, response time: 1279.33ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 2000, tps: 0.00, reads: 27064.39, writes: 7649.35, response time: 1245.69ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 2000, tps: 0.00, reads: 28050.72, writes: 7713.01, response time: 1263.34ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 2000, tps: 0.00, reads: 26776.58, writes: 7952.98, response time: 1300.95ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 2000, tps: 0.00, reads: 27386.35, writes: 7679.34, response time: 1218.77ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 466256

other: 0

total: 2098152

transactions: 0 (0.00 per sec.)

read/write requests: 2098152 (34626.68 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.5935s

total number of events: 116564

total time taken by event execution: 120707.0844s

response time:

min: 423.71ms

avg: 1035.54ms

max: 2282.45ms

approx. 95 percentile: 1288.55ms

Threads fairness:

events (avg/stddev): 58.2820/3.50

execution time (avg/stddev): 60.3535/0.15

平均QPS为34626，平均响应时间为1035ms。

第三轮测试：每个测试都是5000个线程。

测试一：

直连RDS，4张表，每个表1亿条记录，不分库不分表。

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 5000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 5000, tps: 0.00, reads: 37359.17, writes: 7279.03, response time: 2751.21ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 5000, tps: 0.00, reads: 38007.54, writes: 10971.96, response time: 2674.08ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 5000, tps: 0.00, reads: 38382.25, writes: 10995.07, response time: 2678.08ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 5000, tps: 0.00, reads: 37929.12, writes: 10869.37, response time: 2796.05ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 5000, tps: 0.00, reads: 37995.16, writes: 10833.19, response time: 2943.78ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 5000, tps: 0.00, reads: 35917.23, writes: 10232.40, response time: 3197.31ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 5000, tps: 0.00, reads: 35513.69, writes: 10140.34, response time: 3354.17ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 5000, tps: 0.00, reads: 34832.22, writes: 9944.39, response time: 3531.39ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 5000, tps: 0.00, reads: 36050.71, writes: 10277.34, response time: 3546.22ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 5000, tps: 0.00, reads: 35856.78, writes: 10299.27, response time: 3458.16ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 5000, tps: 0.00, reads: 36944.55, writes: 10547.06, response time: 3540.91ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 5000, tps: 0.00, reads: 36243.65, writes: 10349.57, response time: 3338.15ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 5000, tps: 0.00, reads: 35570.37, writes: 10174.44, response time: 3585.71ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 5000, tps: 0.00, reads: 35570.24, writes: 10190.97, response time: 3462.31ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 5000, tps: 0.00, reads: 35755.34, writes: 10236.00, response time: 3407.81ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 5000, tps: 0.00, reads: 35656.32, writes: 10113.57, response time: 3557.91ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 5000, tps: 0.00, reads: 36127.07, writes: 10293.78, response time: 3374.31ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 5000, tps: 0.00, reads: 36704.12, writes: 10581.94, response time: 3323.19ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 5000, tps: 0.00, reads: 36560.81, writes: 10379.71, response time: 3327.17ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 5000, tps: 0.00, reads: 36380.11, writes: 10455.70, response time: 3359.20ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 633668

other: 0

total: 2851506

transactions: 0 (0.00 per sec.)

read/write requests: 2851506 (46676.39 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 61.0910s

total number of events: 158417

total time taken by event execution: 302734.3334s

response time:

min: 39.51ms

avg: 1911.00ms

max: 7278.53ms

approx. 95 percentile: 3282.65ms

Threads fairness:

events (avg/stddev): 31.6834/2.29

execution time (avg/stddev): 60.5469/0.27

平均QPS为46676，平均响应时间为1911ms。

测试二：

PolarDB-X+RDS x 1 分库分表，4张表，每个表1亿条记录。

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 5000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 5000, tps: 0.00, reads: 11660.88, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 5000, tps: 0.00, reads: 4870.34, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 5000, tps: 0.00, reads: 456.33, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 5000, tps: 0.00, reads: 572.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 5000, tps: 0.00, reads: 2606.32, writes: 9.33, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 5000, tps: 0.00, reads: 4913.31, writes: 3475.65, response time: 18005.81ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 5000, tps: 0.00, reads: 11092.73, writes: 2923.35, response time: 20812.93ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 5000, tps: 0.00, reads: 8469.68, writes: 2144.67, response time: 21938.83ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 5000, tps: 0.00, reads: 10101.66, writes: 2721.00, response time: 8809.87ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 5000, tps: 0.00, reads: 8886.34, writes: 2962.67, response time: 10083.24ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 5000, tps: 0.00, reads: 11062.97, writes: 3087.33, response time: 10606.46ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 5000, tps: 0.00, reads: 9534.67, writes: 2651.33, response time: 9297.57ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 5000, tps: 0.00, reads: 10693.97, writes: 3258.66, response time: 9557.18ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 5000, tps: 0.00, reads: 11278.06, writes: 3046.68, response time: 10253.69ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 5000, tps: 0.00, reads: 10991.00, writes: 3204.33, response time: 9115.69ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 5000, tps: 0.00, reads: 11435.67, writes: 3283.00, response time: 9300.36ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 5000, tps: 0.00, reads: 10971.26, writes: 3175.31, response time: 9756.63ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 5000, tps: 0.00, reads: 11874.01, writes: 3344.00, response time: 9004.50ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 5000, tps: 0.00, reads: 12149.67, writes: 3565.67, response time: 8913.32ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 5000, tps: 0.00, reads: 12683.06, writes: 3591.68, response time: 8660.83ms (95%), errors: 0.00, reconnects: 0.00

[ 63s] threads: 5000, tps: 0.00, reads: 7757.68, writes: 5261.34, response time: 7727.32ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 158176

other: 0

total: 711792

transactions: 0 (0.00 per sec.)

read/write requests: 711792 (11204.65 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 63.5265s

total number of events: 39544

total time taken by event execution: 310395.2970s

response time:

min: 2188.99ms

avg: 7849.37ms

max: 25639.60ms

approx. 95 percentile: 18894.82ms

Threads fairness:

events (avg/stddev): 7.9088/1.17

execution time (avg/stddev): 62.0791/0.97

平均QPS为11204，平均响应时间为7849ms。

测试三：

PolarDB-X+RDS x 2 分库分表，4张表，每个表1亿条记录。

/usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp_drds.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser_123 --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=drdsbggamlqgcm2o.drds.aliyuncs.com --mysql-port=3306 --mysql-db=polardb1 --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4' --num-threads=5000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 5000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 5000, tps: 0.00, reads: 17118.22, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 5000, tps: 0.00, reads: 82.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 5000, tps: 0.00, reads: 251.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 5000, tps: 0.00, reads: 3701.00, writes: 667.33, response time: 12020.18ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 5000, tps: 0.00, reads: 23030.66, writes: 7390.33, response time: 13646.82ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 5000, tps: 0.00, reads: 14281.69, writes: 4186.34, response time: 3699.10ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 5000, tps: 0.00, reads: 24695.89, writes: 7331.63, response time: 6915.10ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 5000, tps: 0.00, reads: 17464.65, writes: 4941.00, response time: 4992.95ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 5000, tps: 0.00, reads: 13608.36, writes: 3525.34, response time: 6313.61ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 5000, tps: 0.00, reads: 22826.01, writes: 6709.00, response time: 8204.07ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 5000, tps: 0.00, reads: 11591.35, writes: 3403.01, response time: 7410.18ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 5000, tps: 0.00, reads: 20925.29, writes: 5822.66, response time: 5993.19ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 5000, tps: 0.00, reads: 11318.99, writes: 3451.66, response time: 7947.84ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 5000, tps: 0.00, reads: 23313.74, writes: 6724.35, response time: 7326.37ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 5000, tps: 0.00, reads: 19346.02, writes: 5443.34, response time: 4238.83ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 5000, tps: 0.00, reads: 20299.72, writes: 6435.58, response time: 7771.39ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 5000, tps: 0.00, reads: 25965.52, writes: 7082.72, response time: 4314.36ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 5000, tps: 0.00, reads: 25723.78, writes: 7427.37, response time: 6214.24ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 5000, tps: 0.00, reads: 25466.05, writes: 7131.01, response time: 3543.04ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 5000, tps: 0.00, reads: 24159.96, writes: 7165.66, response time: 4460.12ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 303004

other: 0

total: 1363518

transactions: 0 (0.00 per sec.)

read/write requests: 1363518 (22147.84 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 61.5644s

total number of events: 75751

total time taken by event execution: 304625.9730s

response time:

min: 1174.45ms

avg: 4021.41ms

max: 15089.30ms

approx. 95 percentile: 12333.63ms

Threads fairness:

events (avg/stddev): 15.1502/3.32

execution time (avg/stddev): 60.9252/0.43

平均QPS为22147，平均响应时间为4021ms。

测试四：

PolarDB-X+RDS x 4 分库分表，4张表，每个表1亿条记录。

/usr/local/bin/sysbench --test='/usr/local/share/sysbench/oltp_drds.lua' --oltp_tables_count=4 --oltp-table-size=100000000 --mysql-user=sysbenchuser --mysql-password=sysbenchuser_123 --mysql-table-engine=innodb --rand-init=on --report-interval=3 --mysql-host=drdsbggamlqgcm2o.drds.aliyuncs.com --mysql-port=3306 --mysql-db=polardb2 --max-time=60 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4' --num-threads=5000 run

sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 5000

Report intermediate results every 3 second(s)

Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored

Initializing worker threads...

Threads started!

[ 3s] threads: 5000, tps: 0.00, reads: 16830.55, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 6s] threads: 5000, tps: 0.00, reads: 315.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 9s] threads: 5000, tps: 0.00, reads: 667.67, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00

[ 12s] threads: 5000, tps: 0.00, reads: 11573.66, writes: 3981.33, response time: 11923.42ms (95%), errors: 0.00, reconnects: 0.00

[ 15s] threads: 5000, tps: 0.00, reads: 14659.00, writes: 3901.33, response time: 13516.72ms (95%), errors: 0.00, reconnects: 0.00

[ 18s] threads: 5000, tps: 0.00, reads: 23777.69, writes: 7787.34, response time: 5902.39ms (95%), errors: 0.00, reconnects: 0.00

[ 21s] threads: 5000, tps: 0.00, reads: 24846.24, writes: 7096.97, response time: 3741.41ms (95%), errors: 0.00, reconnects: 0.00

[ 24s] threads: 5000, tps: 0.00, reads: 27099.47, writes: 7583.70, response time: 3933.20ms (95%), errors: 0.00, reconnects: 0.00

[ 27s] threads: 5000, tps: 0.00, reads: 26316.97, writes: 7404.99, response time: 3653.98ms (95%), errors: 0.00, reconnects: 0.00

[ 30s] threads: 5000, tps: 0.00, reads: 25974.67, writes: 7452.33, response time: 3847.03ms (95%), errors: 0.00, reconnects: 0.00

[ 33s] threads: 5000, tps: 0.00, reads: 16890.32, writes: 4757.33, response time: 4066.08ms (95%), errors: 0.00, reconnects: 0.00

[ 36s] threads: 5000, tps: 0.00, reads: 11791.64, writes: 3360.66, response time: 4242.64ms (95%), errors: 0.00, reconnects: 0.00

[ 39s] threads: 5000, tps: 0.00, reads: 20570.97, writes: 5936.99, response time: 9353.40ms (95%), errors: 0.00, reconnects: 0.00

[ 42s] threads: 5000, tps: 0.00, reads: 24726.10, writes: 7044.03, response time: 7630.78ms (95%), errors: 0.00, reconnects: 0.00

[ 45s] threads: 5000, tps: 0.00, reads: 25298.57, writes: 7300.97, response time: 4466.80ms (95%), errors: 0.00, reconnects: 0.00

[ 48s] threads: 5000, tps: 0.00, reads: 23648.54, writes: 6649.97, response time: 4673.33ms (95%), errors: 0.00, reconnects: 0.00

[ 51s] threads: 5000, tps: 0.00, reads: 23196.30, writes: 6727.32, response time: 4662.15ms (95%), errors: 0.00, reconnects: 0.00

[ 54s] threads: 5000, tps: 0.00, reads: 24349.27, writes: 6672.41, response time: 4564.12ms (95%), errors: 0.00, reconnects: 0.00

[ 57s] threads: 5000, tps: 0.00, reads: 18643.65, writes: 5725.33, response time: 4692.96ms (95%), errors: 0.00, reconnects: 0.00

[ 60s] threads: 5000, tps: 0.00, reads: 26207.24, writes: 7315.97, response time: 4488.24ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

queries performed:

write: 339024

other: 0

total: 1525608

transactions: 0 (0.00 per sec.)

read/write requests: 1525608 (24783.33 per sec.)

other operations: 0 (0.00 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 61.5578s

total number of events: 84756

total time taken by event execution: 305031.2099s

response time:

min: 663.80ms

avg: 3598.93ms

max: 15428.41ms

approx. 95 percentile: 11170.19ms

Threads fairness:

events (avg/stddev): 16.9512/3.58

execution time (avg/stddev): 61.0062/0.44

平均QPS为24783，平均响应时间为3598ms。

通过这三轮测试，PolarDB+RDS架构 QPS确实跑上来了，但是和传统单体数据库(RDS)相比，oltp场景下，谁的性能更胜一筹，下篇文章我们进行最终的PK。

weixin_39623082

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
polardb mysql 比较_PolarDB-X 1.0和RDS性能对比之吞吐量对比（二）

测试环境：PolarDB-X 标准版：16C64G(1台)RDS MySQL5.7： 16C64G(4台)ECS 压测机： 8C16G(1台)sysbench 0.5通过阿里云官方文档，找到oltp_drds.lua，如下：pathtest=string.match(test,"(.*/)")ifpathtestthendofile(pathtest.."common.lua")el...
复制链接

扫一扫