Sysbench custom lua script for actual business logic stress testing

Use sysbench for oltp test of mysql. A general test provides an oltp. lua script in testsdb in sysbench for oltp stress test. However, you cannot completely simulate the stress testing of your actual business. Different businesses, data structures, and data volumes are different. to be closer to the actual business's read/write stress testing, you have to write

Use sysbench for oltp test of mysql. Generally, An oltp. lua script is provided in tests/db in sysbench for oltp stress test. However, you cannot completely simulate the stress testing of your actual business. Different businesses, data structures, and data volumes are different. to be closer to the actual business's read/write stress testing, you have to write

Use sysbench for oltp test of mysql. Generally, An oltp. lua script is provided in tests/db in sysbench for oltp stress test.
However, you cannot fully simulate the stress testing of your actual business. Different businesses, data structures, and data volumes are different. to be closer to the actual business's read/write stress testing, you have to write your own lua script, then, perform a stress test through sysbench.
Writing this lua script is simple. You only need to write the lua script.

1. Collect the SQL statements used to access the database from the actual business. 2. Prepare a recovered backup database (import one online) 3. Write the collected SQL statements in the lua script. (For more information, see the following example) 4. test by sysbench's -- test parameter and -- mysql-db parameter (you don't need prepare here, just run it)

For example
Simulate the actual business environment:

CREATE TABLE `t1` (  `id` int(10) NOT NULL AUTO_INCREMENT,  `name` varchar(50) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10101 DEFAULT CHARSET=utf8

Insert simulated business data:

Delimiter $ create procedure add_data (in maxnum int) begin declare I int default 0; declare s varchar (500); while (I
 
  
Prepare the lua Script: [root @ sever3 db] # cat test. lua
  
function thread_init(thread_id)   -- set_vars()   db_connect()endfunction event(thread_id)   local table_name   local rs   table_name = "t1"        db_query("begin")  for i=1, 10000 do      rs = db_query("SELECT name FROM ".. table_name .." WHERE id=" .. i)  endend
  
Set_vars ()? If you need to use more parameters, You need to execute this operation. You need to reference common. lua
  
Db_connect ()?? Is connected to the database, this is the function in sysbench, no matter how many, use it directly.
  
Thread_init ()? The first lua function to be called
  
Event (thread_id )? You can write the SQL logic here ,? -How many num-threads will be called at the same time
  
Then perform the stress test and then OK.
  
[root@sever3 sysbench]# ./sysbench --mysql-socket=/data/mysql_3309/mysql.sock --test=tests/db/test.lua --mysql-user=root --num-threads=12 --report-interval=10 --rand-type=uniform --max-time=30 --max-requests=0 --percentile=99 --mysql-db=test runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 12Report intermediate results every 10 second(s)Random number generator seed is 0 and will be ignoredThreads started![  10s] threads: 12, tps: 0.00, reads/s: 64131.41, writes/s: 0.00, response time: 3291.51ms (99%)[  20s] threads: 12, tps: 0.00, reads/s: 79980.83, writes/s: 0.00, response time: 1947.61ms (99%)[  30s] threads: 12, tps: 0.00, reads/s: 78354.15, writes/s: 0.00, response time: 2418.21ms (99%)OLTP test statistics:    queries performed:        read:                            2280000        write:                           0        other:                           228        total:                           2280228    transactions:                        0      (0.00 per sec.)    deadlocks:                           0      (0.00 per sec.)    read/write requests:                 2280000 (72705.35 per sec.)    other operations:                    228    (7.27 per sec.)General statistics:    total time:                          31.3595s    total number of events:              228    total time taken by event execution: 368.0393s    response time:         min:                                985.61ms         avg:                               1614.21ms         max:                               3756.13ms         approx.  99 percentile:            3289.54msThreads fairness:    events (avg/stddev):           19.0000/3.83    execution time (avg/stddev):   30.6699/0.42
  
The specific use and result interpretation of sysbench can be as follows:
  
Http://imysql.com/2014/10/17/sysbench-full-user-manual.shtml
    
  
Original article address: sysbench custom lua script for actual business logic stress testing. Thanks to the original author for sharing.