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

Visible

+1 you פresent the question very well – Cranford

1000 records isn't nearly enough to draw a conclusion out, there have been numerous discussions about MyISAM and InnoDB, how they work and when they start to deteriorate in performance and why. Try to insert 10m records and see what happens. – Berkey

serverfault.com/questions/236630/… appears to find the opposite conclusions? – Cerellia

Guessing: InnoDB just appends to its binary log, and flushes to disk only sporadically (hence the outliers). MyISAM always directly writes to disk (to the table file). – Cerellia

What are your axis? Why does MyISAM take between 3 and 9, and InnoDB take between 200 and 300? – Cerellia

@Berkey Maybe I should insert more records, but in my opinion there's a very clear difference already in these results, a straight horizontal line versus a almost perfect increasing lineair line. And: my machine was running for 6 hours to process 100 x 1000 InnoDB records, so almost a year to run 10m records (100 times) ;-) – Visible

@Konerak: Y-axis = required time in seconds to insert 1000 records, X-as: 1 = first try and so on til 100 – Visible

@waanders: in seconds? So innoDB takes 200 seconds? Are you sure you tuned MySQL to use InnoDB properly? – Cerellia

@Konerak: No, I'm not sure. I'm new to MySQL and started with the default configuration and started with a InnoDB table (because of the good reviews on the net), but was disappointed by the performance, so now I was testing MyISAM. Which is around 50 times faster (in my case). – Visible

all i can say is read up on innodb as much as you can - start here mysqlperformanceblog.com/2011/03/18/… – Brush

@Visible - if you're running default MySQL config then yes, it'd take quite a while for 10m records to complete. InnoDB has quite a bit of options to tune it for performance. Anyway, InnoDB doesn't flush data to the disk immediately, it's to do with many things - mainly rotation of the disks' magnetic heads and optimization of writing on a medium with variable performance. MyISAM writes to the disk immediately. When data set increases, you'll see deterioration with MyISAM faster than with InnoDB. – Berkey

Now do all InnoDB inserts within a single transaction. – Longevity

f00 N.B. Konerak: thank you all for your comments, it helps me to better understand the behavior of the different storage eniges. I will read the MySQL Performance Blog. And sorry: I said 1.000 records, but it was 10.000 (I checked my script). Also I added to my question the remark that I use a Access/ODBC/DAO frontend to insert the records, I guess this also influences the performance – Visible

@waanders- You didn't say 1,000 records. You said 10,000 :) – Prewitt

@Ashwin A: I corrected my question (if that is what you mean). See my former comments. Anyway, I tested with 100 times 10,000 records – Visible

Possible duplicate: https://mcmap.net/q/909034/-innodb-vs-myisam-insert-query-time/797303 – Workout

Answer 1

To really see the performance differences, you'd need to run some real program on top of the DB. Just inserting tiny batch of records is not telling much. Insert speed depends a lot on things like column count, the amount of indices, transaction pattern, DB constraints and other activities taking place.

Histoid

Sure, I agree. But I tried (try) to explain the difference in my special case. Just a simple table, no primary/foreign keys, no indexes, no auto increment fields, standards MySQL installation (no tweaks or optimization). Just comparing InnoDB and MyISAM and why such big differences? – Visible

Answer 2

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).

Drowsy

As said I used a default MySQL installation, no tweaks or optimization). Yes, single-row inserts. Thanks for your reply, it also gave me a clue: I added a transaction part to the VBA code, I don't know how the DAO transactions are translated to the MySQL inserts but it makes a big difference (with InnoDB), the average time to insert 10,000 records decreases from around 215 seconds to 1.3 seconds! (I'll supplement my question) – Visible