Оптимизация данных - Индексы

В больших таблицах хранится много данных, быстро найти нужные из них помогают индексы. Но количество индексных записей растет вместе с количеством записей в таблице и при извлечении нужных данных приходится обработать много индексных записей. С целью ускорения выборки нужных индексных записей применяется операция кластеризации (cluster) таблиц по указанному индексу, которая упорядочивает индексы так, что поиск по индексам не требует перебора всех страниц индексов на диске и так же упорядочивает сами данные. На больших таблицах кластеризация позволяет в десятки и более раз ускорить выборку данных.

Кластеризация бесполезна в том случае, если происходит много обновлений данных в таблице - все равно будет непрерывная сегментация данных вследствии их изменения. В этом случае сначала нужно создать разделенные таблицы так, чтобы обновлялись лишь некоторые из них. При обновлении части подтаблиц разделенной таблицы можно пользуется всеми выгодами кластеризации и отсутствия мусора в большинстве подтаблиц (разумеется, из часто обновляемых подтаблиц данные будут извлекаться медленнее, чем из необновляемых таблиц). Заметим, что мы не говорим о полном отсутствии обновлений частей разделенной таблицы - достаточно, чтобы эти обновления проиходили значительно реже периода запуска команд vacuum и cluster. Если Вы запускаете указанные команды ежедневно и в течении суток обновляется не более половины подтаблиц (не имеет значения, одни и те же или разные), кластеризация будет Вам полезна. Для тех таблиц, где данные заливаются не интерактивно, имеет смысл проводить кластеризацию сразу после загрузки данных.

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

select * from test where value=10 limit 1;

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

select * from test where value=10 limit 10;

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

Обычно целью кластеризации является устранение самого узкого места в рабочей системе и этим занимаются непосредственно перед настройкой параметров PostgreSQL для обеспечения максимальной производительности.

Из собственного опыта могу сказать, что описанным выше способом удалось ускорить построение определенного вида отчетов в 20 или 30 раз при выборке порядка 0,1% - 1% записей из таблицы с общим числом записей порядка 1 миллиона (одна из подтаблиц разделенной таблицы). Медленная выборка была связана именно с тем, что выбирались записи, разбросанные в файле таблицы на диске. Размер таблицы на диске составлял порядка 100 Мб и еще вдвое больше места занимали индексы.
Я разбиваю таблицы по месяцам, так как в месяц у меня планируется порядка 10 миллионов записей. Остальное довожу до ума кластеризацией. Если в месяц данных значительно больше, одним из вариантов оптимизации будет создание таблиц функционалов и работа с ними. Обычно таблицы функционалов содержат на один-два порядка меньше записей и использование для них техник разделения таблиц и кластеризации оказывается достаточным для достижения желаемой производительности. В случае, когда результат не удовлетворяет, стоит подумать о создании функционалов второго порядка (функционалов для уже вычисленных функционалов) и так далее. В настоящий момент в одной из моих систем совместно применяются функционалы первого и второго порядка, а к исходным данным используется только один вид запросов - выборка по идентификатору объекта и дате, который выполняется быстро за счет разделения таблицы по месяцам и кластеризации по индексу на идентификатор объекта.

Структура таблицы следующая:

CREATE TABLE data."out"
(
save_date timestamp NOT NULL DEFAULT now(),
user_id int4 NOT NULL DEFAULT 0,
object_id int8 NOT NULL,
question_id int8 NOT NULL,
answer_id int8 NOT NULL,
text text NOT NULL
)
WITHOUT OIDS;


Инструкция "WITHOUT OIDS" позволяет ускорить вставку данных в таблицу, так как вычисление очередных элементов последовательности oid требует определенных ресурсов.

На подобной таблице при определенном объеме данных выборка по полю object_id или любому другому существенно замедляется из-за фрагментации данных на жестком диске. В моем случае предварительно вычисленные функционалы сохраняются в других таблицах, но везде ключевыми полями являются идентификатор объекта и дата. Таким образом, при построении итоговых отчетов доступ к таблице с исходными данными не требуется, а для детальных отчетов нужен единственный тип выборки - по идентификатору объекта за указанную дату или интервал дат. Для оптимизации выборки создаю разделенные таблицы по полю save_date и кластеризую каждую из них по полю object_id.

Вот такой командой кластеризуется родительская таблица (если ее не разделили)

cluster out_object_id_idx on data.out;


А после создания разделенных таблиц можно их все кластеризовать таким вот скриптиком:

#!/usr/bin/tclsh
set pg_host xxx
set pg_dbname xxx
set pg_user xxx
set pg_port xxx
set pg_cluster 8.1/xxx
# clustering tables
foreach yy "06 07" {
foreach mm "01 02 03 04 05 06 07 08 09 10 11 12" {
puts "Кластеризация по индексу data.out_yy${yy}mm${mm}_object_id_idx"
catch {eval "exec /usr/bin/psql --cluster $pg_cluster -h $pg_host \
-p $pg_port -U $pg_user -d $pg_dbname -c \
\"cluster out_yy${yy}mm${mm}_object_id_idx on data.out_yy${yy}mm${mm}\""}
}
}


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

cluster;

без аргументов.

Для AOLServer можно использовать следующую функцию для проведения регулярной кластеризации:

proc sheduler_cluster_table table {
ns_log Notice "START sheduled proc sheduler_cluster_table with arg '$table'"
set dbc [ns_db gethandle]
if {$table eq {*}} {
ns_db dml $dbc "CLUSTER"
} else {
ns_db dml $dbc "CLUSTER $table"
}
ns_db releasehandle $dbc
ns_log Notice "END sheduled proc sheduler_cluster_table with arg '$table'"
}

ns_schedule_daily -thread 3 30 sheduler_cluster_table *


Команда ns_schedule_daily устанавливает периодичность запуска функции sheduler_cluster_table с аргументом "*" ежедневно в 3 часа 30 минут. Можно воспользоваться и посторонним шедулером, например, cron. Но в случае внешнего шедулера придется лишний раз указывать параметры базы данных (хост, кластер, порт, имя пользователя, имя базы данных), а в случае встроенного в AOLServer планировщика все параметры указываются единственный раз при настройке пула подключений к базе данных.

Comments

Popular posts from this blog

Открытый софт для научных расчетов

Счетчики в SQLite

Обработка email-сообщений