Оптимизация базы данных

[avegad]

UTM5.2.0-002
Mysql: 4.1.20
База: в MyISAM

Возник вопрос по оптимизации базы данных в связи с:
1. большим объемом > 12GB
2. При большой нагрузке на сервер, обычно вечером с 18 до 24 медленно идёт авторизация("проверка имени и пароля" у абонента может висеть до 30 сек)

В аське Magnum72 мне ответил, что оптимизацию через админку делать нельзя ни в коем случае.

Вопрос:
Так каким же образом её всё таки делать?

[Magnum72]

Вопрос:
Так каким же образом её всё таки делать?

Смысл такой та оптимизация кривая да и тебе не поможет..
В принципе тебе может в будущем помочь фича "Архивные таблицы" которыя появилась в 006 версии, в будущем потому что сейчас действие этой фичи не распостараняется на таблицу dhs_sessions_log
но обещали что сделают..
Но тебе ничего не мешает уже счас переместить старые данные из этой таблицы куда нибудь чтобы потом сделать все как положено.

Тормоза собственно из-за большого кол-ва записей в этой таблице, у меня стоит не родной радиус поэтому точно не ручаюсь за правильность далееизложенного, но помоему оригинальный радиус при открытии сессии туда вставляет запись об открытии, а при закрытии или изменении апдейтит эту запись, вот именнно на апдейте идут конкретные тормоза...

[avegad]

Ошибочка вышла: база в innodb бо в директории /var/lib/mysql/ где лежат базы данных имеется файл ibdata1 весом 12.2 гига.

В таблице dhs_sessions_log
800657 записей за 3 года(уже 4 пошел)

Поможет ли если я грохну записи за 2.5 года?

И ещё вопрос:
Есть таблица discount_transactions_iptraffic_all трафиком за каждую сессию пользователя за тот же период.

Может и в ней убить/переместить записи за последние 2.5 года?

[Magnum72]

Ошибочка вышла: база в innodb бо в директории /var/lib/mysql/ где лежат базы данных имеется файл ibdata1 весом 12.2 гига.

В таблице dhs_sessions_log
800657 записей за 3 года(уже 4 пошел)

Поможет ли если я грохну записи за 2.5 года?

И ещё вопрос:
Есть таблица discount_transactions_iptraffic_all трафиком за каждую сессию пользователя за тот же период.

Может и в ней убить/переместить записи за последние 2.5 года?

Зачем убивать когда есть возможность сохранить и по запросу работать с этими сохранненными данными из биллинга?

[avegad]

Зачем убивать когда есть возможность сохранить и по запросу работать с этими сохранненными данными из биллинга?

1. А запрос на перемещение можете подсказать?
2. Биллинг тормозить надо будет в этот момент или на живом и работающем прокатит?

[mikkey finn]

использовать архивирование списаний(тема на форуме с решениями), но добавить к архивированию еще и эту табличку.
единственное НО: не записывать в таблицу со списком архивов ничего, потом заполнить.

[avegad]

использовать архивирование списаний(тема на форуме с решениями), но добавить к архивированию еще и эту табличку.
единственное НО: не записывать в таблицу со списком архивов ничего, потом заполнить.

А по второму вопросу что можете сказать?

[mikkey finn]

в mysql - необязательно. ренейм происходит очень быстро для таблиц, создание - тоже.

[kaN5300]

Вот фрагмент из моего старенького фака с заметками:

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

sudo /netup/utm5/bin/safe_utm5_core stop

mysql> TRUNCATE special_transactions;
Query OK, 21947595 rows affected (0.23 sec)

mysql> optimize table special_transactions;

Для таблиц InnoDB эффект дефрагментации достигается путём перевода таблицы из InnoDB в MyISAM и обратно. Пример:

mysql> ALTER TABLE special_transactions TYPE = MyISAM;
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE special_transactions TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

Остается почистить discount_transactions_all и discount_transactions_iptraffic_all. Для этого необходимо выбрать дату, ранее которой вся инфа из этих таблиц будет удалена. Соответственно возможность делать отчеты по траффику за этот период пропадёт. После удаления необходимо проделать дефраг, как это показано на примере выше.

delete from discount_transactions_all where discount_date < unix_timestamp('ваша_дата');
delete from discount_transactions_iptraffic_all < unix_timestamp('ваша_дата');

Формат "ваша_дата" такой: 2006-04-01.

Можно сделать предварительное ручное преобразование в юниксформат вот так:

mysql> select unix_timestamp('2006-04-01');

Удаляем всю инфу, ранее апреля 2006 года:

mysql> delete from discount_transactions_all where discount_date < unix_timestamp('2006-04-01');
Query OK, 1104335 rows affected (27 min 30.55 sec)

mysql> delete from discount_transactions_iptraffic_all where discount_date < unix_timestamp('2006-04-01');
Query OK, 610569 rows affected (11 min 2.30 sec)

И после этого дефраг. Говорят помогает еще дамп и рестор всей базы. Но это ИМХО значитально дольше по времени.

Остается сделать рестарт мускула и запустить utm5_core.

Цитата из списка изменений по версии 5.2.1-001:

Для ускорения работы базы данных по умоланию отключена запись в таблицы special_transactions, dhs_sessions_log_attrs, dhs_access_log_attrs, tel_sessions_log_attrs. Для включения записи в эти таблицы необходимо в настроки биллинговой системы добавить параметры: special_write, dialup_attrs_write, access_attrs_write, tel_attrs_write. Mantis ID 494

[avegad]

Подготовил бекапы таблиц:
discount_transactions_all
discount_transactions_iptraffic_all
dhs_sessions_log

На всякий случай если что то пойдет не так.
Собираюсь их очистить за 2.5 года.

Есть вопрос по таблице dhs_sessions_log
дату для периода удаления из какого поля брать
recv_date или last_update_date?

[zooxel]

Неужели разработчикам было трудно добавить функционал архивирования записей в администраторский интерфейс.

Такую вот "кастрированную" политику с их стороны уже наблюдаю в течение 4 лет и до сих пор не могу понять в чем ее смысл.
По началу думал, что компания хочет зарабатывать на выпуске индивидуальных костылей для биллинга, но понял, что ошибался, т.к. все попытки получить что-либо за деньги заканчивались сливом или фразами, что им некогда либо невыгодно этим заниматься.

Может кто-то раскрыл этот секрет?
Или может быть получим комментарий от разработчиков?!

P.S. Вместо того, чтобы дописать этот функционал(пусть даже за деньги) и предложить его своим клиентам, форум забит сообщениями отчаянных админов, которые не совсем понимают или хотят этим заниматься. Сам бы с удовольствием заплатил за данный функционал в админке или пусть даже отдельным скриптом.

[mikkey finn]

есть предположение, что они подсматривают за крупными инсталляциями типа Олимпус. Там реализуют что-то, чего не хватает. Служба мониторинга биллинга замечает, что пошла нетиповая работа с ядром/базой, начинают нюхать... Вынюхивают смысл работы замечают полезный результат, реализуют феньку. Как ею пользоваться - сами не понимают, что это такое - представляют в самых общих чертах. потому им невыгодно или некогда делать то, что нам надо.

[zooxel]

Потому им невыгодно или некогда делать то, что нам надо.

Грустно, но это действительно так.

[kaN5300]

Подготовил бекапы таблиц:
discount_transactions_all
discount_transactions_iptraffic_all
dhs_sessions_log

На всякий случай если что то пойдет не так.
Собираюсь их очистить за 2.5 года.

Есть вопрос по таблице dhs_sessions_log
дату для периода удаления из какого поля брать
recv_date или last_update_date?

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