Как эффективно хранить миллионы статистических записей?
У нас в интернет-магазине около 1,7 миллиона продуктов, мы хотим вести учет того, сколько просмотров было у этих продуктов в течение 1 года, мы хотим записывать просмотры каждые 2 часа, вопрос в том, какую структуру использовать для этой задачи?
Прямо сейчас мы попытались сохранить статистику в течение 30 дней назад в записях, которые имеют 2 столбца classified_id,stats где статистика похожа на разделенный JSON с форматом date: views, date: views... например, запись будет выглядеть
345422,{051216:23212,051217:64233} where 051216,051217=mm/dd/yy and 23212,64233=number of views
Это, конечно, глупо, если вы хотите вернуться на 1 год назад, поскольку, если вы хотите получить сумму просмотров, скажем, 1000 продуктов, вам нужно получить 30 МБ из базы данных и рассчитать ее самостоятельно.
Другой способ, которым мы думаем идти прямо сейчас, это просто иметь массивную таблицу с 3 столбцами. classified_id,date,view и хранить записи в отдельной строке, это, конечно, приведет к огромной таблице с сотнями миллионов строк, например, если у нас будет 1,8 миллиона объявлений и мы будем вести записи 24/7 в течение одного года каждые 2 часа, которые нам нужны
1800000*365*12=7,884,000,000(миллиарды с B) строк, которые, хотя и находятся далеко за пределами теоретического предела postgres, я думаю, что запросы к нему (скажем, для обновления представлений), даже с правильными индексами, будут принимать когда-то.
Какие-либо предложения? Я даже не представляю, как Google Analytics хранит статистику...
3 ответа
Это число не так высоко, как вы думаете. В текущей работе мы храним данные метрик для веб-сайтов, и общее количество строк у нас намного выше. А в предыдущей работе я работал с базой данных pg, которая собирала метрики из мобильной сети, и она собирала ~2 миллиарда записей в день. Так что не бойтесь миллиардов по количеству записей.
Вам определенно нужно будет разделить данные - скорее всего, по дням. С таким количеством данных вы можете найти индексы совершенно бесполезными. Зависит от самолетов, которые вы увидите в EXPLAIN вывод команды. Например, приложение telco вообще не использовало никаких индексов, потому что они просто замедляли бы весь движок.
Другой вопрос, насколько быстрые ответы на запросы вам понадобятся. И какие шаги в детализации (суммы за часы / дни / недели и т. Д.) Для запросов, которые вы разрешите для пользователей. Возможно, вам даже понадобится сделать некоторые агрегации для гранулярности, такие как неделя, месяц или квартал.
Дополнение:
Эти ~2 миллиарда записей в день в этом приложении telco занимали ~290 ГБ в день. И это означало вставку ~23000 записей в секунду, используя массовые вставки с командой COPY. Каждая масса составляла несколько тысяч записей. Необработанные данные были разделены по минутам. Чтобы избежать ожидания диска, в db было 4 табличных пространства на 4 разных дисках / массивах, и на них были распределены разделы. PostreSQL смог справиться со всем этим без проблем. Таким образом, вы должны подумать о правильной конфигурации HW тоже.
Хорошая идея также состоит в том, чтобы переместить каталог pg_xlog на отдельный диск или массив. Нет просто другой файловой системы. Все это должно быть отдельным HW. SSD я могу рекомендовать только в массивах с соответствующей проверкой ошибок. В последнее время у нас были проблемы с поврежденной базой данных на одном SSD.
Просто для того, чтобы предложить вам другой вариант, не относящийся к СУБД (немного не по теме), вы можете отправлять текстовые файлы (CSV, TSV, JSON, Parquet, ORC) в Amazon S3 и использовать AWS Athena для прямого запроса с помощью SQL.
Так как он будет запрашивать бесплатные текстовые файлы, вы можете просто отправить ему нефильтрованные блоги и запросить их через JDBC.
Во-первых, не используйте базу данных для записи статистики. Или, по крайней мере, использовать другую базу данных. Затраты на запись в журналы снижают скорость отклика вашего веб-приложения. А ежедневное резервное копирование займет намного больше времени из-за больших таблиц, которые не нужно делать так часто.
Решение "сделай сам", которое я выбрал бы, состоит в том, чтобы писать асинхронные файлы журналов, а затем обрабатывать эти файлы для построения статистики в базе данных аналитики. В этом ответе есть хороший фрагмент асинхронной записи кода. Или вы можете сравнить любой из многих доступных на Java регистраторов.
Также обратите внимание, что есть такие продукты, как Apache Kafka, специально предназначенные для сбора такого рода информации.
Другой возможностью является создание временного ряда в базе данных, ориентированной на столбцы, такой как HBase или Cassandra. В этом случае у вас будет по одной строке для каждого продукта и столько же столбцов, сколько показов.
Наконец, если вы собираетесь делать это с базой данных, как указывал @JosMac, создавайте разделы, избегайте индексов, насколько это возможно. Установите для параметра fillfactor значение 100. Вы также можете рассмотреть UNLOGGED таблицы. Но внимательно прочитайте документацию по PostgreSQL, прежде чем отключать журнал записи.