Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Перемещение данных из кучи в индексированную таблицу  [new]
__asv__
Member

Откуда:
Сообщений: 6
Здравствуйте, участники форума.

Есть задача накапливать в базе данные поступающие посекундно, значения от 10000-30000 объектов, далее обеспечивать просмотр и анализ накопленных данных.
Хотел использовать бесплатную СУБД MS SQL Server Express.

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

Сделал следующую структуру баз:

Конфигурация системы:
- ПК: ЦП 4-х ядерный Q9500 2.83ГГц, ОЗУ 4 ГБ, HDD 150 ГБ
- Windows 7 Проф. 64
- MS SQL Server 2008 R2 Express

Есть база, в которой созданы две кучи
USE [db_Heaps]
GO
CREATE TABLE [dbo].[t_SignalsValues_Heap_1](
[SignalId] [int] NOT NULL,
[Time] [datetime] NOT NULL,
[Value] [float] NULL,
[Quality] [tinyint] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[t_SignalsValues_Heap_2](
[SignalId] [int] NOT NULL,
[Time] [datetime] NOT NULL,
[Value] [float] NULL,
[Quality] [tinyint] NOT NULL
) ON [PRIMARY]

Есть база с индексированной таблицей из которой будут выполнятся выборки
USE [db_IndexedTables]
GO

CREATE TABLE [dbo].[t_SignalsValues](
[SignalId] [int] NOT NULL,
[Time] [datetime] NOT NULL,
[Value] [float] NULL,
[Quality] [tinyint] NOT NULL,
CONSTRAINT [PK_t_SignalsValues] PRIMARY KEY CLUSTERED
(
[SignalId] ASC,
[Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

Протестировал запись в кучу, вставка 30000 записей выполняется до 0.700 секунды.

Стал тестировать дальше:
Из приложения
одним потоком раз в секунду пишу в кучи по 30000 записей
другим переношу из куч в таблицу
(две кучи использую чтобы запись и перемещение не пересекались на одной кучи, и поток записи не затыкался)

для перемещения вызываю хранимую процедуру которой передаю имя таблицы и имя кучи:
вызываю INSERT SELECT для переноса из кучи в таблицу
потом TRUNCATE TABLE для очистки кучи
...
SET @SQL = 'INSERT INTO ' + @TableName + ' ([SignalId],[Time],[Value],[Quality])
SELECT [SignalId],[Time],[Value],[Quality] FROM ' + @HeapName + ' ORDER BY [SignalId],[Time]';
EXEC (@SQL);
...
SET @SQL = 'TRUNCATE TABLE ' + @HeapName;
EXEC (@SQL);
...

При тестировании (когда индексированная таблица не пуста, а имеет порядка 100 000 000 записей) быстро наступает момент когда перенос из кучи в индексированную таблицу выполняется до десятков минут, и эта пробка приводит к неработоспособности системы.
В это время индексированная таблица заблокирована (блокировка таблицы (TAB)), выбирать данные из нее нельзя.

Перенос из куч в индексированную таблицу оказался узким местом, система в таком варианте неработоспособна.


Подскажите можно ли добиться высокой скорости перемещения данных из кучи в индексированную таблицу внутри SQL Server-а?

Может кто даст совет для решения задачи в рамках SQL Server Express?
11 сен 12, 14:51    [13146280]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
thrashead
Member

Откуда: Россия, Санкт-Петербург
Сообщений: 722
__asv__,
Интересная задачка. Как насчёт "дробления" одной большой итоговой таблицы на несколько таблиц поменьше? Критерий разделения - дата-время записи (поле "time").
11 сен 12, 15:02    [13146389]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
1. Секционирование на уровне представлений.
2. Фалы БД расположить на быстрые диски, разделить по дискам, и вообще поискать узкое место (анализ)
3. Зачем вам промежуточная запись в кучу вообще - лишнее телодвижение?
4. А почему MS SQL?
5. А зачем вам СУБД? Хранить можно и просто в файлах.
11 сен 12, 15:07    [13146452]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
aleks2
Guest
__asv__
Может кто даст совет для решения задачи в рамках SQL Server Express?

1. Ознакомьтесь с понятием "кластерный индекс" и обеспечьте вставку записей в хвост или гриву оного.

2. Встречный вопрос: что вы намереваетесь "анализировать" в 100 000 000 записей?
11 сен 12, 15:09    [13146481]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
SignalID - это ID датчика? Т.е. неуникальное и не монотонно возрастающее значение само по себе? Если "да", то для такого поля категорически противопоказан кластерный индекс. Ибо каждая вставка тогда - это переупорядочивание всех или многих данных в таблице. Еще бы оня вся не блокировалась!
Для начала поставьте поле Time первым полем в кластерном индексе, а SignalID вторым. А для SignalID назначьте отдельный некластерный неуникальный индекс.

Сообщение было отредактировано: 11 сен 12, 15:23
11 сен 12, 15:20    [13146601]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
__asv__
Перенос из куч в индексированную таблицу оказался узким местом, система в таком варианте неработоспособна.
Вы таким переносом расщепляете страницы индексированной таблицы. В пределе получается, что вы перезаписываете целиком всю индексированную таблицу, да ещё и обеспечиваете жуткую фрагментацию.
__asv__
Подскажите можно ли добиться высокой скорости перемещения данных из кучи в индексированную таблицу внутри SQL Server-а?
Только если не расщеплять, добавлять в конец. Может, можно сделать [Time], [SignalId] вместо [SignalId], [Time], если в куче добавляются новые данные с возрастающим [Time]?

Да, смешно у вас во вставке. ORDER BY [SignalId],[Time] - это зачем, чего вы хотите добиться?

Можно ускорить вставку, если делать на куче кластерный индекс по [SignalId],[Time], потом после вставки его удалять. Но это ускорит только сортировку, не саму вставку.
11 сен 12, 15:37    [13146779]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
каждая всегда тогда
Guest
Глеб
Ибо каждая вставка тогда - это переупорядочивание всех или многих данных в таблице.

это pagesplit. ибо.
11 сен 12, 15:37    [13146781]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
который datetime
Guest
__asv__,

time (который datetime) может разбить как раз на date и time?
тонкостей, естественно, не знаю, но скорее всего фильтрация идет по дате/периоду, а время не используется как фильтр, но используется как _данные_. график показаний за вчера или типа того.

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

кластерный по time (или date без time), некластерный по signalid.
11 сен 12, 15:53    [13146964]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
каждая всегда тогда
Глеб
Ибо каждая вставка тогда - это переупорядочивание всех или многих данных в таблице.

это pagesplit. ибо.

Умничаешь? Ну-ну.
11 сен 12, 15:58    [13147006]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
alexeyvg
Только если не расщеплять, добавлять в конец.
Лично мне кажется это недоработкой MS.
Только один механизм записи, только плотная. Ведь очевидно что надо резать блоками вдоль основной колонки (SignalId).
Одна такая стратегия записи и проблем нет, а фрагментация в принципе не решаема (кроме физического разделения сегментирования).

С другой стороны, а нужно ли хранение данных в базе? Нет, "сигналы" надо сразу обработать, непрерывным потоком, в конечный результат. Они итак уже в файлах есть. Более того на уровне источника сразу пред-обработка должна быть (убрать отсутствие изменений и т.п.).
Это уже много раз мусолилась на этом и других формах/блогах опытными разрабами. Иного пути нет.
11 сен 12, 15:59    [13147024]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
Mnior
Ведь очевидно что надо резать блоками вдоль основной колонки (SignalId).
А что имеется в виду?

У МС щас появился колоночный индекс, может вы про это? Хотя в данном случае не подойдёт...
Mnior
С другой стороны, а нужно ли хранение данных в базе? Нет, "сигналы" надо сразу обработать, непрерывным потоком, в конечный результат. Они итак уже в файлах есть. Более того на уровне источника сразу пред-обработка должна быть (убрать отсутствие изменений и т.п.).
В базе тоже имеет некоторый смысл хранить.

Но хранить то можно по датам, не понимаю, зачем хранить по сигналам???

Это классическая схема хранилищ - добавляем последовательно новые данные, время от времени инкрементно сканируем добавленное и обрабатываем, сохраняя уже результат как нужно.
11 сен 12, 17:32    [13147717]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
__asv__
Member

Откуда:
Сообщений: 6
Mnior,
1. Секционирование на уровне представлений.
2. Фалы БД расположить на быстрые диски, разделить по дискам, и вообще поискать узкое место (анализ)
3. Зачем вам промежуточная запись в кучу вообще - лишнее телодвижение?
4. А почему MS SQL?
5. А зачем вам СУБД? Хранить можно и просто в файлах.

1. В описании версии Express я видел что секционирование не поддерживается.
3. В сервер БД данные записываются быстрее чем в инд. таблицу и можно сказать детерминированно по времени.
4, 5 Думал эта СУБД справится с задачей,
по п. 5. думал удобно будет извлекать нужные данные с помощью SELECT, в отличае извлечения из файлов.
11 сен 12, 17:34    [13147730]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
__asv__
Member

Откуда:
Сообщений: 6
alexeyvg
__asv__
Перенос из куч в индексированную таблицу оказался узким местом, система в таком варианте неработоспособна.
Вы таким переносом расщепляете страницы индексированной таблицы. В пределе получается, что вы перезаписываете целиком всю индексированную таблицу, да ещё и обеспечиваете жуткую фрагментацию.
__asv__
Подскажите можно ли добиться высокой скорости перемещения данных из кучи в индексированную таблицу внутри SQL Server-а?
Только если не расщеплять, добавлять в конец. Может, можно сделать [Time], [SignalId] вместо [SignalId], [Time], если в куче добавляются новые данные с возрастающим [Time]?

Да, смешно у вас во вставке. ORDER BY [SignalId],[Time] - это зачем, чего вы хотите добиться?

Можно ускорить вставку, если делать на куче кластерный индекс по [SignalId],[Time], потом после вставки его удалять. Но это ускорит только сортировку, не саму вставку.



Может, можно сделать [Time], [SignalId] вместо [SignalId], [Time], если в куче добавляются новые данные с возрастающим [Time]?
Я об тоже думал, попробую протестить.

Да, смешно у вас во вставке. ORDER BY [SignalId],[Time] - это зачем, чего вы хотите добиться?
выборка с ORDER дольше, но думал вставка отсортированных записей (по кластерному ключу) пойдет быстрее
11 сен 12, 17:57    [13147857]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
__asv__
Member

Откуда:
Сообщений: 6
Большое спасибо всем за помощь.
11 сен 12, 18:47    [13148148]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
__asv__
выборка с ORDER дольше, но думал вставка отсортированных записей (по кластерному ключу) пойдет быстрее
Вставка будет одинаково, всё равно одна и та же сортировка в плане.

Быстрее будет, как я говорил, создать временно кластерный индекс в куче вместо сортировки.
11 сен 12, 19:13    [13148282]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
alexeyvg
Mnior
Ведь очевидно что надо резать блоками вдоль основной колонки (SignalId).
А что имеется в виду?
Что данные по колонке SignalId не смешиваются в рамках одной страницы индекса. И pagesplit на границах перехода должно редко происходить (так же как и на концах).

alexeyvg
В базе тоже имеет некоторый смысл хранить.
Зачем? Если оно не используется. Если приспичит можно из файлов поднять.

alexeyvg
Это классическая схема хранилищ - добавляем последовательно новые данные, время от времени инкрементно сканируем добавленное и обрабатываем, сохраняя уже результат как нужно.
Честно - не очень понимаю почему классическая.
По мне так ненужная нагрузка. И я в основном видел обработку заранее, даже на уровне источника данных (приборов). Вот это классическое.
11 сен 12, 21:39    [13148689]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
__asv__
Mnior
1. Секционирование на уровне представлений.
5. А зачем вам СУБД? Хранить можно и просто в файлах.
1. В описании версии Express я видел что секционирование не поддерживается.
5. думал удобно будет извлекать нужные данные с помощью SELECT, в отличае извлечения из файлов.
1. Не путайте понятия секционирование на уровне таблиц с секционированием на уровне представлений.
5. Это большая проблема то что вы не знаете для чего вам данные. Вы обязаны знать что с ними делать, иначе вы зря мучаете себя и нас.

Уверен, что и данном случае загрузка не нужна, а тупо потоково обработать данные, с-агрегировать.
11 сен 12, 21:39    [13148694]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mnior
alexeyvg
В базе тоже имеет некоторый смысл хранить.
Зачем? Если оно не используется. Если приспичит можно из файлов поднять.
Если посмотреть как организована стратегия доступа данных в крупнейших системах (банковские картели к примеру).
То там многоуровневая система - все данные сильно распределены (DB2), а обработка идёт на агрегированных данных (Oracle).
Иногда приходится делать не автоматизированные запросы к базовым данным (DB2), но это то что нельзя предсказать.

Для данной задачи только для агрегированных данных нужна СУБД (транзакционность, доступность), а для сырых нужна высокая скорость (аппликативная задача, для нативного кода).
11 сен 12, 21:55    [13148745]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
Mnior
alexeyvg
пропущено...
А что имеется в виду?
Что данные по колонке SignalId не смешиваются в рамках одной страницы индекса. И pagesplit на границах перехода должно редко происходить (так же как и на концах).
А, понял.

Ну тут будет зависеть от количества этих SignalId...

Учитывая, что он первый в кластерном индексе, то они будут смешиваться только на границах.

Границ будет 10000-30000, количество записей сейчас 100 000 000

То есть получается по 3-10 тыщ записей с одним SignalId.

Записи должны быть узкие, так что примерно, скажем, один или несколько экстентов на один SignalId

Если записей будет побольше (100 милионов это мизер для узкой таблицы), то нестрашно.

Даже можно проще посчитать - количество сплитов будет всегда равно количеству SignalId :-)
Mnior
alexeyvg
В базе тоже имеет некоторый смысл хранить.
Зачем? Если оно не используется. Если приспичит можно из файлов поднять.

alexeyvg
Это классическая схема хранилищ - добавляем последовательно новые данные, время от времени инкрементно сканируем добавленное и обрабатываем, сохраняя уже результат как нужно.
Честно - не очень понимаю почему классическая.
По мне так ненужная нагрузка. И я в основном видел обработку заранее, даже на уровне источника данных (приборов). Вот это классическое.
Да в общем по двум причинам
1. делается разнообразная обработка, для которой нужны эти данные, при этом алгоритмы могут появляться и меняться уже после получения данных
2. некоторая обработка требует всех данных, её нельзя вычислить зараннее. И родственное ограничение - часть данных поступает позже, и их тоже нужно учесть при вычислении.

Вот у нас сейчас именно такая ситуация, приходится хранить всё. Только у нас данных больше, не всего 100 милионов измерений, а за час милиард :-)

Понятно, что если достаточно хранить только какие то агрегаты, то можно и нужно вычислять всё в потоке, а исходники оставлять в файлах.
11 сен 12, 22:11    [13148795]     Ответить | Цитировать Сообщить модератору
 Re: Перемещение данных из кучи в индексированную таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
alexeyvg
Границ будет 10000-30000
Даже можно проще посчитать - количество сплитов будет всегда равно количеству SignalId
Да, многовато.
Но сплиты будут не всегда, а только если страница заполнится. И это не будут сплиты (с перетасовкой данных), а вставка новой страницы. Сплиты будут только для индексных страниц высшего уровня. Я как раз рассчитываю на особую стратегию заполнения данных (как на концах). Будет лишь сильное фрагментирование.

alexeyvg
некоторая обработка требует всех данных, её нельзя вычислить зараннее. И родственное ограничение - часть данных поступает позже, и их тоже нужно учесть при вычислении.
Вот у нас сейчас именно такая ситуация, приходится хранить всё. Только у нас данных больше, не всего 100 милионов измерений, а за час милиард
Как раз не хватает примеров. Чтобы можно было как-то дальше интерполировать.

Есть случаи когда источник физически "закреплён" - приборы, и все данные "локально близки", поэтому их стоит обработать сразу же.
А есть хаотичные данные (ну видимо что-то типа телефоннов/sms, не знаю), то есть нет "локальной связанности", и их как раз нужно как-то "сгруппировать". Переворошить весь этот хаос дорого, видимо поэтому легче создать линки на все "части", что вы и предлагаете в виде индексирования куч.

Но тут вся загвоздка - как это физически организованно, как устроены каналы и физические потоки данных. Гипотетически можно просто организовать хорошую коммутируемость начальных данных, на уровне "железа", а не софта (СУБД, индексирование).
ХЗ
12 сен 12, 00:47    [13149175]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить