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

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

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

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

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

могу ли я запустить OPTIMIZE TABLE ?

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

• делает данные и индексы таблицы смежными внутри ibdata1
• делает ibdata1 растут, потому что смежные страницы данных и индекса добавлен to 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

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

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

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

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

1. свалка (например, с mysqldump) все базы данных в .sql текстовый файл (SQLData.sql используется ниже)

2. удалить все базы данных (за исключением mysql и information_schema)будьте осторожны: в качестве меры предосторожности, пожалуйста, запустите этот скрипт, чтобы убедиться, что у вас есть все пользовательские гранты место:

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

3. войдите в mysql и запустите SET GLOBAL innodb_fast_shutdown = 0; (это полностью очистит все оставшиеся транзакционные изменения от ib_logfile0 и ib_logfile1)

4. Shutdown MySQL

5. добавить следующие строки /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
   

   (Примечание: независимо от вашего набора для innodb_buffer_pool_size, убедится innodb_log_file_size 25% от innodb_buffer_pool_size.

   также: innodb_flush_method=O_DIRECT недоступна на Windows)

6. удалить ibdata* и ib_logfile* при необходимости, вы можете удалить все папки в /var/lib/mysql, за исключением /var/lib/mysql/mysql.

7. запуск MySQL (это будет воссоздать ibdata1 [10 МБ по умолчанию] и ib_logfile0 и ib_logfile1 на 1G каждый).

8. импорт SQLData.sql

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

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

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

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

я делал это много раз в своей карьере в качестве MySQL DBA. На самом деле, в первый раз, когда я это сделал, я сжался 50GBibdata1 файл до 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 EDT

что касается установления innodb_log_file_size до 25%значение параметра innodb_buffer_pool_size в шаге 5 это общее правило довольно старой школы.

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

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

• Aug 27, 2012:правильная настройка для таблицы 30GB InnoDB на сервере с 48GB Рам
• Jan 17, 2013:MySQL 5.5-Innodb-innodb_log_file_size выше 4 ГБ вместе взятых?

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