- PVSM.RU - https://www.pvsm.ru -
Приветствую!
В этой статье я расскажу о том, как мы боролись с проблемой быстрого роста размера таблиц в базе данных в высоконагруженной 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). Выполняется, скажем, каждый час.
SELECT COUNT(*) INTO if_cold_ts_exists FROM USER_TABLESPACES WHERE tablespace_name = 'EMS_HISTORICAL_DATA';
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;
EXECUTE IMMEDIATE 'drop table ' || history_table_name || ' cascade CONSTRAINTS';
EXECUTE IMMEDIATE 'select * from ' || view_name || ' where 1=0';
Процедура move_history_logs_to_cold_ts(primary_table_name). Выполняется, например, раз в день.
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;
EXECUTE IMMEDIATE 'ALTER TABLE ' || history_tables(i) || ' MOVE TABLESPACE ems_historical_data';
Запуск процедур по расписанию делался с помощью Quartz Sheduler в случае Postgre, и с помощью Oracle Scheduler в случае Oracle, скрипты для конфигурации которого также есть в исходниках.
Полные исходники процедур и скриптов для конфигурации шедулера можно посмотреть на GitHub [1].
Спасибо за внимание!
Автор: Rhythm
Источник [2]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/71391
Ссылки в тексте:
[1] GitHub: https://github.com/SCORE1387/dbTablesRotation
[2] Источник: http://habrahabr.ru/post/240061/
Нажмите здесь для печати.