MySQL – How to Stress Test with Queries Captured from General Log

logsMySQLperformanceperformance-testingscalability

Is there any tool available which can do stress testing using the log file created by MySQL general log?
After a lot of search on google I found few stress testing tools which only use some benchmarks for stress test. One solution is using Apache JMeter, but it does not create test plans from MySQL log file and creating custom test plan for all the queries I have is too time consuming.

Or is there a tool which can at least create .sql file from MySQL log?

Best Answer

This is only a trace not a complete solution, "your" solution is based on your fantasy (and some work) ;)

I have done this kind of test capturing and cleaning query generated by the general query log and using sysbench 0.5 after writing my own lua file, you can find some samples in the official source code, on a mirror of my database(hw and schema/data -a snapshot so I can restore the original status of data immediately- ).

Then you can run a command like this:

./sysbench --mysql-table-engine=myisam --mysql-db=<yourdb> --mysql-user=<user> --mysql-host=<host> --mysql-password=<pass> --test=tests/db/<yourlua>.lua --num-threads=8 --max-requests=8 run

Collect and graph your results, changing concurrency and requests, and monitor your database and HW status.

My lua file contains about 80.000 query and it is composed by insert, update and delete

UPDATE #1

A good starting point that you can try is to start (simply) writing your lua file like this:

pathtest = string.match(test, "(.*/)") or ""

dofile(pathtest .. "common.lua")

function thread_init(thread_id)
   set_vars()

   if (db_driver == "mysql" and mysql_table_engine == "myisam") then
      -- begin_query = "LOCK TABLES ".. table_name .." WRITE"
      -- commit_query = "UNLOCK TABLES"
      begin_query = "SELECT 1>0"
      commit_query = "SELECT 1>0"
   else
      begin_query = "BEGIN"
      commit_query = "COMMIT"
   end
end

function event(thread_id)
    local table_name
    db_query(begin_query)
    rs = db_query("INSERT INTO .....")
    rs = db_query("INSERT INTO .....")
    rs = db_query("SELECT ... FROM ...")
    rs = db_query("DELETE ...")
    db_query(commit_query)
end

Putting your query into the db_query("") function, cleaning your general query log for this kind of process require a bit of work

Here a simple output of a "personal" lua file, like the above, with 9 query, 7 insert and 2 select, +2 BEGIN/COMMIT:

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            32
        write:                           56
        other:                           0
        total:                           88
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 88     (7728.11 per sec.)
    other operations:                    0      (0.00 per sec.)

General statistics:
    total time:                          0.0114s
    total number of events:              8
    total time taken by event execution: 0.0252s
    response time:
         min:                                  2.87ms
         avg:                                  3.15ms
         max:                                  3.65ms
         approx.  95 percentile:               3.65ms

Threads fairness:
    events (avg/stddev):           1.0000/0.00
    execution time (avg/stddev):   0.0032/0.00

Hope this helps

Related Question