Дизайн базы данных для обработки 1 миллиарда строк и подсчета

Мы получаем данные GPS в режиме реального времени со скоростью около 5000 пр. минут (с 4-х TCP-серверов). Каждый сервер использует одно соединение для вставки данных и буферизует данные между вставками. Каждые 15 минут или около того служба получает эти данные и обрабатывает их в поездках. Как только поездки были сгенерированы, фактические данные GPS обычно не так важны, только если пользователь хочет увидеть маршрут на карте.

Проблема заключается в том, что, похоже, база данных пытается не отставать от скорости ввода данных. Иногда, когда нагрузка увеличивается, время вставки резко возрастает (> 30 секунд), что, в свою очередь, позволяет буферизовать большее количество данных, что, в свою очередь, приводит к увеличению вставки и увеличению продолжительности вставки.

Я надеюсь получить некоторые комментарии к текущему дизайну, а также некоторые идеи, которые мы должны улучшить, и ответы на некоторые из наших вопросов - и любые другие советы, которые могут быть у людей!

Текущий дизайн

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

Дизайн таблицы

  • Id (PK, uniqueidentifier) ​​
  • DeviceId (FK, int)
  • PersonId (FK, int)
  • VehicleId (FK, int)
  • TokenId (FK, int)
  • UtcTime (PK, datetime2 (3))
  • Локатор (плавающий)
  • Долгота (с плавающей точкой) литий>
  • Скорость (маленький)
  • Заголовок (маленький)
  • Спутники (tinyint)
  • IOData (varbinary (100))
  • IgnitionState (tinyint)
  • UserInput (tinyint)
  • CreateTimeUtc (datetime2 (3))

Индексы

  • DeviceId_CreateTimeUtc_Desc
  • DeviceId_UtcTime_Desc (Clustered)
  • PersonId_UtcTime_Desc
  • TokenId_UtcTime_Desc
  • VehicleId_UtcTime_Desc

Каждую неделю в настоящее время занимает около 10 ГБ, включая индексы, и в настоящее время в основной базе данных содержится около 300 ГБ данных.

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

Я думаю, что мы также запускаем работу по перестройке индекса в неделю, когда используется новый раздел таблицы (неделя). Усадка не выполняется.

Аппарат представляет собой 8-ядерный HP с 12-гигабайтной памятью, а на диске с основной базой данных работает RAID 10.

Идеи

  • Ограничить объем данных, хранящихся в первичной базе данных, например, максимум 1 месяц. По крайней мере, это сделает базу данных более управляемой для резервного копирования /восстановления, но можем ли мы ожидать улучшения производительности, выполнив это?
  • Создайте 2 файла в файловой группе для текущих данных и распределите их на 2 разных физических раздела
  • Создавать базы данных master-slave, содержащие текущие данные, поэтому вставки и чтения выполняются в разных базах данных.
  • Поместите файлы для текущих данных на SSD-диски (зеркалирование сделает любую разницу в производительности с SSD-дисками?)

Пожалуйста, дайте мне знать, нужна ли дополнительная информация. Есть ужасно много факторов, влияющих на производительность, и, вероятно, одинаково много способов настроить его.

10 голосов | спросил sondergard 5 Maypm14 2014, 18:22:23

1 ответ


8

5000 вставок в минуту составляют около 83 вставок в секунду. С 5 индексами, которые 400 физических строк вставлены в секунду. Если рабочая нагрузка была в памяти, это не создавало бы проблемы даже для самого маленького из серверов. Даже если это была строка за строкой, используя самый неэффективный способ, о котором я могу думать. 83 тривиальных запроса в секунду просто не интересны с точки зрения CPU.

Возможно, вы привязаны к диску. Вы можете проверить это, посмотрев статистику ожидания или STATISTICS IO.

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

Представьте таблицу, в которую вы только физически вставляете в конец из-за постоянно увеличивающегося ключа. Рабочий набор будет одной страницей: последней. Это приведет к генерации последовательного ввода-вывода, а ленивый писатель или процесс контрольной точки записывают «конец» таблицы на диск.

Представьте таблицу со случайными позициями (классический пример: ключ-ключ). Здесь все - это рабочий набор, потому что для каждой вставки будет затронута случайная страница. МО случайны. Это худший случай, когда дело доходит до рабочего набора.

Ты посередине. Ваши индексы имеют структуру (SomeValue, SequentialDateTime). Первый компонент частично рандомизирует последовательность, обеспечиваемую вторым. Я думаю, что существует немало возможных значений для «SomeValue», так что у вас есть много случайно размещенных точек вставки в ваших индексах.

Вы говорите, что данные разбиваются на 10 ГБ таблицы в неделю. Это хорошая отправная точка, потому что рабочий набор теперь ограничен 10 ГБ (без учета любых чтений, которые вы могли бы сделать). С 12 ГБ памяти сервера маловероятно, что все релевантные страницы могут оставаться в памяти.

Если вы можете уменьшить размер еженедельных «разделов» или немного увеличить память сервера, вы, вероятно, хорошо.

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

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

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

ответил usr 7 Maypm14 2014, 14:11:20

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132