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?

2 Answers 2


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


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)

   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"
      begin_query = "BEGIN"
      commit_query = "COMMIT"

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

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

  • Thanks, but I do not know anything about lua files. How can I create lua files from my log files? If I am not wrong just renaming .log to .lua wont work. Commented Apr 4, 2013 at 12:04
  • @RuchitRami, see my update Commented Apr 4, 2013 at 12:20
  • Hmm, this will require me to manually add my queries to code in lua file. It is exactly what i was trying to avoid. Your solution would work for sure, but I was looking for a method to get sql script from log file. For stress test by copying queries manually, I am sure i can work it out with Apache JMeter. But thank anyway for your answer, got something new to learn. Commented Apr 4, 2013 at 12:27
  • not at all! I posted "my" solution ;) ... I have included in my update a sample output of sysbench Commented Apr 4, 2013 at 12:35
  • Hi, after following your steps I am getting error Invalid test name: tests/db/mysqltest.lua. Do you have any idea what might be causing this? Commented Apr 18, 2013 at 8:34

Here is a more direct approach using brute force mysql: Setup the general as a MyISAM table. On installation, you will find the table mysql.general_log as a CSV table.

Rather than reinventing the wheel and doing a whole lot of typing, please read my past posts:

Once you get mysql.general_log converted to MyISAM and queries are getting recorded, simply access the log like any other MyISAM table.

Here is the structure of the MyISAM version of mysql.general_log

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
1 row in set (0.00 sec)

From here, just write all content starting with a query:

SQLSTMT="SELECT argument FROM mysql.general_log"
mysql -uroot -p -ANe"${SQLSTMT}" > SQLCommands.sql

There you go.

Give it a Try !!!

  • Great! It would have taken me few days to figure this out, and I am not sure if i could have reached to such neat solution. Commented Apr 5, 2013 at 5:28
  • @RolandoMySQLDBA, Thanks! I had not thought to pick up the query using this way, so I can avoid working directly and manually on the mysql general log file! Building my lua file from mysql.general_log is surely more simple and fast... +1 Commented Apr 5, 2013 at 6:41
  • @RolandoMySQLDBA, This really helps!! Also can i get the DB used for each query? I am not using the fully qualified table name from code and most like the queries will fail.
    – georgecj11
    Commented Apr 22, 2015 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.