Уменьшение размера БД

Question

[vitalets]

Всем привет,

хочу получить ваш совет по следующей задаче:
есть mysql база, в которой для работы в основном используются недавно добавленные записи (за последние 1-2 месяца). После использования данные по сути просто лежат в бд, занимая место и замедляя работу.
Но удалить их нельзя, т.к. иногда (нечасто) приходится делать отчеты за длительный срок, например за год и больше.
Вопрос — как все это оптимизировать?

Вариант 1:
Создать копию бд, которая будет служить архивом и раз в неделю переносить данные из основной базы старше 2-х месяцев. Использовать sql-скрипт c выражениями вида:

insert archive.document select * from prod.document where create_date <= DATE_SUB(now(), interval 2 month);
delete * from prod.document where create_date <= DATE_SUB(now(), interval 2 month);

Но как в этом случае писать запросы за длительный срок?
Использовать сложные скрипты с UNION или собирать общую базу из двух частей? И то и другое видится не очень удобным…

Вариант 2:
Опять же создать копию бд, которая будет служить архивом и настроить на нее репликацию. И потом каким-то хитрым образом удалять древние строки из основной базы, но чтобы эти изменения не реплицировались. Возможно ли такое в принципе?

Может есть еще какие-то варианты?
Спасибо!

Answer 1

[Dunadan]

А что мешает сделать аггрегацию статистики?

Например, Вы считатете количество просмотров определенной страницы.
Каждый хит — одна запись в БД с ай-пи адресом посетителя.

Для сводной таблицы вполне достаточно будет записи вида page_id, page_view_count, week_id, где
page_id — индекс страницы
page_view_count — просмотров страницы за неделю
week_id — порядковый номер недели в году.

Для отчетов из серии «за неделю-месяц-полгода-год» вполне пойдет. Можено еще количество хостов считать.

В результате вся статистика по одной странице за год займет аж 52 записи.

Answer 2

[ztxn]

Я правильно понимаю, что используя термины «бд»,«база» вы подразумеваете «таблица»?

Если так, можно посмотреть в сторону секционирования.

Answer 3

[vitalets]

2 Dunadan:
идею понял. Проблема в том, что заранее я не могу знать, по каким условиям запросят следующий отчет.

2 ztxn:
Нет, база это именно база. Данные для отчетов хранятся в основных 4-5 таблицах.
Секционирование смотрел. Насколько я понимаю, оно не уменьшит размер бд при дампах, верно?

Comments

[ztxn]

Понял. Почитал голосарий. Оказыавется в MySQL принято называть базой данных то, что в оракле принято называть схемой. Меня это и сбило с толку. В ораклиной терминологии таблцы archive.document и prod.document находятся в одной датабазе, но разных схемах.

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

[ztxn]

Однако ж это решение не снимает проблему получения дампа архивной базы данных ))

Answer 4

[zuborg]

Может сделать триггер на insert на основную таблицу, который будет копировать строку в архивную таблицу. delete соотв оставлять без внимания. update/replace — смотрите сами.
Основные запросы делать на основную таблицу, а для отчетов — на архивную.

Comments

[vitalets]

Думаю что не подойдет. Например, пользователь создал документ в основной таблице, а потом удалил его. Соответственно, в отчетах он появиться не должен, а он появится, т.к. сохранится в архиве, если триггер только на insert.

[ztxn]

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

[zuborg]

Ну как самый дубовый вариант — триггер на удаление оставить, но игнорировать удаляемые строки, которые имеют старый create_date, т.е. те строки которые удаляются для того, чтобы не замусоривать основную таблицу (архивируются).

Answer 5

[Vampiro]

Эм… может что-нить типа
create or replace VIEW forFastReports as select * from mySlowTable where added_at < date_sub(now(),INTERVAL 1 DAY);?

Comments

[Vampiro]

Плин. Там «больше» знак нужно, ну, идею вы поняли, думаю.

[vitalets]

не могли бы уточнить, что даст такая вьюха со старыми данными?

Answer 6

[Владимир]

Я человек не образованный, УЗов не заканчивал — учился в основном по доступным книгам, так что сильно не пинайте :)
Есть такой термин как «денормализация данных». Я для таких случаев придумал для себя «денормализацию таблиц».
Например, у нас есть новостной ресурс, в котором нельзя физически удалять новости. Т.е. удаленные новости должны быть доступны при необходимости из панели управления, но для клиентской части они «отсутствуют». Я делал дубликат таблицы, в которую при удалении переносил запись со всеми полями. Это разгружало рабочую таблицу, но нагружало приложение в случае необходимости запроса к удаленной записи. А в виду того что таких запросов обычно в тысячи раз меньше — я остановился на таком подходе.

Т.е. я в своих решениях использовал (при необходимости) union совместно с кешированием.

Comments

[vitalets]

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

Answer 7

[odmin4eg]

У меня подобная проблема, есть блог на WP в нём БД весит уже в районе 700-800мб

тоже всяко думал как бы старую инфу выливать в другие базы, думал что надо как-то опираться на года, если человек запросил пост с урлой /2008/ то берём данные из БД с именем site_2008

Но до практической реализации не дошло, унёс базу целиком на другой сервер, где много оперы и ресурсов.