SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 22.04.2017 13:51:42

E.Wolf
Участник
Зарегистрирован: 23.04.2016
Сообщений: 10

Управление размерами таблиц на диске

Доброго времени суток уважаемые!

Столкнулся с такой проблемой. Общий (нормальный) размер базы (на диске) 120-150Гб. В один момент, при генерации некоторой "статистики", база разрослась до ~300Гб. После удаления всех "лишних" данных, база на диске меньше не стала.

MySQL 5.6, основной движок - InnoDB.

Лучшее, решение проблемы, которое мне удалось вычитать из документации - это включить настройку innodb_file_per_table, которая позволит хранить данные каждой таблицы в отдельном файле. Соответственно, все "лишние" данные можно будет генерировать в отдельных таблицах, после чего их удалять с диска физически, тем самым освобождая место, т.к. SSD'шник - не резиновый.

Скажите пожалуйста, эта проблема относится к InnoDB и можно/нужно поискать некий альтернативный движок (например, изучить возможности XtraDB, на предмет "как там обстоят дела?"), или возможно она решена в более поздних версиях БД? Или возможно её планируется решить в новых версиях БД?

На данный момент, единственное решение известное мне - создать полный бэкап базы и восстановить её обратно. Возможно есть какие-то менее "варварские" способы, позволяющие работать без остановки работы БД?

Неактивен

 

#2 22.04.2017 17:16:48

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Управление размерами таблиц на диске

На данный момент, единственное решение известное мне - создать полный бэкап базы и восстановить её обратно.

Аналогично столкнулся с проблемой свободного места. Прошерстир интернет, но похоже это единственное решение. sad

Неактивен

 

#3 22.04.2017 17:22:02

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Управление размерами таблиц на диске

а чем не устраивает режим innodb_file_per_table ?

в XtraDB дела обстоят также как и  InnoDB.

Неактивен

 

#4 22.04.2017 17:27:46

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Управление размерами таблиц на диске

vasya написал:

а чем не устраивает режим innodb_file_per_table ?

После его использования объем занимаемого места вырос почти в 2 раза. Из без танцев с бубнами его обратно не вернуть.

Неактивен

 

#5 22.04.2017 17:36:39

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Управление размерами таблиц на диске

довольно странное поведение
в 5.7 добавили general tablespace, позволяет вынести в отдельное пространство лишь часть таблиц

Неактивен

 

#6 22.04.2017 18:12:21

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Управление размерами таблиц на диске

Почему странное? Данные из ibdata1 перенеслись в table.idb, но размер файла ibdata1 ведь не уменьшается.

Неактивен

 

#7 23.04.2017 07:22:20

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Управление размерами таблиц на диске

ага, понял, вы не пересоздаете ibdata
нужно: сделать дамп, остановить сервер, удалить все файлы (данные и журналы) innodb, запустить сервер в  режиме innodb_file_per_table, восстановить данные из бекапа
тогда данные будут в idb файлах, а в общем пространстве только служебная информация
перед остановкой имеет смысл сделать drop баз данных, чтобы при запуске не было frm от несуществующих таблиц

Неактивен

 

#8 23.04.2017 08:47:07

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Управление размерами таблиц на диске

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

Отредактированно klow (23.04.2017 08:47:47)

Неактивен

 

#9 23.04.2017 13:01:15

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Управление размерами таблиц на диске

Для того, чтобы сделать «копию файла без свободного места» можно сделать ALTER:

ALTER TABLE tablename ENGINE=InnoDB;


Да и innodb_file_per_table включен по умолчанию с 5.6, кажется. Просто проделайте описанную Васей процедуру один раз, а потом чистите ALTERами нужные таблички?

Неактивен

 

#10 24.04.2017 09:42:52

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Управление размерами таблиц на диске

Это все понятно, что один раз, но ее нужно проделать на боевой базе  с остановкой на время создания бекапа, восстановления из бекпа и проверки. sad

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson