Amin 's Blog

Итак, картина маслом - биллинговая система, стоит на фряхе, СУБД - 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 и не потребует остановки биллинга. (догадайтесь, к чему приведет вот такой вот дурной совет на доставшейся мне в исходном состоянии БД).