Stress test MySQL with queries captured with general log in MySQL

Question

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?

Answer 1

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:

• Nov 16, 2011 : Log MySQL DB changing queries and users
• Feb 11, 2012 : MySQL general log
• Jan 08, 2012 : How to enable MySQL general log?

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` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

SQLSTMT="SELECT argument FROM mysql.general_log"
SQLSTMT="${SQLSTMT} WHERE LEFT(argument,6) IN"
SQLSTMT="${SQLSTMT} ('INSERT','UPDATE','DELETE','SELECT')"
mysql -uroot -p -ANe"${SQLSTMT}" > SQLCommands.sql

There you go.

Give it a Try !!!

Answer 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

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