Каков наилучший способ уменьшить размер ibdata в mysql?

У меня есть серверы Production, чьи файлы ibdata увеличиваются с каждым днем.

Он уже потреблял 290 ГБ пространства.

Таблицы на серверах в основном InnoDB, и есть высокие запросы на чтение и запись.

Размер файла журнала также увеличивается. В таблицах содержится огромное количество данных.

Как я могу контролировать растущий размер обоих?

Я не использую innodb_file_per_table.

57 голосов | спросил Abdul Manaf 9 FriEurope/Moscow2011-12-09T10:15:25+04:00Europe/Moscow12bEurope/MoscowFri, 09 Dec 2011 10:15:25 +0400 2011, 10:15:25

1 ответ


94

Помните, что самым загруженным файлом в инфраструктуре InnoDB является /var /lib /mysql /ibdata1

Этот файл обычно содержит много классов информации (когда innodb_file_per_table - 0)

  • Данные таблицы
  • Табличные индексы
  • MVCC (многоуровневое управление параллелизмом).
    • Откат сегментов
    • Отменить табличное пространство
  • Метаданные таблицы
  • См. Изобразительное представление

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

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

  • Делает данные таблицы и индексы смежными внутри ibdata1
  • Это увеличивает ibdata1, потому что смежные данные добавляются к ibdata1

Вы можете отделить данные таблицы и таблицы таблицы от ibdata1 и управлять ими независимо, используя innodb_file_per_table . Чтобы сжать ibdata1 раз и навсегда, вы должны сделать следующее

Шаг 01) MySQLDump все базы данных в текстовый файл SQL (назовите его SQLData.sql) ( Подробнее здесь )

Шаг 02) Отбросьте все базы данных (кроме mysql, performance_schema и information_schema)

Шаг 03) Завершение работы mysql

Шаг 04) Добавьте следующие строки в /etc/my.cnf

[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.

Шаг 05) Удалите ibdata1, ib_logfile0 и ib_logfile1

В этот момент должна быть только схема mysql в /var /lib /mysql

Шаг 06) Перезапустить mysql

Это будет воссоздать ibdata1 в 10MB, ib_logfile0 и ib_logfile1 по 1G каждый

Шаг 07) Перезагрузите SQLData.sql в mysql

ibdata1 будет расти, но будет содержать только метаданные таблицы

Каждая таблица InnoDB будет существовать вне ibdata1

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

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

ibdata1 больше не будет содержать данные и индексы InnoDB.

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

Я делал это много раз в своей карьере в качестве DBA MySQL

Фактически, в первый раз, когда я это сделал, я свернул 50GB ibdata1-файл в 500 МБ.

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

Если вы хотите узнать, сколько фактических данных хранится в MyISAM и InnoDB, запустите этот запрос:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;
ответил RolandoMySQLDBA 9 FriEurope/Moscow2011-12-09T10:30:22+04:00Europe/Moscow12bEurope/MoscowFri, 09 Dec 2011 10:30:22 +0400 2011, 10:30:22

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132