Шринк? Что это и зачем?
Шринк (shrink) — это сжатие файлов данных для уменьшения занимаемого ими пространства на диске. Выполняется за счет перемещения страниц данных с конца файла в свободное пространство в начало файла. После этого страницы в конце файла становятся неиспользованными и это пространство может быть возвращено файловой системе.
Не путать понятия!
В стародавние времена считалось нормальным выполнять шринк базы данных. Некоторые администраторы даже настраивали регламентные процедуры для "шринкования" вне рабочего времени. К счастью, такое уже редко где можно увидеть, но случаи еще есть.
Почему шринк не стоит делать регулярно на рабочем окружении? Вот основные причины:
- Сама по себе операция сжатия файла данных очень ресурсоемка и на больших базах может выполняться десятки часов, а то и больше. Также во время ее выполнения пользователи могут, и скорее всего будут, ощущать замедление работы системы.
- Значительное снижение эффективности работы индексов. Шринк переносит страницы в начало файла, так он может перенести и индекс. Причем часть его страниц может быть в самом начале файла, другая где-то по середине, а третья вообще разбросана где попало. В итоге фрагментация индекса будет под 99.9%, что значительно снижает производительность. Индексы попросту не могут быть использованы должным образом. Спасти может только перестроение или реорганизация (иногда), но это снова может увеличит размер файла данных. Есть отличная статья об этом от Brent Ozar.
- Появление дополнительных задержек при увеличении файла данных. После шринка база занимает минимальный размер, но если информационная система жива, то новые данные в нее будут поступать снова и снова. Новые данные = нужно больше места. Каждое увеличение файла данных потребует дополнительных ресурсов. Оптимизировать можно уменьшив количество таких операций за счет большего шага автоувеличения файла в настройках базы данных. О влиянии на производительность можно узнать вооооот здесь.
Постойте! Но статья же о быстром шринке! Если сам по себе он так плох, то неужели статью можно уже закончить?
Конечно, нет! Бывают ситуации, когда шринк для базы целесообразен. Замете, целесообразен, но не обязателен!
Вот несколько кейсов, когда его стоит использовать:
- В базе данных произошли серьезные изменения. Например, вы удалили какой-либо исторический регистр из базы 1С, который занимал 100 ГБ. И если эти 100 ГБ важны и их нужно освободить на диске, то от шринка не уйти.
- Вы применили сжатие страниц для таблиц и индексов, что уменьшило размер занимаемых данных на 70%! А файл базы данных на диске не изменился! Снова шринк! Но опять же, если место и правда нужно под что-то освободить, ведь рано или поздно данные в базе смогут занять его снова.
- Вы готовите тестовую базу и удаляете из нее данные, чтобы она не весила 1ТБ. Без шринка тоже никуда, но вопрос производительности может быть неактуальным. Ведь это тестовая среда.
Вообщем, смысл думаю уже понятен.
Классический путь
Стандартный способ выполнить шринк файлов базы данных — воспользоваться такими командами как "SHRINKDATABASE" или "SHRINKFILE". Вот примеры.
-- Первым параметром указываем имя базы данных.
-- Вторым параметром указываем сколько процентов свободного пространства
-- данных мы хотим освободить
DBCC SHRINKDATABASE ('DatabaseName', 10);
или
-- Первым параметром указываем логическое имя файла.
-- Вторым параметром указываем целевой размер
-- для сжатия файла в мегабайтах.
DBCC SHRINKFILE ('LogicalFileName', 100)
Разница между ними заключается лишь в том, что "SHRINKDATABASE" сжимает все файлы данных и журналы транзакций для указанной базы, а "SHRINKFILE" применяет изменения только для указанного файла.
На практике использовать шринк всей базы данных практически никогда не приходится. Да что говорить, если сам шринк — это последнее дело, которое нужно делать, то необходимость применять его для всей базы вообще редкость. В случае если без него не обойтись, то лучше применять его для конкретного файла с помощью второй инструкции.
Также для каждой операции могут быть указаны дополнительные параметры, о который вы можете узнать по ссылкам:"SHRINKDATABASE" или "SHRINKFILE".
Выше мы уже говорили, что сам по себе шринк является болезненной операцией для базы данных как с точки зрения производительности, так и с позиции времени обслуживания. У Вас не должно остаться сомнений по поводу того, что стандартный способ ужать файлы очень неоптимальный и его регулярное применение не имеет никакого смысла.
Ах да, ни в коем случае не ставьте в настройках базы данных включенной опцию "Auto Shrink".
ALTER DATABASE [bsl] SET AUTO_SHRINK OFF WITH NO_WAIT
Все еще сомневаетесь? Прочитайте перевод статьи от разработчика Microsoft, который имел прямое отношение к алгоритму сжатия файлов.
Быстрее, выше, сильнее
Есть и другой способ сжать файл данных, при этом он будет быстрее, т.к. стандартные операции шринка будут использоваться по минимуму.
Речь идет о переносе данных из одной файловой группы в другую, при котором есть два пути:
- Старую файловую группу, из которой данные перемещены, после этого удаляют.
- Если исходную файловую группу удалить нельзя (например, если это группа "PRIMARY"), то ее можно обработать стандартной операцией шринка. В любом случае это будет быстрее, чем использовать стандартное сжатие на исходном файле.
На сколько перемещение данных в новую файловую группу быстрее, чем стандартный шринк? Трудно сказать, т.к это зависит от дисковой подсистемы, где находятся файловые группы, а также от объема BLOB-данных в базе. Как показала практика, чем больше таких данных хранится, тем медленнее выполняется шринк, тем быстрее будет работать сжатие через файловые группы.
На моем опыте использование этого подхода позволяло ускорить сжатие файла данных от 3 до 8 раз, да и последствий для производительности куда меньше.
Как это сделать для баз 1С? Допустим, у нас есть некоторая база "bsl" на инстансе SQL Server и мы решили ее сжать через файловые группы. Т.к. файловая группа по умолчанию только одна, то нужно добавить еще одну дополнительную и файл данных для нее.
-- Добавляем файловую группу
ALTER DATABASE [bsl]
ADD FILEGROUP [FOR_SHRINK]
GO
-- После добавляем новый файл данных для новой файловой группы
ALTER DATABASE [bsl]
ADD FILE (
NAME = N'AfterShrink',
FILENAME = N'F:DBsMSSQL14.MSSQLSERVERMSSQLDATAAfterShrink.mdf' ,
SIZE = 8192KB ,
FILEGROWTH = 65536KB )
TO FILEGROUP [FOR_SHRINK]
GO
Теперь с помощью этих скриптов мы можем переместить все таблицы, индексы и даже BLOB-данные в эту файловую группу вот так.
EXEC dbo.sp_MoveTablesToFileGroup
-- Фильтр по имени схемы (LIKE-оператор)
@SchemaFilter = '%',
-- Фильтр по имени таблицы (LIKE-оператор)
@TableFilter = '%',
@DataFileGroup = 'FOR_SHRINK', -- Имя файловой группы назначения (куда переносим)
-- 1 означает "Перенести все кластерные индексы", то есть таблицы, где есть первичный ключ / кластерный индекс
@ClusteredIndexes = 1,
-- 1 означает "Переместить все дополнительные индексы"
@SecondaryIndexes = 1,
-- 1 означает "Перенести все кучи" - то есть таблицы без кластерного индекса.
@Heaps = 1,
-- 1 - значит только сгенерировать скрипт, ничего выполнять не нужно. 0 - сразу выполнить перемещение
@ProduceScript = 0
Скрипт не универсальный, по крайней мере в текущей его версии, и не может перенести таблицы, в которых, например, только одно поле с BLOB-типом. Для баз 1С это таблица "DBSchema" с описанием структуры базы данных, которую автоматически в новую файловую группу переместить нельзя. Для этого нужно выполнить немного ручной работы:
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_DBSchema
(
SerializedData varbinary(MAX) NOT NULL
) ON FOR_SHRINK
TEXTIMAGE_ON FOR_SHRINK
GO
ALTER TABLE dbo.Tmp_DBSchema SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.DBSchema)
EXEC('INSERT INTO dbo.Tmp_DBSchema (SerializedData)
SELECT SerializedData FROM dbo.DBSchema WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.DBSchema
GO
EXECUTE sp_rename N'dbo.Tmp_DBSchema', N'DBSchema', 'OBJECT'
GO
COMMIT
Так как файловую группу "PRIMARY" удалить нельзя, то мы можем ее сжать через стандартный шринк. Это уже будет работать гораздо быстрее, чем выполнение этой операции до переноса данных.
-- Сжимаем файл основной файловой группы 'PRIMARY'
DBCC SHRINKFILE('bsl', 0)
В итоге все данные перемещены в новую файловую группу, что можно проверить с помощью этого скрипта.
По ссылке выше в своей статье Paul S. Randal этот способ рекомендовал использовать вместо стандартного сжатия данных. Так почему бы не прислушаться? Если бы исходную файловую группу можно было бы удалить (если это не "PRIMARY"), то можно было бы сделать следующее.
-- Удаляем все данные из файла перенося их в другие файловые группы
-- Подробнее о параметре смотреть здесь:
-- https://docs.microsoft.com/ru-ru/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2024
DBCC SHRINKFILE ('AfterShrink', EMPTYFILE);
GO
-- Удаляем пустой файл
ALTER DATABASE [bsl] REMOVE FILE [AfterShrink]
GO
-- Удаляем файловую группу
ALTER DATABASE [bsl] REMOVE FILEGROUP [FOR_SHRINK]
GO
В этом случае файл на диске, на котором ранее располагалась исходная файловая группа, был бы сначала освобожден от всех данных, в потом удален. "PRIMARY" удалять нельзя, т.к. она содержит различную служебную информацию о базе, которую также и переместить в другую файловую группу нельзя.
Конечно, у этого способа есть минус — он требует больший объем свободного места на диске, т.к. при перемещении данных исходный файл сразу не уменьшается в размере. Но по сравнению с остальными плюсами и недостатками стандартного шринка — это может быть несущественным.
База в базу
Можно пойти дальше и переносить данные не в новые файловые группы, а в новую базу данных. Довольно радикальный способ, но работоспособный. Можно выделить несколько основных способов перемещения таблиц и индексов между базами:
- Использовать Bulk-операции, как, например, описано здесь.
- Штатный мастер импорта и экспорта данных
- Использовать утилиту "sqlpackage.exe", входящую в состав SQL Server Data Tools.
- Сгенерировать скрипт с помощью SQL Server Managment Studio
- Использовать операцию "INSERT INTO".
Подробнее на этом способе останавливаться не будем. За более подробной информацией и развернутыми примерами Вы можете обратиться к отличной статье о шести различных способах передачи данных между базами для SQL Server.
Вы все еще шринкуете?
На этом все. Думаю, в публикации не открыл особо ничего нового для сообщества, но может хотя бы скрипты пригодятся. Главное помнить — шринку нет места в продакшене! Хватит шринковать! 🙂
Другие ссылки
Related Posts
Восстановление последовательности документов при закрытии месяца в Бухгалтерия 3.0 не завершается
Заполнение табличных частей
Формирование сводных актов выполненных работ
Ввод поступления в переработку на основании передачи сырья (между организациями)- Конспект по установке сервера 1С на linux
Получение имени компьютера и его IP локально и в терминале
Поставил плюс. Вот только мой шринк быстрее и эффектнее: drop database!
(1) с этим не поспоришь! 😀
(1) Для внедрений, хранящих БД на механике, существуют устройства, реализующие аппаратный шринк.
По ссылке описание и скрипт поэтапного сжатия файла бд . Немного доработал, так как попытки сжатия идут даже после отсутствия свободного пространства.
Код и описание скрипта пошагового сжатия файла БД . Немного доработал, так как попытки сжатия не прекращаются при отсутствии свободного места.
Показать
(5) шринкуете на постоянной основе? 🙂
(6) Да, сжатие как этап при переносе с прода на тест.
SQL 2014
В плане обслуживания присутствует «Сжатие БД».
Никакого «Значительного снижения эффективности работы индексов» не замечено.
Проверим статистику индексов самых популярных таблиц.
(8) утверждаете, что все что в стать «от лукавого»?
(9) У меня нет глубоких знаний и опыта администрирования БД. Просто факт. Но похоже, что «Сатана тут точно замешан».
(10) тут на самом деле два варианта:
1. вам повезло и при шринке данные индексов не были перемещены.
2. Вы делали шринк без перемещения данных, который только освобождает место в конце файла. При этом пустое место внутри файла не затрагивается.
Подробнее посмотрите статью Brent Ozar по ссылке в публикации. Она хоть и на английском, но все хорошо показано о влиянии на индексы.
(10) Статистика собрана после выполнения плана в котором отработало сжатие? Если план ежедневный, на следующий день после сжатия у вас произойдет обсуживание индексов и статитика по идексам будет в норме. Кстати, возможно вы забыли в плане обновление статистики и очистку кэша?
(7)
Сжатие — это архивирование т.е. базе будет нужно каждый раз, при обращении к данных, распаковывать данные.
Будет тратится время …
Либо на тесте — использовать полную копию рабочей базы (не нужно сжимать),
либо — обрезать таблицы за предыдущие года, оставлять последний год (месяц).
(11)
Натыкался я на него однажды, ютуб канал ведёт. Непутёвый он.
(12) После. В понедельник выполнялся, 2 дня прошло. Этот план выполняется только по понедельникам.
(13) В данном случае архивирование — это то, что нужно, т.к. процессорных мощностей в избытке, а вот дисковое пространство нет (тестовая среда работает на ssd). Полная копия рабочей базы (одной из) занимает ~600 гб и пока от предыдущих данных избавиться нельзя.
(14) мощное заявление.
Спасибо, хорошая статья.
(8) Сколько строк в таблицах?
(17) спасибо!
Прочитал по диагонали в надежде уловить, какую задачу вы решаете?
Чем база со свободным местом «хуже», чем без него?
Предполагая, что база наполняется данными, а не пребывает в RO состоянии, что реиндексация способна генерировать большое расширение пространства базы, делаю вывод, что шринк файла данных не нужен чаще, чем «никогда».
А превентивное выделение пространства? А fillfactor? А 24/7 и отсутствие «техокон»? А…….
В чем тогда смысл шринка? Давайте холиварить )))
Лишние (свободные) 50 Гб заполнятся гораздо быстрее, чем DBA потратит время на борьбу за экономию места
PS: а статья да… хорошая ))
PPS_UPD: все нашел строки про «смысл» )))) согласен
(20) холивар можно быстро закончить.
Вот удалили Вы из базы файлы размером 300 ГБ, и чтобы место на серверном SSD освободить под некоторую другую задачу. — нужно сделать шринк за минимальное время.
Никогда не говори никогда! =D
(18) достаточно: документов 100к и цен 10кк. Базе более 10 лет.
«Главное помнить — шринку нет места в продакшене! Хватит шринковать! :)»
Да, вот товарищ тоже с этим согласен:
«Почему вы не должны сжимать ваши файлы данных»
habr.com/ru/post/330492/
По сути шринк, получается, та же дефрагментация диска.
Лишний напряг железа — износ механики и нагрева электроники жёстких дисков, что снижает
надёжность.
Один штатовский товарищ считает по тому же поводу, что любой RAID кроме RAID0 — зло
в плане долгожительства жёстких дисков.
Кстати, сегодня была новость, что импортные медики выявили закономерность внезапной смерти человека от его пульса, человек, у которого пульс в покое 55 ударов в минуту, даже если пьёт и курит и т.п. имеет шансов дожить до глубокой старости больше, чем тот, у кого в покое пульс 75 ударов в минуту.
Сердечко, видимо, тоже имеет свой ресурс по количеству сокращений…
Юрий, а нет более радикального решения избавления от фрагментации — иметь, к примеру,
два идентичных дисковых массива, один рабочий а другой пустой, на который периодически
сливаются данные с первого массива, но так, чтобы при записи на пустой массив происходила уже дефрагментация, после чего в работу запускается бывший пустым массив с дефрагментированными данными, а первый, рабочий массив, очищается и ждёт своей очереди по включению в работу?
Да, затратно, но в плане долговечности дисков, возможно выигрыш.
(23)
Диски в серверах уже давно расходный материал.
(24)Это у кого как, к сожалению…
(25)
У тех у кого объёмы данных диски до дыр затирают, остальным смысла нет беспокоится.
У меня серверные диски выходили из строя на сервере на котором 4 буха иногда считали ЗП и вели бух учёт примерно два раза в неделю. И база была что то под 500Мб. Однако пара серверных дисков за пару лет вышли из строя.
«Однако пара серверных дисков за пару лет вышли из строя.»
От брака никто на застрахован!
Мы вот тоже наскребли немного денег на два серверных SSD,
так вот один был в плёнке в упаковке, другой без плёнки.
Который был в плёнке, работает третий год.
Который был без плёнки, был неисправен, когда даёшь нагрузку, он вообще пропадал
из системы и биоса, выключаешь сервер, отключаешь питание, снова появляется…
Заменили, правда прошло пара месяцев и на другой тип, так как замены уже не было.
Хотя в фирме, где диски покупали, сказали, что тестировали диск несколько часов, ничего не отваливалось.
По моему настоянию диск, таки отправили на завод, откуда сообщили, что да, брак…
(23)
два идентичных дисковых массива, один рабочий а другой пустой, на который периодически
сливаются данные с первого массива, но так, чтобы при записи на пустой массив происходила уже дефрагментация, после чего в работу запускается бывший пустым массив с дефрагментированными данными, а первый, рабочий массив, очищается и ждёт своей очереди по включению в работу?
Да, затратно, но в плане долговечности дисков, возможно выигрыш.
На мой взгляд это сильно зависит от назначения этой базы, интенсивности изменения в ней данных, особенностей планов обслуживания.
Я бы не стал так делать на регулярной основе, ведь все пустое пространство в файле базы рано или поздно будет заполнено и выделено новое. А для разовых операций шринка с помощью тех же файловых групп достаточно выделять временные дисковые ресурсы.
Но возможно и есть какое-то решение по Вашей схеме, надо думать и экспериментировать 🙂
«Но возможно и есть какое-то решение по Вашей схеме, надо думать и экспериментировать :)»
Надо экспериментировать! 🙂
Я в Вас верю, всё получится! 🙂
Давайте беречь жёсткие и твердотельные диски! 🙂
В принципе, есть и дополнительный стимул — пока данные на грязном
дисковом массиве не грохнули, он вам будет копией базы, разумеется без
новых данных, но всё таки…
(29)
новых данных, но всё таки…
Ну вот, весь энтузиазм убили 😀
(30)Не, это побочный эффект! 🙂
Можно, даже, для экономии электроэнергии пустой массив отключать!
Хотя, некоторые говорят, что лучше не выключать — установившийся режим лучшее для жёстких дисков, а старт/стоп могут приводить к неисправностям.
Был давно у нас такой случай, сервер работа себе и работал, решили пропылесосить, всё, жёсткий диск больше не включился…
Что плохого в том, чтобы делать шринк например раз в месяц в технологическое окно? SSD не резиновые так-то :))
(32) Написано же, фрагментация 99.9%. Если с highload-ом не сталкивались, то это не очень страшно. А вот когда к диску пойдет 10000 запросов в секунду, а строк в таблицах будет по миллиарду…..
(33) Ну то есть это не про 1С :))
(34) Про 1С, но таких компаний немного. Спроси у брокеров, если есть знакомые, например, БКС или Открытие, как они свои базы 1С обслуживают.
(11) да у него база 51Мб, плюс в базе оставляет 10% места. И статистику проверил лишь по 3м объектам.
Я читал товарища Брантозавра. И тест выполнил на рабочей базе размером более 200Гб. После этого пришел к админам и смог на цифрах подтвердить — почему я и раньше был против регулярного шринка базы.
(35) есть еще на просторах родины Highload в 1С =)
А можно вопросик почти в тему? Что shrink (обрезание) файла для фрагментации индексов очень плохо — это понятно. А как повлияет backup transaction log при full модели восстановления на фрагментацию? Ведь по логике тоже самое происходит… Может кто-нибудь ткнуть носом в хорошую статью по этой теме.
(38) нет, шринк лога транзакций не влияет на фрагментацию в файле данных, также не практически не влияет на фрагментацию в самом файле журнала. Но есть нюансы.
Вот отличный материал по этой теме, там есть ссылки над другие статьи, это тема очень обширная. А дочитав до конца Вы поймете как работает лог транзакций.
Но шринк лога транзакций не должен быть регулярной операцией. Зачем? Если это полная модель восстановления, то достаточно делать бэкап лога, тогда данные в нем будут перезаписываться, если так можно выразиться.
(39) Дык я и не говорил про шринк лога транзакций. Я спрашивал про backup transaction log и его влияние на фрагментацию индексов.
Скажем в лоб — делаем rebuld index, а потом backup transaction log. Соответственно все данные из transaction log переносятся в файл с данными. И что происходит с индексами в этот момент?
(40) понял.
Влияет. При бэкапировании части лога транзакций (VLF), которые уже закоммичены и сохранены в файлы основных данных, помечаются как неактивными. При этом эти части освобождаются в строгом порядке, то есть фрагментация поэтому и появляется и становится возможной.
На производительность это может влиять и есть рекомендации по этому поводу.
Тут дальше можно отдельную статью написать 🙂
Пару лет назад смотрел вот это:
Там где-то в видео было подробное описание про влияние бэкапирования на лог транзакций и как с этим бороться. Рекомендую посмотреть.
(41) Провел тест с утра… Выгрузил базу в бэкап, отребилдил индексы скриптом с , посмотрел степень фрагментации, сделал backup transaction log, посмотрел степень фрагментации … один в один до backup. В общем похоже backup transaction log на фрагментацию индексов никак не влияет.
Вот что интересно.. в процессе обнаружил, что ребилд скриптом работает значительно лучше чем rebuild index task из Maintance Plan… ибо на обработанной rebuild index task базе снизил фрагментацию раза в два.
(42) мы говорим на разных языках похоже.
Я говорю про фрагменьацию в файле логов.
В файле данных на фрагментацию не влияет, это я еще в прошлом комментарии написал.
Посмотрите видео, там ответы на все ваши вопросы.
Спасибо за статью.
Натолкнуло на интересную идею:
Для создания тестовой базы минимально возможного размера использовать метод копирования из рабочей в тестовую базу, предварительно в тестовой создав таблицы и применив к ним сжатие, а только потом заливка данными.
Идеально если применительно к своей конфигурации можно было бы исключать некоторые таблицы от копирования, а некоторые ограничить фильтром, например по дате.
Если у кого есть (полу)готовые наработки, поделитесь плз.
p.s. В виду того что тестовая база на другом SQL сервере и соединение между ними 1ГБит, требует проверки скорость такого копирования для базы 400ГБайт…