В современных условиях очень странно бывает иногда слышать "нам нужно свернуть БД 1С - её объём превышает 50 ГБ". Если бы такое собирались сделать администраторы систем SAP R3 или Oracle e Business Suite или даже MS Dynamics Ax их бы наверное уволили. Тем не менее, для 1С это является "стандартной практикой".
Для файловых версий история тянется ещё с версии 1С 7.7 с ограничением в 2ГБ на размер базы. Сейчас ограничение 2ГБ уже только на размер таблицы, размер файла уже может получиться очень и очень не маленьким. Правда если база у вас выросла до такого размера, то наверное туда активно вносились данные - может нужно задуматься о клиент-сервере?
Собственно целью данной статьи является "отговорить" от выполнения свертки БД пользователей клиент-серверного варианта 1С, за счет использования несколько более "продвинутых" технологий.
Итоговая цифра получается 30-40 т. минимум против 20-25 в случае покупки жесткого диска, и получения 500 ГБ дополнительного места
Поэтому появляются продукты вроде
Хорошие наверное продукты, и цели свои выполняют. Вот только меняется структура таблиц от версии к версии платформы. 1С нам об этом не раз говорили. Появился разделитель данных в 14-ом релизе и всё... скорее всего эта обработка для 14 релиза уже не подойдёт. Да и страшно как-то, не говоря уже о нарушении лицензионного соглашения.
И даже после этого найдутся пользователи которым "вдруг неожиданно понадобились" стертые данные, которые "как раз хотели поправить" каку-то циферку, которая "не влияет на последовательнсти" в документе закрытого периода. А хуже если выяснится что кто-то эти документы смотрел постоянно для каких-то только ему ведомых целей. Конечно это всё лишь ошибки в методике работы, но тем не менее недовольство пользователей будет.
-
Открываем Management Studio в списке баз выбираем нужную, открываем её свойства.
- Переходим на вкладку "Файловые группы" как показано на рисунке, и добавляем ещё одну файловую группу (на примере она названа SECONDARY)
- Переходим на вкладку "Файлы" и добавляем новый файл, для которого выбираем созданную файловую группу. Этот файл МОЖНО РАСПОЛОЖИТЬ НА ДРУГОМ ДИСКЕ
-
Теперь используя обработку к примеру: определяем какие таблицы мы можем смело "пожертвовать" на более медленный (ну или наоборот всё на медленный, остальные - на более быстрый) носитель. Правило 80/20 здесь действует. 80% операций проводятся с 20% данными, так что думайте какие таблички вам нужны оперативно, а какие не очень. "Хранилище дополнительной информации", документы ввода начальных остатков, документы которые уже не используете сразу определяйте как те которые можно перенести в "медленную" файловую группу.
Выбираем таблицу которую нужно перенести в другую файловую группу - выбираем меню изменения таблицы (проект) и в свойствах меняем файловую группу:
индексы таблицы при этом тоже переносятся в данную файловую группу.
Достаточно удобный механизм распределения таблиц по дисковым массивом разной скорости. Лицензионному соглашению это не противоречит, т.к. в решении мы не используем доступ к данным и к информационной базе средствами отличными от платформы 1С. Мы лишь организуем хранение этих данных удобным образом.
DBCC TRACEON (1807)
Пишем данную команду в Management Studio, выполняем и можем успешно создавать базы по сети. Само собой при этом экземпляр SQL Server-а должен быть запущен от имени доменной учетной записи, и у этой записи должны быть права на нужную сетевую папку.
Но прошу быть очень внимательными при использовании данной команды в случае если у вас пропадёт сеть при работе с БД вся БД на время её отсутствия будет не доступной. Microsoft не зря закрыли эту возможность для массового использования. Вообще эта возможность предполагается для создания баз на NAS хранилищах, что и настоятельно рекомендую. Подойдёт так же стабильный и надежный файловый сервер, имеющий прямое подключение к серверу на котором запущен MS SQL СУБД.
Подробнее про другие флаги трассировки можно прочитать в статье http://msdn.microsoft.com/ru-ru/library/ms188396.aspx
Т.е. часть файловую группу можно вообще хранить в сети, а уж там дисковое пространство расширяется без проблем.
Разделить таблицы на разные файловые группы конечно хорошо... но вы скажете что есть тут парочка таблиц... которые тянутся с 2005 года... и уже занимают с десяток гигабайт.. вот бы в них все данные да отдельный диск положить, а текущие оставить.
Вы не поверите, но и это тоже возможно, хотя и не очень просто:
Создаём функуцию секционирования по дате:
create partition function YearSection(datetime)
as range right for values ("20110101");
Всё что до 2011 года будет попадать в одну секцию, всё что после - в другую.
Создаём схему секционирования
create partition scheme YearScheme
as partition YearSection to (SECONDARY, PRIMARY);
Этим говорим, что все данные до 11 года будут попадать в файловую группу "Secondary" а после - в "Primary"
Теперь осталось таблицу перестроить с разделением на секции. Для этого проще всего воспользоваться уже management studio, потому как процесс не простой. Вам нужно перестроить кластерный индекс для таблицы (который по сути и является самой таблицей), выбрав для индекса созданную схему секционирования:
На рисунке вы видите что выбор не доступен - всё правильно, секционирование таблиц возможно только в версии Enterprise MS SQL Server . Кластерный индекс отличить легко - картинка с круглыми скобками. Для РН и всех объектов 1С он создаётся. Для РН кластерный индекс по периоду есть всегда. Для документов и справочников хорошо бы конечно создать другой, который включает реквизит по которому будет секционирование... но это уже будет являться нарушением лицензионного соглашения.
Но для этого нужно не сворачивать базу, а проделать следующее:
а) Объяснить всем как пользоваться отборами, как они сохраняются, как пользоваться интервалами журнала, как они сохраняются
б) Пометить на удаление ненужные данные если они не несут никакой смысловой нагрузки (контрагентов и номенклатуру, с которыми больш не работаете) - этим вы принесёте пользователям больше пользы чем сверткой. В случае наличия ресурсов настроить автоматическую пометку на удаление неиспользуемых объектов и сделать отбор по умолчанию в программном коде для того чтобы не отображались по умолчанию не нужные пользователям объекты - помеченные на удаление
в) Настроить другие полезные "отборы по умолчанию" - например чтобы каждый менеджер по умолчанию видел только свои документы. А если хочет посмотреть документы "товарища" - нужно отключать отбор.
По всем реквизитам, которые участвуют в отборе не забывайте ставить признак "Индексировать с доп. упорядочиванием" - тогда на производительности системы такие "удобства" не скажутся.
В этой статье я собираюсь продемонстрировать особенности планов исполнения запросов при обращении к секционированным таблицам. Обратите внимание, что существует большая разница между секционированными таблицами (которые стали доступны только с SQL Server 2005) и секционированными представлениями (которые были доступных ещё в SQL Server 2000, и по-прежнему доступны в SQL Server 2005 и последующих версиях). Особенности планов запросов к секционированным представлениям я продемонстрирую в другой статье.
Просмотр таблицы
Давайте создадим простую секционированную таблицу:
create partition function pf(int) as range for values (0, 10, 100)
create partition scheme ps as partition pf all to ()
create table t (a int, b int) on ps(a)
Этот сценарий создает таблицу с четырьмя секциями. SQL Server присвоил значения идентификаторам каждой из четырех секций так, как это показано в таблице:
| PtnId | Values |
| 1 | t.a <= 0 |
| 2 | 0 < t.a <= 10 |
| 3 | 10 < t.a <= 100 |
| 4 | 100 < t.a |
Теперь давайте рассмотрим план такого запроса, который бы вынудил оптимизатор использовать просмотр всей таблицы (Table Scan):
……|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]))
В представленном выше плане, SQL Server явно указывает все идентификаторы секции в операторе «Constant Scan», который реализует просмотр таблицы и поставляет данные оператору соединения вложенных циклов. Тут следует напомнить, что оператор соединения вложенных циклов выполняет проход по внутренней таблице (в данном случае это полный просмотр таблицы) один раз для каждого значения из внешней таблицы (в нашем случае это «Constant Scan»). Таким образом, мы выполняем просмотр таблицы четыре раза; один раз для каждого идентификатора секции.
Следует также отметить, что соединение вложенных циклов показывает явно что внешняя таблица является значениями столбца , где хранятся ID секций. Хотя это не сразу видно в текстовом представлении плана исполнения (к сожалению, мы иногда не замечаем эту информацию), просмотр таблицы использует столбец с идентификаторами секций, которые выбираются для выполнения просмотра и определяют какую секцию сканировать. Эта информация всегда доступна в графическом плане исполнения (нужно заглянуть в свойства оператора просмотра таблицы), а также в XML представление плана исполнения запроса:
Статическая фильтрация секций
Рассмотрим следующий запрос:
select * from t where a < 100
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
…….|–Constant Scan(VALUES:(((1)),((2)),((3))))
<(100)) PARTITION ID:())
Предикат «а <100» явно исключает все строки для секции со значением идентификатора равным 4. В данном случае, нет смысла в просмотре соответствующей секции, поскольку ни одна из строк этой секции не удовлетворяет условию предиката. Оптимизатор учитывает этот факт и исключает эту секцию из плана исполнения запроса. В операторе «Constant Scan» указаны только три секции. У нас принято называть это статической фильтрацией секций (static partition elimination), поскольку мы знаем, что во время компиляции список просматриваемых секций остаётся статичным.
Если в результате статичной фильтрации будут исключены все разделы, кроме одного, нам вообще не понадобятся операторы «Constant Scan» и «Nested Loops Join»:
select * from t where a < 0
|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<(0)) PARTITION ID:((1)))
Обратите внимание, что указание «PARTITION ID:((1))», которое задаёт идентификатор подлежащей просмотру секции, теперь является частью оператора просмотра таблицы (Table Scan).
Динамическая фильтрация секций
В некоторых случаях SQL Server не может определить, что на момент компиляции состав просматриваемых секций не изменится, но ему видно, что некоторые секции можно исключить.
select * from t where a < @i
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
…….|–Filter(WHERE:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
…….| |–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<[@i]) PARTITION ID:())
Это параметризованный запрос. Так как до исполнения значение параметра мы не знаем (то, что я использую константу в качестве параметра в том же батче, не меняет положение вещей), то на этапе компиляции невозможно определить значение идентификатора секции для оператора «Constant Scan». Возможно придётся просматривать только секцию 1, или это будут секции 1 и 2, и так далее. Поэтому, в этом операторе указаны все четыре идентификатора секций, и мы используем фильтрацию идентификаторов секций на этапе исполнения. Мы называем это «Динамическая фильтрация секций» (Dynamic Partition Elimination).
Фильтр сравнивает каждый идентификатор секции c результатом работы специальной функции «RangePartitionNew». Эта функция вычисляет результаты применения функции секционирования к значению параметра. Аргументами этой функции (слева направо) являются:
- значение (в данном случае параметр @i), который мы хотим отобразить на ID секции;
- булевой флаг, указывающий, отображает ли функция секционирования граничные значения слева (0) или справа (1);
- граничные значения секций (в данном случае это 0, 10, и 100).
В этом примере, поскольку @i имеет значение 0, результатом «RangePartitionNew» является 1. Таким образом, мы просматриваем только секцию с идентификатором 1. Заметим, что в отличие от примера со статической фильтрацией секций, хотя мы сканируем только один раздел, мы по-прежнему имеем «Constant Scan» и «Nested Loops Join». Нам потому нужны эти операторы, что до этапа исполнения мы не знаем секции, которые будут просмотрены.
В некоторых случаях оптимизатор уже на этапе компиляции может определить, что мы будем сканировать только одну секцию, даже если он не может определить, какую именно. Например, если в запросе используется предикат эквивалентности по ключу секционирования, тогда мы знаем, что только одна секция может удовлетворять такому условию. Поэтому, несмотря на то, что у нас должна была быть динамическая фильтрация секций, у нас отпадает необходимость в операторах «Constant Scan» и «Nested Loops Join». Пример:
select * from t where a = @i
|–Table Scan(OBJECT:([t]), WHERE:([t].[a]=[@i]) PARTITION ID:(RangePartitionNew([@i],(0),(0),(10),(100))))
Сочетание статической и динамической фильтрации секций
SQL Server может совмещать статическую и динамическую фильтрацию секций в одном плане запроса:
select * from t where a > 0 and a < @i
|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
……|–Filter(WHERE:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
……| |–Constant Scan(VALUES:(((2)),((3)),((4))))
……|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<[@i] AND [t].[a]>(0)) PARTITION ID:())
Обратите внимание, что в последнем плане присутствует статическая фильтрация секции ID = 1 с использованием «Constant Scan», и также присутствует динамическая фильтрация для других секций, определяемых предикатами.
$partition
Можно явно вызвать функцию RangePartitionNew, используя $partition:
select *, $partition.pf(a) from t
|–Compute Scalar(DEFINE:(=RangePartitionNew([t].[a],(0),(0),(10),(100))))
……|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
………..|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
………..|–Table Scan(OBJECT:([t]))
Отличительной особенностью такого плана исполнения запроса является появление оператора Compute Scalar.
Дополнительная информация
В ходе работ над большими таблицами мы постоянно сталкиваемся с проблемами с производительностью их обслуживания и обновления данных. Одним из наиболее продуктивных и удобных решений возникающих проблем является секционирование.
Если в общих словах, то секционирование – это разбиение таблицы или индекса на блоки. В зависимости от настройки секционирования блоки могут быть различных размеров, могут храниться в разных файловых группах и файлах.
У секционирования есть как преимущества, так и недостатки.
Преимущества хорошо расписаны на сайте компании Microsoft, приведу выдержку:
«Секционирование больших таблиц или индексов может дать следующие преимущества в управляемости и производительности.
- Это позволяет быстро и эффективно переносить подмножества данных и обращаться к ним, сохраняя при этом целостность набора данных. Например, такая операция, как загрузка данных из OLTP в систему OLAP, выполняется за секунды, а не за минуты и часы, как в случае несекционированных данных.
- Операции обслуживания можно выполнять быстрее с одной или несколькими секциями. Операции более эффективны, так как выполняются только с поднаборами данных, а не со всей таблицей. Например, можно сжать данные в одну или несколько секций или перестроить одну или несколько секций индекса.
- Можно повысить скорость выполнения запросов в зависимости от запросов, которые часто выполняются в вашей конфигурации оборудования. Например, оптимизатор запросов может быстрее выполнять запросы на эквисоединение двух и более секционированных таблиц, если в этих таблицах одни и те же столбцы секционирования, потому что можно соединить сами секции.
В процессе сортировки данных для операций ввода-вывода в SQL Server сначала проводится сортировка данных по секциям. SQL Server может одновременно обращаться только к одному диску, что может снизить производительность. Для ускорения сортировки данных рекомендуется распределить файлы данных в секциях по нескольким жестким дискам, создав RAID. Таким образом, несмотря на сортировку данных по секциям, SQL Server сможет одновременно осуществлять доступ ко всем жестким дискам каждой секции.
Кроме того, можно повысить производительность, применяя блокировки на уровне секций, а не всей таблицы. Это может уменьшить количество конфликтов блокировок для таблицы
».
К недостаткам же можно отнести сложность в администрировании и поддержке работы секционированных таблиц.
Мы не будем останавливаться на вопросах реализации секционирования, так как данный вопрос очень хорошо описан на сайте компании Microsoft .
Вместо этого мы постараемся показать способ оптимизации работы секционированных таблиц, а точнее покажем оптимальный (на наш взгляд) способ обновления данных за любой промежуток времени.
Большим плюсом секционированной таблицы является физическое разграничение данных секций. Это свойство позволяет нам менять секции местами между собой или же с любой другой таблицей.
При обычном обновлении данных с использованием скользящего окна (к примеру, за месяц), нам потребуется пройти следующие этапы:
1. Найти нужные строки в большой таблице;
2. Удалить найденные строки из таблицы и индекса;
3. Вставить новые строки в таблицу, обновить индекс.
При хранении в таблице миллиардов строк эти операции займут довольно продолжительное время, мы же можем ограничиться практически одним действием: просто заменить нужную секцию на подготовленную заранее таблицу (или секцию). При этом нам не потребуется удалять или вставлять строки, а так же нужно обновлять индекс на всей большой таблице.
Перейдем от слов к делу и покажем, как же это реализовать.
1. Для начала настраиваем секционированную таблицу так, как написано в статье, указанной выше.
2. Создаем таблицы, необходимые для обмена.
Для обновления данных нам потребуется мини-копия целевой таблицы. Мини-копией она является потому, что в ней будут храниться данные, которые должны добавиться в целевую таблицу, т.е. данные всего за 1 месяц. Так же потребуется третья пустая таблица для реализации обмена данных. Зачем она нужна – объясню позже.
К мини-копии и таблице для обмена ставятся жесткие условия:
- До использования оператора SWITCH должны существовать обе таблицы. Перед выполнением операции переключения в базе данных должны существовать и таблица, откуда перемещается секция (исходная таблица), и таблица, получающая секцию (целевая таблица).
- Секция-получатель должна существовать и должна быть пустой. Если таблица добавляется как секция в уже существующую секционированную таблицу или секция перемещается из одной секционированной таблицы в другую, то секция-получатель должна существовать и быть пустой.
- Несекционированная таблица-получатель должна существовать и должна быть пустой. Если секция предназначена для формирования единой несекционированной таблицы, то необходимо, чтобы таблица, получающая новую секцию, существовала и являлась пустой несекционированной таблицей.
- Секции должны быть из одного и того же столбца. Если секция переключена из одной секционированной таблицы в другую, то обе таблицы должны быть секционированы по одному и тому же столбцу.
- Исходная и целевая таблицы должны находиться в одной и той же файловой группе. Исходная и целевая таблицы в инструкции ALTER TABLE...SWITCH должны храниться в одной и той же файловой группе, так же как и их столбцы с большими значениями. Любые соответствующие индексы, секции индексов или индексированные представления секций также должны храниться в той же файловой группе. Однако она может отличаться от файловой группы для соответствующих таблиц или других соответствующих индексов.
Объясню ограничения на нашем примере:
1. Мини-копия таблицы должна быть секционирована по тому же столбцу, что и целевая. Если мини-копия является не секционированной таблицей, то она должна хранится в той же файловой группе, что и заменяемая секция.
2. Таблица для обмена должна быть пустой и так же должна быть секционированна по тому же столбцу или же должна храниться в той же файловой группе.
3. Реализуем обмен.
Сейчас мы имеем следующее:
Таблица с данными за все времена (далее Table_A)
Таблица с данными за 1 месяц (далее Table_B)
Пустая таблица (далее Table_C)
Первым делом нам нужно узнать в какой секции у нас хранятся данные.
Узнать это можно запросом:
SELECT
count(*) as
, $PARTITION.(dt) as
, rank() over (order by $PARTITION.(dt))
FROM dbo. (nolock)
group by $PARTITION.(dt)
В этом запросе мы получаем секции, в которых есть строки с информацией. Количество можно не подсчитывать – мы это делали для проверки необходимости обмена данных. Rank же используем, чтобы можно было идти в цикле и обновлять несколько секций в одной процедуре.
Как только узнали в каких секциях у нас хранятся данные – их можно менять местами. Допустим, что данные хранятся в секции 1.
Тогда нужно выполнить следующие операции:
Поменять секции из целевой таблицы с таблицей для обмена.
ALTER TABLE . SWITCH PARTITION 1 TO . PARTITION 1
Теперь мы имеем следующее:
В целевой таблице не осталось данных в нужной нам секции, т.е. секция пуста
Поменять местами секции из целевой таблицы и мини-копии
ALTER TABLE . SWITCH PARTITION 1 TO . PARTITION 1
Теперь мы имеем следующее:
В целевой таблице появились данные за месяц, а в мини-копии теперь пустота
Очистить или удалить таблицу для обмена.
Если у вас на таблице имеется кластерный индекс, то так же не проблема. Его необходимо создать на всех 3 таблицах с секционированием по одному и тому же столбцу. При смене секций индекс автоматически обновится, не перестраиваясь.
Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации , но разве ж это меня остановит?).
Вступление
Вообще под секционированием в общем случае понимают не какую-то технологию, а скорее подход к проектированию БД, появившийся задолго до того, как СУБД начали поддерживать т.н. секционированные таблицы. Мысль очень простая - разделить таблицу на несколько частей меньшего размера. Различают два подвида - горизонтальное и вертикальное секционирование.Горизонтальное секционирование
Части таблицы содержат разные ее строки. Положим у нас есть таблица логов некоего абстрактного приложения - LOGS. Мы можем разбить ее на части - одна для логов за январь 2009, другая - за февраль 2009, и т.д.Вертикальное секционирование
Части таблицы содержат разные ее столбцы. Найти применение для вертикального секционирования (когда оно действительно оправдано) несколько сложнее, чем для горизонтального. В качестве сферического коня предлагаю рассмотреть такой вариант: таблица NEWS имеет столбцы ID, SHORTTEXT, LONGTEXT, и пусть поле LONGTEXT используется намного реже первых двух. В таком случае имеет смысл разбить таблицу NEWS по столбцам (создать две таблицы для SHORTTEXT и LONGTEXT соответственно, связанных первичными ключами + создать view NEWS, содержащую оба столбца). Таким образом, когда нам нужно только описание новости, СУБД не придется читать с диска еще и весь текст новости.Поддержка секционирования в современных СУБД
Большинство современных СУБД поддерживают секционирование таблиц в том или ином виде.- Oracle - поддерживает секционирование начиная с 8й версии. Работа с секциями с одной стороны очень простая (вообще можно о них не думать, работаешь как с обычной таблицей*), а с другой - все очень гибко. Секции можно разбивать на «subpartitions», удалять, делить, переносить. Поддерживаются разные варианты индексирования секционированной таблицы (глобальный индекс, секционированный индекс). Ссылочка на объемное описание.
- Microsoft SQL Server - поддержка секционирования появилась недавно (в 2005). Первое впечатление от использования - «Ну наконец-то!!:)», второе - «Работает, вроде все ок». Документация на msdn
- MySQL - поддерживает начиная с версии 5.1.
- И так далее…
Секционирование в Postgresql
Секционирование таблиц в postgresql несколько отличается в реализации от остальных БД. Основой для секционирования служит наследование таблиц (вещь присущая исключительно postgresql). То есть, у нас должна быть основная таблица (master table), а ее секциями будут таблицы-наследники. Будем рассматривать секционирование на примере задачи, приближенной к реальности.Постановка задачи
База данных используется для сбора и анализа данных о посетителях сайта/сайтов. Объемы данных достаточно велики для того, чтобы задуматься о секционировании. При анализе в большинстве случаев используются данные за последний день.1. Создаем основную таблицу:
CREATE TABLE analytics.events
user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);
2. Секционировать будем по дням по полю event_time. На каждый день будем создавать новую секцию. Именовать секции будем по правилу: analytics.events_DDMMYYYY. Вот например секция для 1го января 2010 года.
CREATE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) PRIMARY KEY ,
CHECK (event_time >= TIMESTAMP "2010-01-01 00:00:00" AND event_time < TIMESTAMP "2010-01-02 00:00:00" )
) INHERITS (analytics.events);* This source code was highlighted with Source Code Highlighter .
При создании секции явно задаем поле event_id (PRIMARY KEY не наследуется) и создаем CHECK CONSTRAINT на поле event_time, дабы не вставить лишнего.
3. Создаем индекс на поле event_time. При разбиении таблицы на секции, мы подразумеваем, что большинство запросов к таблице events будут использовать условие на поле event_time, так что индекс на этом поле нам очень поможет.
CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 USING btree(event_time);* This source code was highlighted with Source Code Highlighter .
4. Мы хотим добиться того, чтобы при вставке в основную таблицу, данные оказывались в предназначенной им секции. Для этого делаем следующий финт - создаем триггер, который будет управлять потоками данных.
CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" AND
NEW .event_time < TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (NEW .*);
ELSE
RAISE EXCEPTION "Date % is out of range. Fix analytics.events_insert_trigger" , NEW .event_time;
END IF ;
RETURN NULL ;
END ;
$$
LANGUAGE plpgsql;* This source code was highlighted with Source Code Highlighter .
CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();* This source code was highlighted with Source Code Highlighter .
5. Все готово, у нас теперь есть секционированная таблица analytics.events. Можем начинать яростно анализировать ее данные. Кстати, CHECK constraints мы создавали не только для того, чтобы защитить секции от некорректных данных. Postgresql может их использовать при составлении плана запроса (правда при живом индексе на event_time выигрыш это даст минимальный), достаточно воспользоваться директивой constraint_exclusion:
SET constraint_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;* This source code was highlighted with Source Code Highlighter .
Конец первой части
Итак, что мы имеем? Давайте по пунктам:1. Таблицу events, разбитую на секции, анализ имеющихся данных за последние сутки становится проще и быстрее.
2. Ужас от осознания того, что все это нужно как-то поддерживать, создавать вовремя секции, не забывая менять триггер соответствующим образом.
О том как просто и беззаботно работать с секционированными таблицами расскажу во второй части.
UPD1: Заменил партиционирование на секционирование
UPD2:
По мотивам замечания одного из читателей, не имеющего, к сожалению, аккаунта на хабре:
С наследованием связано несколько моментов, которые стоит учитывать при проектировании. Секции не наследуют первичный ключ и внешние ключи на их столбцы. То есть, при создании секции, нужно явно создавать PRIMARY KEY и FOREIGN KEYs на столбцы секции. От себя замечу, что создавать FOREIGN KEY на столбцы секционированной таблицы не лучший путь. В большинстве случаев секционированная таблица является «таблицей фактов» и сама ссылается на «dimension» таблицы.
Страница 23 из 33
Секционирование диапазона - сведения о продажах
Характер использования сведений о продажах зачастую изменчив. Как правило, данные текущего месяца - это оперативные данные; данные предшествующих месяцев - это в большой степени данные, предназначенные для анализа. Чаще всего анализ производится ежемесячно, ежеквартально, либо ежегодно. Поскольку разным аналитикам могут потребоваться значительные объемы различных аналитических данных одновременно, то секционирование лучше всего позволит изолировать их деятельность. В рассматриваемом далее сценарии данные стекаются из 283 узлов и поставляются в виде двух файлов стандартного формата ASCII. Все файлы отправляются на центральный файл-сервер не позднее 3.00 am первого дня каждого месяца. Размеры файлов колеблются, но в среднем составляют примерно 86.000 заказов в месяц. Каждый заказ в среднем составляет 2.63 позиции, поэтому файлы OrderDetails составляют в среднем по 226180 строк. Каждый месяц добавляется примерно 25 миллионов новых заказов и 64 миллиона строк номенклатуры заказов. Сервер анализа истории поддерживает данные за 2 последних года. Данные за два года - это чуть меньше 600 миллионов заказов и более 1.5 миллиардов строк в таблице OrderDetails. Поскольку данные часто анализируются путем сравнения показателей месяцев одного и того же квартала, либо одних и тех же месяцев за предыдущие годы, то выбрано диапазонное секционирование. В качестве размера диапазона выбран месяц.
На основе схемы 11 ("Шаги по созданию секционированной таблицы") мы решили секционировать таблицу, используя диапазонное секционирование по столбцу OrderDate. Наши аналитики в основном объединяют и анализируют данные последних 6 месяцев, либо последних 3 месяцев текущего и прошлого годов (например, январь-март 2003 плюс январь-март 2004). Чтобы максимально усилить расслоение дисков, а заодно изолировать большинство группировок данных, на одном физическом диске будет располагаться по несколько файловых групп, но они будут смещены на шесть месяцев за тем, чтобы уменьшить количество конфликтов при разделении ресурсов. Текущий месяц - октябрь 2004, и все 283 обособленных офисов управляют своими текущими продажами локально. Сервер хранит данные с октября 2002 по сентябрь 2004 включительно. Для того чтобы воспользоваться преимуществом новой 16-процессорной системы и SAN (Storage Area Network - высокоскоростная сеть, связывающая хранилища данных), данные каждого месяца будут находиться в своем собственном файле файловой группы, и располагаться на наборе чередующихся зеркал (RAID 1+0). Рисунок 12 иллюстрирует размещение данных на логических дисках.
Рисунок 12: Секционированная таблица Orders
Каждый из 12 логических дисков использует конфигурацию RAID 1+0, поэтому общее количество дисков, необходимое для таблиц Orders и OrderDetails, равно 48. Не смотря на это, SAN поддерживает до 78 дисков, так что остальные 30 дисков используются для transaction log, TempDB, системных баз данных и прочих небольших таблиц, таких как Customers (9 миллионов записей) и Products (386 750 записей), и т.д. Таблицы Orders и OrderDetails будут использовать одни и те же граничные условия и одно и то же размещение на диске; фактически, они будут использовать одну и ту же схему секционирования. В результате (взгляните на два логических диска E:\ и F:\ на Рисунке 13) данные таблиц Orders и OrderDetails за одни и те же месяцы будут располагаться на одних и тех же дисках:
Рисунок 13: Размещение экстентов диапазонных секций на дисковых массивах
Хотя это и выглядит запутанным, все это весьма просто реализовать. Самое сложное в создании нашей секционированной таблицы - это доставка данных из большого количества источников - 283 хранилища должны иметь стандартный механизм доставки. Тем не менее, на центральном сервере есть только одна таблица Orders и одна таблица OrderDetails. Чтобы превратить обе таблицы в секционированные, мы должны сначала создать функцию и схему секционирования. Схема секционирования определяет физическое расположение секций на дисках, таким образом, файловые группы также должны существовать. Поскольку для наших таблиц необходимы файловые группы, то следующим шагом является их создание. Синтаксис операторов создания каждой файловой группы идентичен приведенному ниже, тем не менее, данным образом должны быть созданы все двадцать четыре файловые группы.Вы можете поменять названия/расположения дисков на один-единственный диск, для того чтобы протестировать и изучить синтаксис. Убедитесь, что Вы исправили размеры файла на MB вместо GB, и выбрали меньший начальный размер файлов, исходя из доступного вам дискового пространства. Двадцать четыре файла и файловые группы будут созданы в базе данных SalesDB. Все будут иметь схожий синтаксис, за исключением местоположения, имени файла и имени файловой группы:
| ALTER
DATABASE
SalesDB |
Как только все двадцать четыре файла и файловые группы будут созданы, Вы сможете определить функцию и схему секционирования. Убедиться в том, что ваши файлы и файловые группы созданы, вы можете при помощи системных хранимых процедур sp_helpfile и sp_helpfilegroup.
Функция секции будет определена по столбцу OrderDate с типом данных datetime. Для того чтобы обе таблицы можно было секционировать по столбцу OrderDate, этот столбец должен присутствовать в обеих таблицах. В действительности, значения ключей секционирования обоих таблиц (если обе таблицы будут секционированы по одному и тому же ключу) будут дублировать друг друга; однако это необходимо для получения преимуществ выравнивания, к тому же в большинстве случаев размер ключевых столбцов будет относительно небольшим (размер поля datetime всего 8 байт). Как уже описывалось в Главе "CREATE PARTITION FUNCTION для диапазонных секций", наша функция будет диапазонной функцией секционирования, у которой первое граничное условие будет в первой (LEFT) секции.
| CREATE
PARTITION FUNCTION
TwoYearDateRangePFN(datetime
) |
Поскольку и крайне левый, и крайне правый граничные случаи охвачены, эта функция секционирования фактически создает 25 секции. Таблица будет поддерживать 25-ую секцию, которая останется пустой. Для этой пустой секции не требуется никакой специальной файловой группы, поскольку никакие данные не должны когда-либо в нее попасть. Для того чтобы гарантировать, что никакие данные в нее не попадут, constraint ограничит диапазон данных этой таблицы. Для того чтобы направить данные на соответствующие диски используется схема секционирования, отображающая секции на файловые группы. Схема секционирования будет использовать явное определение файловых групп для каждой из 24 файловых групп, содержащих данные, и PRIMARY - для 25-ой пустой секции.
| CREATE
PARTITION SCHEME |
Таблица может быть создана с тем же синтаксисом, который поддерживали предыдущие релизы SQL Server - используя предложенную по умолчанию, либо определенную пользователем файловую группу (для создания НЕ секционированной таблицы) - либо используя схему (для создания секционированной таблицы). Что касается того, какой из вариантов предпочтительнее (даже если эта таблица в будущем станет секционированной), то все зависит от того, как таблица будет заполняться и сколькими секциями вы собираетесь манипулировать. Наполнение кучи (heap) и последующее создание в ней кластерного индекса, вероятно, обеспечит лучшую производительность, чем загрузка в таблицу, содержащую кластерный индекс. Кроме того, в мультипроцессорных системах вы можете загружать данные в таблицу параллельно, и затем тоже параллельно строить индексы. В качестве примера создадим таблицу Orders и загрузим в нее данные, используя операторы INSERT … SELECT. Чтобы создать таблицу Orders в качестве секционированной, определите схему секционирования в выражении ON оператора CREATE TABLE.
| CREATE
TABLE
SalesDB.. |
Поскольку таблица OrderDetails собирается использовать ту же схему, она должна включать в себя столбец OrderDate.
| CREATE
TABLE
.( |
На следующем шаге в таблицы загружаются данные из новой учебной базы данных AdventureWorks. Убедитесь, что вы установили базу данных AdventureWorks.
| INSERT
dbo. INSERT
dbo. |
Теперь, когда вы загрузили данные в секционированную таблицу, Вы можете воспользоваться новой встроенной системной функцией для того чтобы определить секцию, на которой будут располагаться данные. Следующий запрос для каждой из содержащих данные секций возвращает информацию о том, сколько строк содержится в каждой из секций, а также минимальное и максимальное значения поля OrderDate. Секция, которая не содержит строк, не попадет в итоговый результат.
| SELECT
$partition.TwoYearDateRangePFN(o.OrderDate) SELECT
$partition.TwoYearDateRangePFN(od.OrderDate) |
И наконец теперь, после того как вы загрузили данные, Вы можете создать кластерный индекс и внешний ключ (Foreign key) между таблицами OrderDetails и Orders. В данном случае кластерный индекс будет построен на первичном ключе (Primary Key) точно так же, как вы идентифицируете обе эти таблицы по их ключу секционирования (для OrderDetails к индексу Вы добавите столбец LineNumber для уникальности). По умолчанию при построении индексов на секционированной таблице происходит их выравнивание по отношению к секционированной таблице согласно той же самой схеме секционирования; явно задавать схему не обязательно.
| ALTER
TABLE
Orders
|
Полный синтаксис, определяющий схему секционирования, выглядел бы так:
| ALTER
TABLE
Orders ALTER
TABLE
dbo.OrderDetails |
