Sysbench custom Lua scripts to implement actual business logic stress testing

Source: Internet
Author: User
Tags lua prepare


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.