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

Руководство по настройке плагина партицирования

Для начала отключите стандартную ротацию логов

$conf{USE_PARTITIONING}=1

Ручной запуск, с выводом допинформации на экран

/usr/abills/libexec/billd partitioning DEBUG=1

Плагин инициализирует партиционизацию таблиц и обеспечивает чистку. Плагин нужно запускать один раз в день через крон.

  1 3 * * * root /usr/abills/libexec/billd partitioning

Раздел "Для чайников": Как это работает и зачем нужно?

1. В чем проблема сейчас?

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

Когда вы хотите найти запись за "вчера", системе приходится листать весь этот толстый журнал.
Это медленно.
Журнал занимает много места в оперативной памяти ("на столе").

2. Что делает плагин (Партицирование)?

Плагин берет этот толстый журнал и разрезает его на тоненькие тетрадки (партиции).

Каждая тетрадка — это один месяц (или день).
Все эти тетрадки лежат в одной папке и подписаны: "Январь 2024", "Февраль 2024" и т.д.
Для вашей программы (биллинга) ничего не меняется: она думает, что это всё ещё один большой журнал.

3. Как это ускоряет работу?

Когда вы просите "Покажи платежи за сегодня":

Система **не трогает** старые пыльные тетрадки за 2015 год.
Она сразу берет только **одну** тонкую тетрадку за текущий месяц.
Это происходит мгновенно, потому что тетрадка маленькая, и она легко помещается в память.

4. Где хранятся данные?

Физически: Данные лежат в разных файлах на диске (партициях).
Количество: Будет создано примерно 120 партиций (тетрадок) за последние 10 лет. Каждый месяц плагин автоматически создает новую пустую тетрадку для будущего месяца.

5. Что мы настроили (Safe Mode / Симлинк)?

Мы сказали плагину:

"Разрежь журнал на тетрадки, чтобы работать со свежими данными быстро".
"Но не выбрасывай старые тетрадки 10 лет".
"И не разрывай связи (скрепки), которыми записи в журнале прикреплены к другим документам".

Теперь у вас есть скорость работы с "сегодняшним днем" и полная сохранность архива за 10 лет.

6. ЧАВО: В чем разница между "Периодом" и "Сроком хранения"?

Чтобы не путаться в цифрах (почему 3650?):

Период (Period): Это как часто мы заводим новую тетрадку.
- Пример: "Каждый день" или "Каждый месяц". Это нужно для скорости.
Срок хранения (History/Retention): Это когда мы выбрасываем старые тетрадки в мусорку.
- Пример: "Через 10 лет".
Настройка: Мы ставим 3650 дней (10 лет), чтобы плагин только заводил новые тетрадки, но ничего не выбрасывал.

Техническая конфигурация

Чтобы включить партицирование для определенных групп таблиц, добавьте следующие параметры в файл `config.pl`.

Значение параметра указывает количество дней или месяцев для хранения истории.

Важно: По умолчанию мы установили хранение истории 10 лет (3650 дней или 120 месяцев), чтобы ничего не удалялось.

Категории конфигурации

1. Логи трафика

$conf{PARTITIONING_TRAFFIC}

Таблицы:

ipn_traf_detail (День, По умолчанию: 3650 дней / 10 лет)
s_detail (День, По умолчанию: 3650 дней / 10 лет)
ipn_log (День, По умолчанию: 3650 дней / 10 лет)

Пример: `$conf{PARTITIONING_TRAFFIC} = 3650;`

2. Логи администраторов

$conf{PARTITIONING_ADMIN}

Таблицы:

admins_full_log (День, По умолчанию: 3650 дней / 10 лет)
errors_log (День, По умолчанию: 3650 дней / 10 лет)
admin_actions (Месяц, По умолчанию: 120 мес / 10 лет)
admin_system_actions (Месяц, По умолчанию: 120 мес / 10 лет)

Пример: `$conf{PARTITIONING_ADMIN} = 3650;`

3. Логи оборудования

$conf{PARTITIONING_EQUIPMENT}

Таблицы:

equipment_mac_log (День, По умолчанию: 3650 дней / 10 лет)
equipment_ping_log (День, По умолчанию: 3650 дней / 10 лет)

Пример: `$conf{PARTITIONING_EQUIPMENT} = 3650;`

4. Логи сообщений

$conf{PARTITIONING_MESSAGES}

Таблицы:

sms_log (Месяц, По умолчанию: 120 мес / 10 лет)
msgs_messages (Месяц, По умолчанию: 120 мес / 10 лет)
msgs_reply (Месяц, По умолчанию: 120 мес / 10 лет)
msgs_delivery_users (Месяц, По умолчанию: 120 мес / 10 лет)
ureports_log (Месяц, По умолчанию: 120 мес / 10 лет)

Пример: `$conf{PARTITIONING_MESSAGES} = 120;`

5. Финансовые логи (Safe Mode / Симлинк)

$conf{PARTITIONING_FINANCE}

Рекомендуемая настройка:

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

$conf{PARTITIONING_FINANCE} = 120; # Хранить 10 лет
$conf{PARTITIONING_PRESERVE_FK} = 1; # НЕ удалять связи (работает как симлинк)

Таблицы:

payments (Месяц, По умолчанию: 120 мес / 10 лет)
fees (Месяц, По умолчанию: 120 мес / 10 лет)
paysys_log (Месяц, По умолчанию: 120 мес / 10 лет)
extfin_balance_reports (Месяц, По умолчанию: 120 мес / 10 лет)

6. Логи пользователей

$conf{PARTITIONING_USERS}

Таблицы:

users_development (Месяц, По умолчанию: 120 мес / 10 лет)

Пример: `$conf{PARTITIONING_USERS} = 120;`

Детальный анализ рисков (Архив)

Финансовые логи и внешние ключи

Внимание: Если вы НЕ используете опцию `$conf{PARTITIONING_PRESERVE_FK} = 1`, то партицирование удалит внешние ключи.

7. Логи сообщений (Дополнительные риски)

Внимание: Таблица `msgs_messages` также имеет внешние ключи.

Анализ:

Таблица `msgs_messages` является родительской для других таблиц (например, таблицы рассылок или ответов), которые ссылаются на `id` сообщения.

Последствия:

При включении партицирования для `msgs_messages` ($conf{PARTITIONING_MESSAGES}), плагин удалит внешние ключи, связывающие её с зависимыми таблицами.
Это необходимо для ротации (удаления) старых сообщений.
Риск минимален, так как сообщения обычно устаревают одновременно, но теоретически возможны "сироты" в связанных таблицах, если удаление происходит несинхронно.

Вариант Г: "Симлинк" - Партицирование без удаления ключей (Safe Mode)

Суть: Это ответ на вопрос "Можно ли реализовать это аналогами симлинка?".
Как это работает: Вы включаете специальную опцию `$conf{PARTITIONING_PRESERVE_FK} = 1;`. Плагин не удаляет внешние ключи (Foreign Keys).
Результат:
- Таблицы (`payments`, `fees`) физически делятся на части (партиции) по месяцам.
- Логически это одна таблица, поэтому все ссылки из документов (`docs_receipts`) работают и не ломаются.
- MySQL сама выступает "менеджером симлинков", направляя запросы в нужную партицию.
Важное условие: Вы должны установить огромный срок хранения истории (например, 10-20 лет), чтобы плагин не пытался удалить старые данные (так как удаление старых данных, на которые есть ссылки, вызовет ошибку базы данных).

Настройка для режима "Симлинк":

$conf{PARTITIONING_FINANCE} = 240; # 20 лет истории (удаление отключено фактически)
$conf{PARTITIONING_PRESERVE_FK} = 1; # Запрет на удаление Foreign Keys

Итог:

Свежие данные (последние месяцы) летают (попадают в кэш, маленькие индексы), старые данные лежат мертвым грузом в старых партициях, но доступны, и целостность ссылок 100% сохранена.

Расширенная конфигурация

Вы также можете определить конкретные таблицы вручную, используя `$conf{PARTITIONING_TABLES}`:

$conf{PARTITIONING_TABLES} = {
  'table_name' => { 'period' => 'day', 'keep_history' => '30', 'main_field' => 'date_field' }
};

Нюансы работы

Перед началом партиционизации таблиц, приведите контрольные поля к правильному формату

ALTER TABLE `ipn_traf_detail` CHANGE COLUMN s_time s_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `ipn_traf_detail` CHANGE COLUMN f_time f_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `s_detail` CHANGE COLUMN start start TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `errors_log` CHANGE COLUMN date date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `internet_log` CHANGE COLUMN start start TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `fees` CHANGE COLUMN date date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `fees` ADD UNIQUE `id` (`id`, `date`);
ALTER TABLE `fees` DROP PRIMARY KEY;
ALTER TABLE `fees` ADD PRIMARY KEY (`id`, `date`);

ALTER TABLE `payments` CHANGE COLUMN date date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `payments` ADD UNIQUE `id` (`id`, `date`);
ALTER TABLE `payments` DROP PRIMARY KEY;
ALTER TABLE `payments` ADD PRIMARY KEY (`id`, `date`);

ALTER TABLE `fees` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, date);
ALTER TABLE `payments` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, date);
ALTER TABLE `errors_log` ADD COLUMN `id` INT AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id, date);
ALTER TABLE `internet_log` ADD COLUMN `id` INT AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id, start);
ALTER TABLE `ipn_traf_detail` ADD COLUMN `id` INT AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id, s_time);

# также нужно удалить форейн ключи в таблица docs_receipts docs_invoice2payments

SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    CONSTRAINT_NAME, 
    REFERENCED_TABLE_NAME, 
    REFERENCED_COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'docs_receipts'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

=========================================================

SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    CONSTRAINT_NAME, 
    REFERENCED_TABLE_NAME, 
    REFERENCED_COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'docs_invoice2payments'
    AND REFERENCED_TABLE_NAME IS NOT NULL;


Это мы получили названия внешних ключей, далее:

ALTER TABLE docs_receipts DROP FOREIGN KEY имя_внешнего_ключа;
ALTER TABLE docs_invoice2payments DROP FOREIGN KEY имя_внешнего_ключа;

Также если используете Api логи $conf{API_LOG}

ALTER TABLE `api_log` CHANGE COLUMN `date` `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `api_log` ADD UNIQUE `id` (`id`, `date`);
ALTER TABLE `api_log` DROP PRIMARY KEY;
ALTER TABLE `api_log` ADD PRIMARY KEY (`id`, `date`);

Для MySQL < 5.6.5

До версии 5.6.5 существовало ограничение на одну колонку с значением по умолчанию CURRENT_TIMESTAMP.

Если у вас возникает ошибка

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

и нет возможности обновить mysql-server, то вместо запроса

ALTER TABLE `ipn_traf_detail` CHANGE COLUMN f_time f_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

нужно применить

ALTER TABLE `ipn_traf_detail` CHANGE COLUMN f_time f_time TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';

Ошибки

DBD::mysql::db do failed: Table has no partition for value 1663149265 at /home/asm/abills/libexec/../Abills/mysql/dbcore.pm line 280.

Нужно пересоздать партиции

/usr/abills/libexec/billd partitioning DEBUG=1

Опции

SKIP_TABLES='fees,payments';	Не партиционировать таблицы из списка
TABLES	Выбор отдельных таблиц

Дерево страниц

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

Руководство по настройке плагина партицирования

Раздел "Для чайников": Как это работает и зачем нужно?

1. В чем проблема сейчас?

2. Что делает плагин (Партицирование)?

3. Как это ускоряет работу?

4. Где хранятся данные?

5. Что мы настроили (Safe Mode / Симлинк)?

6. ЧАВО: В чем разница между "Периодом" и "Сроком хранения"?

Техническая конфигурация

Категории конфигурации

1. Логи трафика

2. Логи администраторов

3. Логи оборудования

4. Логи сообщений

5. Финансовые логи (Safe Mode / Симлинк)

6. Логи пользователей

7. Логи сообщений (Дополнительные риски)

Настройка для режима "Симлинк":

Итог:

Нюансы работы

Перед началом партиционизации таблиц, приведите контрольные поля к правильному формату

Для MySQL < 5.6.5

Ошибки

Опции