Как: Очистить движок хранения MySQL InnoDB?

Question

Как: Очистить движок хранения MySQL InnoDB?

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

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

139

mysql innodb

Источник

user463304 13 окт '10 в 20:22

4 ответа

Решение

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

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

5

Источник

user475306 14 окт '10 в 04:05

Я следую этому руководству для полного сброса (как root):

      mysqldump --all-databases --single-transaction | gzip -c > /tmp/mysql.all.sql.gz
service mysql stop
mv /var/lib/mysql /var/lib/mysql.old; mkdir -m700 /var/lib/mysql; chown mysql:mysql /var/lib/mysql

mysql_install_db                # mysql 5.5
mysqld --initialize-insecure    # mysql 5.7

service mysql start
zcat /tmp/mysql.all.sql.gz | mysql
service mysql restart

2

Источник

user1450967 09 июл '21 в 15:41

Кажется, никто не упоминает о влиянии innodb_undo_log_truncateнастройка может иметь.

Взгляните на мой ответ Как сжать/очистить файл ibdata1 в MySQL .

1

Источник

user220239 05 май '22 в 06:03

Другие вопросы по тегам mysql innodb

user491757 29 окт '10 в 22:11 2010-10-29 22:11 · Accepted Answer · 2010-10-29 22:11

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

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

Данные таблицы
Табличные индексы
Данные MVCC (Multiversioning Concurrency Control)
- Откат сегментов
- Отменить пространство
Метаданные таблицы (словарь данных)
Double Write Buffer (фоновая запись для предотвращения зависимости от кэширования ОС)
Вставить буфер (управление изменениями в неуникальных вторичных индексах)
Увидеть 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 с датадир из /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: в качестве меры предосторожности, пожалуйста, запустите этот скрипт, чтобы убедиться, что у вас есть все пользовательские права на месте:
```
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 на винде)
```
[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 будет на самом деле сжаться.

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

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

ПРЕДОСТЕРЕЖЕНИЕ

На шаге 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 ПО ВОСТОЧНОМУ ВРЕМЕНИ

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

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

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

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

Как: Очистить движок хранения MySQL InnoDB?

4 ответа

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

Могу ли я бежать OPTIMIZE TABLE?

Могу ли я бежать OPTIMIZE TABLE с innodb_file_per_table?

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

ПРЕДОСТЕРЕЖЕНИЕ

ОБНОВЛЕНИЕ 2013-06-04 11:13 ПО ВОСТОЧНОМУ ВРЕМЕНИ

Могу ли я бежать `OPTIMIZE TABLE`?

Могу ли я бежать `OPTIMIZE TABLE` с `innodb_file_per_table`?