0. YPermitin 3337 17.04.19 08:09 Сейчас в теме

Самый быстрый шринк на Диком Западе

Шринк (shrink) базы данных. Наглядное объяснение что это, зачем, когда применять и как это можно ускорить.

Перейти к публикации

Комментарии
Избранное Подписка Сортировка: Древо
1. Hekeus 17.04.19 09:33 Сейчас в теме
Поставил плюс. Вот только мой шринк быстрее и эффектнее: drop database!
succub1_5; YPermitin; +2 Ответить
2. YPermitin 3337 17.04.19 09:34 Сейчас в теме
3. asved.ru 36 17.04.19 09:50 Сейчас в теме
(1) Для внедрений, хранящих БД на механике, существуют устройства, реализующие аппаратный шринк. Вот например.
4. bykrash 17.04.19 09:55 Сейчас в теме
По ссылке описание и скрипт поэтапного сжатия файла бд https://www.sqlshack.com/shrinking-your-database-using-dbcc-shrinkfile/. Немного доработал, так как попытки сжатия идут даже после отсутствия свободного пространства.
5. bykrash 17.04.19 10:00 Сейчас в теме
Код и описание скрипта пошагового сжатия файла БД https://www.sqlshack.com/shrinking-your-database-using-dbcc-shrinkfile/. Немного доработал, так как попытки сжатия не прекращаются при отсутствии свободного места.
USE [DBname]
DECLARE @FileName sysname = N'LogicalFileName';
DECLARE @TargetSize INT = (SEL ECT 1 + size*8./1024 FR OM sys.database_files WHERE name = @FileName);
DECLARE @Factor FLOAT = .99;
DECLARE @ActualSize INT = 0;
 
WHILE @TargetSize > @ActualSize
BEGIN
    SET @TargetSize *= @Factor;
    DBCC SHRINKFILE(@FileName, @TargetSize);
	SET @ActualSize = (SELECT 1 + size*8./1024 FR OM sys.database_files  Wh ere name = @FileName) * 0.99; 
    DECLARE @msg VARCHAR(200) = CONCAT('Shrink file completed. Target Size: ', 
         @TargetSize, ' MB. Actual size: ', @ActualSize, 'MB. Timestamp: ', CURRENT_TIMESTAMP);
    RAISERROR(@msg, 1, 1) WITH NOWAIT;
     WAITFOR DELAY '00:00:01';
END;
Показать
artichoke; YPermitin; +2 Ответить
6. YPermitin 3337 17.04.19 10:02 Сейчас в теме
(5) шринкуете на постоянной основе? :)
7. bykrash 17.04.19 10:06 Сейчас в теме
(6) Да, сжатие как этап при переносе с прода на тест.
YPermitin; +1 Ответить
13. user-z99999 18 17.04.19 10:28 Сейчас в теме
(7)
Сжатие - это архивирование т.е. базе будет нужно каждый раз, при обращении к данных, распаковывать данные.
Будет тратится время ...

Либо на тесте - использовать полную копию рабочей базы (не нужно сжимать),
либо - обрезать таблицы за предыдущие года, оставлять последний год (месяц).
15. bykrash 17.04.19 10:45 Сейчас в теме
(13) В данном случае архивирование - это то, что нужно, т.к. процессорных мощностей в избытке, а вот дисковое пространство нет (тестовая среда работает на ssd). Полная копия рабочей базы (одной из) занимает ~600 гб и пока от предыдущих данных избавиться нельзя.
8. triviumfan 10 17.04.19 10:08 Сейчас в теме
SQL 2014
В плане обслуживания присутствует "Сжатие БД".
Никакого "Значительного снижения эффективности работы индексов" не замечено.
Проверим статистику индексов самых популярных таблиц.
Прикрепленные файлы:
maksa2005; YPermitin; +2 Ответить
9. YPermitin 3337 17.04.19 10:10 Сейчас в теме
(8) утверждаете, что все что в стать "от лукавого"?
10. triviumfan 10 17.04.19 10:13 Сейчас в теме
(9) У меня нет глубоких знаний и опыта администрирования БД. Просто факт. Но похоже, что "Сатана тут точно замешан".
YPermitin; +1 Ответить
11. YPermitin 3337 17.04.19 10:17 Сейчас в теме
(10) тут на самом деле два варианта:
1. вам повезло и при шринке данные индексов не были перемещены.
2. Вы делали шринк без перемещения данных, который только освобождает место в конце файла. При этом пустое место внутри файла не затрагивается.

Подробнее посмотрите статью Brent Ozar по ссылке в публикации. Она хоть и на английском, но все хорошо показано о влиянии на индексы.
14. triviumfan 10 17.04.19 10:43 Сейчас в теме
(11)
Brent Ozar

Натыкался я на него однажды, ютуб канал ведёт. Непутёвый он.
(12) После. В понедельник выполнялся, 2 дня прошло. Этот план выполняется только по понедельникам.
Прикрепленные файлы:
maksa2005; YPermitin; +2 Ответить
16. YPermitin 3337 17.04.19 10:57 Сейчас в теме
36. Дмитрий74Чел 151 18.04.19 17:48 Сейчас в теме
(11) да у него база 51Мб, плюс в базе оставляет 10% места. И статистику проверил лишь по 3м объектам.
Я читал товарища Брантозавра. И тест выполнил на рабочей базе размером более 200Гб. После этого пришел к админам и смог на цифрах подтвердить - почему я и раньше был против регулярного шринка базы.
12. bykrash 17.04.19 10:26 Сейчас в теме
(10) Статистика собрана после выполнения плана в котором отработало сжатие? Если план ежедневный, на следующий день после сжатия у вас произойдет обсуживание индексов и статитика по идексам будет в норме. Кстати, возможно вы забыли в плане обновление статистики и очистку кэша?
18. nicxxx 229 17.04.19 12:35 Сейчас в теме
(8) Сколько строк в таблицах?
22. triviumfan 10 17.04.19 15:37 Сейчас в теме
(18) достаточно: документов 100к и цен 10кк. Базе более 10 лет.
17. ids79 3235 17.04.19 11:50 Сейчас в теме
Спасибо, хорошая статья.
YPermitin; +1 Ответить
19. YPermitin 3337 17.04.19 14:12 Сейчас в теме
20. nvv1970 17.04.19 14:19 Сейчас в теме
Прочитал по диагонали в надежде уловить, какую задачу вы решаете?
Чем база со свободным местом "хуже", чем без него?

Предполагая, что база наполняется данными, а не пребывает в RO состоянии, что реиндексация способна генерировать большое расширение пространства базы, делаю вывод, что шринк файла данных не нужен чаще, чем "никогда".
А превентивное выделение пространства? А fillfactor? А 24/7 и отсутствие "техокон"? А.......

В чем тогда смысл шринка? Давайте холиварить )))
Лишние (свободные) 50 Гб заполнятся гораздо быстрее, чем DBA потратит время на борьбу за экономию места

PS: а статья да... хорошая ))

PPS_UPD: все нашел строки про "смысл" )))) согласен
YPermitin; +1 Ответить
21. YPermitin 3337 17.04.19 14:22 Сейчас в теме
(20) холивар можно быстро закончить.

Вот удалили Вы из базы файлы размером 300 ГБ, и чтобы место на серверном SSD освободить под некоторую другую задачу. - нужно сделать шринк за минимальное время.

Никогда не говори никогда! =D
23. 3vs 17.04.19 16:57 Сейчас в теме
"Главное помнить - шринку нет места в продакшене! Хватит шринковать! :)"
Да, вот товарищ тоже с этим согласен:
"Почему вы не должны сжимать ваши файлы данных"
habr.com/ru/post/330492/

По сути шринк, получается, та же дефрагментация диска.
Лишний напряг железа - износ механики и нагрева электроники жёстких дисков, что снижает
надёжность.
Один штатовский товарищ считает по тому же поводу, что любой RAID кроме RAID0 - зло
в плане долгожительства жёстких дисков.

Кстати, сегодня была новость, что импортные медики выявили закономерность внезапной смерти человека от его пульса, человек, у которого пульс в покое 55 ударов в минуту, даже если пьёт и курит и т.п. имеет шансов дожить до глубокой старости больше, чем тот, у кого в покое пульс 75 ударов в минуту.
Сердечко, видимо, тоже имеет свой ресурс по количеству сокращений...

Юрий, а нет более радикального решения избавления от фрагментации - иметь, к примеру,
два идентичных дисковых массива, один рабочий а другой пустой, на который периодически
сливаются данные с первого массива, но так, чтобы при записи на пустой массив происходила уже дефрагментация, после чего в работу запускается бывший пустым массив с дефрагментированными данными, а первый, рабочий массив, очищается и ждёт своей очереди по включению в работу?
Да, затратно, но в плане долговечности дисков, возможно выигрыш.
YPermitin; +1 Ответить
24. TODD22 17 17.04.19 17:03 Сейчас в теме
(23)
Да, затратно, но в плане долговечности дисков, возможно выигрыш.

Диски в серверах уже давно расходный материал.
YPermitin; +1 Ответить
25. 3vs 17.04.19 17:04 Сейчас в теме
(24)Это у кого как, к сожалению...
YPermitin; +1 Ответить
26. TODD22 17 17.04.19 17:09 Сейчас в теме
(25)
Это у кого как, к сожалению...

У тех у кого объёмы данных диски до дыр затирают, остальным смысла нет беспокоится.

У меня серверные диски выходили из строя на сервере на котором 4 буха иногда считали ЗП и вели бух учёт примерно два раза в неделю. И база была что то под 500Мб. Однако пара серверных дисков за пару лет вышли из строя.
YPermitin; +1 Ответить
27. 3vs 17.04.19 17:26 Сейчас в теме
"Однако пара серверных дисков за пару лет вышли из строя."
От брака никто на застрахован!
Мы вот тоже наскребли немного денег на два серверных SSD,
так вот один был в плёнке в упаковке, другой без плёнки.
Который был в плёнке, работает третий год.
Который был без плёнки, был неисправен, когда даёшь нагрузку, он вообще пропадал
из системы и биоса, выключаешь сервер, отключаешь питание, снова появляется...
Заменили, правда прошло пара месяцев и на другой тип, так как замены уже не было.
Хотя в фирме, где диски покупали, сказали, что тестировали диск несколько часов, ничего не отваливалось.
По моему настоянию диск, таки отправили на завод, откуда сообщили, что да, брак...
YPermitin; +1 Ответить
28. YPermitin 3337 17.04.19 20:01 Сейчас в теме
(23)
Юрий, а нет более радикального решения избавления от фрагментации - иметь, к примеру,
два идентичных дисковых массива, один рабочий а другой пустой, на который периодически
сливаются данные с первого массива, но так, чтобы при записи на пустой массив происходила уже дефрагментация, после чего в работу запускается бывший пустым массив с дефрагментированными данными, а первый, рабочий массив, очищается и ждёт своей очереди по включению в работу?
Да, затратно, но в плане долговечности дисков, возможно выигрыш.


На мой взгляд это сильно зависит от назначения этой базы, интенсивности изменения в ней данных, особенностей планов обслуживания.

Я бы не стал так делать на регулярной основе, ведь все пустое пространство в файле базы рано или поздно будет заполнено и выделено новое. А для разовых операций шринка с помощью тех же файловых групп достаточно выделять временные дисковые ресурсы.

Но возможно и есть какое-то решение по Вашей схеме, надо думать и экспериментировать :)
29. 3vs 17.04.19 20:12 Сейчас в теме
"Но возможно и есть какое-то решение по Вашей схеме, надо думать и экспериментировать :)"
Надо экспериментировать! :-)
Я в Вас верю, всё получится! :-)

Давайте беречь жёсткие и твердотельные диски! :-)
В принципе, есть и дополнительный стимул - пока данные на грязном
дисковом массиве не грохнули, он вам будет копией базы, разумеется без
новых данных, но всё таки...
YPermitin; +1 Ответить
30. YPermitin 3337 17.04.19 20:16 Сейчас в теме
(29)
массиве не грохнули, он вам будет копией базы, разумеется без
новых данных, но всё таки...


Ну вот, весь энтузиазм убили :D
31. 3vs 17.04.19 20:20 Сейчас в теме
(30)Не, это побочный эффект! :-)

Можно, даже, для экономии электроэнергии пустой массив отключать!
Хотя, некоторые говорят, что лучше не выключать - установившийся режим лучшее для жёстких дисков, а старт/стоп могут приводить к неисправностям.
Был давно у нас такой случай, сервер работа себе и работал, решили пропылесосить, всё, жёсткий диск больше не включился...
32. genayo 18.04.19 06:22 Сейчас в теме
Что плохого в том, чтобы делать шринк например раз в месяц в технологическое окно? SSD не резиновые так-то :))
33. nicxxx 229 18.04.19 07:32 Сейчас в теме
(32) Написано же, фрагментация 99.9%. Если с highload-ом не сталкивались, то это не очень страшно. А вот когда к диску пойдет 10000 запросов в секунду, а строк в таблицах будет по миллиарду.....
34. genayo 18.04.19 08:59 Сейчас в теме
(33) Ну то есть это не про 1С :))
35. nicxxx 229 18.04.19 09:01 Сейчас в теме
(34) Про 1С, но таких компаний немного. Спроси у брокеров, если есть знакомые, например, БКС или Открытие, как они свои базы 1С обслуживают.
37. YPermitin 3337 18.04.19 20:54 Сейчас в теме
(35) есть еще на просторах родины Highload в 1С =)
38. DonAlPatino 53 23.04.19 16:03 Сейчас в теме
А можно вопросик почти в тему? Что shrink (обрезание) файла для фрагментации индексов очень плохо - это понятно. А как повлияет backup transaction log при full модели восстановления на фрагментацию? Ведь по логике тоже самое происходит... Может кто-нибудь ткнуть носом в хорошую статью по этой теме.
YPermitin; +1 Ответить
39. YPermitin 3337 23.04.19 20:16 Сейчас в теме
(38) нет, шринк лога транзакций не влияет на фрагментацию в файле данных, также не практически не влияет на фрагментацию в самом файле журнала. Но есть нюансы.

Вот отличный материал по этой теме, там есть ссылки над другие статьи, это тема очень обширная. А дочитав до конца Вы поймете как работает лог транзакций.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a538cb11-634d-4f5b-91d6-b2257d903783/shrinking-the-log-file-and-fragmentation?forum=sqltools

Но шринк лога транзакций не должен быть регулярной операцией. Зачем? Если это полная модель восстановления, то достаточно делать бэкап лога, тогда данные в нем будут перезаписываться, если так можно выразиться.
40. DonAlPatino 53 23.04.19 20:40 Сейчас в теме
(39) Дык я и не говорил про шринк лога транзакций. Я спрашивал про backup transaction log и его влияние на фрагментацию индексов.
Скажем в лоб - делаем rebuld index, а потом backup transaction log. Соответственно все данные из transaction log переносятся в файл с данными. И что происходит с индексами в этот момент?
YPermitin; +1 Ответить
41. YPermitin 3337 23.04.19 23:18 Сейчас в теме
(40) понял.

Влияет. При бэкапировании части лога транзакций (VLF), которые уже закоммичены и сохранены в файлы основных данных, помечаются как неактивными. При этом эти части освобождаются в строгом порядке, то есть фрагментация поэтому и появляется и становится возможной.

На производительность это может влиять и есть рекомендации по этому поводу.

Тут дальше можно отдельную статью написать :)
Пару лет назад смотрел вот это: https://www.youtube.com/watch?v=WnLupnOoPXw

Там где-то в видео было подробное описание про влияние бэкапирования на лог транзакций и как с этим бороться. Рекомендую посмотреть.
42. DonAlPatino 53 24.04.19 11:36 Сейчас в теме
(41) Провел тест с утра... Выгрузил базу в бэкап, отребилдил индексы скриптом с https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754, посмотрел степень фрагментации, сделал backup transaction log, посмотрел степень фрагментации ... один в один до backup. В общем похоже backup transaction log на фрагментацию индексов никак не влияет.
Вот что интересно.. в процессе обнаружил, что ребилд скриптом работает значительно лучше чем rebuild index task из Maintance Plan... ибо на обработанной rebuild index task базе снизил фрагментацию раза в два.
43. YPermitin 3337 24.04.19 12:51 Сейчас в теме
(42) мы говорим на разных языках похоже.

Я говорю про фрагменьацию в файле логов.

В файле данных на фрагментацию не влияет, это я еще в прошлом комментарии написал.

Посмотрите видео, там ответы на все ваши вопросы.
44. СергейК 51 24.04.19 23:01 Сейчас в теме
Спасибо за статью.
Натолкнуло на интересную идею:
Для создания тестовой базы минимально возможного размера использовать метод копирования из рабочей в тестовую базу, предварительно в тестовой создав таблицы и применив к ним сжатие, а только потом заливка данными.
Идеально если применительно к своей конфигурации можно было бы исключать некоторые таблицы от копирования, а некоторые ограничить фильтром, например по дате.

Если у кого есть (полу)готовые наработки, поделитесь плз.

p.s. В виду того что тестовая база на другом SQL сервере и соединение между ними 1ГБит, требует проверки скорость такого копирования для базы 400ГБайт...
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

Программист 1С
Владивосток
зарплата от 60 000 руб. до 90 000 руб.
Полный день

Автор новостных обзоров на тему 1С и бухучета
Санкт-Петербург
По совместительству

Программист 1С
Москва
зарплата от 120 000 руб.
Полный день

Руководитель проекта, аналитик, консультант
Санкт-Петербург
По совместительству

Программист 1С
Самара
зарплата от 100 000 руб. до 150 000 руб.
Полный день