Как вынести большую таблицу из базы и сделать её общей?

Question

[Daniel Newman]

В нашем туристическом проекте есть БД размером в ~7Gb. И дампы базы превращаются в настоящий многочасовой ад. К моем большому удивлению (я с базой едва знаком) на непосредственно бизнес-данные приходится до 100Mb, а все остальное место занимают таблицы логов и таблицы, формирующиеся по запросам системы к внешним поставщикам:

system_logs 4.4Gb
hotel_ostrovok_images 1.6Gb
hotel_ostrovok 0.7Gb
hotel_ostrovok_amenities 0.2Gb

Таких БД - под каждого разработчик - 6 штук.

Мало того, что мы с каждым новым разработчиком теряем по 6Gb однотипных таблиц, так это выжирает время и лочит, в процессе дампа, всю базу.

Есть красивая теория на percona, но мы ищем практику. Кто это и как делал, что приобрел и что потерял?

Comments

[Станислав Макаров]

Что значит - БД под каждого разработчика? Совершенно непонятна структура вашего проекта, и почему с каждым разработчиком вы теряете по 6Гб. У вас тестовые данные на продовом сервере?

И если вы едва знакомы с базой, почему вы этим занимаетесь-то? Очевидно, нужно понять, что это за system_logs. Как вы собираетесь куда-то её вынести и сделать её общей (не знаю правда, что вы под этим подразумеваете), если вам неивзестно, что там хранится?

[landergate]

Ответы, которые смогли помочь, можно отметить "Решением". =)

[Daniel Newman]

Станислав Макаров: Это таблица логов взаимодействия с API нашего проекта и API сторонних поставщиков. Так как иногда требуется расширение таблиц, добавление столбцов и пр., а сервис имеет SOAP-интерфейс и протоколы взаимодействия, задача синхронизации всего этого хозяйства немного усложняется. Объем таблиц, содержащих гигабайты данных поставщика, кеш отелей Букинга, например, позволяющий нам не гонять адовые объемы онлайн и платить за это мегабабки. И далее - это уже вообще ничего не имеющее общего с постановкой задачи.

Если я за неделю все это осознал - оно меня не сделало ближе к ришению. И третьим лицам оно тоже мало чем поможет (знание это).

Что скажите?

[Станислав Макаров]

Daniel Newman
Так а что конкретно вам мешает сделать эту таблицу логов одну на всех? Ну залейте ОДНУ её копию в отдельную базу (чтобы продакшен не трогать) и поставьте права только на чтение (SELECT), и пусть девелоперы ковыряются. Если read-only не годится - ну тогда сделайте тестовые выборки небольшие, процентов 5-10 допустим, и используйте их. Я поэтому и спрашивал про структуру этих логов, т.к. непонятно, для чего они, и какой процент от этих логов можно использовать в качестве полноценных тестовых данных. Т.к. если это логи событий - то событий можно взять последние 100/1000 штук, а не все миллион, а если это какие-то логи синхронизации, то тут уже надо внимательнее.

landergate кстати спрашивает у вас то же самое - что это за дампы, зачем они нужны, зачем дампить базу с логами, почему надо дампить всю базу, а не только схему? Такое ощущение, что нужно просто завести на проекте нормальные тестовые данные и тестовую базу скромного (но достаточного) размера. Что ж это за проект, где всем девелоперам нужны шестигиговые логи? Расчёт процесса слияния черных дыр? ;)

[Daniel Newman]

Станислав Макаров: Вы представляете, как под ZendFramework2 выглядит решение с двумя БД, что бы этот код и на девах и на проде был один? То, что я мечтал услышать - какое-то решение формата "прокси": sqlrelay.sourceforge.net/sqlrelay/images/replicated.png Причем очень давно мечтаю о таком решении. Но, оно не срывается с уст сообщества разработчиков, значит это решение недостаточно себе хорошее. Это что об одной таблице на всех. Структура очень простая - каждая транзакция, влекущая финансовые обязательства третьих лиц и перед этими лицами. Как клиентские, так и к поставщику. Думаю с логами на деве можно как-то распрощаться. Ваша правда.

За дыры тычок засчитан. Девелоперу, действительно, весь объем данных не нужен. А вот сама задача не хранить на боевом проекте все яйца в одной БД - гораздо интереснее. Но, возвращаясь к картинке выше, об это я только фантазирую. У меня есть стойкое ощущение, что логирование транзакций (запрос к поставщику/ответ) и сами сущностные транзакции (начисление/списание/билет/возврат) - нужно разносить. БД Отелей и номеров, допустим, в тройку гигабайт (там только отели и ссылки на статику, в виде картинок, странно держать вместе с транзакциями. Мне так кажется. А решения я пока не понимаю.

Answer 1

[Дмитрий Энтелис]

Imho:
1) БД 7 гигов как бы не много. Оно должно дампиться за несколько минут.
2)
- Зачем каждому разработчику актуальный дамп продакшена?
- Зачем это все хостить на боевом сервере?
3) Идея писать логи в основную БД весьма порочна. Поднимите под неё какую нибудь монгу, ну или хотя бы отдельный sql сервер.
4) Идея хранить картинки в базе конечно имеет право на жизнь, но для этого нужны веские аргументы.
Мне кажется вполне можно вынести работу с картинками в отдельный сервис (единый для всех) и хранить это все дело в файловой системе.

Comments

[Daniel Newman]

1) Вчера писал - утром пришел убивать. Дампил один из разрабов из под phpMyAdmin, потому я сразу и написал, как узнал.
2)
— Он не сверх актуален, но я по факту смотрел список процессов в базе и таблицы, и по нему удивлялся
— Не боевой, а разработческий. Бывает, что сервисы системы, при появлении новых полей, начинаю сыпать ошибками о незаполненных сущностях, которых на мастере фронта просто нет, а в других ветках git'а идет оживленная разработка
3) Вот за это я только за. Вроде бы даже предпринимались попытки
4) Тут тоже согласен, я просто не бэкенд разработчик, меня попросили покараулить)))

Предложу руководству обратиться к вам за "давайте подумаем".

Answer 2

[landergate]

Если самоцель такого выноса - прекратить влияние дампов на доступность чтения с БД, то, при условии, что ваши таблицы в InnoDB и у вас включён binlog, вы можете снимать копии БД с помощью xtrabackup (innobackupex), не останавливая службу и не прерывая доступ к таблицам.
Изменения, произошедшие с момента старта бекапа, "доедут" из бинлога на завершении процесса.
Снимать копии можно как на тот же сервер, так и стримить их (без использования локального диска) на другой.

Если таблицы в MyISAM, можно настроить SLAVE-сервер и снимать дампы с него, не влияя на доступность основного.

Это практика.

Правда из контекста не совсем ясно - это некие регулярные дампы данных для резервирования, или это ручные дампы для разработчиков, которые они могут спонтанно снять в любое время через phpmyadmin?
Если второе, то это не исправить изменением архитектуры. Для чего dev'ы дампят базу с логами? Почему они не могут задампить только нужные мелковесящие таблицы, или даже ограничиться их структурой, чтобы не задевать другие таблицы?
Если для разработки необходим весь снэпшот базы, я бы сделал его снятие регулярным с xtrabackup и публиковал разработчикам для скачивания по сети из одного конкретного места, чтобы никто больше не подключался к продакшену и не делал никаких дампов с продакшена.