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

Откуда:
Сообщений: 1130
SQL 2008 x64 Enterprise, SP4;

Есть нагруженная таблица ~1,5 ТБ, ~0,5 мил. строк, с блоб полями.

В неё идут в основном вставки (insert и bulkinset), круглосуточно ~10 - 100 строк в секунду от множества клиентов.
Обдумываю её партиционововать, в связи с чем вопросы:

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

Заранее спасибо отозвавшимся.
28 мар 18, 18:22    [21294249]     Ответить | Цитировать Сообщить модератору
 Re: партиционирование большой таблицы  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Alexander Us,

всё онлайн.
но это не значит что всё онлайн автоматом само будет работать, придется код переписывать иначе от секционирования толку не будет ваще.
28 мар 18, 18:50    [21294335]     Ответить | Цитировать Сообщить модератору
 Re: партиционирование большой таблицы  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Alexander Us,

и да забыл спросить, вам для каких целей секционирование понадоилось, при вставках оно как бы не очень помогает.
28 мар 18, 18:52    [21294337]     Ответить | Цитировать Сообщить модератору
 Re: партиционирование большой таблицы  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Alexander Us
SQL 2008 x64 Enterprise, SP4;

Есть нагруженная таблица ~1,5 ТБ, ~0,5 мил. строк, с блоб полями.

В неё идут в основном вставки (insert и bulkinset), круглосуточно ~10 - 100 строк в секунду от множества клиентов.
Обдумываю её партиционововать, в связи с чем вопросы:

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

Заранее спасибо отозвавшимся.

Партиционирование не для этого.
Оно для того, чтобы легко сложить архивные данные на HDD, а горячие на SSD. Партиционирование таблицы с кластерным ключом по дате и схеме по дате.

И для того, чтобы переключать секцию из staging таблицы.
Типа - есть таблица table_history, нарезанная на месяцы. Нужно удалить все строки с 3 февраля и загрузить из источника.
Делаете пустую table_staging, нарезанную так же. Очищаете truncate. Меняете секции swith только для февраля и марта 2018 со staging. В staging удаляете неспешно все для марта и большую часть февраля - при этом чтение с большой таблицы не блокируется. Далее из источника загружаете с 3 февраля новые строки в table_staging. А затем переключаете обратно секции февраля и марта - пустые возвращаются в table_staging, а заполненные попадают в table_history.

Плюс к этому можно распараллелить - в SSIS пакете в несколько потоков заливаете отдельно table_staging_1 для февраля, отдельно независимо table_staging_2 для марта (а схемы партиционирования все одинаковые), потом в момент готовности - февраль уже залился, март тупит - быстро переключаете нужную секцию в боевую большую table_history.

За рецептами идти в ветку OLAP и DWH, спрашивать Александра Южакова, он спец по таким загрузкам :)
28 мар 18, 19:26    [21294394]     Ответить | Цитировать Сообщить модератору
 Re: партиционирование большой таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Andy_OLAP
Alexander Us
Под "всё" понимается реализацмя скоьзящего окна - изменение функции, подключение новых и отключение старых секций.

Партиционирование не для этого.

да надо же.
человеку надо реализовать скользящее окно,
т.е. как раз безболезненно отцеплять старые данные,
и оказывается, секционирование не для этого.
т.е. наш дорогой эксперт рекомендует удалять вместо того, чтобы секции переключать?
29 мар 18, 09:25    [21295008]     Ответить | Цитировать Сообщить модератору
 Re: партиционирование большой таблицы  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
@WarAnt,

есть следующие проблемы, которые хотелось бы решить:

  • иногда вставки "зависают" на ~0,5 - 5 секунд, блокировки при этом не наблюдаются, время обновления статистик тоже не совпадает.
    В общем причину я не нашёл, но на таблице меньшего размера проблема не наблюдалась.

  • облегчение административных функций:
    ---очистка старых данных в некоторых столбцах
    ---выгрузка сарых данных в архив
    ---интересно было попробовать бы выгружать старые секции в отдельную файловую группу и бэкапить её отдельно - с целью уменьшить размер еженедельного бэкапа
    ---перестроение PK или некоторых индексов на такой большой таблице (на практике) нереально. Надеюсь что при партиционировании это станет возможным

    @Andy_OLAP,

    нет это не DWH, а обычная "боевая" база.
  • 29 мар 18, 09:32    [21295030]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Yasha123
    Member

    Откуда:
    Сообщений: 1833
    Alexander Us
    ---интересно было попробовать бы выгружать старые секции в отдельную файловую группу и бэкапить её отдельно - с целью уменьшить размер еженедельного бэкапа

    вот как раз это не будет "мгновенно".
    переключить на получится,
    вы будете физически переливать данные из одного файла в другой,
    это уже не metadate only operation
    BOL
    Source and target tables must share the same filegroup.
    The source and the target table of the ALTER TABLE...SWITCH statement must reside in the same filegroup,
    and their large-value columns must be stored in the same filegroup.
    Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup.
    However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.

    ---
    ну и к вопросу об "онлайновости".
    ALTER TABLE..SWITCH накладывает SCH-M на источник и на приемник.
    т.е. таки в этот момент они оба недоступны.
    другое дело, что длится сие удовольствие очень незначительное время
    29 мар 18, 09:41    [21295065]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Yasha123
    Member

    Откуда:
    Сообщений: 1833
    ну и само секционирование большой таблицы это большой хороший оффлайн.
    можно извратиться и делать онлайн, надеясь при этом, что за неделю секционируется,
    но лучше выделить окно и сделать оффлайново.
    29 мар 18, 09:46    [21295085]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1130
    Yasha123
    ну и само секционирование большой таблицы это большой хороший оффлайн...

    Тут можно подготовить вторую, партиционированну, таблицу с данными только последнего периода (на обе полные мож. не хватить диска, а временная недоступность старых данных терпима) и переключить их, затем потихоньку добавлять секции снизу и переливать данные, удаляя их их старой таблицы.
    29 мар 18, 09:57    [21295133]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Yasha123
    Member

    Откуда:
    Сообщений: 1833
    Alexander Us
    Тут можно подготовить вторую, партиционированну, таблицу с данными только последнего периода (на обе полные мож. не хватить диска, а временная недоступность старых данных терпима) и переключить их

    не очень понятно, что вы собрались переключать.
    можно перелить данные (их часть) в новую секционированную таблицу, это да.
    а "переключить" старую или даже часть ее вы ну никак не переключите
    29 мар 18, 10:07    [21295171]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    Alexander Us, сейчас в процессе добавления таймштампов в очень большие таблицы. Это не про секционирование, но суть примерно такая же - онлайновое изменение больших таблиц.

    Пользуемся похожим на этот методом:

    http://michaeljswart.com/2012/04/modifying-tables-online-part-1-migration-strategy/

    Только вместо триггеров используем CDC, но я, если бы с нуля делал, предпочел бы триггеры.
    29 мар 18, 10:41    [21295292]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1130
    Yasha123
    не очень понятно, что вы собрались переключать.

    Ваше определение "перелить" конечно правильно.

    Я же думал переливать старые данные не сразу в партиционированную таблицу, которая будет уже в продуктивном использовании,
    а блоками, в staging таблицу, когда она заполнится - подключать её в партиционированную таблицу "снизу", затем создавать новую staging таблицу, заполнять, подкючать и т.д.
    29 мар 18, 11:21    [21295460]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Alexander Us
    Member

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

    спасибо, буду читать.

    Но скажу сразу, что на данном приложении избегают триггеров.
    Причина - всё должно быть как можно проще, дешевле в обслуживании.
    Обслуживание, поиск ошибок - должны быть доступны для любого, кто имеет соотв. права а не только "избранных" специалистов.
    29 мар 18, 11:27    [21295483]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    WarAnt
    Member

    Откуда: Питер
    Сообщений: 2421
    Alexander Us
    @WarAnt,

    есть следующие проблемы, которые хотелось бы решить:

  • иногда вставки "зависают" на ~0,5 - 5 секунд, блокировки при этом не наблюдаются, время обновления статистик тоже не совпадает.
    В общем причину я не нашёл, но на таблице меньшего размера проблема не наблюдалась.


  • поищите сначала тут
    29 мар 18, 11:59    [21295616]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    Alexander Us
    Minamoto,

    спасибо, буду читать.

    Но скажу сразу, что на данном приложении избегают триггеров.
    Причина - всё должно быть как можно проще, дешевле в обслуживании.
    Обслуживание, поиск ошибок - должны быть доступны для любого, кто имеет соотв. права а не только "избранных" специалистов.

    Это же временно - только на время перелива. Объявляете "техническое обслуживание", настраиваете перелив, после переезда все убираете.
    Альтернатива - как я уже написал, CDC, или использование вьюх, объединяющих обе таблицы, но там тоже нужны будут триггера, на INSTEAD OF.
    29 мар 18, 14:06    [21296218]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1130
    Minamoto
    Объявляете "техническое обслуживание"

    Тут так не работает :)

    Если есть возможность обойтись без "Объявляете техническое обслуживание", то в этом и состоит моя работа.
    29 мар 18, 14:20    [21296283]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Критик
    Member

    Откуда: Москва / Калуга
    Сообщений: 33644
    Блог
    Alexander Us
  • иногда вставки "зависают" на ~0,5 - 5 секунд, блокировки при этом не наблюдаются, время обновления статистик тоже не совпадает


  • если сделаете секционирование, у вас будет как минимум пересортировка данных в tempdb, поэтому время зависания увеличится
    29 мар 18, 15:33    [21296703]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Ferdipux
    Member

    Откуда: Москва
    Сообщений: 553
    Alexander Us
    Yasha123
    ну и само секционирование большой таблицы это большой хороший оффлайн...

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

    Если у вас именно такой сценарий - нужно переехать в партицированную таблицу и мало место для обоих таблиц, то можно обойтись маленьким оффлайном.
    Создаал таблицу table_partitioned с теми же полями и партицированную, начинал оффлайн. Переливал в нее актуальную партицию, далее таблицы переименовывал. Конец оффлайна. Далее - переливаем секцию за секцией из старой таблицы в новую с переключением и заливкой через staging таблицу, чтобы в этот момент не блокировать работу с активной партицией.
    Вариант с триггерами из приведенной выше ссылки - не реализовывал, так как маленький оффлайн можно было получить.
    29 мар 18, 16:11    [21296916]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31355
    Yasha123
    Alexander Us
    Тут можно подготовить вторую, партиционированну, таблицу с данными только последнего периода (на обе полные мож. не хватить диска, а временная недоступность старых данных терпима) и переключить их

    не очень понятно, что вы собрались переключать.
    можно перелить данные (их часть) в новую секционированную таблицу, это да.
    а "переключить" старую или даже часть ее вы ну никак не переключите
    Ээээ, а почему нельзя?
    Навешиваете на старую таблицу правильный констрейн, как должно быть для функции секционирования, и потом подключаете её к секционированной таблице как большую-большую секцию.
    В процессе работы подключаете новые секции, нормального размера.
    И далее 2 варианта - если места нет, то в той большой секции ежедневно удаляете обычным DELETE, и шринкаете файл. А если место есть, то просто ждёте, когда данные станут старыми и ненужными, и сразу удаляете всю эту секцию.
    Сам так делал, вполне нормальный способ для секционирования больших таблиц, но, правда, требует выполнения некоторых условий - что бы старый кластерный индекс совпадал с новым, иначе смысл теряется.
    29 мар 18, 16:22    [21296953]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Yasha123
    Member

    Откуда:
    Сообщений: 1833
    alexeyvg
    Yasha123
    пропущено...

    не очень понятно, что вы собрались переключать.
    можно перелить данные (их часть) в новую секционированную таблицу, это да.
    а "переключить" старую или даже часть ее вы ну никак не переключите
    Ээээ, а почему нельзя?
    Навешиваете на старую таблицу правильный констрейн,

    и какой же констрэйнт ему навесить на всю его огромную таблицу,
    в которой данные с 2000-ого года?
    что там дата >= '2000101' и < '20180401'?
    и это все переключится в секционированную по годам, а того хуже, по yyyymm таблицу?

    вот бы так все и "секционировали" силой своей мечты
    29 мар 18, 17:36    [21297191]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Yasha123
    Member

    Откуда:
    Сообщений: 1833
    он хочет скользящее окно, если вы не прочли первый пост.
    волочить за собой ВСЕ данные ему не надо.
    ему надо часть данных переключить, а переключить не выйдет.
    именно что отливать кусочек в staging table, на нее уже констрэйнт, ее переключать
    29 мар 18, 17:39    [21297208]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31355
    Yasha123
    alexeyvg
    пропущено...
    Ээээ, а почему нельзя?
    Навешиваете на старую таблицу правильный констрейн,

    и какой же констрэйнт ему навесить на всю его огромную таблицу,
    в которой данные с 2000-ого года?
    что там дата >= '2000101' и < '20180401'?
    и это все переключится в секционированную по годам, а того хуже, по yyyymm таблицу?

    вот бы так все и "секционировали" силой своей мечты
    Да, я именно так и делал
    Таблицы 5 Тб, диск тоже, места нет, останавливать нельзя.
    Сделал констрейн дата >= '2000101' и < '20180401' (ну, цифры точные не буду приводить, не помню)
    Он, конечно, долго делался, там же нужно с проверкой данных.
    Потом сделал новую таблицу, у которой первая секция такая же, как этот констрейн, а остальные по суткам
    Потом сделал свитч партишен.
    Получилась таблица, с маленькими партициями по суткам по 150 гигов, и первой большой, на 5 тб
    И удаление суточных секций сделал, но оно, понятно, первые несколько месяцев не работало.
    Yasha123
    он хочет скользящее окно, если вы не прочли первый пост.
    волочить за собой ВСЕ данные ему не надо.
    ему надо часть данных переключить, а переключить не выйдет.
    именно что отливать кусочек в staging table, на нее уже констрэйнт, ее переключать
    А, если данные не все нужны, то понятно, не подходит, невнимательно прочитал.
    30 мар 18, 00:31    [21297901]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Yasha123
    Member

    Откуда:
    Сообщений: 1833
    ну так у вас окна-то и не было.
    а ему нужны 12 месяцев -- 12 секций (например. он не сказал, чему равна секция)
    а вашим способом "работа на будущее" -- через 12 месяцев окно появится.
    с тем же успехом можно просто с 0 таблицу сделать сразу секционированной
    и 12 месяцев ждать ее заполнение
    30 мар 18, 09:18    [21298152]     Ответить | Цитировать Сообщить модератору
     Re: партиционирование большой таблицы  [new]
    Alexander Us
    Member

    Откуда:
    Сообщений: 1130
    Yasha123
    а ему нужны 12 месяцев -- 12 секций

    Дорогие Yasha123, alexeyvg, вы оба правы.
    Я хоть и определил желаемое как скользящее окно, на самом деле мне хоть тушкой хоть чучелом - надо просто решить проблему.
    3 апр 18, 09:46    [21307063]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить