Главная

Как я писал свой «велосипед» для ротирования таблиц в Oracle и Postgre

2014-10-11 в 14:24, admin, рубрики: oracle, postgresql

Приветствую!

В этой статье я расскажу о том, как мы боролись с проблемой быстрого роста размера таблиц в базе данных в высоконагруженной EMS системе. Свою изюминку добавляет то, что проблема решалась для двух баз данных: Oracle и Postgre. Заинтересовавшихся прошу под кат.

Начальные условия

Итак, есть некая EMS-система, которая получает и обрабатывает сообщения от сетевых элементов. Запись о каждом сообщении заносится в таблицы базы данных. По требованиям заказчика количество приходящих сообщений (и, соответственно, количество записей в таблицы) составляет в среднем 100 в секунду, при этом пиковая нагрузка может возрастать до 1500.
Нетрудно посчитать, что за день в среднем набирается более 8 миллионов записей в таблицу. Проблема появилась, когда выяснилось, что при объемах данных более 20 млн. строк некоторые запросы системы начинают тормозить и выходить за пределы времени работы, запрашиваемого заказчиком.

Задача

Таким образом, нужно было придумать, что делать c данными, чтобы и информация никакая не терялась и запросы работали быстро. При этом изначально система работала на Postgre, но в ближайшее время планировался переход на Oracle и хотелось, чтобы при переходе было минимум проблем с переносом функционала.
Вариант использования partitioning отпал сразу, т.к. было известно, что Oracle Partitioning точно не будет входить в лицензию, да и переделывать с одного партишнинга на другой не очень хотелось, поэтому стали думать над реализацией какого-то своего велосипеда.
Существенно облегчило задачу то, что логи старше пары дней не нужны для отображения в системе, т.к. для расследования подавляющего большинства проблем должно было хватить сообщений двухдневной давности. Но хранить их «на всякий пожарный» все же нужно. Тут-то и родилась идея реализовать процедуры для периодического «ротирования» данных в таблицах, т.е. переноса их из таблиц для отображения в некие исторические таблицы.

Решение и реализация

Было решено держать 2 таблицы с наиболее актуальными данными для отображения (назовем их table — основная и table_secondary — дополнительная). На эти две таблицы навешено представление table_view из которого брались данные для отображения: оно нужно, чтобы после момента переноса данных в UI резко не пропадали все записи. Более старые записи переносятся в исторические таблицы с именами типа H$table_NUM, где NUM — номер исторической таблицы (чем данные в ней старше тем номер выше). Исторические таблицы, дабы не засорять основной tablespace, также периодически перетаскиваются в «холодный» tablespace, таблицы которого можно хранить на медленных дисках. Операция это, вообще говоря, тяжелая, поэтому делается реже отдельной процедурой. Кроме того эта же процедура удаляет из «холодного» tablespace слишком старые таблицы.
Касательно того, как именно производится перенос данных: из-за большого количества индексов на таблицах перенос непосредственно записей с помощью insert`а работал медленно, поэтому был выбран подход с переименовыванием таблиц и пересозданием индексов и триггеров.
Схематически работа процедур представлена на рисунке:

Итак, алгоритм работы получился примерно такой (алгоритм и примеры кода процедур привожу для oracle, для postgre сможете посмотреть на github):
Процедура rotate_table(primary_table_name). Выполняется, скажем, каждый час.

Проверяем, что количество строк в основной таблице превысило некоторый лимит;

Проверяем, что существует «холодный» tablespace:

SELECT COUNT(*) INTO if_cold_ts_exists FROM USER_TABLESPACES WHERE tablespace_name = 'EMS_HISTORICAL_DATA';

Создаем вспомогательную пустую таблицу new_table на основе текущей основной таблице. Для этого в postgre есть удобный функционал CREATE TABLE… (LIKE… INCLUDING ALL), для Oracle же пришлось писать свой аналог — процедуру create_tbl_like_including_all(primary_table_name, new_table_name, new_idx_trg_postfix, new_idx_trg_prefix), которая создает аналогичную пустую таблицу:

SELECT
	replace(dbms_metadata.get_ddl('TABLE', primary_table_name), primary_table_name, new_table_name)
INTO ddl_query
FROM dual;
ddl_query := substr(ddl_query, 1, length(ddl_query) - 1);
EXECUTE IMMEDIATE ddl_query;

А также триггеры и индексы к ней:

FOR idx IN (SELECT idxs.index_name FROM user_indexes idxs WHERE idxs.table_name = primary_table_name)
LOOP
	ddl_query := REPLACE(
		REPLACE(dbms_metadata.get_ddl('INDEX', idx.index_name), primary_table_name, new_table_name),
		idx.index_name, new_idx_trg_prefix || idx.index_name || new_idx_trg_postfix);
	ddl_query := substr(ddl_query, 1, length(ddl_query) - 1);
	EXECUTE IMMEDIATE ddl_query;
END LOOP;

Переименовываем таблицы:

EXECUTE IMMEDIATE 'alter table ' || secondary_table_name || ' rename to ' || history_table_name;
EXECUTE IMMEDIATE 'alter table ' || primary_table_name || ' rename to ' || secondary_table_name;
EXECUTE IMMEDIATE 'alter table ' || new_table_name || ' rename to ' || primary_table_name;

Переименовываем триггеры и индексы для них;
Если холодный tablespace не существует, то считаем, что исторические данные хранить не нужно, и удаляем соответствующую таблицу:
```
EXECUTE IMMEDIATE 'drop table ' || history_table_name || ' cascade CONSTRAINTS';
```
Выполняем ребилд представления (только для oracle):
```
EXECUTE IMMEDIATE 'select * from ' || view_name || ' where 1=0';
```

Процедура move_history_logs_to_cold_ts(primary_table_name). Выполняется, например, раз в день.

Если «холодный» tablespace существует, ищем все исторические таблицы, которые не находятся в этом tablespace:

EXECUTE IMMEDIATE
'select
	table_name
from
	user_tables
where
	table_name like ''' || history_table_pattern || '''
	and (tablespace_name != ''EMS_HISTORICAL_DATA'' or tablespace_name is null)'
BULK COLLECT INTO history_tables;

Перемещаем каждую таблицу в «холодный» tablespace:

EXECUTE IMMEDIATE 'ALTER TABLE ' || history_tables(i) || ' MOVE TABLESPACE ems_historical_data';

Удаляем для перемещенных таблиц триггеры и индексы;
Выполняем удаление слишком старых таблиц из «холодного» tablespace.

Запуск процедур по расписанию делался с помощью Quartz Sheduler в случае Postgre, и с помощью Oracle Scheduler в случае Oracle, скрипты для конфигурации которого также есть в исходниках.