Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
Всем привет
Есть задача архивирования большого числа покупок/товаров за месяц в отдельное хранилище, чтобы оно не мешало быстрой вставке и оперативному доступу к текущему месяцу. В месяц приблизительно будет 100 тыс покупок и где-то 5-6 товаров к покупке. В конце месяца эти данные должны перекочевывать в архив. Автоматически если возможно.
Вроде все решается секционированием. Как я вижу:
1. Две файл группы
2. 1-я для месяца, вторая для архива
3. В 1-й файл группе лежат данные за текущий месяц
4. Во 2-й - все остальные
5. Создать функцию секционирования, схему
6. Расскидать и индексы и таблицы по этим секциям

Вопросы:
1. В таблицах есть дата покупки. Можно ли на нее завязать функцию или необходимо отдельно хранить месяц год?
2. Как автоматически в конце месяца данные перельются в архив? Джоб? Или сервер сам это делает?
Спасибо
15 окт 12, 12:55    [13319327]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
Сервер mssql2008
15 окт 12, 13:33    [13319693]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Shurik55
Вопросы:
1. В таблицах есть дата покупки. Можно ли на нее завязать функцию или необходимо отдельно хранить месяц год?
2. Как автоматически в конце месяца данные перельются в архив? Джоб? Или сервер сам это делает?
Спасибо


1. можно
2. срочно читать БОЛ на тему секционирование
15 окт 12, 13:38    [13319760]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3034
1. Ф-ция секционирования поддерживает один параметр (его тип), так что использование "дата покупки" самое оно
2. Сервер сам ничего не делает. Придется самому писать механизм переливки данных в архив (я использую джоб и ХП)
15 окт 12, 13:38    [13319768]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
Knyazev Alexey
1. можно
2. срочно читать БОЛ на тему секционирование

Спасибо! Читал, думал где-то недоглядел про встроенные механизмы.
15 окт 12, 13:59    [13319999]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
В общем попробовал, есть вопросы. Посмотрел форумы и я не один такой :)
1. Проблема с первичным ключом. Я не могу создать первичный ключ не включив туда колонку по которой идет секционирование. Приблизительная структура таблиц: Id - гуид, Created - дата/время, остальные колонки. По полю Created - будет идти секционирование. Когда я пытаюсь создать ПК по Id, то сервер требует включить туда Created, что сводит на нет, потом создание внешних ключей, так как в связанных таблицах обязана быть такая колонка (допустим это нормально и даже необходимо), но как быть с целостностью данных? Значение в главной таблице, должно совпадать со значением в подчиненных таблицах. Разместить первичный ключ в PRIMARY сведет на нет выигрыш от секционирования, так как данные не будут выровнены... Замкнутый круг.
Придется или отказаться от внешних ключей, целостности или реализовать проверку на триггерах... Что совсем не хочется... наверное отказаться от ПК и ФК, благо приложение отлажено.
2. Я хочу создать две секции. 1-я - текущий месяц (оперативная), 2-я - все остальное (архив). Вопрос: 2-я секция будет огромной, какие проблемы меня ждут? Я так понимаю затрудняется резервное копирование. размер диска. Какие еще? Возможен ли следующий сценарий: 30 секций - 1-я = текущий месяц (оперативная), потом 12 секций помесячно (текущий год), 14-я и далее - архив по предыдущим годам?
3. Для переброски данных из оперативной секции, я планирую запускать "холостой" апдейт, который присвоит Created его же значение и функция секционирования сработает и сервер физически перекинет данные в архив. Кстати тут же возникнет вопрос с внешними ключами. Насколько я понимаю сервер не перебросит связанные записи? Приемлем ли такой способ? Насколько он проблемный, если скажем предполагаемые размеры: 1 к 100, т.е в оперативной секции будет где-то сотая часть записей. Или решение со split/merge лучше? Насколько я понимаю, там все происходит на уровне метаданных? Или данные физически тоже перебрасываются?
Спасибо!
18 ноя 12, 14:37    [13490571]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
aleks2
Guest
Любит же народ "фантазировать" и изобретать себе трудности.

1. Если вы там собрались чего-то куда-то "перемещать" - секции вам помогут как собаке пятая нога.
2. Есть тока один механизм - отключение/подключение секций. Все остальное эквивалентно insert/delete несекционированных таблиц.
3. Поэтому читаете ишо раз, до просветления, BOL по секционированию.
4. Делаете стока месячных секций, скока месяцев хотите хранить в оперативном доступе.
5. И тренируетесь подключать-отключать секции.
18 ноя 12, 15:23    [13490646]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
aleks2
Любит же народ "фантазировать" и изобретать себе трудности.

1. Если вы там собрались чего-то куда-то "перемещать" - секции вам помогут как собаке пятая нога.
2. Есть тока один механизм - отключение/подключение секций. Все остальное эквивалентно insert/delete несекционированных таблиц.
3. Поэтому читаете ишо раз, до просветления, BOL по секционированию.
4. Делаете стока месячных секций, скока месяцев хотите хранить в оперативном доступе.
5. И тренируетесь подключать-отключать секции.

Спасибо за ответ :)
1. Почему? Можно поподробнее?
4. Не понимаю. У меня один месяц оперативный, в который будут идти массовые вставки, и частые селекты. К архиву будет только аналитика.
5. Я так понимаю проблему с ПК и ФК это не решит?
18 ноя 12, 15:27    [13490658]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
thugtronik
Member

Откуда:
Сообщений: 23
Shurik55,


Проблема если и есть то не с первичным ключем, а с кластерным индексом, первичный ключ ты можешь сделать с некластерным индексом.
Хотя я сомневаюсь, что проблема есть вообще.
А даты тебе зачем апдейтить?

Для того что бы перенести данные в архив у тебя должна быть левосторонняя функция, и в конце месяца ты будешь просто делать merge для первой даты прошлого(позапрошлого) месяца и split для первой даты нового месяца. И у тебя данные будут помещаться сами в архив.
18 ноя 12, 15:30    [13490667]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
В месяц приблизительно будет 100 тыс покупок и где-то 5-6 товаров к покупке. В конце месяца эти данные должны перекочевывать в архив.


Зачем?! Вы испытываете какие-то проблемы с такими мизерными объемами данных?
18 ноя 12, 15:32    [13490674]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
thugtronik
Проблема если и есть то не с первичным ключем, а с кластерным индексом, первичный ключ ты можешь сделать с некластерным индексом.
Хотя я сомневаюсь, что проблема есть вообще.

Ну это не проблема :) Это ограничение. Колонка, по которой идет секционирование обязанаа быть в уникальных/неуникальных, кластерных/некластерных индексах. Этим обеспечивается выравнивание данных, для того чтобы не шерстить все секции.

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

оперативный месяц и архив - одна таблица. Не две, а одна. Как это можно мержем и сплитом сделать? Можно пример?
18 ноя 12, 15:40    [13490698]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
pkarklin
Зачем?! Вы испытываете какие-то проблемы с такими мизерными объемами данных?

Архив предполагается огромным. Идея такая, чтобы запросы, а их большинство, используя фильтры, которые попадают в оперативный месяц не будут шерстить 99% ненужной информации.
18 ноя 12, 15:42    [13490703]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Shurik55
Архив предполагается огромным.


Насколько огромным?

Shurik55
Идея такая, чтобы запросы, а их большинство, используя фильтры, которые попадают в оперативный месяц не будут шерстить 99% ненужной информации.


Для этого существуют индексы.
18 ноя 12, 15:44    [13490712]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
aleks2
Guest
Shurik55
pkarklin
Зачем?! Вы испытываете какие-то проблемы с такими мизерными объемами данных?

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


Чувак, ты хоть прочитал про индексы ваще и про кластерный индекс в частности?
18 ноя 12, 15:49    [13490730]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
pkarklin
Насколько огромным?

Если в оперативном месяце связанных записей будет где-то 1-1,5 миллиона, то в архиве до миллиарда.


pkarklin
Для этого существуют индексы.

Предполагается не только частые выборки из оперативного месяца, но и частые вставки. Задача - собирать информацию из большого кол-ва магазинов в режиме онлайн и запускать правила по рассчету бонусов, скидок и т.д.
Кроме того как физически это хранить, если допустим можно оперативный месяц (файлгруппу) поместить в шустрое хранилище, а архив - в более медленное, но намного больше? Чем меня индексы спасут? насколько я правильно понимаю, то при секционировании индексы тоже размазаны по секциям, что в свою очередь поможет быстрой вставке и обновлению. Или я заблуждаюсь?
18 ноя 12, 15:51    [13490737]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
aleks2
Чувак, ты хоть прочитал про индексы ваще и про кластерный индекс в частности?

Прочитал. Я не претендую на гуру. Если вы подскажете как решить задачу используя кластерные индексы - буду премного благодарен.
18 ноя 12, 15:52    [13490740]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
Если в оперативном месяце связанных записей будет где-то 1-1,5 миллиона, то в архиве до миллиарда.


Вы всерьез полагаете, что Ваша самописка проработает 100 лет?

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


И... Какие проблемы со вставкой в оперативный месяц?!

автор
Задача - собирать информацию из большого кол-ва магазинов в режиме онлайн и запускать правила по рассчету бонусов, скидок и т.д.


Ну, задача, как задача. Никак не связанная с OLTP нагрузкой и ее надо решать не над транзакционной базой.

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


Ага. Горе от ума это называется. У Вас мизерные объемы. Если Ваши 1,5 миллиона в месяц пересчитать на кол-во инсертов в секунду, то с этим справится пара SATA винтов в зеркале. При правильной стратегии индексирования Ваши объемы без секционирования проживут лет 50-75.
18 ноя 12, 15:57    [13490753]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
thugtronik
Member

Откуда:
Сообщений: 23
Вы понимаете, что партицированная функция работает по одному полю? Зачем тогда выровняная таблица да и как собственно собираетесь партицировать и сопровождать ее если у вас внешний ключ как я понимаю это GUID?


Вы не про тот merge думаете.
Alter partition function .. Merge range/split range
18 ноя 12, 15:58    [13490754]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Shurik55
Если вы подскажете как решить задачу используя кластерные индексы - буду премного благодарен.


У Вас нет задачи. Пока у Вас сферический конь в вакууме. Какие-то абстрактные покупки с неизвестной атрибутикой...
18 ноя 12, 15:58    [13490756]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
thugtronik
Вы понимаете, что партицированная функция работает по одному полю? Зачем тогда выровняная таблица да и как собственно собираетесь партицировать и сопровождать ее если у вас внешний ключ как я понимаю это GUID?

Да, партиция по одному полю - дата/время. От внешних ключей откажусь, если придется :)

thugtronik
Вы не про тот merge думаете.
Alter partition function .. Merge range/split range

Не понимаю. Вот мой пример:
USE master
GO
IF EXISTS (
       SELECT NAME
       FROM   sys.databases
       WHERE  NAME = N'TestDB'
   )
    DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY(
              NAME = 'TestDB_Part1',
              FILENAME =
              'D:\DB\TestDB_Part1.mdf',
              SIZE = 3,
              FILEGROWTH = 1
          ),
FILEGROUP TestDB_Part2(
                          NAME = 'TestDB_Part2',
                          FILENAME =
                          'D:\DB\TestDB_Part2.ndf',
                          SIZE = 3,
                          FILEGROWTH = 1
                      );
GO
USE TestDB;
GO
CREATE PARTITION FUNCTION TestDB_PartitionRange(datetime2)
AS RANGE RIGHT FOR
VALUES(DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0));
GO
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);
GO
CREATE TABLE TestTable
(
	Id int not null,
	CreatedOn DATETIME2
)
ON TestDB_PartitionScheme(CreatedOn);
GO
CREATE CLUSTERED INDEX [ITestTable_CreatedOn] ON TestTable
(
	CreatedOn
) ON TestDB_PartitionScheme(CreatedOn)
GO 
INSERT INTO TestTable (Id, CreatedOn)
VALUES(1, '20120101')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(2, '20120201')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(3, '20120301')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(4, '20120401')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(5, '20120501')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(6, '20120601')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(7, '20120701')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(8, '20120801')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(9, '20120901')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(10, '20121001')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(11, '20121002')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(12, '20121020')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(13, '20121031')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(14, '20121021')
GO
INSERT INTO TestTable (Id, CreatedOn)
VALUES(15, '20121120')
GO

Можете подсказать как мне используя Alter partition function перебросить 11-й месяц в архив и начать складывать в оперативный месяц 12-й?
18 ноя 12, 16:05    [13490767]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Shurik55
Можете подсказать как мне используя Alter partition function перебросить 11-й месяц в архив и начать складывать в оперативный месяц 12-й?


Для решения Ваших "задач" не надо партицирования. Спроектируйте качественно модель данных и грамотно ее проиндексируйте. Счастье до скончания Ваших веков обеспечено.
18 ноя 12, 16:12    [13490775]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
pkarklin
У Вас нет задачи. Пока у Вас сферический конь в вакууме. Какие-то абстрактные покупки с неизвестной атрибутикой...

ок. опишу что смогу.
Есть БД, структура уже готова и приложение работает. Регистрирует из торговых точек информацию, рассчитывает бонусы, скидки. Пока только в тестировании. Теоретически массовые вставки, селекты и апдейты затрагивают только оперативный месяц. По крайней мере мы так закладываем. Например, есть 1000-10000 торговых точек, все они пишут свои покупки в БД, в ней запускается ХП, которые рассчитывают бонусы, скидки, все это должно происходить в реальном времени, а не вечером, когда нет нагрузки. Т.е сделав покупку, покупатель сразу может узнать сколько бонусов или какую скидку он может получить по свой корпоративной карточке. Все правила работают с данными за месяц, что было в прошлом нас не интересует. Ну и т.д. и т.п.
Начитавшись всего про секционирование решили попробовать. Логично предположить, что разместив оперативный месяц на каком-то быстром хранилище (а там будет не одна таблица, несколько, которые связаны между собой) то это ускорит нам работу, а архив разместить физически на другом, что не будет мешать оперативным расчетам и т.п. Аналитика по архиву если и запускается, то осознано и не должна мешать работе оперативным.
Можно конечно переписать приложение, поменять структуру данных, сделать одну таблицу для оперативных данных, выделить архив вообще в отдельную ОЛАП БД, но НЕ ХОЧЕТСЯ :(. Если это неизбежно и секционирование действительно будет как пятая нога, тогда да - засучиваем рукава и переделываем стурктуру БД и приложения.
18 ноя 12, 16:22    [13490795]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
Начитавшись всего про секционирование решили попробовать


Документация, это не художественная литература. Ее просто так не читают.

автор
Начитавшись всего про секционирование решили попробовать. Логично предположить ... это ускорит нам работу


Ах, таки есть какие -то проблемы с производительностью? Вы пытались выяснить их причины прежде, чем бросаться в пучину секционирования?
18 ноя 12, 16:26    [13490800]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
Shurik55
Member

Откуда:
Сообщений: 897
pkarklin
Ах, таки есть какие -то проблемы с производительностью? Вы пытались выяснить их причины прежде, чем бросаться в пучину секционирования?

Стыдно сказать, но та скорость которая вышла в замерах - 300 записей в секунду :( Это был тест на таблицах с 50 милл. записей, Все таблицы в PRIMARY, ПК и ФК - гуид, кластерных индексов нет. Тесты на виртуалке...
Сейчас полетят помидоры...
18 ноя 12, 16:36    [13490811]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование спасет? Задача про покупки и архивирование  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Shurik55
Стыдно сказать, но та скорость которая вышла в замерах - 300 записей в секунду


Без секционирования? Отличная скорость! Помидоры не полетят. На запись - неплохо. Вот что со чтением?
18 ноя 12, 16:39    [13490817]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить