UTM5 и аццкая БД

Posted: 2011-10-21 in IT
Метки:

ISP, биллинг, БД на 20 Гб и практически не работающие внутренние отчеты. Точнее работающие, но очень уж долго. Страшно ? Нет ?! Ну ладно, читаем дальше. …

Итак, картина маслом — биллинговая система, стоит на фряхе, СУБД — MySQL, унутре — две гигантские таблицы, содержащие по 60 и 100 млн. записей соответственно. Размеры таблиц тоже не кислые — 8 и 11 Гб соответственно. Размер остальных таблиц БД на фоне этих двух несущественен. При детальном осмотре поциента обнаружилось, что некая добрая душа когда-то в незапамятные времена снесла с этих таблиц все индексы, оставив только первичный ключ PRIMARY(`id`). Итог — построение практически любого отчета приводило к фуллсканам этих чудовищных таблиц, в результате чего элементарные отчеты строились минут по 40, а отчеты посложнее было не построить вообще.

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

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

Кроме того, я считаю, что если скрипт обрабатывает только SQL-ную базу, то и написан он должен быть целиком на SQL, а не на Perl/Python или Bas. И уж тем более не на VBS/PowerShell/СMD и прочей непортабельной нечисти (хотя мне трудно представить сумасшедших, кто рискнет поднять биллинг даже на самой серверастой виндоффс).

Попытка построить индекс "в лоб" командой ALTER TABLE … ADD INDEX тоже не порадовала — БД надолго уходила в себя (на P4-3GHz/4Gb RAM), а тестовый биллинг начинал жутко срать кирпичами. Вообщем, оно не уложилось в сутки. Поэтому единственный путь — переименование таблиц и создание новых по шаблону.
В итоге был написан такой SQL-скрипт:

USE UTM5;   -- База биллинга UTM-5 сборка .009          // 2011-09-06, Amin

-- Дата (YYYY-MM-DD), до которой включительно будут архивированы данные
    SELECT CONCAT('-- Start script: ', CURRENT_DATE(), ' ', CURRENT_TIME());
SET @CUTDATE=replace(CURRENT_DATE(),"-","");   -- для таблиц - убрать тире из имён

-- Определим начальное время для каждой части архива
    SELECT CONCAT('   Search MIN() dates started... ', CURRENT_DATE(), ' ', CURRENT_TIME());
SELECT @bd1 := MIN(discount_date) , CURRENT_DATE(), ' ', CURRENT_TIME() FROM discount_transactions_all ;
SELECT @bd2 := MIN(discount_date) , CURRENT_DATE(), ' ', CURRENT_TIME() FROM discount_transactions_iptraffic_all ;
SELECT @bd3 := MIN(actual_date) , CURRENT_DATE(), ' ', CURRENT_TIME() FROM payment_transactions ;

-- Старые большие таблицы просто переименуем
  SELECT CONCAT('   Rename BIG Tables: ', CURRENT_DATE(), ' ', CURRENT_TIME());

SET @q1 = CONCAT("ALTER TABLE discount_transactions_all RENAME discount_transactions_all_", @CUTDATE);
SET @q2 = CONCAT("ALTER TABLE discount_transactions_iptraffic_all RENAME discount_transactions_iptraffic_all_", @CUTDATE);
SET @q3 = CONCAT("ALTER TABLE payment_transactions RENAME payment_transactions_", @CUTDATE);

PREPARE q1 FROM @q1;
EXECUTE q1;
    SELECT CONCAT('     T1 Renamed ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q2 FROM @q2;
EXECUTE q2;
    SELECT CONCAT('     T2 Renamed ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q3 FROM @q3;
EXECUTE q3;
    SELECT CONCAT('     T3 Renamed ', CURRENT_DATE(), ' ', CURRENT_TIME());

-- Определим конечное время
SET @edts = UNIX_TIMESTAMP(CONCAT( CURRENT_DATE(), " ", CURRENT_TIME() ) );

-- Создадим новые таблицы только для новых данных

    SELECT CONCAT('   Create NEW tables ... ', CURRENT_DATE(), ' ', CURRENT_TIME());
SET @q11 = CONCAT("CREATE TABLE discount_transactions_all LIKE discount_transactions_all_", @CUTDATE);
SET @q21 = CONCAT("CREATE TABLE discount_transactions_iptraffic_all LIKE discount_transactions_iptraffic_all_", @CUTDATE);
SET @q31 = CONCAT("CREATE TABLE payment_transactions LIKE payment_transactions_", @CUTDATE);

PREPARE q11 FROM @q11;
EXECUTE q11;
    SELECT CONCAT('     New T1 created ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q21 FROM @q21;
EXECUTE q21;
    SELECT CONCAT('     New T2 created ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q31 FROM @q31;
EXECUTE q31;
    SELECT CONCAT('     New T3 created ', CURRENT_DATE(), ' ', CURRENT_TIME());

-- Search AUTOINCREMENT

    SELECT CONCAT('   Search AUTOINCREMENT ... ', CURRENT_DATE(), ' ', CURRENT_TIME());
SET @q111 = CONCAT("SELECT @ai1 := MAX(id)+10 FROM discount_transactions_all_", @CUTDATE);
SET @q211 = CONCAT("SELECT @ai2 := MAX(id)+10 FROM discount_transactions_iptraffic_all_", @CUTDATE);
SET @q311 = CONCAT("SELECT @ai3 := MAX(id)+10 FROM payment_transactions_", @CUTDATE);

PREPARE q111 FROM @q111;
EXECUTE q111;
    SELECT CONCAT('     T1 AutoInc Found ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q211 FROM @q211;
EXECUTE q211;
    SELECT CONCAT('     T2 AutoInc Found ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q311 FROM @q311;
EXECUTE q311;
    SELECT CONCAT('     T3 AutoInc Found ', CURRENT_DATE(), ' ', CURRENT_TIME());

-- Fix AUTOINCREMENT
  SELECT CONCAT('   Fix AUTOINCREMENT : ', CURRENT_DATE(), ' ', CURRENT_TIME());

SET @q10 = CONCAT("ALTER TABLE discount_transactions_all AUTO_INCREMENT=", @ai1);
SET @q20 = CONCAT("ALTER TABLE discount_transactions_iptraffic_all AUTO_INCREMENT=", @ai2);
SET @q30 = CONCAT("ALTER TABLE payment_transactions AUTO_INCREMENT=", @ai3);

PREPARE q10 FROM @q10;
EXECUTE q10;
    SELECT CONCAT('     T1 AutoInc Fixed  ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q20 FROM @q20;
EXECUTE q20;
    SELECT CONCAT('     T2 AutoInc Fixed ', CURRENT_DATE(), ' ', CURRENT_TIME());

PREPARE q30 FROM @q30;
EXECUTE q30;
    SELECT CONCAT('     T3 AutoInc Fixed ', CURRENT_DATE(), ' ', CURRENT_TIME());


-- Создадим записи в таблице архивов
    SELECT CONCAT('   Make archive records ...', CURRENT_DATE(), ' ', CURRENT_TIME());
SET @q13 = CONCAT("INSERT INTO archives (archive_id, table_type, table_name, start_date, end_date) VALUES ('", @edts, "', '1', 'discount_transactions_all_", @CUTDATE,"', '", @bd1, "', '", @edts, "')");
SET @q23 = CONCAT("INSERT INTO archives (archive_id, table_type, table_name, start_date, end_date) VALUES ('", @edts, "', '2', 'discount_transactions_iptraffic_all_", @CUTDATE,"', '", @bd2, "', '", @edts, "')");
SET @q33 = CONCAT("INSERT INTO archives (archive_id, table_type, table_name, start_date, end_date) VALUES ('", @edts, "', '7', 'payment_transactions_", @CUTDATE,"', '", @bd3, "', '", @edts, "')");

PREPARE q13 FROM @q13;
EXECUTE q13;

PREPARE q23 FROM @q23;
EXECUTE q23;

PREPARE q33 FROM @q33;
EXECUTE q33;

    SELECT CONCAT('     Archive records created. ', CURRENT_DATE(), ' ', CURRENT_TIME());

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

Итак, у нас есть почти пустая таблица TABLE (с индексами) и ее архив с момента создания по сегодняшнее число TABLE_date (без индексов, ибо получена переименованием старой кривой таблицы).
Мой совет — не пытайтесь "в лоб" построить индекс на архивной таблице с сотней миллионов записей — ваш сервер просто умрет в страшных муках, подавившись памятью, а попытка построения отчета приведёт к подвисаниям интерфейса пользователя. Делаем аналогично — создаем "хорошую архивную" таблицу вида TABLE_date_NEW той же структуры, что и в архиве (CREATE TABLE TABLE_date_NEW LIKE TABLE_date), меняем тип хранилища на что-нить легковесное (хоть MyISAM, писать туда уже никто не будет) создаем там индекс по дате, после чего переливаем данные с помощью INSERT INTO TABLE_date_NEW SELECT * FROM TABLE_date. Потом по завершении переноса данных переименовываем сперва TABLE_date в TABLE_date_BAD_INDEX, потом TABLE_date_NEW просто в TABLE_date, той же командой ALTER TABLE … RENAME. Это лучше заскриптовать и запустить вечером, за сутки как раз сделается. Смысл — чтобы не останавливать биллинг надолго и чтобы не мешать работе других пользователей. В отличии от ALTER TABLE … ADD INDEX, тут ничего надолго не блокируется и работе не мешает.
С утра проверяем, что все данные перенеслись, и что теперь отчеты строятся не 40 минут, а секунд 10-15. Если все ОК — через недельку смело дропаем TABLE_date_BAD_INDEX.

Замечания:
— На таблицах с индексом по дате скрипт отрабатывает почти мгновенно.
— Можно запускать скрипт скажем раз в год, архивы будут создаваться корректно при повторных запусках.
— Слишком мелкие архивы (например, за неделю) смысла не имеют. Много мелких таблиц, соединение их пачкой — зло, да и неудобно. ИМХО, делать подобное раз в год — оптимально.
— Если индексы уже есть в исходной таблице, то и в копии они тоже будут. Поэтому если у вас с индексами все хорошо — пользуйте скрипт как есть и индексы вообще не трогайте.
— Бэкап конечно же обязателен.
— Не доводите базы до такого состояния, в котором даже простые операции превращаются в лютый пиздец.
— Архивирование в середине года никаких проблем не представляет — при построении отчетов за любой период биллинг сам автоматически подключит нужные части архива.
— Для удаления старых данных достаточно будет удалить строки из таблицы архивов и дропнуть старые таблицы, что займет секунд 5 и не потребует остановки биллинга. (догадайтесь, к чему приведет вот такой вот дурной совет на доставшейся мне в исходном состоянии БД).

- комментарии
  1. gregzakharov:

    Perl для обработки SQL — все равно что серпом по яйцам. про биллинг на серверной винде подмечено верно, я бы даже сказал аксиоматично 😉