All Products
Search
Document Center

Test the DRDS performance based on Sysbench

Last Updated: Oct 09, 2020

Introduction to Sysbench

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 Distributed Relational Database Service (DRDS) instances by using Sysbench in online transaction processing (OLTP) scenarios and SELECT scenarios.

Test design

  • Purchase DRDS instances, Elastic Compute Service (ECS) instances, and ApsaraDB RDS for MySQL instances.
    • Four DRDS 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 DRDS 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 DRDS 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

  1. --test='/usr/local/share/sysbench/oltp_drds.lua' //Use oltp_drds.lua in OLTP scenarios and select.lua in SELECT scenarios.
  2. --mysql_table_options='dbpartition by hash('id') tbpartition by hash(id) tbpartitions 2' //The syntax for database and table sharding in a DRDS instance. In this example, two table shards are created for each database shard.
  3. --oltp-table-size=160000000 //Prepare 160 million rows of data.
  4. --oltp_auto_inc=off //Disable auto-increment primary keys.
  5. --oltp_skip_trx=on //Skip transactions.
  6. --oltp_secondary //Set the id column to a non-primary key column to prevent primary key conflicts.
  7. --oltp_range_size=5 //Specify five consecutive values. Therefore, five shards will be involved in queries.
  8. --rand-init=on //Enable Sysbench to fill each test table with random data.
  9. --num-threads=200 //The number of concurrent threads for each scenario. For more information, see the following table.

Sample test statement:

  1. 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

  1. pathtest = string.match(test, "(.*/)")
  2. if pathtest then
  3. dofile(pathtest .. "common.lua")
  4. else
  5. require("common")
  6. end
  7. function get_range_end(start)
  8. return start + oltp_range_size - 1
  9. end
  10. function thread_init(thread_id)
  11. set_vars()
  12. if (((db_driver == "mysql") or (db_driver == "attachsql")) and mysql_table_engine == "myisam") then
  13. local i
  14. local tables = {}
  15. for i=1, oltp_tables_count do
  16. tables[i] = string.format("sbtest%i WRITE", i)
  17. end
  18. begin_query = "LOCK TABLES " .. table.concat(tables, " ,")
  19. commit_query = "UNLOCK TABLES"
  20. else
  21. begin_query = "BEGIN"
  22. commit_query = "COMMIT"
  23. end
  24. end
  25. function event(thread_id)
  26. local rs
  27. local i
  28. local table_name
  29. local range_start
  30. local c_val
  31. local pad_val
  32. local query
  33. table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
  34. if not oltp_skip_trx then
  35. db_query(begin_query)
  36. end
  37. if not oltp_write_only then
  38. for i=1, oltp_point_selects do
  39. rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
  40. end
  41. if oltp_range_selects then
  42. for i=1, oltp_simple_ranges do
  43. range_start = sb_rand(1, oltp_table_size)
  44. rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. get_range_end(range_start))
  45. end
  46. for i=1, oltp_sum_ranges do
  47. range_start = sb_rand(1, oltp_table_size)
  48. rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. get_range_end(range_start))
  49. end
  50. for i=1, oltp_order_ranges do
  51. range_start = sb_rand(1, oltp_table_size)
  52. rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. get_range_end(range_start) .. " ORDER BY c")
  53. end
  54. for i=1, oltp_distinct_ranges do
  55. range_start = sb_rand(1, oltp_table_size)
  56. rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. get_range_end(range_start) .. " ORDER BY c")
  57. end
  58. end
  59. end
  60. if not oltp_read_only then
  61. for i=1, oltp_index_updates do
  62. rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))
  63. end
  64. for i=1, oltp_non_index_updates do
  65. c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
  66. query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
  67. rs = db_query(query)
  68. if rs then
  69. print(query)
  70. end
  71. end
  72. for i=1, oltp_delete_inserts do
  73. i = sb_rand(1, oltp_table_size)
  74. rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
  75. c_val = sb_rand_str([[
  76. ###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
  77. pad_val = sb_rand_str([[
  78. ###########-###########-###########-###########-###########]])
  79. 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))
  80. end
  81. end -- oltp_read_only
  82. if not oltp_skip_trx then
  83. db_query(commit_query)
  84. end
  85. end