Разница в требуемом времени для вставки записей InnoDB/MyISAM

Я вставляю записи в таблицу MySQL и стараюсь понять разницу во времени между таблицей MyISAM и таблицей InnoDB.

Это код для создания таблицы:

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

Я вставляю 10000 записей, измеряю время, которое занимает секунды, и повторяю это 100 раз. Я положил результаты в две таблицы Excel:

Итак, MyISAM увеличивается и InnoDB более или менее постоянный.

Может ли кто-нибудь объяснить различия? Что-то связано с количеством записей в таблице? И почему эти выбросы в InnoDB?

Конфигурируемый компьютер:

Windows XP SP3
Intel Core2 Duo
3.00 Ghz
Оперативная память 2 ГБ
MySQL 5.5 CE

ОБНОВЛЕНИЕ: Я должен был упомянуть, что я вставляю записи с VBA script в front-end приложении Access. Я подключаюсь к базе данных MySQL с помощью DSN системы ODBC.

Код VBA:

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

ОБНОВЛЕНИЕ:

Я добавил функцию транзакции DAO, и теперь среднее время вставки InnoDB для 10 000 записей уменьшилось с 215 секунд до среднего значения в 1,3 секунды! (Благодаря @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

Ответ 1

Чтобы действительно увидеть различия в производительности, вам нужно запустить определенную программу поверх БД. Просто вставка крошечной партии записей не очень много говорит. Скорость вставки зависит от таких вещей, как количество столбцов, количество индексов, шаблон транзакции, ограничения БД и другие действия.

Ответ 2

Непонятно, какие параметры долговечности вы включили в MyISAM или Innodb, и не используете ли вы однострочные вставки или пакетные вставки в обоих случаях.

Если вы используете однострочные вставки, вы не используете транзакции, и у вас есть долговечность (значение по умолчанию в InnoDB), то вы, вероятно, увидите, что производительность InnoDB сильно ограничена требованием совершить каждую транзакцию (т.е. диск) после каждой вставки строки.

MyISAM не имеет такой проблемы, поскольку она не является долговечной в любом случае, то есть, если машина выйдет из строя, вы более или менее гарантированно потеряете некоторые недавно записанные данные, которые ранее заявляли ранее, была написана успешно (если не вся таблица!).

Если вы хотите достойную производительность вставки, используйте пакетные вставки и/или транзакции, иначе вы просто измеряете скорость записи, за которой следует fsync(), который (на RAID-контроллере, не поддерживающем батарею, на вращательном магнитном медиа) - это просто скорость вращения диска.

Итак, причина, по которой innodb настолько последователен, заключается в том, что вы измеряете скорость вращения вашего диска.

Сказав, что если у вас есть занятый сервер, вы определенно абсолютно хотите использовать RAID-контроллер с батарейным питанием, тогда вы сможете добиться достойной производительности транзакции и хорошей долговечности (предполагая, что мощность не будет работать дольше, чем аккумулятор длится, и сервер не взрывается и т.д.).