Ответ 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.
-
Aug 27, 2012: Правильная настройка для таблицы 30b InnoDB на сервере с 48-гигабайтной ОЗУ -
Jan 17, 2013: MySQL 5.5 - Innodb - innodb_log_file_size более 4 ГБ вместе?
Лично я по-прежнему буду использовать правило 25% для начальной настройки. Затем, по мере того, как рабочая нагрузка может быть более точно определена с течением времени в производстве, вы можете изменить размер журналов в течение цикла обслуживания всего за несколько минут.