Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
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. –  Ruchit Rami Apr 4 '13 at 12:04
    
@RuchitRami, see my update –  Cristian Porta Apr 4 '13 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. –  Ruchit Rami Apr 4 '13 at 12:27
    
not at all! I posted "my" solution ;) ... I have included in my update a sample output of sysbench –  Cristian Porta Apr 4 '13 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? –  Ruchit Rami Apr 18 '13 at 8:34

Here is a more direct approach using brute forec 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` (
  `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 !!!

share|improve this answer
    
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. –  Ruchit Rami Apr 5 '13 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 –  Cristian Porta Apr 5 '13 at 6:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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