Oracle после удаления строк таблица не уменьшилась
У меня есть одна таблица MySQL, использующая механизм хранения InnoDB; он содержит около 2 млн строк данных. Когда я удалил строки данных из таблицы, выделенное дисковое пространство не освободилось. Размер файла ibdata1 не уменьшился после выполнения optimize table команды.
Есть ли способ вернуть дисковое пространство из MySQL?
Я в плохой ситуации; это приложение запущено примерно в 50 различных местах, и теперь проблема нехватки места на диске возникает почти во всех из них.
«Размер файла ibdata1 не уменьшился после выполнения команды оптимизации таблицы» , потому что ваш innodb_file_per_table выключен. Хорошая новость в том, что on в последних версиях MySQL эта опция включена по умолчанию.
MySQL не уменьшает размер ibdata1. Когда-либо. Даже если вы используете, optimize table чтобы освободить пространство, используемое от удаленных записей, он будет повторно использовать его позже.
Альтернативой является настройка сервера для использования innodb_file_per_table , но для этого потребуется резервное копирование, удаление базы данных и восстановление. Положительным моментом является то, что файл .ibd для таблицы уменьшается после расширения optimize table .
Документация MySQL 5.5 о состоянии режима InnoDB file-per-table «Чтобы воспользоваться преимуществами функций [InnoDB file-per-table] для существующей таблицы, вы можете включить настройку file-per-table и запустить ALTER TABLE t ENGINE=INNODB на существующей таблице. " Это означает, что вы можете включить эту функцию, «преобразовать» существующие таблицы для использования отдельного файла InnoDB с помощью команды ALTER TABLE, а затем ОПТИМИЗИРОВАТЬ таблицу, чтобы уменьшить ее размер. Однако, как только вы закончите, вам нужно будет выяснить, как удалить (огромный) исходный файл InnoDB .
Я предполагаю, что это технически отвечает на вопрос, но я ожидаю, что большинство людей, ищущих эту тему, ищут фактический процесс сокращения / освобождения пространства, которого этот ответ не предоставляет.
@Manachi Процесс: «настроить сервер для использования innodb_file_per_table», «создать резервную копию» сервера, «удалить базу данных (и)», остановить mysql, удалить .ibd, запустить сервер и восстановить базу (и). С MySQL 5.5+ вы можете использовать то, что сказал Джош, и после изменения всех таблиц остановить сервер, удалить огромный .ibd и запустить его снова.
У меня была такая же проблема.
Что происходит, так это то, что даже если вы отбрасываете базу данных, innodb все равно не освобождает дисковое пространство. Мне пришлось экспортировать, остановить mysql, удалить файлы вручную, запустить mysql, создать базу данных и пользователей, а затем импортировать. Слава богу, у меня было только 200 МБ строк, но это сэкономило 250 ГБ файла innodb.
Неудача по замыслу.
MySql 5.5 имеет ту же проблему: я запускал «оптимизацию таблицы», чтобы уменьшить использование диска для таблицы размером 28 ГБ. Операция, вероятно, пыталась создать оптимизированный клон исходного, и при этом израсходовалось все пространство на разделе. Теперь "оптимизировать таблицу" не удалось, и у меня не осталось места в разделе даже после того, как я сбросил всю базу данных . очень разочаровывает.
Если вы не используете innodb_file_per_table , освобождение дискового пространства возможно, но довольно утомительно и требует значительного времени простоя.
« Как сделать» довольно подробно, но я вставил соответствующую часть ниже.
Не забудьте также сохранить копию своей схемы в дампе.
В настоящее время вы не можете удалить файл данных из системного табличного пространства. Чтобы уменьшить размер системного табличного пространства, используйте эту процедуру:
Используйте mysqldump для сброса всех ваших таблиц InnoDB.
Остановите сервер.
Удалите все существующие файлы табличных пространств, включая файлы ibdata и ib_log. Если вы хотите сохранить резервную копию информации, скопируйте все файлы ib * в другое место перед удалением файлов в вашей установке MySQL.
Удалите все файлы .frm для таблиц InnoDB.
Настройте новое табличное пространство.
Перезагрузите сервер.
Импортируйте файлы дампа.
Спасибо за то, что вы добавили шаги - аффикт, ссылка «как сделать» больше не содержит этой информации
Десять лет спустя и у меня была такая же проблема. Я решил это следующим образом:
- Оптимизировал, все базы остались.
- Я перезапустил свой компьютер и MySQL в службах (Windows + r -> services.msc)
Другой способ решить проблему освобождения пространства: создать несколько разделов в таблице - разделы на основе диапазона, разделы на основе значений и просто отбросить / усечь раздел, чтобы освободить пространство, что освободит пространство, используемое целыми данными, хранящимися в определенном разделе.
Когда вы введете разделение для своей таблицы, потребуются некоторые изменения в схеме таблицы, например - Уникальные ключи, индексы для включения столбца раздела и т. Д.
В моем приложении мы загружаем огромные данные. Если какие-либо неправильно загруженные данные, мы можем удалить данные. Итак, какие бы данные ни удалили это пространство, когда они будут восстанавливать данные?. Есть ли какое-либо влияние на производительность Есть ли способ вернуть пространство после большое удаление в оракуле? .Как освободить место?
задан 24 фев '12, 14:02
4 ответы
В Oracle удаление данных не освобождает дисковое пространство автоматически. База данных будет хранить свое хранилище, пока вы не сделаете это. что-то административный к табличному пространству. Ожидается, что такое поведение состоит в том, что если вам нужно хранилище в один момент, оно, вероятно, понадобится вам снова, и поэтому было бы более эффективно просто сохранить выделение.
Что касается влияния на производительность, меньший объем обрабатываемых данных обычно ускоряет выполнение запросов. :)
Чтобы освободить место, у вас есть несколько вариантов. Эта статья с ORACLE-BASE имеет довольно всесторонний взгляд на эту ситуацию.
Кроме того, зачем вам вставлять данные, а затем определять их «плохо», а затем удалять их? Не лучше ли не вводить данные с самого начала?
Это серьезная проблема в нашей среде разработки, которая заставляет нас создавать новую базу данных каждые 6-8 месяцев и удалять старую. Мы добавляем от 10 до 15 новых пользователей за ночь для тестирования / разработки и удаляем старых через неделю. Проблема с возвратом табличного пространства состоит в том, что если какие-либо данные находятся в табличном пространстве ближе к концу файла, вы не можете освободить какое-либо свободное пространство в середине. На самом деле это довольно неприятно. - продает
Если вы постоянно отбрасываете / создаете пользователей и имеете много странных шаблонов использования, то почему бы не предоставить каждому пользователю собственное табличное пространство? - Адам Хоукс
Это один из вариантов - и, посмотрев на эту последнюю сборку, я предоставил «автоматическим» пользователям их собственное табличное пространство - поэтому я могу удалить их и отбросить табличное пространство, и мне не нужно отбрасывать базу данных. Я не уверен, что хочу использовать табличное пространство для каждого пользователя, но согласен, что это жизнеспособный вариант. Спасибо за вклад . . - продает
Вам нужно исследовать Знак высокой воды (ХВМ).
По сути, это максимальное количество блоков, которое когда-либо использовалось определенной таблицей. Если вы загружаете большой объем данных, вы можете увеличить HWM, удаление этих записей не приведет к уменьшению HWM.
Ссылка на - отличная статья о том, как настроить HWM, и если, как только вы это поймете, вы думаете, что это может повлиять на вашу среду, используйте прилагаемые советы, чтобы уменьшить размер HWM.
ответ дан 18 авг.
Невозможно освободить место после удаления. но если после удаления размер оставшихся данных относительно невелик, создайте новую таблицу с данными, отбросьте старую таблицу и переименуйте новую таблицу на старое имя
Я знаю, что это старый вопрос, но для будущих ссылок:
- УДАЛИТЬ: очистить таблицу, но место уже заблокировано для будущих заполнений.
- TRUNCATE TABLE делает трюк (вам нужно отключить все внешние ключи, ссылающиеся на таблицу, чтобы работать).
Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками oracle or задайте свой вопрос.
Последние вопросы
T-SQL. Почему пишут WHERE 1=1?
Новичков часто ставит в тупик конструкция типа: SELECT * FROM [dbo] . [bkp_product] WHERE 1 = 1 AND [model] = 'Product 1' AND [quantity] = 939 Зачем пишут в коде 1=1? Ответ - для удобства при отладке кода. Покажу на примере. Напишем простой селект SELECT * FROM [Table_1] c WHERE [c] . [id] = 1 AND [c] . [v_sm] <> 'd' AND [c] . [qty] >= 1000 Допустим при отладке кода, нам нужно из условия WHERE убрать [ c ] . [ id ] = 1 Что мы делаем, комментируем условие [ c ] . [ id ] = 1 и AND SELECT * FROM [Table_1] c WHERE --[c].[id] = 1 --AND [c] . [v_sm] <> 'd' AND [c] . [qty] >= 1000 А если у нас изначально было написано SELECT * FROM [Table_1] c WHERE 1 = 1 AND [c] . [id] = 1 AND [c] . [v_sm] <> 'd' AND [c] . [qty] >= 1000 То нам нужно будет закомментировать всего одну строку SELECT * FROM [Table
После Google не могу найти простой способ вернуть свободное место после удаления таблицы.
Я нашел много объяснений, рассказывающих, как файл данных становится фрагментированным, большой стек скучных запросов, которые вам нужно выполнить, чтобы переместить «пустое пространство» в конец файла данных (таблица за таблицей . даже когда вы есть 200 столов!?).
Затем вам нужно уменьшить размер файла данных, «угадав», насколько вы можете его уменьшить, или вы должны точно знать, каков ваш «размер блока» . И, наконец, вы не должны забывать «перестраивать индексы».
Существует ли простая процедура PL / SQL, которая, учитывая имя табличного пространства или имя файла данных, будет выполнять эту работу? Или любой подобный инструмент Oracle?
Краткий ответ - нет . К сожалению, способ сделать это в Oracle требует «большого количества скучных запросов». Статьи, на которые вы ссылаетесь, являются одними из лучших на эту тему. Файл данных действительно фрагментируется, поэтому даже если свободное пространство находится ниже самого высокого сегмента, Oracle не будет автоматически консолидировать его, когда RESIZE будет выполнено a .
Чтобы «дефрагментировать» табличное пространство, вам нужно переместить эти сегменты в начало файла данных, а не в конец. Для таблиц это автономный процесс, то есть таблица будет недоступна во время перемещения. Индексы можно перемещать либо в автономном режиме, либо с помощью Enterprise Edition их можно перемещать в режиме онлайн. Поскольку у вас есть окно отключения, я рекомендую вам выполнить следующие шаги.
A. Сжатие файлов данных со свободным пространством за отметкой максимальной воды. Это можно сделать следующим образом (запрос аналогичен процедуре Frosty Z):
Б. После того, как вы сузили положение выше верхней отметки, выясните, какие табличные пространства все еще выиграют от перемещения сегментов.
C. Для каждого из этих табличных пространств определите, какие сегменты необходимо переместить. (Замените USERS именем вашего табличного пространства или присоедините его к предыдущему запросу)
D. Переместите каждую таблицу и перестройте индексы и статистику.
E. повторите шаг A.
Я только что построил большинство этих запросов, так что вы захотите тщательно протестировать их перед использованием. Я полагаю, вы могли бы создать процедуру, которая использовалась бы EXECUTE IMMEDIATE для создания фактических операторов для динамического выполнения, но поскольку запросы будут получать ORA-08103: объект больше не существует, пока выполняется перемещение, я думаю, что лучше контролировать этот процесс вручную, даже если если это действительно означает немного больше времени / усилий.
Этот вопрос был перенесен из Super User, поскольку на него можно ответить в Exchange Stack Exchange для администраторов баз данных. Мигрировал 7 лет назад .
Я плохо разбираюсь в SQL, но у меня есть база данных для обслуживания.
Для этого почти не осталось места, поэтому я решил удалить все данные, скажем, за 2008 год. После выполнения запроса на удаление (было очищено около 10 000 000 строк) и очистки журнала транзакций я обнаружил, что мой действия не влияли на размер базы данных. Есть ли что-то еще, что я должен сделать?
Хотя сокращение действительно опасно по причинам, указанным здесь. Между ответом Джимбо и ответом Джона есть хорошая середина . Вы всегда должны серьезно задуматься, хотите ли вы уменьшить свою базу данных.
В идеальном мире - вы бы создали свою БД с большим количеством свободного пространства для роста. Я называю это "Правильный размер" вашей базы данных. Вы бы позволили этому свободному пространству быть там и не стремились его вернуть и сохранили ваш общий размер прямо на вашем использованном размере. Почему? Потому что ваша база данных со временем снова вырастет . Затем вы снова сократитесь . И вы застрянете в этой ужасной схеме бесполезных сокращений, сопровождаемых ростом - и все время, как указали немногие, вы будете увеличивая вашу фрагментацию индекса.
Я написал в блоге об этом, где я увещевал людей: « Не прикасайтесь к этой термоусадочной кнопке! », Но иногда . Иногда вам нужно. Если у вас есть большая база данных, вы только что освободили значительное пространство и не рассчитываете на ее дальнейшее увеличение - хорошо, тогда можно считать сжатие однократной операцией, если впоследствии вы сможете позаботиться о фрагментации индекса с помощью перестройки их. Операция сжатия может занимать много времени, поэтому вам нужно запланировать ее на время, когда вы можете заплатить эту цену за работу сокращения. Подход к созданию пустой БД и копированию в нее данных работает, но это может стать очень трудным с большими базами данных и большим количеством данных.
Если вы планируете добавить это пространство обратно в БД с помощью обычного использования и моделей роста в будущем, то вы можете просто оставить это место там.
Также Вы сказали, что «очистили» свой журнал транзакций. Мне было бы любопытно узнать, как вы это сделали, но, прочитав пост, которым я поделился, и другие статьи этой серии, вы увидите несколько советов по управлению журналом транзакций. Но вкратце - если вы находитесь в режиме полного восстановления, вы должны регулярно делать резервные копии журналов, чтобы журнал сам себя использовал повторно. В противном случае - без резервного копирования журнала в полноэкранном режиме - файл журнала продолжает расти, расти и расти и всегда сохраняет то, что вы сделали, потому что вы сказали SQL, что вы не просто хотите поддерживать этот журнал для восстановления после сбоя, но хотите сохранить его ручное резервное копирование для воспроизведения транзакций / отмены транзакций для восстановления к определенному моменту времени для целей восстановления . Если у вас все просто и вы видите, что журнал чрезмерно растет, BEGIN TRAN . do work. COMMIT TRAN или вы просто сделали одно большое DELETE заявление и удалили целый беспорядок данных в одной неявной транзакции.)
Я также предполагаю, что вы ищете это свободное место в вашей файловой системе. Если вы ищете его в SQL и в том большом файле, который у вас есть - возможно, вы ожидаете завершения очистки ghost, если ищете сразу после своей операции. Пол Рэндал пишет о Ghost Cleanup .
Связанные вопросы
Комментарии
Это здорово работает, когда в табличном пространстве всего одна таблица. Добавьте 2-ю таблицу и данные в нее после добавления данных в 1-ю, а потом покажите, как шринкануть пространство.
Связанные теги
Данный сайт использует файлы cookie, чтобы предоставить Вам наилучший сервис. Продолжая использовать сайт, Вы принимаете нашу политику в отношении файлов cookie. OkПодробнее
В табличном пространстве всего одна таблица. В таблицу можно загрузить всего 100 строк. Загружаем построчно в таблицу с 1 по 100 строки. Табличное пространство заполнено под завязку.
Теперь удалим с 1 по 50 строку. Осталось половина - 50 строк. Смотрим на размер файла, а он остался те же 100 мб.
Т.е. при заполнении таблицы (и соответственно табличного пространства) файл увеличивается, но не уменьшается при их удалении.
Для того что бы уменьшить размер есть команда – ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\MGMT.DBF' RESIZE 50 M
Почему так происходит? Дело в том, что табличное пространство уменьшается (режется) с конца. А в конце у нас лежат данные.
Для этого (перед командой сжатия) необходимо выполнить дефрагментацию данных в табличном пространстве (т.е. перемещение блоков с данными на свободное место) командой – ALTER TABLE HR.ABC SHRINK SPACE COMPACT
Создаем новое табличное пространство с начальным размером 50 мб, авто расширением по 10мб до предельного размера – 100 мб.
Опция COMPACT проводит дефрагментацию, но не корректирует HWM, и не освобождает высвободившееся пространство. Опция CASCADE сжимает не только названную таблицу, но и любые зависимые объекты, например, индексы.
- Получить ссылку
- Электронная почта
- Другие приложения
Читайте также: