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

Откуда:
Сообщений: 77
Есть база данных, в ней содержатся данные, приходящие с датчиков (датчиков больше 400, и опрашиваются они каждую минуту) и результаты анализа этих данных (анализ выполняется в фоновом режиме раз в несколько минут). Самые большие таблицы - это таблица с полученными значениями и таблицы журнала, в котором просто содержатся текстовые сообщения, записываемые по ходу анализа. За последние несколько месяцев набралось под 30 миллионов записей в первой таблице и во второй тоже больше миллиона. Сервер стал тормозить, служба, выполняющая сбор показаний и анализирующая их, не справляется, в логах постоянно появляются SqlTimeoutException'ы. Как-то раз я удалил старые данные (которым больше 1 месяца) и целиком транкейтнул таблицу журнала, просто сохранив бакап, но больше так делать не хочу, чтобы иметь доступ к старым данным. Доступ к старым данным нужен только в режиме "получить показания некоторых датчиков с такого-то по такое-то число".
Хочу рядом с основной базой создать вторую базу данных и добавить поток, в фоновом режиме занимающийся переносом устаревших данных из "оперативной" бд в архивную. Мучает вопрос - поможет ли это или все же необходимо думать о использовании другого хранилища в качестве архивного (какого-нибудь MongoDB) ? Не будет ли рядом лежащая база при редких запросах на чтение и постоянных инсертах (перенос данных) отжирать слишком много ресурсов SQL сервера (памяти, например) ? Ему же нужно еще запросы к основной бд отрабатывать.
21 дек 11, 13:24    [11802556]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
libru
Member

Откуда:
Сообщений: 877
партиции?
21 дек 11, 13:31    [11802636]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
elwood,

Озвучте версию сервера.
А так на вскидку.
1. Секционирование таблицы.
2. Текущий день, таблица куча. + Таблица данных с индексами. В определенный момент происходит переключение данных из одной в другую. Можно через временную, для создания индексов.
21 дек 11, 13:38    [11802709]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
Glory
Member

Откуда:
Сообщений: 104751
elwood
Хочу рядом с основной базой создать вторую базу данных и добавить поток, в фоновом режиме занимающийся переносом устаревших данных из "оперативной" бд в архивную.

Не пробовали начать с анализа запросов, которые выбирают данные ?
21 дек 11, 13:40    [11802739]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
elwood
Member

Откуда:
Сообщений: 77
Glory, в основном там запросы вида SELECT TOP(10) where ObjectId = :id order by DateTimeCreated desc
остальные запросы на порядок реже выполняются

Пока буду читать про партицирование, видимо оно как раз для моего случая
21 дек 11, 13:53    [11802906]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
Glory
Member

Откуда:
Сообщений: 104751
elwood
Glory, в основном там запросы вида SELECT TOP(10) where ObjectId = :id order by DateTimeCreated desc

И вы проанализировали план этого запроса ?
У вас есть оптимальный индекс для него ?


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

И по какому критерию вы будете строить партиции для запроса where ObjectId = :id ? По ObjectId ?
21 дек 11, 13:59    [11802962]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
1d0
Member

Откуда: инфа100%
Сообщений: 2521
можно сделать так:

создать в той же бд ещё одну таблицу, и в момент наименьшей нагрузки переносить туда устаревшие данные.
21 дек 11, 13:59    [11802972]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
elwood
Member

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

MS SQL 2005 Enterprise
21 дек 11, 14:00    [11802975]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
elwood
Member

Откуда:
Сообщений: 77
Glory
elwood
Glory, в основном там запросы вида SELECT TOP(10) where ObjectId = :id order by DateTimeCreated desc

И вы проанализировали план этого запроса ?
У вас есть оптимальный индекс для него ?
elwood
Пока буду читать про партицирование, видимо оно как раз для моего случая

И по какому критерию вы будете строить партиции для запроса where ObjectId = :id ? По ObjectId ?

Индекс по DateTimeCreated есть, вроде бы ничего больше и не нужно..
Критерий партиции пока не знаю, наверное по DateTimeCreated как-то
21 дек 11, 14:03    [11803002]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
Glory
Member

Откуда:
Сообщений: 104751
elwood
Индекс по DateTimeCreated есть, вроде бы ничего больше и не нужно..

Вам это кто-то сказал или вы сами решили ?


elwood
Критерий партиции пока не знаю, наверное по DateTimeCreated как-то

Ну так и как ваш запрос SELECT TOP(10) where ObjectId = :id order by DateTimeCreated desc будет использовать эти партиции ? Опять сканировать все ?
21 дек 11, 14:05    [11803032]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
elwood
Glory, в основном там запросы вида SELECT TOP(10) where ObjectId = :id order by DateTimeCreated desc
...
Индекс по DateTimeCreated есть, вроде бы ничего больше и не нужно..

Как минимум кластерный индекс по DateTimeCreated + индекс по ObjectId. (я бы так сделал). И можно обойтись без секционирования. Единственным плюсом секционирования на мой взгляд будет обслуживание индексов. А если грамотно распределить периоды еще и количество обрабатываемых строк в запросе.
з.ы. Если таблица оперативно используется, а в ващем случае так и есть
автор
(датчиков больше 400, и опрашиваются они каждую минуту)

Я бы других индексов не делал т.к. они замедляют вставку.
21 дек 11, 14:31    [11803312]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
elwood
Glory, в основном там запросы вида SELECT TOP(10) where ObjectId = :id order by DateTimeCreated desc
остальные запросы на порядок реже выполняются

Пардон, не обратил внимание, сразу, что в условии запроса не используется временной интервал, а лишь сортировка. При таком запросе Секционирование не выгодно, т.к. будет сканировать все секции, а это большие расходы на систему ввода вывода.
На моей практики идентичные запросы к талице в файловой группе и к таблице в схеме секционирования, по планам были примерно 10/90. т.к. при файловой группе затраны на IO = 0.0012, а при секционировании = 0.0125. И единственным положительным поводом расположения таблицы в секции служило именно обслуживание индексов. т.к. предыдущие партиции были только для чтения.
21 дек 11, 14:49    [11803507]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
elwood
Member

Откуда:
Сообщений: 77
Glory
elwood
Индекс по DateTimeCreated есть, вроде бы ничего больше и не нужно..

Вам это кто-то сказал или вы сами решили ?

Я конечно не специалист в базах данных, но для таблицы вида ObjectId DateTimeCreated Value разве нужны какие-то дополнительные индексы ? Оптимизатор видит наличие индекса по одному полю, использует его, а для уточнения ObjectId выполняет сканирование. Впрочем я попробую посмотреть на план запроса для того, чтобы быть уверенным, вы правы.
Glory
elwood
Критерий партиции пока не знаю, наверное по DateTimeCreated как-то

Ну так и как ваш запрос SELECT TOP(10) where ObjectId = :id order by DateTimeCreated desc будет использовать эти партиции ? Опять сканировать все ?

Не знаю, я пока еще не решил буду ли использовать секционирование, сначала прочту что это такое и как работает.
21 дек 11, 15:14    [11803723]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
Glory
Member

Откуда:
Сообщений: 104751
elwood
Я конечно не специалист в базах данных, но для таблицы вида ObjectId DateTimeCreated Value разве нужны какие-то дополнительные индексы ? Оптимизатор видит наличие индекса по одному полю, использует его, а для уточнения ObjectId выполняет сканирование.

Вот вы это все в плане выполнения видите ? Или просто так рассуждаете ?
Или для вас, что сканирование индекса, что поиск по индексу - все едино ?
21 дек 11, 15:16    [11803733]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
elwood
Member

Откуда:
Сообщений: 77
Glory
elwood
Я конечно не специалист в базах данных, но для таблицы вида ObjectId DateTimeCreated Value разве нужны какие-то дополнительные индексы ? Оптимизатор видит наличие индекса по одному полю, использует его, а для уточнения ObjectId выполняет сканирование.

Вот вы это все в плане выполнения видите ? Или просто так рассуждаете ?
Или для вас, что сканирование индекса, что поиск по индексу - все едино ?

Просто так рассуждаю. Я же говорю - я не разбираюсь в планах, поэтому рассуждаю чисто логически, по-рабочекрестьянски "есть индекс по дате - запрос на top N order by date desc скорее всего его и использует, раз других индексов нет и у оптимизатора не должно возникать вопроса, какие из индексов использовать и в каком порядке". Я понимаю, что для вас такое отношение это ламерство, но к сожалению за пару часов навряд ли сумею разобраться в деталях работы оптимизатора и использования индексов, чтобы говорить с вами на равных. Поэтому не пинайте ногами, а лучше разъясните на пальцах для дураков-девелоперов :)

Пока я все же склоняюсь к тому, чтобы сделать отдельную табличку рядом или базу данных, и туда в фоне переносить данные, которые стали устаревшими. Немножко дописать код придется, чтобы он знал, что есть еще и архивная бд, но зато это должно четко разделить 2 таблицы, и запросы к основной табличке будут выполняться быстро. Либо придется шаманить с настройкой секций, файловых групп и тд, что может и не дать результата ? Я правильно понимаю ?
21 дек 11, 15:33    [11803903]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
elwood
Просто так рассуждаю. Я же говорю - я не разбираюсь в планах, поэтому рассуждаю чисто логически, по-рабочекрестьянски "есть индекс по дате - запрос на top N order by date desc скорее всего его и использует, раз других индексов нет и у оптимизатора не должно возникать вопроса, какие из индексов использовать и в каком порядке". Я понимаю, что для вас такое отношение это ламерство, но к сожалению за пару часов навряд ли сумею разобраться в деталях работы оптимизатора и использования индексов, чтобы говорить с вами на равных. Поэтому не пинайте ногами, а лучше разъясните на пальцах для дураков-девелоперов :)

Пока я все же склоняюсь к тому, чтобы сделать отдельную табличку рядом или базу данных, и туда в фоне переносить данные, которые стали устаревшими. Немножко дописать код придется, чтобы он знал, что есть еще и архивная бд, но зато это должно четко разделить 2 таблицы, и запросы к основной табличке будут выполняться быстро. Либо придется шаманить с настройкой секций, файловых групп и тд, что может и не дать результата ? Я правильно понимаю ?

Вы ошибаетесь оптимизатор выбирает оптимальный план выполнения и если по-рабочекрестьянски то он будет использовать тот индекс где будут присутствовать те же поля (либо часть полей) что и в условии запроса (where) и если у вас по на таблице один индекс по дате, то скорее всего предпочтет полное сканирование по таблице или кластерному индексу, опять же если такокой имеется.
21 дек 11, 15:45    [11804014]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
elwood
для таблицы вида ObjectId DateTimeCreated Value разве нужны какие-то дополнительные индексы ?

Создайте в стороне табличку идентичную вашей, создайте кластерный индекс по DateTimeCreated. И индекс по ObjectId можно с включением столбца Value. И тогда запросы вида
select ObjectId DateTimeCreated Value from tabla where ObjectId = :id

будут просто летать. И никакое секционирование вам не надо.
21 дек 11, 15:52    [11804079]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
elwood
Member

Откуда:
Сообщений: 77
gds
elwood
для таблицы вида ObjectId DateTimeCreated Value разве нужны какие-то дополнительные индексы ?

Создайте в стороне табличку идентичную вашей, создайте кластерный индекс по DateTimeCreated. И индекс по ObjectId можно с включением столбца Value. И тогда запросы вида
select ObjectId DateTimeCreated Value from tabla where ObjectId = :id

будут просто летать. И никакое секционирование вам не надо.
21 дек 11, 16:15    [11804292]     Ответить | Цитировать Сообщить модератору
 Re: Перенос старых данных в архив  [new]
elwood
Member

Откуда:
Сообщений: 77
Упс, нажалось случайно. Сейчас попробую, спасибо
21 дек 11, 16:16    [11804304]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить