Tuesday, December 05, 2006

MySQL : InnoDB vs MyISAM vs Archive Benchmark

MySQL has a few storage engines. To list them type :

mysql> show engines;

First of all I'm interested in the INSERT speed of DML.
It is crytical for some type of applications.
So, here is my benchmark.
All of engines have different INSERT speed.
To test it, but not the speed of my
network connection or local loop interface,
I decided to create dummy stored
procedure for inserting a lot of data into some table.
Here is the code :

delimiter //
create procedure test_insert()
begin
declare counter mediumint;
set counter = 0;
while counter < 100000
do
INSERT INTO
test values
(1,'sample dummy text',now());
set counter = counter + 1;
end while;
end //
DELIMITER ;

The DDL of sample table is :
CREATE TABLE test
(
first int(11) default NULL,
second varchar(20) default NULL,
third date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

We can change the storage engine for existing table as so

mysql> alter table test engine=archive;
Query OK, 100000 rows affected (0.98 sec)
Records: 100000 Duplicates: 0 Warnings: 0


Really fast ! That is all what we need to do our benchmark.
The results (the number of rows 100 000):

  1. Archive : 5.20 sec
  2. MyISAM : 7.13 sec
  3. InnoDB : very slow !!!

No comments: