- PVSM.RU - https://www.pvsm.ru -
«There are three kinds of lies: lies, damned lies, and statistics» © Бенджамин Дизраэли, 40-й премьер-министр Великобритании
Статистическая информация о данных в СУБД играет важную роль в производительности системы. С учетом ряда комментариев к прошлой статье [1], мы решили описать, зачем нужна статистика в СУБД Teradata, как она учитывается оптимизатором, на что влияет, и дать ряд практических советов по сбору статистики для тех, кто работает с нашей СУБД.
Статистическая информация о данных нужна, в первую очередь, для обеспечения работы оптимизатора запросов. Зная статистические характеристики данных и то, по каким критериям пользователь их запрашивает, оптимизатор может выбирать те или иные способы извлечения этих данных. Простой пример: пользователь запросил выборку всех жителей города Белозерск [2]. Предположим, что у нас есть индекс на поле с кодом города. Если у оптимизатора есть информация, что в Белозерске проживает около 10 тыс. человек (из ~143 миллионов жителей РФ), то он предпочтет доступ по индексу, так как это будет много быстрее, нежели читать таблицу целиком. С другой стороны, если выбирать данные по одному или нескольким большим городам, которые в сумме дадут несколько десятков миллионов записей, то в этом случае, наоборот, результат получается быстрее не при использовании доступа по индексу, а после прочтения всей таблицы целиком. Это очень упрощенный пример, но достаточный для того, чтобы показать, как статистика способна влиять на решения, принимаемые оптимизатором.
В предыдущей статье [1] мы рассказывали, что в СУБД Teradata нет хинтов оптимизатора. Это означает, что оптимизатор запросов принимает все решения, основываясь на объективной информации, которая ему доступна. В расчет берется: количество AMP'ов в системе, количество узлов, количество и типы процессоров, доступная в данный момент память, типы дисков и многое другое, включая демографию данных. Демографическая информация, которую мы и называем статистикой, включает в себя количество строк в таблице, средний размер строки, количество строк с одним и тем же значением колонки, количество NULL'ов и прочее. Знание этих параметров также позволяет оптимизатору правильно рассчитывать размеры временной памяти (spool), выделяемой запросу для проведения преобразований данных.
Аналогично статистике по отдельным колонкам возможен сбор статистики по индексам. Принципы абсолютно те же.
Статистику распределения данных часто надо отслеживать не только по какому-то одному полю таблицы, а часто и по комбинациям полей. Например, вы часто запрашиваете людей, у которых фамилия Иванов и живут они в том же Белозерске. Если собрать отдельно статистику по фамилии и отдельно по городу, то это не даст хорошей информации оптимизатору, т.к. людей с фамилией Иванов может быть 1 млн., жителей Белозерска 10 тыс., а в комбинации будет много меньше. Поэтому необходимо собирать статистику по сочетанию полей. Причем для Teradata абсолютно неважен порядок полей, если собрать статистику по «Фамилии, Городу» и «Городу, Фамилии», результат будет одинаков (чтобы понять, почему это так – просто посмотрите еще раз на то, какие демографические показатели используются).
Собранная статистика хранится в словаре (DBC.TVFields, DBC.Indexes и DBC.StatsTbl для 14-й версии) и с точки зрения СУБД представляет собой интервальные гистограммы. Чем больше в последней число интервалов, тем более точно она может отражать распределение данных. Так, в Teradata 13.10 максимальное число интервалов в гистограмме равно 200, что примерно дает 0.5% данных в каждом интервале. При этом можно вспомнить эпиграф к этой статье и понять, что статистика показывает некоторую среднюю температуру по больнице. Количество строк в каждом интервале может варьироваться. Например: в таблице 1 млн. строк, значит, в среднем в одном интервале будет 5000 строк при условии 200 интервалов в гистограмме. Допустим, что в одном из интервалов на одно значение приходится 4900 строк, а на следующее значение – 300 строк. Может статься, что эти строки будут помещены в один интервал и оценка количества строк в нем будет 4900+300=5200. А может случиться так, что 300 строк будут помещены в следующий интервал и тогда в предыдущем окажется всего 4900.
Если какое-то значение встречается более чем в 0.25% строк, то оно сохраняется в специальных интервалах, отведенных под часто встречающиеся значения. Справедливости ради надо сказать, что количество интервалов растет от версии к версии. Начиная с версии Teradata 12 количество интервалов было увеличено со 100 до 200, а в Teradata 14 по умолчанию число интервалов равно 250 и его можно увеличить вплоть до 500.
В зависимости от распределения значений используется один из трех видов гистограмм для сохранения статистики:
Процесс сбора статистик в Teradata, равно как и в СУБД других вендоров, запускается командой COLLECT STATISTICS. Каждая статистика, определенная для таблицы, требует отдельного прохода по таблице. Начиная с 14-й версии, можно объединять сбор нескольких статистик в один проход по таблице. При этом для каждой собираемой статистики осуществляется ряд шагов:
SELECT x1, y1, COUNT(*) AS cnt FROM t_coll_stats GROUP BY 1,2;
При выполнении данного запроса могут быть использованы любые альтернативные пути доступа к данным таблиц, например JOIN/HASH-индексы.
Для больших (миллионы строк) и очень больших (десятки миллиардов строк) таблиц, сплошь и рядом встречающихся в проектах хранилищ данных, этот процесс может занимать часы, несмотря на распараллеливание. Именно по этой причине к сбору статистик необходимо подходить ответственно и не загружать систему без реальной необходимости. Для того чтобы неподготовленный пользователь не смог случайно загрузить систему, в Teradata реализована специальная привилегия на сбор статистик.
В дополнение к организационным ограничениям, для минимизации нагрузки на систему можно использовать сбор статистик с использованием сэмплирования (COLLECT STATISTICS USING SAMPLE с опциональным указанием % строк). В этом случае Teradata сначала выполняет запрос на получение случайного набора строк. Для этого может использоваться TOP n или SAMPLE (для секционированных таблиц). Строки, полученные в результате, используются для последующей агрегации и создания гистограмм. Однако пользоваться сэмплированием можно лишь в случае когда есть уверенность в том, что рассматриваемая часть данных (сэмпл) таблицы адекватно предоставляет демографию всей таблицы для тех колонок, для которых планируется использовать сбор сэмпловой статистики. Если это не так, то нужно собирать полную статистику.
Итак, статистики собраны. Как оптимизатор будет их использовать? Ниже приведена диаграмма, описывающая процесс оптимизации запроса с учетом статистик:
При выполнении запроса оптимизатор:
В описанном выше процессе важное место отводится статистикам, полученным динамическим сэмплированием (Random AMP sampling). Как совершенно справедливо отметил bebop [4] в одном из комментариев к нашей первой статье:
«ребят, никакая статистика никогда не бывает полной – у оптимизатора нет полной информации о том, сколько записей вернёт каждый подзапрос в плане, особенно если критерии отбора достаточно сложные».
Особенно это справедливо для приложений хранилищ данных, в которых значительная часть запросов является ad hoc [5] и невозможно заранее предусмотреть все необходимые статистики.
Teradata выполняет сбор статистик с помощью динамического сэмплирования всегда, отключить это невозможно. Процесс выглядит следующим образом:
Кол-во строк в таблице = Количество строк в блоке * Количество блоков в цилиндре * Количество цилиндров * Количество AMP’ов
Данный метод позволяет собрать меньшее число статистик по сравнению с традиционным:
Только для индексов могут быть дополнительно собраны:
Для сбора статистик данным методом характерно экстремально малое время выполнения. Кроме того, данные статистики хранятся в кэше (по умолчанию не более 4-х часов) и не пересобираются при выполнении каждого запроса.
Еще одно назначение получаемых данным методом статистик – детектирование устаревания статистик, собираемых пользователями. Оно определяется путем сравнения числа строк таблицы, полученных при динамическом сборе статистик с хранимым значением. При отклонении более чем на 10% статистика считается устаревшей. При идентификации статистики как устаревшей Teradata может компенсировать это, экстраполировав собранные пользователем статистики.
Соображения касательно применения того или иного метода сбора статистик приведены в таблице ниже:
Метод | Характеристики | Применение |
Полная статистика |
|
|
Сэмплированная статистика |
|
|
Динамическая сэмплированная статистика |
|
|
В завершение также хочется упомянуть наличие в Teradata механизма «наследования статистик». Статистики могут наследоваться как дочерними объектами от родительских объектов (TABLE -> JOIN INDEX), так и наоборот (TABLE < — JOIN INDEX). Применение данного механизма также позволяет без потери производительности минимизировать использование ресурсов системы для сбора статистической информации.
Так где и как надо собирать статистику? Основное правило – собирать только ту статистику, которая релевантна вашим запросам.
Может статься, что у вас есть хитрый запрос, по поводу которого еще придется разобраться, какая статистика нужна. Но существует ряд типовых рекомендаций по сбору статистики, а именно:
Собирайте полные статистики
Можно положиться на Dynamic AMP Sampling и не собирать статистику
Используйте сэмплированные статистики (USING SAMPLE)
Собирайте многоколоночные статистики
Для таблиц с секционированным первичным индексом всегда рекомендуется собирать статистики на:
Для таблиц с секционированным первичным индексом, если колонка секционирования не является частью первичного индекса:
Dynamic AMP sampling позволяет выполнять сэмплирование с участием всех AMP’ов вместо одного (по умолчанию). Для маленьких таблиц (менее 25 строк на AMP) сэмплирование с участием всех AMP’ов выполняется автоматически. При использовании сэмплирования с участием всех AMP’ов нужно принимать во внимание, что:
Для temporal таблиц все описанные выше рекомендации также актуальны.
Как часто следует собирать статистики? Здесь всё зависит от того, насколько значительно меняются данные с течением времени. Как правило, если данные в таблице изменились более чем на 5-10%, то нужно обновить статистику по этой таблице, чтобы оптимизатор знал об этих изменениях.
Отдельно следует обратить внимание на то, что Teradata не обновляет статистику автоматически без Вашего ведома. Вы сами контролируете, когда и как запускать сбор статистики. Эти рекомендации представляют собой некий базис. Полный же процесс оптимизации физических структур («физического дизайна», как мы его называем) – достаточно творческий и состоит не только из работы со статистикой. Впрочем, это уже предмет отдельной статьи :)
Автор: Teradata_Russia
Источник [6]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/statistika/26126
Ссылки в тексте:
[1] прошлой статье: http://habrahabr.ru/company/teradata/blog/160821/
[2] города Белозерск: http://maps.yandex.ru/?text=%D0%91%D0%B5%D0%BB%D0%BE%D0%B7%D0%B5%D1%80%D1%81%D0%BA
[3] здесь: http://www.teradataforum.com/attachments/a100531d.pdf
[4] bebop: http://habrahabr.ru/users/bebop/
[5] ad hoc: http://ru.wikipedia.org/wiki/Ad_hoc
[6] Источник: http://habrahabr.ru/post/167801/
Нажмите здесь для печати.