MySQL Storage engine benchmarking

Here’s a stored procedure I use to perform some simple benchmarking of inserts for MySQL. It takes three parameters; p_table_type which should be set to the storage engine you wish to benchmark i.e. ‘MyISAM’, ‘InnoDB’, p_inserts ; set this to the number of inserts to perform. p_autocommit ; set the autocommit variable (relevant to InnoDB only) to 0 or 1.

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `table_engine_test`$$
        CREATE DEFINER=`root`@`%`
        PROCEDURE `table_engine_test`(IN p_table_type VARCHAR(20),
                                      IN p_inserts INT,
                                      IN p_autocommit TINYINT)
                SQL SECURITY INVOKER
        BEGIN

                DECLARE sql_string VARCHAR(300);

                # Set session autocommit
                SET SESSION autocommit = p_autocommit;

                # TABLE TO hold session times
                CREATE TABLE IF NOT EXISTS test_session
                             (
                                          Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                                          table_type VARCHAR(20) NOT NULL ,
                                          inserts INT NOT NULL,
                                          autocommit TINYINT NOT NULL,
                                          started DATETIME NULL ,
                                          finished DATETIME NULL
                             );

                CREATE TABLE IF NOT EXISTS test_session_inserts
                             (
                                          id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
                                          test_session_id INTEGER NOT NULL ,
                                          started DATETIME NOT NULL ,
                                          finished DATETIME NOT NULL
                             );

                # clean up ANY existing test TABLE
                DROP TABLE IF EXISTS test_table_type;

                SET sql_string = CONCAT('CREATE TABLE test_table_type
					(
						id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
						random INTEGER,
						text1 VARCHAR(10) NOT NULL,
						text2 VARCHAR(10) NOT NULL
					)ENGINE = ', p_table_type);
                # PREPARE SQL AND EXECUTE
                SET @q = sql_string;
                PREPARE stmt FROM @q;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
                # START the test session
                INSERT
                INTO test_session
                       (
                              table_type,
                              inserts,
                              autocommit,
                              started
                       )
                       VALUES
                       (
                              p_table_type,
                              p_inserts,
                              p_autocommit,
                              NOW()
                       );

                SET @id = LAST_INSERT_ID();
                SET @i = 0;
                while_loop: WHILE (@i < p_inserts) DO
                # Generate VALUES for insert
                SET @random = RAND();
                SET @text1 = SUBSTRING(MD5(RAND()), -10);
                SET @text2 = SUBSTRING(MD5(RAND()), -10);
                SET @started = NOW();
                # INSERT the test row
                INSERT
                INTO test_table_type
                       (
                              random,
                              text1 ,
                              text2
                       )
                       VALUES
                       (
                              @random,
                              @text1 ,
                              @text2
                       );

                # record INSERT times
                        INSERT
                        INTO test_session_inserts
                               (
                                      test_session_id,
                                      started ,
                                      finished
                               )
                               VALUES
                               (
                                      @id ,
                                      @started,
                                      NOW()
                               );

                        # increment counter
                        SET @i = @i + 1;
                END WHILE while_loop;
                # Finish the session
                UPDATE test_session
                SET finished = NOW()
                WHERE id = @id;

END$$
DELIMITER ;

Run your tests like so…

CALL table_engine_test('MyISAM', 10000, 0);
CALL table_engine_test('InnoDB', 10000, 0);
CALL table_engine_test('InnoDB', 10000, 1);
CALL table_engine_test('MyISAM', 100000, 0);
CALL table_engine_test('InnoDB', 100000, 0);
CALL table_engine_test('InnoDB', 100000, 1);

The test_session contains some summary information about each of the tests;

SELECT *
FROM test_session;

Calculate the time taken for each test with the following query;

SELECT *, TIMEDIFF(finished, started) AS seconds
FROM test_session;

Happy Benchmarking!