В чем лучше хранить данные для быстрого доступа?

Question

[Андрей Никифоров]

Здравствуйте!

Исходные данные: есть таблица в mysql в 31 миллион строк, весом в 40Гб. Со всеми необходимыми индексами, естественно. В день эта таблица растет на 100к записей.
С записью проблем нет: записи вставляются равномерно в течении 12 часов, и с этим сервер справляется.

Проблема возникает тогда, когда нужно из таблицы читать. В таблице хранятся данные проверок сайтов, и основные запросы — это аккумуляция данных по конкретному сайту. В силу объема таблицы даже индексированные запросы не быстрые.

Возникла идея хранить в mysql только текущее состояние проверок, а архивные данные сбрасывать в другое хранилище. Но мне все еще нужно иметь возможность аналитики по архивным данным.

Посоветуйте хранилище, которое лучше подойдет для хранения таких объемов данных и аналитики по ним. Или дайте совет по ускорению текущего.

Answer 1

[werw]

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

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

Если требования не жесткие, то есть данные нужно получать изредка и не очень быстро и посему нет смысла отдельную таблицу городить, то - посмотреть внимательнее на индексы и запросы. Может, используются не те индексы? Так как 40 Г для современного железа не является большой проблемой. Что говорит сервер по "план запроса"?

При жестких требованиях на скорость можно агрегировать прямо в оперативной памяти, например, при помощи Tarantool, это будет довольно быстро. Наверняка, агрегированная база данных в разы меньше основной, то есть при наличие 40 Г БД выделить 4 Г на хранение агрегированных данных в оперативной памяти для нынешних серверов не является проблемой.

Answer 2

[Walt Disney]

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

Соответственно результаты будут состоять из двух запросов: простого селекта по уже агрегированным данным + сам агрегирующий запрос по суточной таблице.

Т.е. если вы, каким либо способом, избавитесь от запросов по всему датасету в онлайне, то эта схема проработает у вас очень долго (до полного исчерпания аппаратных ресурсов) в независимости от того какую БД вы будете использовать.

Answer 3

[Игорь]

Попробуйте заюзать partition или по другому построить логику (к примеру каждый день в новую таблицу писать, использовать merge таблиц)

Answer 4

[Dimonchik]

перенесите часть логики на вставку - по триггеру обновляйте флаги/поля, которые так или иначе должны обновляться, там же можно и суммировать

но вообще странновато, что проблема для всего 31 млн записей и уже есть

Answer 5

[Alex XYZ]

Было как-то дело, я работал с тестовым набором в миллионы строк на одной таблице. Попробовал ElasticSearch. По скорости работы агрегации примерно на уровне коммерческой версии MSSQL (не знал, что бесплатная версия и коммерческая MSSQL сильно отличаются по производительности, а ElasticSearch делал выборки не медленнее коммерческого MSSQL). Но в агрегацию ElasticSearch въехать не просто.

Answer 6

[Draconian]

Есть подозрение, что нужно либо добавить индексы, либо проверить существующие, потому что выборки по индексированным полям должны выполняться очень быстро при таких размерах.

В остальном, Walt Disney дал правильные советы: разделить эту таблицу на две - архивную (в идеале - партицированную) и оперативную, в которой хранить данные за определенный период, джобами по истечении срока перекидывать эти данные в архив.
Можно еще дополнительно иметь "супер-оперативную" таблицу, в которой хранить необходимые вам уже агрегированные данные, которые обновлять триггерами после вставки информации в оперативную таблицу. Таким образом, после обновления оперативной таблицы, у вас уже будет вся аналитика за текущий период (день\неделю и т.д.).

Что касается аналитики по архиву, из моего опыта, заказчик всегда просит оперативные, быстро работающие отчеты, и агрегированные отчеты по месяцам\годам из архива, которые лишь бы построились. :-)