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.