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

Для улучшения производительности нужно использовать партиционирование таблиц.

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

$conf{USE_PARTITIONING}=1

Для ручного партиционирования есть billd плагин

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

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

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

Сейчас плагин следит за таблицами

'ipn_traf_detail'
's_detail'       
'errors_log'     
'internet_log'
'fees'
'payments'

Партиционирование финансовых таблиц включается опцией $conf{PARTITIONING_FIN}=1; (Не обязательно, включено по умолчанию)

таблиці

'fees'
'payments'

Время хранения:

Для таблиц fees и payments:

Период партиционирования: месяц ('period' => 'month')
Срок хранения: 60 месяцев (5 лет) ('keep_history' => '60')

Для других таблиц:

ipn_traf_detail, s_detail, errors_log, api_log:

Период: день ('day')

Хранение:
ipn_traf_detail, s_detail - 30 дней
errors_log - 60 дней
api_log - 14 дней
internet_log:
Период: месяц ('month')
Хранение: 60 месяцев (5 лет)

Для выбора отдельной таблицы используйте аргумент TABLES=xxx,xx,xxx

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

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: Партиционирование таблиц

Ошибки