Форум
В папке OpenServer\userdata\MySQL-5.5, есть файл ibdata1. У меня, при 2-х (не большие +- 1,5 Мб) БД, он занимает аж 23,2 Гб.
Подскажите плз, что это за файл? Можно ли его безболезненно удалить (все же 23 Гб — многовато. )
Максим Сообщения: 6024 Зарегистрирован: 11 дек 2010, 20:29
Re: ibdata1 что это?
Это файл баз данных в формате innoDB. Его особенность в том, что если вы создали базу размером 23 гигабайта, а потом её удалили, то файл не уменьшается в размере. Свободное место в нём просто остаётся на будущее для других баз данных. Если вам это не нравится то пересоздайте все базы путём импорта/экспорта одним sql файлом или используйте MyISAM формат бд.
Dgikar Сообщения: 11 Зарегистрирован: 13 июн 2012, 17:56
Re: ibdata1 что это?
Максим, спасибо за ответ.
Я правильно понимаю, что, если я, пересоздам БД, этот файл уменьшиться? Или его, сейчас можно удалить, а потом пересоздать БД и все будет дальше ОК? Как правильно сделать?
DelphinPRO Сообщения: 857 Зарегистрирован: 01 фев 2012, 17:27 Откуда: Самара
Re: ibdata1 что это?
или используйте MyISAM формат бд.
А вот этого делать я бы не советовал ни при каких условиях. myisam — нетранзакционное хранилище без внешних ключей и этим всё сказано
Максим Сообщения: 6024 Зарегистрирован: 11 дек 2010, 20:29
Re: ibdata1 что это?
Dgikar, импортируете все базы в один файл с опцией пересоздания таблиц. Убеждаетесь что информация в файле корректна, а не какие нибудь там кракозяблы или знаки вопросов вместо данных. Останавливаете сервер. Потом удаляете ibdata1, ib_logfile0, ib_logfile1. Потом запускаете сервер и импортируете файл с базами.
keilman Сообщения: 10 Зарегистрирован: 20 авг 2015, 16:10
Re: ibdata1 что это?
DelphinPRO писал(а): А вот этого делать я бы не советовал ни при каких условиях. myisam — нетранзакционное хранилище без внешних ключей и этим всё сказано
Вы уж так категорично не отвечали бы!
Сначала нужно узнать, для чего человек использует базу данных, что хранит, какой объем и др.
Я бы сказал, что в большинстве случаев лучше подходит myisam чем innodb.
DelphinPRO Сообщения: 857 Зарегистрирован: 01 фев 2012, 17:27 Откуда: Самара
Re: ibdata1 что это?
UPD
В прошлый раз некогда было. Сейчас напишу.
Вам важна целостность ваших данных? Думаю да. Представим сферическую БД в вакууме. В ней несколько взаимосвязанных таблиц. При изменении одной таблицы вам необходимо сделать синхронные апдейты в парочке других. Вы начинаете изменять одну таблицу, потом вторую, и тут, бац! — какой-то сбой. Неважно что произошло, суть в том, что последнюю таблицу мы не успели изменить и данные в ней некорректные. Если в этой таблице хранится список котиков, загруженных пользователем на сайт, это не так страшно, как если бы речь шла о финансовых операциях, но для того пользователя это все же важно.
Какой выход вы видите для исключения подобных ситуаций? Лично я вижу только один — обернуть все операции с таблицами в транзакцию. Но MyIsam понятия не имеет о транзакциях. И шо делать.
Про использование внешних ключей, коих тоже нет в MyIsam я пожалуй промолчу, и так увлекся.
Очистка файла mysql ibdata1
ibdata1 хранит данные таблиц (кроме схем — они хранятся в других файлах), индексы и некоторую другую информацию. Этот файл нельзя просто удалить, так как это приведет к уничтожению данных таблиц. Кроме того файл ibdata1 фактически не может быть сокращен в размере, если только вы не удалите все базы данных, не удалите файлы и не перезагрузите дамп.
С целью оптимизации потребления дискового пространства файлом ibdata1 я предлагаю комплексный подход: настроить MySQL на использование отдельных файлов для каждой таблицы и затем освобождение дискового пространства, занятого файлом ibdata1 :
Чтобы настроить сервер для использования отдельных файлов для каждой таблицы, в my.cnf добавьте следующую директиву:
Борьба с разросшимся ibdata1 файлом MySQL
Случилась недавно проблемка. Получил уведомление о том, что на рабочем сервере заканчивается свободное место. Был слегка удивлен, ведь на нем хостились всего лишь тестовые версии сайтов, для демонстрации клиентам.
Начал разбираться что происходит. Первая идея — кто то, снова, нашел дыру и пытается спамить. Такое уже бывало. Рабочие папки exim (по моему) росли в размере с невероятной скоростью, пока последний не был отключен.
Теория не подтвердилась.
Пришлось локализовать «распухшее» место программой du :
# ищем переростка в корне директории больше 200 Мб $ du -sh /* --threshold=200M | sort -h # ищем в следующей самой жирной директории, например $ du -sh /var/* --threshold=200M | sort -h # . и так далее, пока не локализуем виновника В моем случае это оказался файл ibdata1 — файл с базами данных.
Как выяснилось, до 5.6.6, MySQL по умолчанию не «резала» таблицы и базы на отдельные папки и файлы, а сохраняла все в один файл.
Пошел смотреть какая база разрослась. Все оказалось просто — я не отключил один старый сайт на Magento CMS . Она собирала информацию о пользователях и их устройствах, переходах, действиях и чёрт знает что еще. И, внимание, хранила все это она в БД.
Не поверите, на «мертвом» сайте за 5+ лет побывало то ли 7, то ли 8 тыс человек. Которые сгенерировали по 14 000 000 (четырнадцать миллионов) записей в двух таблицах.
Порядка 7 Gb логов всего в двух табличках!
Данные эти мне не нужны, поэтому TRUNCATE и дело с концом.
И вот тут меня ждал очень неприятный сюрприз — размер файла ibdata1 не уменьшился вообще.
ALTER TABLE <. >ENGINE=innodb не помогло. OPTIMIZE TABLE <. >— тоже.
Решение
Нашел решние на Stackoverflow.
Оказалось, что это, типа, так и должно быть. Файл можно уменьшить только удалив ibdata1 и ib_log .
1. Делаем дамп базы
Надо учесть, что базы mysql и performance_schema мы удалять не будем.
Самый простой способ, если баз не так много:
$ mysqldump -uroot -proot --databases db_name1 [db_name2 db_name3. ] > dump.sql Если баз много, то можно воспользоваться такой конструкцией:
$ echo 'show databases;' | mysql -uroot -proot | grep -v ^mysql$ | grep -v ^performance_schema$ | xargs mysqldump -uroot -proot --databases > dump.sql Если баз, которые хочется исключить, больше двух, то можно, конечно, все их перечислить в примере выше. Но как по мне, так увеличивается вероятность ошибиться.
Поэтому я предлагаю сделать 3 простых шага:
# 1. получаем список всех таблиц в файл $ echo 'show databases;' | mysql -uroot -proot > dblist.txt # 2. удалить из этого файлика все ненужные таблицы # 3. Сделать дамп $ cat dblist.txt | xargs mysqldump -uroot -proot --databases > dump.sql 2. Удаляем все базы данных.
КРОМЕ mysql и performance_schema
$ mysql -uroot -proot mysql > DROP DATABASE db_name1; mysql > DROP DATABASE db_name2; . mysql > DROP DATABASE db_nameN; Важно из справки по DROP DATABASE
Оператор DROP DATABASE удаляет все таблицы в указанной базе данных и саму базу. Если вы выполняете DROP DATABASE на базе данных, символически связанной с другой, то удаляется как ссылка, так и оригинальная база данных. Будьте ОЧЕНЬ внимательны при работе с этой командой!
3. Тормозим mysql
Ubuntu:
$ sudo service mysqld stop ## или $ sudo service mysql stop CentOS 7
$ sudo systemctl stop mysql 4. Исправляем конфиг my.cnf
Данный файл лежит, скорее всего, где то тут: /etc/mysql/my.cnf .
Чтобы найти все места откуда забирается конфиг, можно выполнить следующую команду:
$ mysql --help По идее, вы должны будете увидеть нечто подобное (строки 8-9):
$ mysql --help mysql Ver 15.1 Distrib 10.3.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Usage: mysql [OPTIONS] [database] Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf . . После того, как нашли конфиг, в него нужно найти секцию [mysqld] и добавить (или исправить, если есть) значение параметра:
innodb_file_per_table=1 5. Удаляем файлы ibdata1 и ib_log
6. Стартуем mysql
Ubuntu:
$ sudo service mysqld start ## или $ sudo service mysql start CentOS 7
$ sudo systemctl start mysql 7. Восстанавливаем дамп
$ mysql -uroot -proot < dump.sql Большой /var/lib/mysql/ibdata1
Я сохранял данные из таблиц в текстовые файлы. Всего вышло примерно 40-60 кбт. Но все файлы mySql занимают 760 мб. В процессе разработки в таблицы бд вставлялись данные, а также данные обновлялись и удалялись. Жаль я точно не помню сколько занимал mySql (то есть директория где размещены файлы бд) сразу же после установки. Но было гораздо меньше чем 700 мб. Сейчас моя бд "занимает" 300 с чем-то кбт, ну то есть это frm файлы заполненные нулями.
du -hs /var/lib/mysql/* 0 /var/lib/mysql/debian-5.5.flag 763M /var/lib/mysql/ibdata1 5,0M /var/lib/mysql/ib_logfile0 5,0M /var/lib/mysql/ib_logfile1 316K /var/lib/mysql/mbs 1,1M /var/lib/mysql/mysql 4,0K /var/lib/mysql/mysql_upgrade_info 212K /var/lib/mysql/performance_schema
Как нормализовать (уменьшить, дефрагентировать(?)) файлы бд? И почему оно так распухло? Где-то когда-то читал, что это так потому что mySql типа резервирует место для данных - не помню :(. Таблицы в InnoDB, сервер 5.5.54. Сначала таблицы были в MyISAM.
Отслеживать
задан 4 апр 2017 в 23:33
402 2 2 золотых знака 7 7 серебряных знаков 25 25 бронзовых знаков
а какая версия MySql? какой тип таблиц используется?
4 апр 2017 в 23:40
Таблицы в InnoDB, сервер 5.5.54.
5 апр 2017 в 0:13
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Because the system tablespace never shrinks, and is shared across all databases in an instance, avoid loading huge amounts of temporary data on a space-constrained system when innodb_file_per_table is disabled. Set up a separate instance in such cases, so that you can drop the entire instance to reclaim the space.
Резюме: Сделать дамп всех БД (либо временно сменить движок - например, на MyISAM), переинициализировать системное хранилище, восстановить БД.
Рекомендация: Включить innodb_file_per_table.
Отслеживать
ответ дан 5 апр 2017 в 4:42
31.4k 3 3 золотых знака 21 21 серебряный знак 40 40 бронзовых знаков
🙁 "всей манны сразу не скушаешь". "Рекомендация: Включить innodb_file_per_table." - по названию пвраметра догадывюсь что будет не один большой файл, а по 1 на каждую таблицу.
5 апр 2017 в 17:37
Именно. И при этом хранилище каждой отдельно взятой таблицы очень даже нормальненько шринкается. Причём без дополнительных телодвижений.