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

Откуда:
Сообщений: 9
Что посоветуете, разнести таблицы по партициям (разнести по разным файловым шруппам) или оставить данные только за последний год? БД ~ 100 гигов. Число записей в самых больших таблицах ~10 - 20 миллионов, в наиболее используемых ~1-3 миллиона. Можно разбить, скажем, на 10 партиций или удалить ~6 лет (примерно, из-за ссылочной целостности). Опыт показывает, что удаление старых записей даёт больший эффект, но это потеря данных и непонятно, как часто пользователям они будут необходимы. Может потом вообще придётся сливать базы. При разбитии на партиции данные не теряются, т. е. головной боли, что кто-то что-то не нашёл нет, но вот эффект ... Хотя и сами таблицы по ним разносил и первичный ключ.

Microsoft SQL Server Enterprise Edition (64-bit) 2008, Microsoft Windows NT 6.1 (7601), XEON 32 логических CPU, 16 физических, дисковая подсистема RAID-10, n-ое количество дисков.
10 фев 12, 11:46    [12068043]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
https://www.sql.ru/articles/mssql/2005/073102PartitionedTablesAndIndexes.shtml
10 фев 12, 11:55    [12068129]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Demigold
Member

Откуда:
Сообщений: 9
tpg, статьи я читал, с теорией знаком. Интересуют практические отзывы разработчиков и администраторов.
10 фев 12, 12:28    [12068357]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
underalex
Member

Откуда: Киев
Сообщений: 50
Demigold,

Опишите проблему, характер нагрузки.
На описанном железе проблем с таким объемом данных быть не должно, если только там не выполняется пару сотен конкурентных запросов в секунду. К секционированию нужно переходить, если с индексами уже все отлично, ИМХО.
10 фев 12, 12:37    [12068428]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
2 underalex

я бы сказал если в 90% запросов фигурирует критерий секционирования :)
а индексы как раз бывает "плывут" из-за секционирования

2 Demigold

у вас каша на постановочном уровне. определитесь с целью работ сначала - чего вы хотите добиться в результате?
секционирование в любом случае улучшит администрирование и ухудшит прикладуху ибо накладные расходы
10 фев 12, 12:43    [12068495]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Demigold
Member

Откуда:
Сообщений: 9
underalex, проблема простая, работают одновременно 250 - 300 пользователей, при работе с основными расходными локументами задержки при обращении к БД от обычных 1-3 секунд до 25 (это как раз таблицы 1-3 млн. записей). При работе с аудитами (таблицы 10-20 млн.) приходится ждать обычно десятки секунд (естественно работают с этими таблицами гораздо реже). Если уменьшить эти таблицы в 10 раз, то работа с расходными документами будет практически мгновенной, работа с аудитами будет выполняться с маленькой 1-3 сек. задержкой. НО, хотелось бы добиться этого секционированием.
10 фев 12, 12:56    [12068600]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
iljy
Member

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

а всю возможную оптимизацию индексов и запросов вы уже провели?
10 фев 12, 13:31    [12068914]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Demigold
Member

Откуда:
Сообщений: 9
iljy, на наиболее используемых таблицах индексы оптимизированы. У этих таблиц есть ещё проблема - достаточно сложная логика на триггерах - у наиболее используемых документов достаточно сложные взаимосвязи - требование бизнеса. В принципе, были попытки переделать - итог несколько тысяч некорректных документов, которые пришлось разбирать вручную. Сейчас уже ни кто не хочет рисковать. Но итог я написал - при уменьшении таблиц ~ в 10 раз всё начинает летать и работать становится обычному сотруднику очень комфортно.
10 фев 12, 14:06    [12069288]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Demigold
Но итог я написал - при уменьшении таблиц ~ в 10 раз всё начинает летать и работать становится обычному сотруднику очень комфортно.


ну так секционирование не уменьшит объема данных для обработки
10 фев 12, 14:13    [12069349]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
underalex
Member

Откуда: Киев
Сообщений: 50
Demigold
задержки при обращении к БД от обычных 1-3 секунд до 25 (это как раз таблицы 1-3 млн. записей).

А приведите пример такого запроса, может станет понятно почему так долго.
Ну и желательно структуру таблиц и индексов, которые участвуют в запросе.
10 фев 12, 14:49    [12069661]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Demigold
Member

Откуда:
Сообщений: 9
Crimean, как я рассуждаю. Разбиваем таблицу по годам (в моём случае получаем 7 лет), в кластерный индекс включаем дату вместе с ID документа и создаём его по той же Partition scheme, все партиции в разных файловых группах. Получается, если в запросе идёт условие по полю типа datetime, включённому в кластерный индекс и если период в пределах одного года (99.9% случаев за исключением начала года), то сервер должен использовать только одну партицию, т. е. маленькую таблицу. Если всего таблице ~ 2 млн. записей, то сервер должен работать как буд-то в таблице всего 300 тыс. записей. Если запрос связывает несколько секционированных таким же образом таблиц, то при условии, что на всех них есть условие по дате, и в этом случае должны использоваться в выборке данные только нужных партиций. Да и при обращении/изменении документа или его детальной части, по ID сервер сразу должен определять нужную партицию (ведь ID включен в кластерный индекс). Т. е. прибавка к скорости должна быть и при том существенной. На деле все может работать даже медленней, а если и есть прибавка, то несколько процентов.
10 фев 12, 14:56    [12069739]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
iljy
Member

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

у вас не такое количество записей, чтоб секционирование помогло само по себе. Для начала выполните общую оптимизацию: найдите самые тяжелые запросы и посмотрите, почему они такие тяжелые. А пока вы будете гадать - ничего у вас не выйдет.
И еще. У вас много триггеров, вы уверены, что они написаны оптимально?
10 фев 12, 15:02    [12069829]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Demigold,

если при выборке оптимизатор не использует кластерный ключ то никакое секционирование работать не будет, это я к тому что надо планы запросо смотреть там будет видно где косяк.
а вобще странно что таи мелкие таблицы "~10 - 20 миллионов" вызывают такие дикие тормоза "до 25" что то у вас сильно не оптимизировано в запросах\индексах, вобщем совет снимательно присмотреться к планам.
10 фев 12, 15:03    [12069844]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Demigold
Member

Откуда:
Сообщений: 9
iljy, на счёт триггеров уверен, что не оптимально, но переписать почти не реально.
10 фев 12, 15:16    [12069987]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Demigold
я рассуждаю. Разбиваем таблицу по годам (в моём случае получаем 7 лет), в кластерный индекс включаем дату вместе с ID документа и создаём его по той же Partition scheme, все партиции в разных файловых группах. Получается, если в запросе идёт условие


вот вы сначала это и сделайте. без секций. секции НЕ УСКОРЯТ. секции облегчат администрирование
10 фев 12, 15:18    [12070005]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Demigold
Member

Откуда:
Сообщений: 9
underalex, каждый запрос выполняется быстро. При открытии документа, выполняется много таких запросов, по многим таблицам. Что бы привести пример придётся описывать фактически всю структуру БД, а это ~1000 таблиц.
10 фев 12, 15:18    [12070009]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
Demigold
Crimean, как я рассуждаю. Разбиваем таблицу по годам (в моём случае получаем 7 лет), в кластерный индекс включаем дату вместе с ID документа и создаём его по той же Partition scheme, все партиции в разных файловых группах. Получается, если в запросе идёт условие по полю типа datetime, включённому в кластерный индекс и если период в пределах одного года (99.9% случаев за исключением начала года), то сервер должен использовать только одну партицию, т. е. маленькую таблицу. Если всего таблице ~ 2 млн. записей, то сервер должен работать как буд-то в таблице всего 300 тыс. записей.
Да, всё правильно.

Demigold
Т. е. прибавка к скорости должна быть и при том существенной.
Тут не понял перехода.

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

При выборке по кластерному индексу (или по его диапазону) количество операций будет зависить исключительно от того количества записей (и соответственно страниц), которое попадёт в критерии выборки, независимо от того, сколько записей в таблице - всего 300 тыс. или целых 300 млрд. То есть количество чтений при выборке по кластерному индексу легко подсчитать, и оно не зависит от размера таблицы (точнее, зависимость логарифмическая).

Ну и при секционировании будут дополнительные расходы на выбор секции (микроскопические), а без секционирования может добавиться чтение одной странички из за увеличения количества уровней б-дерева инедкса. Но это всё несущественно.

А если кластерный индекс не в условиях выборки, то понятно, с секционированием скорость сильно уменьшится.
10 фев 12, 15:25    [12070082]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Demigold
Member

Откуда:
Сообщений: 9
underalex, да, на счёт 1000 таблиц я по горячился, но тем не менее, придётся вникать в структуру нескольких десятков.
10 фев 12, 15:26    [12070089]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
underalex
Member

Откуда: Киев
Сообщений: 50
Demigold,
Ну про 1000 я понял...

Наверное самым действенным способом в данном случае будет вооружиться профайлером и анализировать, какие из запросов наиболее влияют на эти 1...25 секунд и пробовать улучшить ситуацию. Гляди там 2 из 50 запросов занимают 90% времени и рессурсов..
10 фев 12, 16:53    [12071012]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Demigold
Member

Откуда:
Сообщений: 9
Всем спасибо, так и буду делать. Записал результат работы профайлера с умными настройками за 1.5 часа в таблицу, теперь по чтению, записи и цпу буду анализировать. Правда сам лог 30 млн. записей и запрос по нему, а приходится ставить условие по полю vrachar(2048) не быстр.
10 фев 12, 19:08    [12071926]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблиц на партиции удаление старых данных в таблицах.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Demigold
Всем спасибо, так и буду делать. Записал результат работы профайлера с умными настройками за 1.5 часа в таблицу, теперь по чтению, записи и цпу буду анализировать. Правда сам лог 30 млн. записей и запрос по нему, а приходится ставить условие по полю vrachar(2048) не быстр.

Можете еще посмотреть sys.dm_exec_query_stats, возможно это будет проще чем анализ лога в 30 миллионов строк.
13 фев 12, 04:14    [12079276]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить