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.
Commentaires
I would like to clarify:
1. Did you use INSERT DELAYED for MyISAM/InnoDB tables?
2. When inserting into InnoDB, is AUTOCOMMIT 0 or 1?
3. Did you test on single INSERTs in a loop or on extended INSERTs (like INSERT INTO table VALUES (...), (...), (...))?
I got these results on my old PC (I inserted 30,000 rows row by row):
* HEAP: 22.2 secs;
* InnoDB:
** INSERT: 28.7 secs;
** INSERT DELAYED: 50.9 secs;
* MyISAM:
** INSERT: 27.2 secs;
** INSERT DELAYED: 22.4 secs.
AUTOCOMMIT was set to 0, for InnoDB the transaction was COMMIT'ed after all INSERTs had been executed.
Precisions
Thanks for reading the post and maybe should I have been more specific and precise.
The idea was to measure the speed of a SINGLE insert of a SINGLE row, the kinda operation that is KNOWN to be slow with MyISAM (with default DELAY_KEY_WRITE=OFF where you have statistically over 1 physical write per operation x index).
So I wrote a Stored Procedure that INSERT automatically, looping over an INSERT statement that insert 1 single row.
The idea was basically to measure statistically the INSERT 1 row speed.
I keep the default AUTOCOMMIT (enabled per default on MySQL), because I don't want to measure the INSERT speed of 10000 rows, but the speed of 30000 INSERT of a single row, to be able to have an estimate of a single row INSERT statement.
This was simple INSERT, no INSERT_DELAYED.
The interesting fact is that when you enable DELAY_KEY_WRITE=ALL, MyISAM offers an excellent level of performance, better than the one of InnoDB, even on INSERT operations!
Notice that different architectures (CPU & memory) will produce different results and the different Engines on MySQL may not scale the same way!
Does it answer your questions?
Yes, thank you very much!
Actually, DELAY_KEY_WRITE=1 in CREATE TABLE is enough :-)
> The interesting fact is that when you enable
> DELAY_KEY_WRITE=ALL, MyISAM offers an excellent
> level of performance, better than the one of
> InnoDB, even on INSERT operations!
Yes, but there are hidden dangers - for example, performance degrades if there are concurrent INSERT/SELECT operations and the table is not optimized etc
Not perfect!
Yes it's not perfect, but it's a good improvment on MyISAM basic level of performance.
Remember to ensure to EVER use mysqldump to export sequentially all data if mysql crash, and reimport to rebuild tables and indexes.
Anyway this is also necessary when you do DELAY_KEY_WRITE=OFF.
(nobody could insure what a crash may have done to INDEX, if it's checkable or fixable!)
It was lessons from 3.23 and before, and they seems to be usefull albeit I don't saw mysql crash these last years!!! :-)
Poster un commentaire
Rétroliens
URL pour faire un rétrolien vers ce message :
http://www.canalblog.com/cf/fe/tb/?bid=96635&pid=6119631
Liens vers des weblogs qui référencent ce message :