Уровень транзакций, nolock/readpast и параллелизм

У нас есть система, которая одновременно вставляет большой объем данных с нескольких станций, а также предоставляет интерфейс запроса данных. Схема выглядит примерно так (извините за плохое форматирование):

[SyncTable]
  SyncID
  StationID
  MeasuringTime


[DataTypeTable]
  TypeID
  TypeName


[DataTable]
  SyncID
  TypeID
  DataColumns...

Вставка данных выполняется в «Синхронизации» и происходит следующим образом (мы только вставляем данные в систему, мы никогда не обновляем)

INSERT INTO SyncTable(StationID, MeasuringTime) VALUES (X,Y); SELECT @@IDENTITY

INSERT INTO DataTable(SyncID, TypeID, DataColumns) VALUES 
  (SyncIDJustInserted, InMemoryCachedTypeID, Data)
  ... lots (500) similar inserts into DataTable ...

И запросы выглядят так (для данной станции, времени измерения и типа данных)

SELECT SyncID FROM SyncTable WHERE StationID = @StationID 
                               AND MeasuringTime = @MeasuringTime 
SELECT DataColumns FROM DataTable WHERE SyncID = @SyncIDJustSelected
                                  AND DataTypeID = @TypeID

Мой вопрос заключается в том, как мы можем объединить уровень транзакций для вставок и подсказки NOLOCK/READPAST для запросов, чтобы:

  1. Мы максимизируем параллелизм в нашей системе, отдавая предпочтение вставкам (нам нужно хранить много данных, что-то вроде 2000+ записей в секунду).
  2. Запросы возвращают данные только из «зафиксированной» синхронизации (нам не нужен набор результатов с наполовину вставленной синхронизацией или синхронизация с некоторыми пропущенными записями из-за пропуска блокировки)
  3. Нас не волнует, включены ли в запрос «самые новые» данные, мы больше заботимся о согласованности и отзывчивости, чем о «живых» и актуальных данных.

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

ОБНОВЛЕНИЕ: Просто добавьте немного больше информации для будущих ответов. Мы запускаем SQL Server 2005 (2008 год, вероятно, через шесть месяцев) в сети SAN с 5+ ТБ хранилища на начальном этапе. Я не уверен, какой тип RAID настроен на SAn и сколько именно дисков у нас есть.


sql-server optimization transactions concurrency locking
person soren.enemaerke    schedule 13.11.2008    source источник

Ответы (3)


arrow_upward
0
arrow_downward

  1. Какой тип дисковой системы вы будете использовать? Если у вас большой массив RAID с чередованием, операции записи должны выполняться хорошо. Если вы можете оценить требуемое количество операций чтения и записи в секунду, вы можете подставить эти числа в формулу и посмотреть, будет ли ваша дисковая подсистема соответствовать этим требованиям. Возможно, у вас нет контроля над оборудованием...

  2. Разве вы не обернули бы вставки в транзакцию, что сделало бы их недоступными для чтения, пока вставка не будет завершена?

  3. Это должно произойти, если ваше оборудование настроено правильно, и вы обращаете внимание на кодирование SQL, что, похоже, и есть.

Посмотрите на инструменты SQLIO.exe и SQL Stress:

SQLIOStress.exe SQLIOStress.exe имитирует различные модели поведения ввода-вывода SQL Server 2000, чтобы обеспечить элементарную безопасность ввода-вывода.

Утилиту SQLIOStress можно загрузить с веб-сайта Microsoft. См. следующую статью.

• Как использовать утилиту SQLIOStress для нагрузки на дисковую подсистему, такую ​​как SQL Server http://support.microsoft.com/default.aspx?scid=kb;en-us;231619

Важно! Загружаемый файл содержит полный технический документ с расширенными сведениями об этой утилите.

SQLIO.exe SQLIO.exe — это служебная программа ввода-вывода SQL Server 2000, используемая для получения основных результатов эталонного тестирования.

Утилиту SQLIO можно загрузить с веб-сайта Microsoft. См. следующее: • Средство тестирования производительности SQLIO (разработка SQL) — доступно для клиентов http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO..msi

person Sam    schedule 13.11.2008
comment
Отметив это как ответ, поскольку часть решения была связана с правильной настройкой конкретной дисковой системы, что значительно улучшило пропускную способность. - person soren.enemaerke; 14.10.2009

arrow_upward
1
arrow_downward

Если вы используете SQL 2005 и более поздние версии, рассмотрите возможность реализации изоляции моментальных снимков. Вы не сможете получить стабильные результаты с nolock.

Решить это на SQL 2000 гораздо сложнее.

person Sam Saffron    schedule 14.11.2008

arrow_upward
1
arrow_downward

Это отличный сценарий для функции секционирования SQL Server 2005/2008 Enterprise. Вы можете создать раздел для каждого идентификатора станции, и данные каждого идентификатора станции могут помещаться в свою собственную файловую группу (если хотите, это может не потребоваться в зависимости от вашей нагрузки).

Это дает вам некоторые преимущества с параллелизмом:

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

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

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Секционирование в SQL Server 2008 еще лучше, особенно в отношении параллелизма. Это все еще не серебряная пуля — она требует ручного проектирования и обслуживания квалифицированным администратором баз данных. Это не функция «установи и забудь», и для нее требуется Enterprise Edition, которая стоит больше, чем Standard Edition. Однако мне это нравится - я использовал его несколько раз, и он решил для меня определенные проблемы.

person Brent Ozar    schedule 15.11.2008
comment
Еще одно преимущество разделения по идентификатору станции: если вы создаете правильные кластеризованные индексы (идентификатор станции, syncid) для synctable, (syncid) для таблицы данных и используете удостоверение для syncid, вы никогда не получите разделения страниц из действия вставки, что позволяет вам использовать READPAST. в операторах select, которые затем вообще не мешают операции вставки (они не ждут, чтобы получить свои S-блокировки для записей с X-блокировкой, и без обновлений никакая X-блокировка не выдается для любых строк с S-блокировкой) . Если бы разделение страниц было возможно, READPAST мог иногда приводить к противоречивым результатам, что делало бы этот вариант опасным. - person TToni; 09.09.2013