I'm new to MySQL and I have some doubts about InnoDB performance. I have a table where I want to store some measures and I'm testing intensive insert using a loop in a stored procedure:
CREATE TABLE `measures` ( `Id` int(10) unsigned NOT NULL, `DT` datetime NOT NULL, `TF1` float DEFAULT '0', `IF1` float DEFAULT '0', `PAF1` float DEFAULT '0', `PRF1` float DEFAULT '0', `CF1` float DEFAULT '0', `TF2` float DEFAULT '0', `IF2` float DEFAULT '0', `PAF2` float DEFAULT '0', `PRF2` float DEFAULT '0', `CF2` float DEFAULT '0', `TF3` float DEFAULT '0', `IF3` float DEFAULT '0', `PAF3` float DEFAULT '0', `PRF3` float DEFAULT '0', `CF3` float DEFAULT '0', `CTotal` float DEFAULT '0', PRIMARY KEY (`Id`,`DT`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE PROCEDURE `sp_INSERT_TEST`() BEGIN DECLARE i INT DEFAULT 1; WHILE (i<=6000) DO INSERT INTO measures (Id, DT, TF1, IF1, PAF1, PRF1, CF1, TF2, IF2, PAF2, PRF2, CF2, TF3, IF3, PAF3, PRF3, CF3, CTotal) VALUES (1, TIMESTAMPADD(MINUTE,i, NOW()), 0.1, 1, 10, 100, 1000, 0.2, 2, 20, 200, 2000, 0.3, 3, 30, 300, 3000, i * 10); SET i=i+1; END WHILE; END
The total time of execution is about 150 seconds, but if I use
MyISAM instead of
InnoDB, the time is about 0.2 seconds.
Is this difference of performance between the two engines normal? Is it possible to improve the inserts using InnoDB to get close to MyISAM performance or it's a engine limitation?
Note: I'm using MySQL 5.1.56 (64 bit), Windows 7 x64, with a default configuration for developer machine (I've also tried server configuration but the performance is similar).
Thanks in advance. John
Just like peufeu says you can improve performance a lot by only using one transaction. If you are willing to relax ACID you can get even better performance by setting
Yes that is normal. Like all transactional DBs, InnoDB has to ensure ACID consistency which takes a lot of work, and also what kills your performance is that at the end of each transaction, it has to wait for the harddisk to say "OK the data is written", as for the "D=Durability" in ACID.
Solution is simply to issue your bulk INSERT in a single transaction, ie BEGIN, mass INSERT, COMMIT.
Or use something more adapted, like a LOAD DATA INFILE, or at least use multiline INSERTs.
InnoDB is often slower than MyISAM, being a transactional DB engine with ACID properties. However, changing the configuration or the way you do things can have a big impact on speed.
First something that won't work: adding caches is useless, reducing the number of indexes on the table helps, because you've to create less indexes when inserting, but in your case you've only the primary key.
My suggestion is to try one of the following:
Run all INSERT queries in a single transaction and then commit at the end.
Example: SET autocommit=0; INSERT statements; COMMIT;
Use the extended insert syntax, that allows to INSERT many rows together. I thought InnoDB didn't support that, but I just tried with MySQL 5.0.75 and it worked. I don't think that you can INSERT all 6000 rows at once, because you're limited by max_allowed_packet (you can increase it if you want). You can INSERT 500 rows at a time, for example.
Example: INSERT INTO table (field1, field2) VALUES (1, test'), (2, 'hello');
The other posters are right - do it all in one transaction.
6000 rows is such a tiny table, insert performance is not really relevant for such a tiny data set.
No modern database is optimised for such "toy" systems anyway.
Try a billion records, and do it with sensible size transactions, and see who wins then.
An pull the plug, and see who recovers more quickly (hint: it will not be myisam)
Using LOAD DATA INFILE or a batch insert might give better performance of a stored procedure, even if it's in one transaction, as mysql SP language is not always particularly efficient.
In addition to everything stivlo said - you might find the following article and accompanying video useful.
as Morgan Tocker says in his presentation "friends don't let friends use myisam"
hope you find it enlightening :)
MyISAM is faster than InnoDB when it comes to insert, but not by a margin of 750 times, which indicates you require InnoDB tuning. It's a topic that's been covered before, for example you can read some good things at the following blog.