Sysbench is an open-source, modular, cross-platform, and multi-threaded tool used to test the performance of databases, the CPU, memory, threads, and I/O. Currently, Sysbench is supported in MySQL, Oracle, and PostgreSQL databases. The following tests the performance of PolarDB-X 1.0 instances by using Sysbench in online transaction processing (OLTP) scenarios and SELECT scenarios.
Test design
- Purchase PolarDB-X 1.0 instances, Elastic Compute Service (ECS) instances, and ApsaraDB RDS for MySQL instances.
- Four PolarDB-X 1.0 instances of the following specifications: 8-core 32 GB for Starter Edition, 16-core 64 GB for Standard Edition, 32-core 128 GB for Enterprise Edition, and 64-core 256 GB for Enterprise Edition
- One ECS instance as the stress test machine: 32-core 64 GB, Aliyun Linux 2.1903 64-bit, and enhanced computing network
- Twelve ApsaraDB RDS for MySQL instances: 16-core 64 GB, MySQL 5.7, and dedicated instance
type
All the PolarDB-X 1.0 instances, ECS instances, and ApsaraDB RDS for MySQL instances are located in the same zone and the same Virtual Private Cloud (VPC).
- Create database shards through horizontal partitioning in the PolarDB-X 1.0 console and select the 12 ApsaraDB RDS for MySQL instances that you have purchased.
- Install Sysbench on the ECS instance and prepare 160 million rows of data. For more information about Sysbench, see Sysbench user guide.
Test parameters
--test='/usr/local/share/sysbench/oltp_drds.lua' //Use oltp_drds.lua in OLTP scenarios and select.lua in SELECT scenarios.
--mysql_table_options='dbpartition by hash('id') tbpartition by hash(id) tbpartitions 2' //The syntax for database and table sharding in a PolarDB-X 1.0 instance. In this example, two table shards are created for each database shard.
--oltp-table-size=160000000 //Prepare 160 million rows of data.
--oltp_auto_inc=off //Disable auto-increment primary keys.
--oltp_skip_trx=on //Skip transactions.
--oltp_secondary //Set the id column to a non-primary key column to prevent primary key conflicts.
--oltp_range_size=5 //Specify five consecutive values. Therefore, five shards will be involved in queries.
--rand-init=on //Enable Sysbench to fill each test table with random data.
--num-threads=200 //The number of concurrent threads for each scenario. For more information, see the following table.
Sample test statement:sysbench --test='/usr/local/share/sysbench/oltp_drds.lua' --oltp_tables_count=1 --report-interval=5 --oltp-table-size=160000000 --mysql-user=**** --mysql-password=**** --mysql-table-engine=innodb --rand-init=on --mysql-host=**** --mysql-port=3306 --mysql-db=**** --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 2' --num-threads=200 --max-time=300 run
OLTP test results
Instance specifications | Number of concurrent threads | Number of read/write operations per second |
---|---|---|
8-core 32 GB for Starter Edition | 100 | 20807.12 |
16-core 64 GB for Standard Edition | 230 | 49667.48 |
32-core 128 GB for Enterprise Edition | 450 | 90693.70 |
64-core 256 GB for Enterprise Edition | 900 | 177506.48 |
SELECT test results
Instance specifications | Number of concurrent threads | Number of read/write operations per second |
---|---|---|
8-core 32 GB for Starter Edition | 200 | 41401 |
16-core 64 GB for Standard Edition | 300 | 98182.26 |
32-core 128 GB for Enterprise Edition | 600 | 180500.00 |
64-core 256 GB for Enterprise Edition | 1200 | 366863.48 |
Test scripts for the OLTP scenarios
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
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
end
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
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
end