MySQL и скорость выполнения INSERT для разных типов таблиц
Сравнение скорости выполнения INSERT/INSERT DELAYED для таблиц типа HEAP (MEMORY), InnoDB и MyISAM
Я сейчас работаю над очень интересным проектом, который, как надеется заказчик, составит серьёзную конкуренцию Google Analytics. Но речь не об этом. Разбираясь с архитектурой системы, я обнаружил весьма интересную деталь: 8 гигабайт памяти сервера отдается под несколько таблиц типа HEAP. Так как HEAP-таблицы хранятся в исключительно в памяти, то операция вставки (INSERT) должна выполняться очень быстро, так как временные затраты, связанные с перемещением головок диска и физической записью, отсутствуют. Я решил найти подтверждение этой теории. Google is your friend, и я довольно быстро нашел статью MySQL Engine INSERT speed.
Меня несколько смутили результаты, приведённые автором:
- MyISAM (с
DELAY_KEY_WRITE=ALL): 33 000 INSERT/сек - InnoDB: 24 700 INSERT/сек
- MEMORY: 64 000 INSERT/сек
Смутило, в первую очередь, жуткое отставание InnoDB: хотя я не очень часто использовал данный тип таблиц, о его скорости я наслышан.
Первыми возникшими вопросами были:
- Какой тип вставки использовался для MyISAM/InnoDB-таблиц —
INSERTилиINSERT DELAYED? - Каким образом вставлялись данные — построчно или блочно (extended insert)?
- Значение
AUTOCOMMITпри работе с таблицей InnoDB.
Об этом оставалось только гадать, поэтому я решил повторить этот эксперимент.
Методика эксперимента:
Создаются три таблицы:
`field` INTEGER UNSIGNED NOT NULL
) TYPE=HEAP;
CREATE TABLE `test1` (
`field` INTEGER UNSIGNED NOT NULL
) TYPE=InnoDB;
CREATE TABLE `test2` (
`field` INTEGER UNSIGNED NOT NULL
) TYPE=MyISAM DELAY_KEY_WRITE=1;
Перед началом эксперимента выполнялась команда
Затем в цикле в таблицу вставлялись случайно сгенерированные данные (компьютер, на котором проводился эксперимент, был слабый, поэтому я ограничился тридцатью тысячами записями). После цикла выполнялся COMMIT. Скрипт выполнялся 10 раз, бралось среднее время.
Исходный текст скрипта:
set_time_limit(0);
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return (float)$usec + (float)$sec;
}
$link = mysql_connect('localhost', 'root', '');
mysql_select_db('test', $link);
$schema = "CREATE TABLE `test` (`field` INTEGER UNSIGNED NOT NULL)";
$engines = array(
' TYPE=HEAP',
' TYPE=InnoDB',
' DELAY_KEY_WRITE = 1 TYPE=MyISAM'
);
$inserts = array(
'',
'',
'DELAYED',
);
mysql_query("DROP TABLE IF EXISTS `test0`, `test1`, `test2`");
mysql_query("SET AUTOCOMMIT=0");
for ($i=0; $i<count($engines); ++$i) {
$query = preg_replace('/`test`/', "`test{$i}`", $schema) . $engines[$i];
mysql_query($query);
mysql_query("COMMIT", $link);
$start = microtime_float();
for ($j=0; $j<30000; ++$j) {
$val = mt_rand();
mysql_query("INSERT {$inserts[$i]} INTO `test{$i}` (`field`) VALUES({$val})", $link);
}
mysql_query("COMMIT", $link);
$end = microtime_float();
print $end - $start . "\n";
}
mysql_close($link);
?>
У меня получились такие результаты (я привожу относительную — относительно таблиц типа HEAP — скорость):
| HEAP | InnoDB | MyISAM | |
|---|---|---|---|
| INSERT | 1.000 | 1.277 | 1.162 |
| INSERT DELAYED | 2.125 | 0.998 |
Эксперимент показал, что разница по скорости между INSERT DELAYED для таблицы MyISAM и INSERT для таблицы HEAP очень незначительна. Таблицы InnoDB действительно оказались медленнее, но не сильно. Большую роль здесь могла сыграть «слабость» тестового компьютера и значение переменной innodb_flush_log_at_trx_commit. Вдобавок, MyISAM был сконфигурирован очень даже хорошо, в то время как InnoDB использовал практически значения по умолчанию (мне редко приходится пользоваться таблицами InnoDB, поэтому тонкой настройкой я не занимался). Тем не менее, результаты проведенного мной эксперимента совсем не согласуются с данными, приведёнными в исходной статье. Кто же из нас прав?
Мар
2008
Комментарии к статье «MySQL и скорость выполнения INSERT для разных типов таблиц» (4) »
Пожалуйста, не используйте эту форму для комментирования! Данная форма предназначена исключительно для ботов.
Оставить комментарий к записи «MySQL и скорость выполнения INSERT для разных типов таблиц»
गते गते पारगते पारसंगते बोधि स्वाहा
Меня зовут Владимир, я программист-фрилансер, специализирующийся на Web-программировании и програмировании под Linux.
По совместительству занимаюсь администрированием LAMP/LNMP-серверов и техническим переводом.
HEAP InnoDB MyISAM
INSERT 1.000 1.277 1.162
INSERT DELAYED 2.125 0.998
А как эти данные сопоставить с числом запросов, что есть ЕДИНИЦЕЙ в этой таблице??
Я измерял скорость вставки данных относительно скорости выполнения операции
INSERTв HEAP-таблицу. То есть все полученные значения делились на значение INSERT(HEAP), и таким образом получались относительные значения.Тест проведен не объективно. Во первых, функция mt_rand() выполняется достаточно долго, и существенно искажает результаты теста.
Во вторых, открываем мануал по MySQL (http://www.mysql.ru/docs/man/INSERT_DELAYED.html) и видим
«INSERT DELAYED работает только с таблицами типа ISAM и MyISAM.», возникаем вопрос зачем это было тестить на HEAP и InnoDB, если эти таблицы этого не поддерживают?
идем далее..
mysql_query(«COMMIT», $link)
зачем?? никаких транзакций здесь не было..
Она выполняется для всех типов таблиц. Соответственно, от неё страдают все три типа таблиц. Поэтому и числа приведены не абсолютные, а относительные.
По крайней мере, с MySQL 5.0 это уже не так:
HEAP = MEMORY
Вы код посмотрите
mysql_query("INSERT {$inserts[$i]} INTO `test{$i}` (`field`) VALUES({$val})", $link);DELAYED это
$inserts[2], что соответствует таблице MyISAM. Попытка выполнитьINSERT DELAYEDна InnoDB закончится ошибкой.Для InnoDB
SET autocommit=0;вроде как начинает неявную транзакцию.