Уровень транзакции, блокировка / повторная передача и параллелизм
У нас есть система, которая одновременно вставляет большой объем данных с нескольких станций, а также предоставляет интерфейс запроса данных. Схема выглядит примерно так (извините за плохое форматирование):
[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 по запросам, чтобы:
- Мы максимизируем параллелизм в нашей системе, отдавая предпочтение вставкам (нам нужно хранить много данных, что-то более 2000 записей в секунду)
- Запросы возвращают данные только из "зафиксированной" синхронизации (нам не нужен набор результатов с наполовину вставленной синхронизацией или синхронизация с некоторыми пропущенными записями из-за пропуска блокировки)
- Нам все равно, включены ли в запрос «самые новые» данные, мы больше заботимся о согласованности и оперативности, чем о «живых» и актуальных данных.
Это может быть очень противоречивыми целями и может потребовать высокого уровня изоляции транзакции, но меня интересуют все уловки и оптимизации для достижения высокой скорости отклика как при вставке, так и при выборе. Я с удовольствием уточню, нужны ли дополнительные подробности, чтобы выявить новые хитрости и хитрости.
ОБНОВЛЕНИЕ: просто добавляю немного больше информации для будущих ответов. Мы запускаем SQL Server 2005 (вероятно, 2008 в течение шести месяцев) в сети SAN с объемом памяти 5+ ТБ. Я не уверен, для какого типа RAID настроен SAn и сколько дисков у нас есть в наличии.
Ответы 3
Какой тип дисковой системы вы будете использовать? Если у вас большой RAID-массив с чередованием, запись должна выполняться хорошо. Если вы можете оценить необходимое количество операций чтения и записи в секунду, вы можете подставить эти числа в формулу и посмотреть, будет ли ваша дисковая подсистема не отставать. Может быть, у вас нет контроля над оборудованием ...
Разве вы не обернули бы вставки в транзакцию, что сделало бы их недоступными для чтения, пока вставка не будет завершена?
Это должно произойти, если ваше оборудование настроено правильно и вы обращаете внимание на кодировку 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
Если вы используете SQL 2005 и выше, попробуйте реализовать изоляция моментального снимка. Вы не сможете добиться стабильных результатов с помощью nolock.
Решить эту проблему на SQL 2000 намного сложнее.
Это отличный сценарий для функции разбиения на разделы SQL Server 2005/2008 Enterprise. Вы можете создать раздел для каждого StationID, и данные каждого StationID могут быть помещены в отдельную файловую группу (при желании это может не потребоваться в зависимости от вашей нагрузки).
Это дает вам некоторые преимущества параллелизма:
- Если вы разбиваете по идентификаторам станций, пользователи могут запускать запросы выбора для идентификаторов станций, которые в данный момент не загружаются, и они вообще не столкнутся с какими-либо проблемами параллелизма.
- Если вы разбиваете по идентификатору станции, то несколько станций могут вставлять данные одновременно без проблем с параллелизмом (если они находятся в разных файловых группах)
- Если вы разбиваете по диапазону syncid, вы можете поместить старые данные в более медленное хранилище.
- Если вы разбиваете по диапазону syncid, И если ваши диапазоны достаточно малы (то есть не диапазон с тысячами syncid), вы можете выполнять нагрузки одновременно с запросами ваших пользователей, не сталкиваясь с проблемами параллелизма.
Описываемый вами сценарий имеет много общего с ночными загрузками хранилища данных. Microsoft выполнила технический справочный проект под названием Project Real, который может вас заинтересовать. Они опубликовали его как стандарт, и вы можете прочитать документацию по дизайну и код реализации, чтобы увидеть, как они справляются с действительно быстрой загрузкой:
http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx
В SQL Server 2008 секционирование даже лучше, особенно в отношении параллелизма. Это все еще не серебряная пуля - он требует ручной разработки и обслуживания опытным администратором баз данных. Это не функция «установил и забыл», и для нее требуется Enterprise Edition, которая стоит больше, чем Standard Edition. Но мне он нравится - я использовал его несколько раз, и он решил за меня определенные проблемы.
Еще одно преимущество разделения по идентификатору станции: если вы создаете правильные кластеризованные индексы (stationid, syncid) для synctable, (syncid) для datatable и используете идентификатор для syncid, вы никогда не получите разделение страниц из действия вставки, что позволяет использовать READPAST в операторах select, которые в этом случае вообще не мешают операции вставки (они не дожидаются получения своих S-блокировок для записей с блокировкой X, и без обновлений блокировка X не выполняется для любых строк с блокировкой S) . Если бы страницы были возможны, READPAST иногда приводил бы к противоречивым результатам, что делало бы этот вариант опасным.
Пометить это как ответ, поскольку часть «решения» была связана с правильной настройкой конкретной дисковой системы, что значительно улучшило пропускную способность