OPTIMIZE огромных таблиц в условиях ограниченных ресурсов или закат солнца вручную

Предыстория

Есть проект, в рамках которого приходится работать с большим объем данных. В частности есть одна денормализованная таблица, в которой хранятся все актуальные предложения существующих клиентов, а также устаревшие предложения, помеченные is_deleted = 1, ожидающие удаления.

Количество записей в данной таблице до недавнего времени колебалось от 30 до 50 миллионов. Обычный OPTIMIZE даже при таких условиях не всегда срабатывал. Поэтому отец-основатель (Евгений Васильевич aka haron) придумал пересобирать таблицу таким образом: все актуальные (is_deleted = 0) копировались в таблицу с идентичной структурой с добавлением префикса по дате и времени, а когда копирование завершалось, оставалось только удалить исходную таблицу, а новую переименовать в исходную.

Такой подход работал надежно, пока не потребовалось повысить скорость поиска предложений. И тут начинается наша небольшая история.

Ветер перемен

Для повышения скорости поиска, как ни странно, было решено использовать поисовик. Мы выбрали Solr. Почему? Потому что для наших целей он хорош. Да и не только для наших целей. Если будет время, обязательно напишу статью, посвященную этому поисковому движку.

Все было хорошо, пока после отладки на разработческих серверах мы не выкатили новую версию сайта на продакшен. Поисковик работал, парсеры клиентских прайсов запустились и работали по новой схеме достаточно хорошо, если не считать некоторых шероховатостей. А вот скрипт пересборки таблицы всех предложений начал падать каждую ночь.

Проблема заключалась в том, что Solr располагался на том же серваке, что и мускуль. Для поиска предложений по сайту это было нормально. Но Solr хоть и шустрый зверь, зато требует для своей работы немало ресурсов. Впрочем, любое аналогичное решение делило бы ресурсы сервера пополам между мускулем и поисковым движком. Соответственно, скрипт пересбора предложений падал с ошибкой о нехватке места в tmpfs.

Поиск решения

Вариант нулевой, нереальный. Выделить отдельный сервер для поисковика. Дело в том, что сервис еще не настолько раскрученный и посещаемый, чтобы покупать еще один весьма дорогой сервер.

Первый вариант. Заключался он в попытке как-то оптимизировать уже существующий скрипт. Но успеха мы не добились, и практически сразу от него отказались.

Второй вариант — использовать HandlerSocket. HS — это быстро, это надежно, и это в конце концов модно. Впрочем, оказалось, что HS не подходит для считывания огромного массива данных. HS отлично справляется с быстрым поиском хаотично расположенных отдельных записей. А при последовательном считывании большого массива данных частями происходит замедление на каждом следующем шаге, если использовать limit, offset. Но это не самая большая проблема при использовании HS, — нам нужно было делать выборку по условию is_deleted = 0, а это поле не являлось индексным. И вообще говоря, делать его таковым бессмысленно. Поэтому бравый HandlerSocket, который уже хорошо зарекомендовал себя для других задач, в этот раз надежд не оправдал.

К счастью, был еще третий вариант, которым лично я до этого случая никогда не пользовался. Это — нативный мускульный HANDLER ^[1]. Что он позволяет делать и чем он хорош? Он позволяет осуществлять последовательное считывание записей по определенному условию (даже не по индексному полю) без потери скорости, которая происходит обычно за счет высчитывания limit, offset или between. Все, что вам нужно сделать, — это открыть handler, считать первую порцию данных с определенным условием (READ FIRST) и дальше, не меняя условия, осуществлять READ NEXT, пока есть хоть какие-то данные. Последовательность действий вызывает ассоциацию с C-шным подходом, например, к сканированию директории. И самое радостное здесь в том, что указатель остается на том месте, где мы его оставили в последний раз.

В итоге мы имеем постоянную высокую скорость считывания и разумное использование памяти при считывании данных по всей таблице, даже если в таблице скопилось 270 миллионов записей. Ровно столько было записей к тому моменту, как мы нашли данное решение. Много это или мало? Вопрос относительный. Но если сервис из-за такого объема начинает давать сбои, значит много.

В условиях ограниченных ресурсов это решение оказалось наиболее выигрышным по скорости и по надежности. Я хочу сказать, что если вы смогли считать первые 10000 записей за одну итерацию, значит, вы считаете и все последующие записи, сколько бы их ни было.

PS

Думаю, что данное решение кому-нибудь обязательно пригодится.

Благодарности

Хочу сказать спасибо Юрию Масленникову, который собственно и предложил идею про handler.

Автор: bvorotnikov