Скорость вставки в БД?

Question

[p4s8x]

Возникла задача — вставить множество (десятки миллионов записей) в базу данных.

Вот такая схема:

<font color="black"><font color="#0000ff">CREATE</font> <font color="#0000ff">TABLE</font> <font color="#0000ff">IF</font> <font color="#0000ff">NOT</font> <font color="#0000ff">EXISTS</font> `TaskSolution_60` (<br/>
 `id` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font> AUTO_INCREMENT,<br/>
 `period` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalDepricateCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalDevelopeCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalNewCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `totalRunningCost` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak0` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak1` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak2` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak3` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `ak4` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font>,<br/>
 `status` <font color="#0000ff">int</font>(11) unsigned <font color="#0000ff">NOT</font> <font color="#0000ff">NULL</font> <font color="#0000ff">DEFAULT</font> <font color="#A31515">'0'</font>,<br/>
 <font color="#0000ff">PRIMARY</font> <font color="#0000ff">KEY</font> (`id`)<br/>
) ENGINE=MEMORY <font color="#0000ff">DEFAULT</font> CHARSET=cp1251 AUTO_INCREMENT=1;<br/>
</font><br/>
<font color="gray">* This source code was highlighted with <a href="http://virtser.net/blog/post/source-code-highlighter.aspx"><font color="gray">Source Code Highlighter</font></a>.</font>

Такой запрос:

<font color="black"><font color="#0000ff">INSERT</font> <font color="#0000ff">INTO</font> TaskSolution_60 <font color="#0000ff">SET</font> totalCost=10, totalDepricateCost=9, totalDevelopeCost=8, totalNewCost=7, totalRunningCost=6,ak0=5,ak1=4,ak2=3,ak3=2,ak4=1,period=0</font><br/>
<br/>
<font color="gray">* This source code was highlighted with <a href="http://virtser.net/blog/post/source-code-highlighter.aspx"><font color="gray">Source Code Highlighter</font></a>.</font>

У меня получилась скорость всего 16 000 вставок в секунду, что кажется очень мало.


Экспериментирую на ENGINE = MEMORY.


Пробовал использовать LOCK TABLES WRITE, а также DISABLE KEYS.

statement prepare только замедляет процесс.

рекомендации вроде mysql.ru/docs/man/SEC463.html и dev.mysql.com/doc/refman/5.0/en/insert-speed.html

проверял на различных операционных системах и своих серверах, везде результат примерно одинаковый.


Прошу советы по возможным хитрым оптимизациям.

Хотелось бы увеличить скорость вставки в 10 раз, поэтому рассматривается вариант отказаться от Mysql и использовать другую СУБД, возможно nosql. Рекомендации приветствуются!

Comments

[Melkij]

А как и чем скармливаете данные?

[p4s8x]

Экспериментировал с mysql_query(php) и QSqlRecord::exec(C++)

[p4s8x]

Решение вопроса требовалось для диплома. Защита уже в ближайшие дни. Как выйду из запоя — напишу статью со своими исследованиями этого вопроса.

Answer 1

[homm]

Нужно объединять строки в один запрос:

INSERT INTO TaskSolution_60 (totalCost, totalDepricateCost,
totalDevelopeCost, totalNewCost, totalRunningCost, ak0, ak1, ak2, ak3, ak4, period)
VALUES
(10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0),
(10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0),
(10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0) …

Только смотрите, чтобы не вылезти за максимальный размер запроса.

Comments

[p4s8x]

Да, спасибо, похоже единственное решение. 120 000/сек это уже поинтереснее.

[homm]

А по сколько строк вы объединяете? С такими данными как у вас можно тысяч по 10 пихать.

Answer 2

[edogs]

Если исходные данные позволяют, то load data infile может помочь.

Comments

[Константин]

Попробуйте это:
mysql -u user -p database < dump.sql
без врапперов и всего прочего…

[edogs]

mysql < dump.sql — это банальное выполнение всех запросов из sql файла, это долго.
load data infile — прямая вставка данных, по сути append к файлу данных sql — это намного быстрее.

Answer 3

[Melkij]

Почему спросил про то, как вставляете данные — от этого очень большие различия будуте. Мелкое исследование было тут: forum.php.su/topic.php?forum=28&topic=4026&postid=1318871174#1318871174
Лучшую скорость инзертов показала толпа запросов, объединённых в один mysqli_multi_query. Правда, препарированные запросы некорректно сделаны были, теоретически они должны быть ещё чуть быстрее.

А толпа mysql_query в цикле — само собой куда медленнее.

Comments

[Александр]

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

[Melkij]

Ну а я никак не ожидал, что multi_query окажется быстрее одного запроса на много строк. Однако же.
Тестировать надо…

Answer 4

[Alexander Kouznetsov]

Где-то встречал рекомендации, что больше 10 млн записей в InnoDB складывать в одну таблицу не стоит. Т.е. нужно разносить по разным таблицам.

Про объединение в один запрос уже сказали — сильно легче становится.

А можете код с prepared показать? Я почти двукратного увеличение скорости на Insert и Update с prepared добивался, потому очень интересно.

Answer 5

[FYR]

Однозначно объединять в один запрос. В принципе 16000 это ниочем без инфы о железе.

По MySQL не скажу, но на PostgreSQL 120 000 в секунду чисто чиселок без индексов укладываю в базу с записью на HDD (промышленный сервер, SAS RAID10 на 8-ми дисках) с помощью COPY. Реально думаю и двести потянет.

Но вообще поправьте меня, но 120к в секунду за пару минут 15млн. С такими объемами данных в таблице реляционкам не айс работать.

Answer 6

[codecity]

>Экспериментирую на ENGINE = MEMORY.

А потом планируете на диск писать? У меня для MySQL получается порядка 120-200 простых записей в секунду в многопоточном режиме и около 20 записей в однопоточном режиме.

Проверьте записать на диск, очень интересны ваши результаты.

Comments

[codecity]

>120-200 простых записей

уточню. Это не тысяч а просто штук INSERT-ов. Причем от железа не сильно зависит — больше от диска. Настройки по умолчанию.

Answer 7

[dakotas]

Может ответ будет и не уместным, но... Не пробовал работать с NoSQL решениями?
К примеру с MongoDB я добивался в много раз большей скорости записи/чтения чем с MySQL