Howto: Очистить механизм хранения mysql InnoDB?

Можно ли очистить движок хранения mysql innodb, чтобы он не сохранял данные из удаленных таблиц?

Или мне нужно каждый раз перестраивать новую базу данных?

Ответ 1

Вот более полный ответ в отношении InnoDB. Это немного длительный процесс, но может стоить усилий.

Имейте в виду, что /var/lib/mysql/ibdata1 - самый загруженный файл в инфраструктуре InnoDB. Он обычно содержит шесть типов информации:

Данные таблицы
Табличные индексы
MVCC (Multiversioning Concurrency Control) Данные
- Откат сегментов
- Отменить пробел
Метаданные таблицы (Словарь данных)
Буфер с двойной записью (фоновая запись для предотвращения использования кэширования ОС)
Вставить буфер (управление изменениями для неспецифических вторичных индексов)
Смотрите Pictorial Representation of ibdata1

Архитектура InnoDB

Многие люди создают несколько файлов ibdata, надеясь на лучшее управление диском и производительность, однако эта вера ошибочна.

Можно ли запустить `OPTIMIZE TABLE`

К сожалению, запустите OPTIMIZE TABLE против таблицы InnoDB, хранящейся в общем файле табличного пространства ibdata1 делает две вещи:

Делает таблицы и индексы смежными внутри ibdata1
Увеличивает ibdata1, потому что непрерывные страницы данных и индексов добавлены в ibdata1

Однако вы можете отделить данные таблицы и таблицы таблицы от ibdata1 и управлять ими независимо.

Можно ли запустить `OPTIMIZE TABLE` с `innodb_file_per_table`

Предположим, вы должны добавить innodb_file_per_table в /etc/my.cnf (my.ini). Можете ли вы затем запустить OPTIMIZE TABLE на всех таблицах InnoDB?

Хорошие новости: при запуске OPTIMIZE TABLE с innodb_file_per_table, это приведет к созданию файла .ibd для этой таблицы. Например, если у вас есть таблица mydb.mytable с datadir /var/lib/mysql, она будет вызывать следующее:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

.ibd будет содержать страницы данных и индексные страницы для этой таблицы. Отлично.

Плохая новость. Все, что вы сделали, это извлечение страниц данных и индексных страниц mydb.mytable из жизни в ibdata. Запись словаря данных для каждой таблицы, включая mydb.mytable, по-прежнему сохраняется в словаре данных (см. Изобразительное представление ibdata1). ВЫ НЕ МОЖЕТЕ ПРОСТО ПРОСТО УДАЛИТЬ ibdata1 В ЭТОМ ТОЧКЕ!!! Обратите внимание, что ibdata1 не сокращается вообще.

Очистка инфраструктуры InnoDB

Чтобы сжать ibdata1 раз и навсегда, вы должны сделать следующее:

Дамп (например, с mysqldump) все базы данных в текстовый файл .sql (SQLData.sql используется ниже)
Отбросьте все базы данных (кроме mysql и information_schema) CAVEAT. В качестве меры предосторожности запустите этот script, чтобы убедиться, что у вас есть все пользовательские гранты
```
mkdir /var/lib/mysql_grants
cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
chown -R mysql:mysql /var/lib/mysql_grants
```
Войдите в mysql и запустите SET GLOBAL innodb_fast_shutdown = 0; (это полностью очистит все оставшиеся транзакционные изменения от ib_logfile0 и ib_logfile1)
Завершение работы MySQL
Добавьте следующие строки в /etc/my.cnf (или my.ini в Windows)
```
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
```
(Sidenote: независимо от вашего набора для innodb_buffer_pool_size, убедитесь, что innodb_log_file_size составляет 25% от innodb_buffer_pool_size.

Также: innodb_flush_method=O_DIRECT недоступен в Windows)
Удалить ibdata* и ib_logfile*, необязательно, вы можете удалить все папки в /var/lib/mysql, кроме /var/lib/mysql/mysql.
Запустите MySQL (это будет воссоздать ibdata1 [10 МБ по умолчанию] и ib_logfile0 и ib_logfile1 на 1G каждый).
Импорт SQLData.sql

Теперь ibdata1 будет расти, но будет содержать только метаданные таблицы, потому что каждая таблица InnoDB будет существовать вне ibdata1. ibdata1 больше не будет содержать данные и индексы InnoDB для других таблиц.

Например, предположим, что у вас есть таблица InnoDB с именем mydb.mytable. Если вы посмотрите в /var/lib/mysql/mydb, вы увидите два файла, представляющих таблицу:

mytable.frm (Заголовок двигателя хранения)
mytable.ibd (Данные таблицы и индексы)

С опцией innodb_file_per_table в /etc/my.cnf вы можете запустить OPTIMIZE TABLE mydb.mytable, а файл /var/lib/mysql/mydb/mytable.ibd будет фактически уменьшаться.

Я делал это много раз в своей карьере в качестве DBA MySQL. Фактически, в первый раз, когда я это сделал, я сжал файл размером 50 ГБ ibdata1 до 500 МБ!

Попробуй. Если у вас есть дополнительные вопросы по этому поводу, просто спросите. Доверьтесь мне; это будет работать как в краткосрочной, так и в долгосрочной перспективе.

CAVEAT

На шаге 6, если mysql не может перезагрузиться из-за того, что схема mysql начала отбрасываться, оглянитесь на шаг 2. Вы сделали физическую копию схемы mysql. Вы можете восстановить его следующим образом:

mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql

Вернитесь к шагу 6 и продолжайте

ОБНОВЛЕНИЕ 2013-06-04 11:13 EDT

Что касается установки innodb_log_file_size до 25% innodb_buffer_pool_size на шаге 5, это правило одеяла - довольно старая школа.

Вернувшись на July 03, 2006, у Percona была хорошая статья почему выбрать правильный innodb_log_file_size. Позже, на Nov 21, 2008, Percona продолжил работу над еще одной статьей как рассчитать правильный размер, основанный на максимальной рабочей нагрузке, сохраняя изменения в один час.

С тех пор я написал записи в DBA StackExchange о вычислении размера журнала и где я ссылался на эти две статьи Percona.

Лично я по-прежнему буду использовать правило 25% для начальной настройки. Затем, по мере того, как рабочая нагрузка может быть более точно определена с течением времени в производстве, вы можете изменить размер журналов в течение цикла обслуживания всего за несколько минут.

Ответ 2

Механизм InnoDB не сохраняет удаленные данные. Когда вы вставляете и удаляете строки, неиспользуемое пространство остается выделенным в файлах хранения InnoDB. Со временем общее пространство не будет уменьшаться, но со временем пространство "удаленное и освобожденное" будет автоматически повторно использоваться сервером БД.

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

Ответ 3

Вы можете периодически использовать запрос OPTIMIZE TABLE, чтобы оптимизировать ваше хранилище и немного ускорить запросы SELECT. Ссылка на руководство MySQL: OPTIMIZE TABLE Синтаксис