О бедном MySQL замолвите слово

[dernasherbrezon]

Небольшая заметка/исследование посвященное MySQL и производительности.




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

Дано:

• таблица на 17млн строк вида:

CREATE TABLE `table` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`upc` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `upcpicindex2` (`upc`,`id`)

) ENGINE=MyISAM AUTO_INCREMENT=17533978 DEFAULT CHARSET=utf8

• в формате myisam

Найти:

• полностью обежать таблицу и что то сделать. На самом деле абсолютно неважно что сделать, потому что это не относится к сути проблемы с которой я столкнулся.

Решение:

Лемма 1: Просто взять и выполнить: "select * from table" не получиться потому что 17млн строк не поместятся ни в какую память.

Для этого весь правильный мир делает следующий запрос: "select * from table where id > ? order by id asc limit ?". Работа в Java выглядит так:

PreparedStatement stmt = conn.prepareStatement("select id, upc from table where id > ? order by id asc limit ?");

long curId = 0;

while (true) {

stmt.setLong(1, curId);

stmt.setInt(2, 500);

ResultSet rs = stmt.executeQuery();

while (rs.next()) {

curId = rs.getLong(1);

//do something
}

rs.close();

}

Первый запуск занял почти 5 часов. Проблема. Из за того что первый запуск не полностью отработал мне пришлось готовиться ко второму запуску, а значит решать проблему.

Я добавил немного System.currentTimeMillis() Это помогло понять что stmt.executeQuery() начинает непонятным образом тормозить. Причем сначала это достаточно быстро работает, а потом линейно начинает занимать больше времени.

Я попробовал выполнить следующие запросы:

• начальное смещение не 0 а 10000 - сразу же начинает тормозить. Начал грешить на фрагментацию диска, неправильное заполнение таблицы на диске. Выполнил myisamchk - непомогло.
• "select id from table ..." - работает быстро. Значит MySQL каким то образом разделяет доступ к столбцам в индексе и простым данным.
• "select * from table" + Statement.setFetchSize(500) - в надежде что сервер поймет это и будет сам делать paging. Не делает. По прежнему валиться в OutOfMemory

Спасло то, что у меня была еще одна таблица на 1,5млн строчек. Я на ней попробовал выполнить тот же самый paging и там все заработало.

Как оказалось все дело в движке таблицы. MyISAM vs InnoDB. Во второй таблице был InnoDB поэтому там все работало быстро. Согласно интернетам InnoDB кэширует не только индексы но и данные, видимо поэтому получалось ±1мс.

alter table table engine=InnoDB;