MySQL Read/Write Benchmark

Question

I'm trying to benchmark Read/Write speed in MySQL 5.5. Currently I have a stored procedure that insert rows with random numbers to a table and I'm measuring time of execution before and after configuration changes.

Here's my procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertRand`(IN nor INT, in minr int, in maxr int)
begin
 declare i int;
 declare lastid int;
 set i =1;
 start transaction;
 while i <= nor do
 insert into tbltest(Value) values (minr + CEIL(rand() * (maxr-minr)));
 set lastid = (select MAX(id) from tbltest);
 set i = i +1;
 end while;
 commit;
 end

Do you know any good way to benchmark this ? Maybe my procedure needs some improvement?

Answer 1

You should use MySQL's BENCHMARK() function

Here are my posts on how to use it

• Apr 03, 2012 : How to use the 'select benchmark' command on a procedure
• Apr 16, 2013 : MySQL: CAST + SUBSTR vs. FLOOR + MOD

BTW if you do configuartion changes, you should restart mysqld immediately after the configuration changes are in place. Then, run your I/O tests.

• Piotr Truszkowski: Very precise and useful answer :) exactly what i needed :) thanks a lot!