Как решить, нужно ли вам секционировать таблицы
Пересказ статьи Kendra Little. How To Decide if You Should Use Table Partitioning
О секционировании таблиц было написано очень много. Это сложная функциональность, и вы днями можете читать, чтобы только понять, как это можно применить. Но улучшит ли это производительность? Секционирование таблиц дает большие преимущества для некоторых приложений, но вызывает сильную головную боль для других.
Как узнать, следует ли вам инвестировать свое время в секционирование таблиц?
Я могу помочь.
Давайте немного порассуждаем о том, что делает секционирование таблиц в SQL Server. Во-первых, это функция Enterprise Edition (т.е. платная). Вы можете протестировать её в Developer Edition, но если вы захотите использовать её в производстве, следует убедиться, она стоит затрат на лицензию и ваше время.
Секционирование таблиц позволяет хранить таблицы или индексы в множестве физических секций - секционированный индекс подобен одному большому индексу, составленному из множества мелких. Секционирование таблиц "прозрачно". Это означает, что на секционированную кучу, кластеризованный индекс или некластеризованный индекс можно ссылаться как на единую структуру, хотя она хранится в независимых физических секциях. Другими словами, теоретически вам не придется менять какой-либо код в вызывающих приложениях. (Действительно ли это так? Позже выясним.)
Froyo Division в Contoso Corporation имеет 2Тб базу данных с именем FroyoReports. Каждый день загружается 10 миллионов строк данных продаж в таблицу с именем FroyoSales. Contoso Corp имеет сотрудников по всему миру, которые обращаются к данным с помощью SQL Server Reporting Services. Отчеты выполняются 24х7 в FroyoReports, хотя каждый день имеется двухчасовое окно, когда нагрузка существенно ниже. 95% отчетов выполняются к данным последних двух месяцев, и команда администраторов баз данных (DBA) контролирует и может настраивать запросы, выполняемые каждым отчетом. Команде DBA Froyo необходимо обслуживать только данные 13-ти месяцев в таблице FroyoSales. В качестве меры предосторожности они предпочитают хранить онлайн дополнительно данные трех месяцев, но не хотели бы, чтобы отчеты получали доступ к более старым данным.
Раньше жизнь команды администраторов баз данных Froyo была тяжелой. Каждую ночь, когда загружались данные, отчеты периодически блокировали вставки. Чтобы снизить блокирование, некоторые отчеты модифицировались хинтами NOLOCK. Это означало, что иногда отчеты содержали частичные данные последнего дня, что вызывало проблемы. Пользователи никогда не были по-настоящему уверены, что загрузка данных уже завершилась, чтобы можно было безопасно выполнять отчеты. Команда администраторов Froyo удаляла старые данные в конце недели, и этот процесс тоже был проблемным. Возникали дополнительные блокировки, и удаления существенно снижали производительность. Для решения этих проблем, команда Froyo применила секционирование. Они секционировали таблицу FroyoSales по дате.
Каждую ночь команда Froyo загружает данные с помощью автоматизированного процесса. Сначала он загружает новые фактологические данные в свежую, пустую таблицу с именем FroyoSalesStaging.
Данными загружалась промежуточная таблица
Затем для FroyoSalesStaging добавлялись индексы и ограничения, чтобы её структура соответствовала FroyoSales.
Подготовка промежуточной таблицы для включения в таблицу фактов
Затем они включали отдельную секцию FroyoSalesStaging в секционированную таблицу FroyoSales. (Это содержит несколько предварительных команд для подготовки метаданных секционированной таблицы до включения - мы здесь сделаем обзор.) Это включение занимает небольшое время, после чего все новые данные видны пользователям.
Включение секции из промежуточной таблицы в таблицу фактов
Команда Froyo также автоматизировала то, как удаляются старые данные. Каждую ночь они переносят самый старый день - который теперь выходит за их 13-месячный лимит - из FroyoSales в таблицу с именем FroyoSalesArchive. (Аналогично тут есть несколько команд для очистки метаданных FroyoSales после переноса.)
Переключение секции таблицы в архивную таблицу
Удобно, не правда ли?
Вот крупные особенности секционирования таблиц наряду с мелкими деталями.
Как вы могли видеть выше, целая секция может быть включена в таблицу или выключена, позволяя очень быстро загружать и удалять большое количество данных. Это, по моему мнению, очень большой плюс секционирования.
Есть несколько подводных камней, о которых следует знать. Включение и выключение секций может быть очень быстрым, но требуется эксклюзивная блокировка - называемая ‘SCH-M’ или блокировка модификации схемы. Это означает, что вы можете блокировать загрузку или удаление данных из вашей таблицы - в перспективе на весьма продолжительное время. Кроме того, все включенные некластеризованные индексы должны быть "секционно выровнены" для включения секции. Это означает, что ключ секционирования должен быть частью каждого их этих индексов. Если вам требуется поддерживать уникальность на множестве столбцов, которые не включены в ключ секционирования (что часто имеет место в средах OLTP), это может вызвать проблемы.
SQL Server пытается выяснить, когда он может использовать ограниченные части секционированной таблицы. Оптимизатор запросов SQL Server может отправить запрос только к отдельной секции, множеству секций или всей таблице. Использование числа секций меньшего, чем вся таблица, называется "устранением секций".
Статистика поддерживается для всей секционированной таблицы или индекса - вам не получите дополнительные шаги в гистограмме для каждой секции. Это означает, что оптимизатору запросов SQL Server будет трудно узнать, как много данных будет возвращено вашим запросом, и эта трудность будет возрастать по мере роста вашей таблицы. В результате выполнение запросов может замедлиться.
Запросы будут выполняться лучше, когда вы указываете ключ секционирования в критерии (т.е. в предложении WHERE). Поэтому, хотя секционирование и "прозрачно", для существующих приложений почти всегда потребуется настройка запросов.
Отдельные секции могут:
Вот несколько вещей, которые следует иметь в виду:
Поколоночные индексы - очень популярная функция в SQL Server 2012. Это колоночные индексы оптимизированы для невероятно высокой производительности. Хотя эти индексы будут только на чтение, секции могут включаться в поколоночные индексы.
После знакомства с секционированием таблиц обычно первым вопросом оказывается такой: "Достаточно ли большая моя таблица?" Обычно я отвечаю так: "Давайте поговорим о том, почему вы интересуетесь секционированием таблиц. Какие проблемы вас беспокоят?"
Когда люди рассматривают секционирование в SQL Server, обычно они имеют проблемы с масштабированием своих баз данных. То, что вы испытываете, может принимать различные формы. Проблема может включать одну или более следующих причин:
"Медленно" - это, конечно, весьма относительно. Здесь это означает "мои пользователи жалуются" или "мой веб-сервер уходит в тайм-аут" или "что-то ломается, и мне звонят среди ночи". Часто таблицы, о которых мы говорим, используются как для OLTP-процессов, так и для подготовки отчетов.
Я предпочитаю поговорить с командой и выяснить, какого сорта эта проблема. Буквально: "Что не дает вам спать по ночам из-за этой таблицы?"
Советовать секционирование таблицы - все равно что рекомендовать серьезную операцию - вы никому не пожелаете ложиться под нож, если это не наилучший вариант. Я смотрю на общее состояние системы. Где находятся узкие места? Чего мы ждем? Каково состояние отдельных компонент? Какова текущая производительность запросов, и как выглядят планы запросов? Какие шаблоны используются в исполняемых запросах? Я также смотрю на структуру таблиц и индексов в контексте этих запросов.
Какое количество записей вы хотите загружать ежедневно? Какое количество записей будут удаляться ежедневно в течение шести месяцев? Сколько новых клиентов вы ожидаете приобрести, и каково ожидаемое влияние на вашу систему вызовет прирост операций чтения и записи? Число ожидаемых клиентов может быть сложно перевести в активность базы данных. Проверка состояния SQL Server может дать несколько метрик текущей активности, которую можно использовать для проектирования.
Гибкость, которую вы применяете при настройке запросов, в значительной мере влияет на ваш выбор масштабирования приложения. С одной стороны, секционирование таблиц является "прозрачным", поскольку имя секционированных объектов не изменяется. С другой стороны, вы хотите настроить запросы, чтобы добиться исключения секций и получения наилучших возможных планов запросов после секционирования - и иногда вам нужно будет проявить немного творчества. Структура таблиц и то, как сейчас написаны запросы, будет играть огромную роль, если у вас ограниченные (или нет) возможности настройки запросов.
Вот мой секрет: я не отвечаю на вопрос: "Должен ли я использовать секционирование таблицы?" Вместо этого я отвечаю на вопрос: "Какой лучший способ масштабировать это приложение?"
Правильный подход к проблеме масштабирования может включать секционирование таблицы - возможно само, возможно в комбинации с другими технологиями. В правильном приложении разбиение таблицы на секции может действительно дать потрясающий эффект. Но мы также можем оказаться способными масштабировать ваше приложение другим способом - возможно, более дешево, возможно, более быстро, а, возможно, способом, который включает встроенное георазнообразие. Все зависит от состояния вашей базы данных, требований к производительности, бюджета и гибкости.
Я могу помочь.
Секционирование таблиц в SQL Server: основы
Давайте немного порассуждаем о том, что делает секционирование таблиц в SQL Server. Во-первых, это функция Enterprise Edition (т.е. платная). Вы можете протестировать её в Developer Edition, но если вы захотите использовать её в производстве, следует убедиться, она стоит затрат на лицензию и ваше время.
Секционирование таблиц позволяет хранить таблицы или индексы в множестве физических секций - секционированный индекс подобен одному большому индексу, составленному из множества мелких. Секционирование таблиц "прозрачно". Это означает, что на секционированную кучу, кластеризованный индекс или некластеризованный индекс можно ссылаться как на единую структуру, хотя она хранится в независимых физических секциях. Другими словами, теоретически вам не придется менять какой-либо код в вызывающих приложениях. (Действительно ли это так? Позже выясним.)
Учебный пример секционирования таблицы
Froyo Division в Contoso Corporation имеет 2Тб базу данных с именем FroyoReports. Каждый день загружается 10 миллионов строк данных продаж в таблицу с именем FroyoSales. Contoso Corp имеет сотрудников по всему миру, которые обращаются к данным с помощью SQL Server Reporting Services. Отчеты выполняются 24х7 в FroyoReports, хотя каждый день имеется двухчасовое окно, когда нагрузка существенно ниже. 95% отчетов выполняются к данным последних двух месяцев, и команда администраторов баз данных (DBA) контролирует и может настраивать запросы, выполняемые каждым отчетом. Команде DBA Froyo необходимо обслуживать только данные 13-ти месяцев в таблице FroyoSales. В качестве меры предосторожности они предпочитают хранить онлайн дополнительно данные трех месяцев, но не хотели бы, чтобы отчеты получали доступ к более старым данным.
Раньше жизнь команды администраторов баз данных Froyo была тяжелой. Каждую ночь, когда загружались данные, отчеты периодически блокировали вставки. Чтобы снизить блокирование, некоторые отчеты модифицировались хинтами NOLOCK. Это означало, что иногда отчеты содержали частичные данные последнего дня, что вызывало проблемы. Пользователи никогда не были по-настоящему уверены, что загрузка данных уже завершилась, чтобы можно было безопасно выполнять отчеты. Команда администраторов Froyo удаляла старые данные в конце недели, и этот процесс тоже был проблемным. Возникали дополнительные блокировки, и удаления существенно снижали производительность. Для решения этих проблем, команда Froyo применила секционирование. Они секционировали таблицу FroyoSales по дате.
Каждую ночь команда Froyo загружает данные с помощью автоматизированного процесса. Сначала он загружает новые фактологические данные в свежую, пустую таблицу с именем FroyoSalesStaging.
Данными загружалась промежуточная таблица
Затем для FroyoSalesStaging добавлялись индексы и ограничения, чтобы её структура соответствовала FroyoSales.
Подготовка промежуточной таблицы для включения в таблицу фактов
Затем они включали отдельную секцию FroyoSalesStaging в секционированную таблицу FroyoSales. (Это содержит несколько предварительных команд для подготовки метаданных секционированной таблицы до включения - мы здесь сделаем обзор.) Это включение занимает небольшое время, после чего все новые данные видны пользователям.
Включение секции из промежуточной таблицы в таблицу фактов
Команда Froyo также автоматизировала то, как удаляются старые данные. Каждую ночь они переносят самый старый день - который теперь выходит за их 13-месячный лимит - из FroyoSales в таблицу с именем FroyoSalesArchive. (Аналогично тут есть несколько команд для очистки метаданных FroyoSales после переноса.)
Переключение секции таблицы в архивную таблицу
Удобно, не правда ли?
Основные особенности секционирования таблиц - и подводные камни
Вот крупные особенности секционирования таблиц наряду с мелкими деталями.
Переключите эту секцию
Как вы могли видеть выше, целая секция может быть включена в таблицу или выключена, позволяя очень быстро загружать и удалять большое количество данных. Это, по моему мнению, очень большой плюс секционирования.
Есть несколько подводных камней, о которых следует знать. Включение и выключение секций может быть очень быстрым, но требуется эксклюзивная блокировка - называемая ‘SCH-M’ или блокировка модификации схемы. Это означает, что вы можете блокировать загрузку или удаление данных из вашей таблицы - в перспективе на весьма продолжительное время. Кроме того, все включенные некластеризованные индексы должны быть "секционно выровнены" для включения секции. Это означает, что ключ секционирования должен быть частью каждого их этих индексов. Если вам требуется поддерживать уникальность на множестве столбцов, которые не включены в ключ секционирования (что часто имеет место в средах OLTP), это может вызвать проблемы.
Производительность запросов на секционированных таблицах: устранение секций и не только
SQL Server пытается выяснить, когда он может использовать ограниченные части секционированной таблицы. Оптимизатор запросов SQL Server может отправить запрос только к отдельной секции, множеству секций или всей таблице. Использование числа секций меньшего, чем вся таблица, называется "устранением секций".
Статистика поддерживается для всей секционированной таблицы или индекса - вам не получите дополнительные шаги в гистограмме для каждой секции. Это означает, что оптимизатору запросов SQL Server будет трудно узнать, как много данных будет возвращено вашим запросом, и эта трудность будет возрастать по мере роста вашей таблицы. В результате выполнение запросов может замедлиться.
Запросы будут выполняться лучше, когда вы указываете ключ секционирования в критерии (т.е. в предложении WHERE). Поэтому, хотя секционирование и "прозрачно", для существующих приложений почти всегда потребуется настройка запросов.
Управление секциями
Отдельные секции могут:
- Быть перестроены индивидуально, как для кластеризованных, так и некластеризованных индексов.
- Быть установлены в режим только на чтение - дает варианты для оптимизации резервных копий.
- Находиться на различных дисковых подсистемах - редко востребованные данные могут размещаться на медленных дисках, а часто востребованные - на быстрых. И все это в пределах одной и той же таблицы! Вы можете перемещать секцию на быстрый или медленный диск в режиме онлайн, используя решения виртуализации и SAN.
Вот несколько вещей, которые следует иметь в виду:
- Будьте осторожны при разделении секций - производительность может быть очень низкой.
- В SQL Server 2005 и 2008 отдельные секции могут перестраиваться только в режиме офлайн. Весь секционированный индекс может перестраиваться в режиме онлайн - но это проблема, если ваша база данных работает 7х24.
- Установка файловой группы в режим только на чтение не исключает накладных расходов на управление блокировками - это справедливо только для баз данных только на чтение.
Поколоночные индексы и секционирование таблиц
Поколоночные индексы - очень популярная функция в SQL Server 2012. Это колоночные индексы оптимизированы для невероятно высокой производительности. Хотя эти индексы будут только на чтение, секции могут включаться в поколоночные индексы.
Когда таблица считается достаточно большой для секционирования?
После знакомства с секционированием таблиц обычно первым вопросом оказывается такой: "Достаточно ли большая моя таблица?" Обычно я отвечаю так: "Давайте поговорим о том, почему вы интересуетесь секционированием таблиц. Какие проблемы вас беспокоят?"
Скажите, где у вас болит
Когда люди рассматривают секционирование в SQL Server, обычно они имеют проблемы с масштабированием своих баз данных. То, что вы испытываете, может принимать различные формы. Проблема может включать одну или более следующих причин:
- "Медленные" запросы, которые возвращают небоьшое количество данных.
- "Медленные" запросы, которые возвращают большое количество данных.
- "Медленная" загрузка данных.
- Блокирование чтения и записи (insert или update).
- Долго выполняющиеся задания (job) обслуживания индексов (или невозможность выполнить их вообще по причине их продолжительности).
"Медленно" - это, конечно, весьма относительно. Здесь это означает "мои пользователи жалуются" или "мой веб-сервер уходит в тайм-аут" или "что-то ломается, и мне звонят среди ночи". Часто таблицы, о которых мы говорим, используются как для OLTP-процессов, так и для подготовки отчетов.
Я предпочитаю поговорить с командой и выяснить, какого сорта эта проблема. Буквально: "Что не дает вам спать по ночам из-за этой таблицы?"
Как ваше общее состояние?
Советовать секционирование таблицы - все равно что рекомендовать серьезную операцию - вы никому не пожелаете ложиться под нож, если это не наилучший вариант. Я смотрю на общее состояние системы. Где находятся узкие места? Чего мы ждем? Каково состояние отдельных компонент? Какова текущая производительность запросов, и как выглядят планы запросов? Какие шаблоны используются в исполняемых запросах? Я также смотрю на структуру таблиц и индексов в контексте этих запросов.
Какие характеристики производительности желательны?
Какое количество записей вы хотите загружать ежедневно? Какое количество записей будут удаляться ежедневно в течение шести месяцев? Сколько новых клиентов вы ожидаете приобрести, и каково ожидаемое влияние на вашу систему вызовет прирост операций чтения и записи? Число ожидаемых клиентов может быть сложно перевести в активность базы данных. Проверка состояния SQL Server может дать несколько метрик текущей активности, которую можно использовать для проектирования.
Сколько запросов вы можете настроить?
Гибкость, которую вы применяете при настройке запросов, в значительной мере влияет на ваш выбор масштабирования приложения. С одной стороны, секционирование таблиц является "прозрачным", поскольку имя секционированных объектов не изменяется. С другой стороны, вы хотите настроить запросы, чтобы добиться исключения секций и получения наилучших возможных планов запросов после секционирования - и иногда вам нужно будет проявить немного творчества. Структура таблиц и то, как сейчас написаны запросы, будет играть огромную роль, если у вас ограниченные (или нет) возможности настройки запросов.
Каков лучший подход к масштабированию вашего приложения?
Вот мой секрет: я не отвечаю на вопрос: "Должен ли я использовать секционирование таблицы?" Вместо этого я отвечаю на вопрос: "Какой лучший способ масштабировать это приложение?"
Правильный подход к проблеме масштабирования может включать секционирование таблицы - возможно само, возможно в комбинации с другими технологиями. В правильном приложении разбиение таблицы на секции может действительно дать потрясающий эффект. Но мы также можем оказаться способными масштабировать ваше приложение другим способом - возможно, более дешево, возможно, более быстро, а, возможно, способом, который включает встроенное георазнообразие. Все зависит от состояния вашей базы данных, требований к производительности, бюджета и гибкости.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой