Статистика на уровне фрагментов и не только

Есть настолько очевидные вещи, 

что иногда удивляешься, 

почему их сделали 

совсем недавно

Как то я писал, что при работе с фрагментированными таблицами статистика обновляется целиком на всю таблицу. Получается что сервер делает лишнюю работу, если например новые данные добавляются в новые фрагменты, а старые данные, которые находятся в старых фрагментах не меняются. Если схема работы с данными в таблице такова, что данные добавляются и изменяются только в новом фрагменте, то в этом случае ясно, что статистика будет менятся только для этого фрагмента, а для старых фрагментов статистика не меняется. И вот в версии 11.70xC1 сделали статистику на уровне фрагментов. Для использования этой возможности надо при создании таблицы указать опцию STATLEVEL FRAGMENT. Непонятно только как делать обновление статистики по такой таблице, поскольку в UPDATE STATISTICS я не нашел как указывать что статистику надо обновлять на фрагмент. Возможно это делается автоматически, т.е. определяется процент измененных данных во фрагменте и после этого обновляется статистика по фрагментам где было много измененных данных.
Кстати сам процент изменений при которых статистика будет считаться устаревшей на таблице, можно определить при создании таблицы с помощью опции STATCHANGE (действует на уровне экземпляра и на уровне таблицы), и при запуске UPDATE STATISTICS AUTO статистика будет обновляться только для таблиц у которых она устарела. Это свойство относится не только к фрагментированным таблицам. Процент изменений по умолчанию равен 10, но его можно изменить при создании таблицы.

Скажи нет JFS и RAID5 для файлов баз данных

No JFS, no RAID5 ?

Интересная заметка в блоге Art Kagel на тему использования журналируемых ФС для файлов баз данных. Вообще я давно подозревал что дополнительное журналирование на уровне файловой системы (ФС) ухудшает производительность СУБД. Во первых СУБД и так обеспечивает журналирование и восстановление данных, это заложено в ее архитектуру. Может быть журналирование на уровне ФС обеспечит дополнительную защиту? Но оказывается что это не так. Дополнительный слой в виде журналирования ФС может снизить надежность хранения данных. Даже если производится только журналирование метаданных - результат скажется на производительности базы, поскольку на уровне ФС блоки файлов данных базы могут оказатся в разных местах диска и возрастет фрагментация.
Автор заметки предлагает использовать для хранения файлов базы либо сырые устройства, что логично, или старую добрую ext2 которая не имеет журналирования.
Вот что касается опции журналирования - ее что, совсем нельзя отключить на ext4 например? А JFS в AIX, у нее как обстоят с этим дела?

Оптимизация использования буферных пулов и нагрузки на диски

В любой базе данных есть таблицы которые занимают много и даже очень много места на дисках. Также есть таблицы которые на дисках занимают места мало. В старой модели использования буферного пула в Informix, все таблицы должны были разделять один и тот же буферный пул. Существенный минус такой модели состоит в том, что при частом использовании больших таблиц, возникал эффект вытеснения страниц остальных таблиц в буферном пуле, в результате чего увеличивалась нагрузка на диски, поскольку требовалось повторное чтение страниц таблиц, которые были замещены страницами больших таблиц. Частично эта проблема в Informix решалась с помощью использования Light Scan, когда страницы больших таблиц считывались в специальные пулы в разделяемом сегменте памяти, но не в буферный пул. Однако такое поведение работает далеко не всегда, должен соблюдатся целый ряд условий, чтобы Light Scan работал. Начиная с 10 версии Informix появилась возможность размещать таблицы в dbspace с размером страницы 2, 4, 8, 16, 32Кб, и назначать таким dbspace соответствующий буферный пул. Таким образом можно разделить большие таблицы и все остальные, и снизить конкуренцию за буферный пул между таблицами.
На практике это делается так. Сначала анализируем базу на предмет наличия больших таблиц, которые часто читаются или модифицируются. Либо на этапе проектирования выявляем таблицы, которые в будущем существенно вырастут по сравнению с остальными. Затем создаем непосредственно хранилище для таких таблиц. Допустим на данной платформе размер страницы по умолчанию в Informix 4Кб. Для больших таблиц надо создать dbspace с размером страницы например 8Кб а также создать буферный пул с таким же размером страницы 8Кб.

Предварительно конфигурируем буферный пул, например размером 512Мб или 65536 буферов по 8Кб в файле $ONCONFIG:

BUFFERPOOL size=8K,buffers=65536,lrus=127,lru_min_dirty=50,lru_max_dirty=60

Создаем dbspace с именем bigdata8k, размером страницы 8Кб и размером первого чанка 25 Gb:

onspaces -c -d bigdata8k -k 8 -p /informix/data/bigdata8k.000 -o 8 -s 26214384

Теперь можно либо перенести в новый dbspace большие таблицы, либо создать в нем новые.

Upd. Для версии Informix начиная с 11.50.UC6 можно глобально включить Light Scans с помощью параметра конфигурации BATCHEDREAD_TABLE, или же то же самое можно сделать для отдельной сессии с помощью set environment IFX_BATCHEDREAD_TABLE "1";
Это конечно же не отменяет преимуществ использования больших таблиц в отдельных dbspace с буферными пулами, а прекрасно дополняет эту возможность.

Копирование данных без длинных транзакций

Как быть если требуется перенести большой объем данных из одной таблицы в другую? Можно воспользоваться например выгрузкой данных в промежуточный файл. Если данных не очень много то можно попробовать использовать прямую вставку с помощью insert into ... select ... from ... однако такой способ не будет работать если данных очень много, поскольку высока вероятность длинной транзакции. Напрашивается способ использования таблицы без журналирования (raw table) однако он не подходит если сервер используется в паре HDR.
Следующий способ был бы универсальным однако он не реализован:

insert into t1 select ... from t2 where ... commitrows N;


Таким образом приходится придумывать свои способы. Один из таких способов это использование хранимой процедуры и объявления курсора через foreeach. Этот способ позволяет делать commit через нужное число вставленных строк. Вот примерная реализация этого способа (нечто подобное обсуждали как то на конференции comp.databases.informix):

CREATE PROCEDURE batch_move(commitrows int);
...
DEFINE count_rows INT;
...
LET count_rows = 0;
BEGIN WORK;
...
lock table table2 in exclusive mode;
FOREACH cur_ins WITH HOLD FOR
SELECT список полей INTO список переменных FROM table1
LET count_rows = count_rows + 1;
INSERT INTO table2 values (список переменных);
IF mod(count_rows, commitrows) = 0 THEN
COMMIT WORK;
BEGIN WORK;
lock table table2 in exclusive mode;
END IF;
END FOREACH;
COMMIT WORK;
...
END PROCEDURE;


commitrows в параметре процедуры это число записей, через которые последует очередной commit. Таким способом можно перенести любое число записей не опасаясь длинной транзакции, надо лишь задавать реалистичное значение commitrows, которое выбирается индивидуально исходя из настроек сервера и размера записи в таблице-источнике. Опционально можно эксклюзивно блокировать таблицу источник и таблицу приемник, выставлять нужный уровень изоляции и т.д.

btscanner и производительность: настраиваем range scan

Ранее я уже писал про btscanner. Сегодня расскажу как его настроить на тип сканирования range scan.
Не секрет что по умолчанию в информиксе btscanner настроен на очистку индексных страниц при помощи способа leafscan, который является довольно затратным и сильно влияет на общую производительность экземпляра при очистке больших индексов. В этом можно убедиться понаблюдав за работой btscanner'a при очистке индексов на больших таблицах: работа нити btscanner приводит к ощутимой нагрузке на диски и буферный пул при таком методе. Чтобы избежать существенного снижения производительности при очистке больших индексов, надо настроить btscanner на другой метод очистки, например range scan. Для настройки есть специальный параметр который надо добавить в файл $ONCONFIG. Например настроим btscanner следующим образом: 1 нитка, минимальный размер индекса для очистки методом range scan не менее 10000 страниц:

BTSCANNER num=1,rangesize=10000

Для того чтобы новые параметры вступили в силу надо перезапустить экземпляр информикса.
То же самое можно сделать и без перезапуска с помощью следующей команды:

onmode -C rangesize 10000

Однако чтобы при последующем перезапуске изменения оставались в силе, все же необходимо добавить в файл конфигурации параметр BTSCANNER c необходимыми значениями.

Через некоторое время когда накопится статистика, с помощью команды onstat -C можно посмотреть сколько индексов каким способом обрабатывалось, вот пример:

...
Number of leaves pages scanned 420428
Number of leaves with deleted items 23992
Time spent cleaning (sec) 631
Number of index compresses 3940
Number of deleted items 934612
Number of index range scans 100
Number of index leaf scans 562
Number of index alice scans 0

Как видим часть индексов (которые превысили установленный размер rangesize) обрабатываются методом range scan, остальные индексы чистятся как обычно методом leaf scan.

Сбор статистики по фрагментированной таблице

Как то обсуждая на sql.ru сбор статистики по большим таблицам один из участников обсуждения пожаловался что нельзя собирать статистику отдельно для каждого фрагмента таблицы. Действительно, предположим что есть фрагментированная таблица (или например разбитая на partitions). Если данные в такой таблице меняются только в некоторых фрагментах, а остальные остаются неизменными, то нет смысла запускать сбор статистики по всей таблице. Гораздо рациональнее было бы собирать статистику только по фрагментам с измененными данными. Однако такая возможность сбора статистики в информиксе не реализована.

Упреждающее чтение

Когда пользовательская нить читает данные, сначала она ищет необходимые ей страницы с данными в буферном пуле. Затем, если требуется, и не все данные оказались в буферах, нужные страницы с данными подгружаются в буферный пул с диска. Наиболее эффективное чтение происходит, когда сервер читает данные с диска не по отдельным страницам, а блоками по несколько страниц. Такое поведение в Информиксе реализовано, когда происходит последовательное сканирование таблицы или индекса в поисках нужных данных. Называется оно упреждающим чтением (Read Ahead). Можно настроить Информикс на чтение блоками по заданному кол-ву страниц, для этого служат параметры

RA_PAGES # Кол-во страниц упреждающего чтения
RA_THRESHOLD # Кол-во страниц которые не обработаны, и после которого начинается новое чтение

Условие использования упреждающего чтения - это последовательное сканирование. Если сервер выбрал для запроса последовательное сканирование, то будет включено упреждающее чтение для запроса.

Следующий этап оптимизации - это легкое сканирование или Light Scans. При таком типе чтения буферный пул Информикса не используется. Вместо него выделяется специальный пул в виртуальном сегменте, в который и производятся чтения большими блоками страниц. Light Scans имеет преимущество в том смысле что не вытесняются другие страницы из буферного пула, что позволяет не снижать производительность запросов к таблицам при чтении больших таблиц.

Условия для включения Light Scans в сессии:
- оптимизатор выбирает последовательное сканирование
- кол-во страниц в таблице больше чем число буферов в буферном пуле
- уровень изоляции сессии Dirty Read, Repeatable Read, Commited Read

Чтобы узнать, использует ли сессия Light Scans следуеть посмотреть вывод onstat -g ses sesid, раздел Memory pools, там могут быть пулы light_scan, lt_scan_rbuff, lt_scan_bufs. Или посмотреть выделенные фрагменты для пула с помощью onstat -g afr sesid (там может быть пул с именем light_scan). Далее можно посмотреть вывод команды onstat -g lsc.

Размер буферов для Light Scans вычисляется вот так:

( ( RA_PAGES + RA_THRESHOLD)/(MAXAIOSIZE + PAGESIZE))