Difference in required time to insert InnoDB/MyISAM records

Question

I'm inserting records into a MySQL table and try to understand the differences in time it takes between a MyISAM table and a InnoDB table.

This is the code to create the table:

CREATE TABLE SpectrumData (
    ID INT(11) NULL DEFAULT NULL,
    `Set` INT(11) NULL DEFAULT NULL,
    Wavelength DOUBLE NULL DEFAULT NULL,
    Intensity DOUBLE NULL DEFAULT NULL,
    Error INT(11) NULL DEFAULT NULL,
    `Status` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=xxx
ROW_FORMAT=DEFAULT

I insert 10000 records, measure the time it takes in seconds and repeat this 100 times. I put the results in two Excel charts:

So MyISAM increasing and InnoDB more or less constant.

Can anyone explain the differences? Something to do with the number of records in the table? And why these outliers with InnoDB?

Configuration used computer:

• Windows XP SP3
• Intel Core2 Duo
• 3.00 Ghz
• 2 GB RAM
• MySQL 5.5 CE

UPDATE: I should have mentioned I insert the records with a VBA script in a Access front-end application. I connect to the MySQL database by a ODBC System DSN.

The VBA code:

Dim RsSpectrumData As DAO.Recordset
Dim Db As Database
Dim i As Integer
Dim j As Integer
Dim TimerStart

Set Db = CurrentDb
Set RsSpectrumData = Db.OpenRecordset("SpectrumData")

For i = 1 To 100
    TimerStart = Timer
    For j = 1 To 10000
        With RsSpectrumData
            .AddNew
            !Set = 1
            !Wavelength = 100
            !Intensity = 25000
            !Error = 0
            !Status = 0
            .Update
        End With
    Next
    Print #1, Timer - TimerStart
Next

RsSpectrumData.Close

UPDATE AGAIN:

I added DAO transaction functionality and now the average InnoDB insert time for 10,000 records decreased from 215 seconds to an average of 1.3 seconds! (Thanks to @MarkR):

Dim RsSpectrumData As DAO.Recordset
Dim Db As Database
Dim Ws As DAO.Workspace
Dim i As Integer
Dim j As Integer
Dim TimerStart

Open "C:\TEMP\logtest.txt" For Append As #1

Set Db = CurrentDb
Set Ws = DBEngine.Workspaces(0)
Set RsSpectrumData = Db.OpenRecordset("SpectrumData")

For i = 1 To 20
    TimerStart = Timer
    Ws.BeginTrans
    For j = 1 To 10000
        With RsSpectrumData
            .AddNew
            !Set = 1
            !Wavelength = 100
            !Intensity = 25000
            !Error = 0
            !Status = 0
            .Update
        End With
    Next
    Ws.CommitTrans
    Print #1, Timer - TimerStart
Next
RsSpectrumData.Close

Close #1

Answer 1

It is unclear what durability settings you have enabled in MyISAM or Innodb, nor whether you're using single-row inserts, or batch inserts in either case.

If you are using single-row inserts, you aren't using transactions, and you have durability enabled (the default setting in InnoDB), then you are likely to see InnoDB performance severely limited by the requirement to commit each transaction to durable storage (i.e. disc) after each row insert.

MyISAM has no such problem, because it is not durable anyway, i.e. if the machine crashes you are more-or-less guaranteed to lose some recently written data which the database had previously claimed was written successfully (if not the whole table!).

If you want decent insert-performance, use batch inserts and/or transactions, otherwise you're just measuring the speed of a write followed by a fsync(), which (on a non-battery backed RAID controller on rotational magnetic media) is just the speed of your disc spinning.

So the reason innodb is so consistent, is that you're measuring the spin speed of your disc.

Having said that, if you have a busy server, you definitely, absolutely, want to use a battery-backed RAID controller, then you can achieve decent transaction commit performance AND proper durability (Assuming power does not fail for longer than the battery lasts, and the server doesn't explode etc).