Статистика в СУБД Teradata

    «There are three kinds of lies: lies, damned lies, and statistics» Бенджамин Дизраэли, 40-й премьер-министр Великобритании

    Статистическая информация о данных в СУБД играет важную роль в производительности системы. С учетом ряда комментариев к прошлой статье, мы решили описать, зачем нужна статистика в СУБД Teradata, как она учитывается оптимизатором, на что влияет, и дать ряд практических советов по сбору статистики для тех, кто работает с нашей СУБД.

    Что такое статистика и зачем она нужна


    Статистическая информация о данных нужна, в первую очередь, для обеспечения работы оптимизатора запросов. Зная статистические характеристики данных и то, по каким критериям пользователь их запрашивает, оптимизатор может выбирать те или иные способы извлечения этих данных. Простой пример: пользователь запросил выборку всех жителей города Белозерск. Предположим, что у нас есть индекс на поле с кодом города. Если у оптимизатора есть информация, что в Белозерске проживает около 10 тыс. человек (из ~143 миллионов жителей РФ), то он предпочтет доступ по индексу, так как это будет много быстрее, нежели читать таблицу целиком. С другой стороны, если выбирать данные по одному или нескольким большим городам, которые в сумме дадут несколько десятков миллионов записей, то в этом случае, наоборот, результат получается быстрее не при использовании доступа по индексу, а после прочтения всей таблицы целиком. Это очень упрощенный пример, но достаточный для того, чтобы показать, как статистика способна влиять на решения, принимаемые оптимизатором.

    В предыдущей статье мы рассказывали, что в СУБД 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.

    В зависимости от распределения значений используется один из трех видов гистограмм для сохранения статистики:
    1. Гистограмма с одинаковым размером интервалов. В каждом интервале сохраняется одинаковое количество значений. Возможно при наличии более-менее равномерного распределения значений, без явных перекосов (skew).
    2. Гистограмма со смещенными интервалами. В каждом интервале хранится максимум два значения. Такой вид используется только в тех случаях, когда распределение имеет существенный перекос.
    3. Сжатая гистограмма. Диаграмма, в которой комбинируются интервалы как одинаковых значений, так и интервалы лишь с двумя значениями. Если кому-то будет интересно посмотреть вплоть до байт, что и как хранится в статистике, то это наглядно показано на диаграммах здесь.

    Сбор статистики


    Процесс сбора статистик в Teradata, равно как и в СУБД других вендоров, запускается командой COLLECT STATISTICS. Каждая статистика, определенная для таблицы, требует отдельного прохода по таблице. Начиная с 14-й версии, можно объединять сбор нескольких статистик в один проход по таблице. При этом для каждой собираемой статистики осуществляется ряд шагов:
    1. Выполняется запрос следующего вида:
      SELECT x1, y1, COUNT(*) AS cnt FROM t_coll_stats GROUP BY 1,2;
      
      При выполнении данного запроса могут быть использованы любые альтернативные пути доступа к данным таблиц, например JOIN/HASH-индексы.
    2. Используя полученный агрегат, Teradata выполняет следующие шаги:
      • Строит таблицу интервалов
      • Используя эту таблицу, определяет интервалы гистограмм
      • Направляет структуру гистограмм AMP’ам
      • Каждый AMP читает агрегированный Spool и заполняет свои гистограммы
      • Система консолидирует локальные гистограммы
      • Консолидированные гистограммы записываются в словари, хранящие статистику

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

    В дополнение к организационным ограничениям, для минимизации нагрузки на систему можно использовать сбор статистик с использованием сэмплирования (COLLECT STATISTICS USING SAMPLE с опциональным указанием % строк). В этом случае Teradata сначала выполняет запрос на получение случайного набора строк. Для этого может использоваться TOP n или SAMPLE (для секционированных таблиц). Строки, полученные в результате, используются для последующей агрегации и создания гистограмм. Однако пользоваться сэмплированием можно лишь в случае когда есть уверенность в том, что рассматриваемая часть данных (сэмпл) таблицы адекватно предоставляет демографию всей таблицы для тех колонок, для которых планируется использовать сбор сэмпловой статистики. Если это не так, то нужно собирать полную статистику.

    Как статистика используется


    Итак, статистики собраны. Как оптимизатор будет их использовать? Ниже приведена диаграмма, описывающая процесс оптимизации запроса с учетом статистик:
    статистика в СУБД Teradata

    При выполнении запроса оптимизатор:
    • Ищет заголовок таблицы в кэше
      Если заголовок таблицы обнаружен в кэше, выполняется динамический сэмплированный сбор статистик (Random AMP sampling), если необходимо. Если он в кэше не обнаружен, производится его чтение с диска и выполняется динамический сэмплированный сбор статистик.
    • Ищет статистики для оптимизации запроса
      Teradata ищет необходимые статистики в кэше. Если они там не обнаружены, выполняется запрос на чтение статистик с диска и их кэширование.
    • Использует статистики для оптимизации запроса
      Если статистики на были собраны или устарели, оптимизатор может использовать статистики, полученные путем динамического сэмплирования. Это справедливо только для индексных колонок, в противном случае используется эвристика.

    В описанном выше процессе важное место отводится статистикам, полученным динамическим сэмплированием (Random AMP sampling). Как совершенно справедливо отметил bebop в одном из комментариев к нашей первой статье:
    «ребят, никакая статистика никогда не бывает полной – у оптимизатора нет полной информации о том, сколько записей вернёт каждый подзапрос в плане, особенно если критерии отбора достаточно сложные».

    Особенно это справедливо для приложений хранилищ данных, в которых значительная часть запросов является ad hoc и невозможно заранее предусмотреть все необходимые статистики.
    Teradata выполняет сбор статистик с помощью динамического сэмплирования всегда, отключить это невозможно. Процесс выглядит следующим образом:
    1. Путем хэширования идентификатора таблицы, для которой выполняется сэмплирование, выбирается AMP, чьи данные будут использоваться.
    2. Читается мастер-индекс для получения списка цилиндров, содержащих данные таблицы.
    3. Подсчитывается количество цилиндров с данными.
    4. Случайным образом выбирается один из цилиндров и читается список блоков, содержащих данные таблицы.
    5. Подсчитывается количество блоков.
    6. Случайным образом выбирается блок данных и подсчитывается количество строк в нем.
    7. Подсчитывается количество строк в таблице по следующей формуле:
      Кол-во строк в таблице = Количество строк в блоке * Количество блоков в цилиндре * Количество цилиндров * Количество AMP’ов
      


    Данный метод позволяет собрать меньшее число статистик по сравнению с традиционным:
    • Количество строк
    • Среднее количество строк на значение

    Только для индексов могут быть дополнительно собраны:
    • Среднее количество строк в индексе
    • Средний размер индекса на AMP
    • Количество уникальных значений

    Для сбора статистик данным методом характерно экстремально малое время выполнения. Кроме того, данные статистики хранятся в кэше (по умолчанию не более 4-х часов) и не пересобираются при выполнении каждого запроса.

    Еще одно назначение получаемых данным методом статистик – детектирование устаревания статистик, собираемых пользователями. Оно определяется путем сравнения числа строк таблицы, полученных при динамическом сборе статистик с хранимым значением. При отклонении более чем на 10% статистика считается устаревшей. При идентификации статистики как устаревшей Teradata может компенсировать это, экстраполировав собранные пользователем статистики.

    Соображения касательно применения того или иного метода сбора статистик приведены в таблице ниже:
    Метод Характеристики Применение
    Полная статистика
    • Собирает все статистики
    • Может занимать значительное время
    • Наиболее полные и точные статистики
    • Статистики хранятся в словаре данных
    • Лучший выбор для колонок или индексов со значительным «перекосом»
    • Рекомендуется для таблиц с менее чем 1000 строками на AMP
    • Рекомендуется для колонок с небольшим количеством уникальных значений
    • Рекомендуется для большинства NUSI, колонки PARTITION и прочих колонок, используемых в предикатах
    • Рекомендуется для всех индексов и колонок, для которых сэмплированная статистика не дает удовлетворительной точности
    Сэмплированная статистика
    • Собирает все статистики, но без чтения всех строк таблицы
    • Значительно быстрее по сравнению с полной
    • Статистики хранятся в словаре данных
    • Рекомендуется для уникальных или почти уникальных колонок и индексов
    • Рекомендуется для очень больших таблиц, с целью экономии ресурсов
    • Не рекомендуется для таблиц, число строк в которых менее чем 20 * Число AMP’ов в системе
    Динамическая сэмплированная статистика
    • Собирает меньшее число статистик
    • Очень быстрое выполнение
    • Статистики хранятся в кэше
    • Сбор осуществляется автоматически
    • Автоматически обновляется после пакетных обновлений (INSERT … DELETE) данных при изменении размера таблиц более чем на 10%
    • Подходит для определения размера таблицы с очень малым «перекосом» или при его отсутствии для таблиц с числом строк, значительно большим, чем число AMP’ов в системе
    • Собирает достоверные статистики для NUSI, если они имеют незначительный «перекос» и таблица содержит значительно больше строк, чем количество AMP’ов в системе
    • Является незаменимым механизмом защиты от отсутствующей/устаревшей статистики

    В завершение также хочется упомянуть наличие в Teradata механизма «наследования статистик». Статистики могут наследоваться как дочерними объектами от родительских объектов (TABLE -> JOIN INDEX), так и наоборот (TABLE < — JOIN INDEX). Применение данного механизма также позволяет без потери производительности минимизировать использование ресурсов системы для сбора статистической информации.

    Практические рекомендации по сбору статистик


    Так где и как надо собирать статистику? Основное правило – собирать только ту статистику, которая релевантна вашим запросам.

    Может статься, что у вас есть хитрый запрос, по поводу которого еще придется разобраться, какая статистика нужна. Но существует ряд типовых рекомендаций по сбору статистики, а именно:

    Собирайте полные статистики
    • по неиндексированным колонкам, используемым в предикатах (условиях WHERE);
    • по колонкам джойна, если колонка неуникальна;
    • по всем Non-Unique Secondary Index (удаляйте неиспользуемые индексы NUSI);
    • по тем Unique Secondary Index/Unique Primary Index, которые используются в предикатах с условием, отличным от «равно»;
    • по Non-Unique Primary Index;
    • по всем индексам или колонкам, используемым в предикатах маленьких таблиц (менее 100 строк на AMP);
    • по колонке PARTITION всех таблиц – независимо от того, секционированы они или нет (собираются очень быстро).

    Можно положиться на Dynamic AMP Sampling и не собирать статистику
    • если Unique Secondary Index/Unique Primary Index используются в предикатах только по условию «равно»;
    • если равномерно распределенный NUPI используется для джойна.

    Используйте сэмплированные статистики (USING SAMPLE)
    • для колонок уникальных индексов;
    • для колонок, близких к уникальным (уникальность > 95%).

    Собирайте многоколоночные статистики
    • для группы колонок, часто используемых совместно в предикатах с условием «равно», если первые 16 байт значения сгруппированных колонок достаточно уникальны;
    • для групп колонок, используемых для джойнов или агрегаций, при условии корреляции между значениями колонок.


    Дополнительная информация


    Для таблиц с секционированным первичным индексом всегда рекомендуется собирать статистики на:
    • Колонку PARTITION. Это даст оптимизатору информацию о числе секций и количестве строк в них.
    • Колонку секционирования. Это позволит определить количество строк на значение при использовании колонки секционирования в предикате запроса.

    Для таблиц с секционированным первичным индексом, если колонка секционирования не является частью первичного индекса:
    • (PARTITION, PI). Эта статистика наиболее важна, если значение PI может присутствовать в нескольких секциях; позволяет оценить стоимость соединений sliding-window и rowkey-based merge join, равно как и dynamic partition elimination.
    • (PARTITION, PI, partitioning column). Эта статистика дает количество уникальных значений для комбинации PI и partitioning columns после выполнения partition elimination.

    Dynamic AMP sampling позволяет выполнять сэмплирование с участием всех AMP’ов вместо одного (по умолчанию). Для маленьких таблиц (менее 25 строк на AMP) сэмплирование с участием всех AMP’ов выполняется автоматически. При использовании сэмплирования с участием всех AMP’ов нужно принимать во внимание, что:
    • Dynamic all-AMP sampling дает более точный результат для таблиц с NUPI. Важно в случае отсутствия статистик на NUPI, когда NUPI имеет неравномерное распределение.
    • Экстраполяция статистик для любой колонки таблицы выполняется, если оптимизатор выявляет увеличение таблицы. Последнее определяется путем сравнения числа строк таблицы, полученного сэмплированием, с сохраненной статистикой. Если динамическое сэмплирование с участием одного AMP’а дает неточный результат, рекомендуется применять динамическое сэмплирование с участием всех AMP’ов или пересобрать статистику по колонке PARTITION.
    • Время парсинга запросов может увеличиться. Статистики хранятся в кэше словаря данных, и их повторный сбор не выполняется до момента сброса кэша (периодического или по иным причинам). Это означает, что использование динамического сэмплирования с участием всех AMP’ов не увеличит времени парсинга всех запросов.

    Для temporal таблиц все описанные выше рекомендации также актуальны.

    Как часто следует собирать статистики? Здесь всё зависит от того, насколько значительно меняются данные с течением времени. Как правило, если данные в таблице изменились более чем на 5-10%, то нужно обновить статистику по этой таблице, чтобы оптимизатор знал об этих изменениях.

    Отдельно следует обратить внимание на то, что Teradata не обновляет статистику автоматически без Вашего ведома. Вы сами контролируете, когда и как запускать сбор статистики. Эти рекомендации представляют собой некий базис. Полный же процесс оптимизации физических структур («физического дизайна», как мы его называем) – достаточно творческий и состоит не только из работы со статистикой. Впрочем, это уже предмет отдельной статьи :)
    • +13
    • 10,4k
    • 6
    Teradata 28,39
    Компания
    Поделиться публикацией
    Комментарии 6
    • +2
      Раньше за такое минусовали, но всё же не удержусь. Спасибо за хорошую и подробную статью!
      • +3
        Нас покритиковали, что предыдущая была поверхностная. Мы услышали. Так что эта подробная :)
        • 0
          Продолжайте в том же духе :)
      • 0
        В роли бизнес юзера, т.е. без прав (и обязанностей) администратора базы я всегда добавляю модификаторы сессии diagnostic helpstats on for session; Tогда запуск EXPLAIN в конце текста показывает, каких статистик не хватает оптимайзеру. Сбор всех предложенных статистик по моим таблицам (исключительно на которые у меня и есть права) дешевле, чем запуск неоптимизированных запросов. Особенно из-за того что как бизнес юзер, я все-время создаю маленькие таблички для фильтрации или группировки, которые участвуют в джойнах.

        Вообще я бы всем бизнес юзерам вставил макро, которое бы собирало статистики для таблиц в их SQL перед выполнением запроса. В тех DWH на Терадате, где я работал, это наверняка было бы лучше, чем терпеть неоптимизированные запросы начинающих юзверей.
        • 0
          Разница между наличием или отсутствием статистики иногда огромная. Практически можно либо перегрузить базу косым (skewed) запросом и получить нагоняй от админа, а главное такие запросы либо требуют гораздо больше времени или вообще не работают. Сбор же статистики сам по себе практически не виден в сумме потраченного CPU времени (по которому мы платим за систему).
          • 0
            Идея с автосбором статистик по мелким таблицам в песочницах бизнес-пользователей интересная. При таком подходе, правда, придется заставить пользователя запускать запросы через специальную процедурину (или хотя бы перед запуском самого запроса прогонять текст запроса через ту самую процедурину). Но возникает другой вопрос — какие статистики собирать, а какие нет. Diagnostic helpstats безусловно полезная штука, но ее тоже надо уметь готовить. Сбор всех под ряд рекомендованых статистик штука опасная. CPU время на сбор статистики и правда может быть мало, другой вопрос с IO. Потому что при сборе статистики происходит полное сканирование таблицы.
            Я это к чему — автопомогалку по сбору статистики для песочниц бизнес-пользователей прикрутить можно, и это хорошая идея «на подумать». Но все равно потребуется вовлечение мозга, чтобы прикинуть сколько займет сам сбор статистики и насколько это поможет запросу. Можно встроить оценки по количеству строк в таблице и какие-то другие объективные критерии. В общем, есть над чем подумать)
            P.S. Мы еще часто используем продукт TASM (Teradata Active System Management) для обнаружения «на лету» плохих пользовательских запросов, и, либо их остановки, либо выделения им отдельных ресурсов, чтобы они не мешали другим, или побыстрее выполнились и «вышли вон» из системы.

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

        Самое читаемое