06 septembre 2007
MySQL Engine INSERT speed
For a client, I evaluate different Engine SQL INSERT speed.
The table is really simple (an auto incremented uid, and a useless integer column).
CREATE TABLE bench ( uid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, useless INT );
and the SQL statement is INSERT INTO bench (useless) VALUES (1);
I used MyISAM (with delay_key_write=ON naturally!), InnoDB and MEMORY (aka HEAP) Engines, to have a clear picture of RAW Insert speed.
Insert Speed (on a Core2 Duo 2.0Ghz 3GB computer) is:
- MyISAM : 33 000 INSERT/s
- InnoDB : 24 700 INSERT/s
- MEMORY : 64 000 INSERT/s
As you discover, using MyISAM (with Delay_key_write=ALL) gives an immediate 33% boost from InnoDB, and MEMORY is only 2X faster than MyISAM, albeit working only on main memory.
Many articles told about MyISAM limits when inserting or updating datas, and it's true, MyISAM is rally limited when used without DELAY_KEY_WRITE=ALL. But if you have serious backup and a backup/restore/recovery procedure that use mysqldump, there's no need to limit MyISAM engine speed.