Sysbench custom Lua scripts to implement actual business logic stress testing

Using Sysbench for MySQL OLTP testing, generic tests in Sysbench provide a Oltp.lua script that can be used for OLTP stress testing in tests/db.
But can not completely simulate their actual business of the stress test, different business, data structure, the volume is not the same, in order to closer to the actual business of reading and writing pressure test, you have to write the Lua script, and then through the sysbench for stress testing.
Writing this Lua script is simple enough to write a LUA script.

1, the first collection of actual business access to the database SQL;
2, prepare a recovery of a good backup library (from the line to guide one)
3, write the collected SQL in the Lua script (how to write, for example)
4, through the Sysbench--test parameters and--mysql-db parameters to test (there is no need to prepare, direct run on the line)
Here's a little example.
Simulate the actual business environment:

CREATE TABLE ' T1 ' (
' ID ' int (a) not NULL auto_increment,
' Name ' varchar DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=10101 DEFAULT Charset=utf8
To insert simulated business data:

Delimiter $$
CREATE PROCEDURE add_data (in Maxnum int)
BEGIN
 declare i int default 0;
 decl are S varchar (500);
 while (i<maxnum) do
  select concat ("ABCDEDSFSFSDFSFSF", I) into S;
  insert into T1 (name) values (s); 
  set i=i+1;
 end while;
End $$
Del Imiter;
 
Call Add_data (10000);
Prepare Lua script: [Root@sever3 db]# cat Test.lua

function Thread_init (thread_id)
--Set_vars ()
Db_connect ()
End

function 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
End
End
Set_vars () If you need to use more parameters, you need to do this, before you need to refer to the next Common.lua
Db_connect () is connected to the database, this is the Sysbench function, no matter how much, directly with the line.
Thread_init () The LUA function of the first call
Event (thread_id) can write SQL logic here, –num-threads how many, will call the number of
And then the pressure test is 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-- Mysql-db=test Run
Sysbench 0.5:multi-threaded System Evaluation Benchmark

Running the test with following options:
Number of Threads:12
Intermediate results every second (s)
Random number generator seed is 0 and would be ignored


Threads 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. Percentile:3289.54ms

Threads Fairness:
Events (Avg/stddev): 19.0000/3.83
Execution Time (Avg/stddev): 30.6699/0.42