Хранение огромного количества (простых) данных графика временной шкалы в БД

Мне нужно хранить количество воспроизведений за каждую секунду подкаста/аудиофайла. Это приведет к простому графику временной шкалы (например, графику «посещений» в Google Analytics) с секундами на оси x и воспроизведением на оси y.

Тем не менее, эти подкасты потенциально могут продолжаться до 3 часов, и 100 000 воспроизведений в секунду вполне реально. Это 10 800 секунд, каждая из которых воспроизводится до 100 000 раз. Очевидно, что хранить каждую сыгранную секунду в отдельной строке нереально (это приведет к 1+ миллиарду строк), так как я хочу иметь возможность быстро извлекать эти необработанные данные.

Итак, мой вопрос: как мне лучше всего хранить эти огромные объемы данных временной шкалы?

Одна из идей, которые у меня были, состояла в том, чтобы использовать столбец text/blob, а затем разделять воспроизведения запятыми, каждая запятая представляет новую секунду (в последовательности), а затем число, обозначающее количество раз, когда эта секунда была воспроизведена. Итак, если 100 000 воспроизведений за первую секунду и 90 000 воспроизведений за вторую секунду и 95 000 воспроизведений за третью секунду, то я бы сохранил это так: «100000,90000,95000,[...]» в столбце text/blob.

Является ли это возможным способом хранения таких данных? Есть ли способ лучше?

Спасибо!

Изменить: данные отслеживаются в другом источнике, и мне нужно только обновлять необработанные данные графика каждые 15 минут или около того. Следовательно, быстрое чтение является главной проблемой.

Примечание. Из-за характера этого проекта каждую сыгранную секунду придется отслеживать отдельно (другими словами, я не могу просто отслеживать «начало» и «конец» каждого воспроизведения).


database database-design timeline data-structures
person Jamie Appleseed    schedule 14.07.2010    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Проблема с хранилищем больших двоичных объектов заключается в том, что вам необходимо обновить весь большой двоичный объект для всех изменений. Это не обязательно плохо. Используя ваш формат: (100000, 90000,...), 7 * 3600 * 3 = ~ 75 КБ. Но это означает, что вы обновляете этот 75-килобайтный объект для каждой игры каждую секунду.

И, конечно же, большой двоичный объект непрозрачен для SQL, поэтому запрос «в какой секунде какой песни больше всего играет» будет невозможным запросом на уровне SQL (это, по сути, сканирование таблицы всех данных, чтобы узнать это).

И есть много накладных расходов на синтаксический анализ этих данных.

С другой стороны. Идентификатор подкаста (4 байта), второе смещение (2 байта без знака позволяют транслировать поды продолжительностью до 18 часов), количество воспроизведений (4 байта) = 10 байтов в секунду. Итак, за вычетом накладных расходов на блокировку, 3-часовая песня составляет 3600 * 3 * 10 = 108 КБ на песню.

Если вы сохранили его как большой двоичный объект, а не текст (длинный блок), 4 * 3600 * 3 = 43 КБ.

Таким образом, структура второй/строки "всего" в два раза больше (в идеальном мире, обратитесь за подробностями к серверу БД) двоичного двоичного объекта. Учитывая дополнительные преимущества, которые это дает вам с точки зрения возможности запрашивать вещи, это, вероятно, стоит сделать.

Единственная обратная сторона секунды/на строку заключается в том, что если вам нужно много обновлений (несколько секунд одновременно для одной песни), это большой трафик UPDATE в БД, тогда как с методом blob это, вероятно, одно обновление.

Ваши модели трафика повлияют на это больше, чем на что-либо.

person Will Hartung    schedule 14.07.2010
comment
Спасибо за эти плюсы/минусы - очень полезно! Я смогу получать данные отслеживания партиями с 15-минутными интервалами (= легко 1000 обновлений), так что это определенно плюс для BLOB-подхода. Кроме того, мне нужно использовать эти данные только для графика временной шкалы, поэтому возможность запрашивать данные не имеет значения. При этом я нахожу гибкость отдельных строк привлекательной (и, глядя на ответы, многие люди, кажется, думают так же). Однако кажется, что подход с использованием BLOB-объектов осуществим, поэтому я проведу некоторое тестирование обоих подходов и посмотрю, какой из них лучше всего работает на практике. - person Jamie Appleseed; 15.07.2010

arrow_upward
0
arrow_downward

Будет ли проблематично использовать каждую секунду и сколько воспроизведений в секунду? Это означает 10 тысяч строк, что неплохо, и вам просто нужно каждую секунду ВСТАВЛЯТЬ строку с текущими данными.

РЕДАКТИРОВАТЬ: я бы сказал, что эти решения лучше, чем делать что-то через запятую в столбце TEXT ... тем более, что получение и обработка данных (которые, как вы говорите, вы хотите сделать) были бы очень грязными.

person zebediah49    schedule 14.07.2010
comment
Прежде всего, спасибо за ваш быстрый ответ! Возможно, я должен уточнить, что меня не слишком волнует логика обновления, когда сама структура данных такая простая, и я не слишком озабочен частотой обновления (допустимо только обновление каждые 15 минут). Насколько я понимаю, мне понадобятся три столбца: podcast_id, second и plays. Чтобы получить данные графика, мне нужно получить 10 000 записей, запрошенных по внешнему ключу и отсортированных по целому числу. Разве это не займет секунду или две, чтобы получить? - person Jamie Appleseed; 15.07.2010

arrow_upward
0
arrow_downward

Я бы рассматривал это как проблему ключ-значение.

for each second played

   Song[second] += 1

end

В качестве реляционной базы данных -

song
----
name | second | plays

И хак psuedo-sql для запуска второго:

insert into song(name, second, plays) values("xyz", "abc", 0)

и еще один для обновления второго

update song plays = plays + 1 where name = xyz and second = abc

В 3-часовом подкасте будет 11 тысяч строк.

person Paul Nathan    schedule 14.07.2010
comment
Спасибо за ваш ответ. 10-11 тыс. строк кажутся много для каждого подкаста, если мне нужно быстро получить необработанные данные графика (предпочтительно около 200 мс). Тем более, что строки нужно запрашивать и сортировать. Есть ли у вас какой-либо опыт в отношении того, сколько времени требуется для извлечения 10-11 тыс. строк при запросе по внешнему ключу и сортировке по целочисленному столбцу? Большая часть материала, с которым я работал до сих пор, составляла около 100 строк (представляющих страницы в CMS), а это совсем другая история! - person Jamie Appleseed; 15.07.2010
comment
Если данные обновляются каждые 15 минут, то зачем вам получать необработанные данные за 200 мс? Получить его за 3 секунды и кэшировать до следующего обновления. - person WW.; 15.07.2010
comment
Ждать 3 секунды при первой загрузке нежелательно. Тем более, что большинство графиков будут открываться всего несколько раз в день (это означает, что у большинства пользователей время загрузки составляет 3 секунды). Думаю, я мог бы изучить его предварительное кэширование (генерировать кеш сразу после обновлений), однако это потребовало бы очень много вычислительной мощности для всех обновляемых подкастов каждые 15 минут. - person Jamie Appleseed; 15.07.2010

arrow_upward
0
arrow_downward

Это действительно зависит от того, что генерирует данные.

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

Какие фрагменты в событии, единице работы или транзакции вы загружаете?

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

Если это так, вы можете иметь стол

  • идентификатор подкаста
  • второе смещение
  • playCount

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

update t устанавливает playCount = playCount +1, где podCastId = x и secondOffset между y и z

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

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

person Rawheiser    schedule 14.07.2010