1с sql сжать базу
Доброго времени суток!
Имеется 1С-ная база, которая крутится на SQL 2012.
1) Размер базы 570 ГБ
2) Модель восстановление "Простая"
3) Автоувеличение/максимальный размер "С шагом по 20 МБ, без ограничений"
4) Каждую ночь выполняется план обслуживания: Перестроение индекса, обновление статистики, Создание резервной копии, Очистка старых копий
Захожу в меню "Сжатие файла данных". Система сообщает, что Доступно свободного пространства 26%, а значит базу можно сжать до 420 ГБ. Выполняю сжатие, база ужимается до указанных 420 ГБ.
Через сутки проверяю параметры базы - ее размер снова 570 ГБ!
Делал данную операцию несколько раз, все-равно через день объем базы возростает до 570 ГБ.
Вопрос - почему? И как высвободить это пространство?
А почему Автоувеличение такое маленькое значение* Из какой логики вы исходили ставя такой размер?
Опять таки каждую ночь идет "перестроение индексов и прочее" и вас удивляетесь что база меняет свой размер?
2) "Опять таки каждую ночь идет "перестроение индексов и прочее" и вас удивляетесь что база меняет свой размер" - собственно говоря ДА, удивляюсь. Хотите сказать перестроение индексов увеличивает базу на 26% ?
(5) понятие не имею что вы там делаете днем. Может вы там каждый день перепроводите. Или с утра создаете 100500 документов а в обед удаляете.
Как знать что там у вас происходит. Попробуйте отключить и узнаете
(6) "непонятно зачем ограничивать." - в смысле зачем сжимать?
Чем меньше база, тем меньше она съедает места на жестких + тем меньше бекап и т.д.
(8) "непонятно зачем ограничивать." - Ок, общая рекомендация какая? Т.е. какая рекомендация по настройке при каких поведенческих схемах?
(4) Ого как долго. У меня база тоже 540 гиг, скульный бэкап при работающих пользователях (они работают круглосуточно, просто разные нагрузки) длится в районе - 20- 25 минут.
(11) В том числе. Бекапы потом по нескольким точкам копируются, в том числе на удаленный FTP сервер через инет. Поэтому размер имеет значение.
(13) Не поверишь - ДАННЫЕ!
Какая разница, что мы храним. Что обычно хранят в 1С-ных базах торговые предприятия? Вопрос топика в другом!
(0) Нет смысла файлы шринкать. Только хуже будет.
(9) Бэкап можно делать со сжатием на лету - тогда пофигу на размер файлов.
(3) +100
Сами сказали:
" Каждую ночь выполняется план обслуживания: Перестроение индекса" -Отсюда и размер.
Забейте на шринк.
(17) Это данные за более чем 10 лет.
+ Система контроля изменения данных. НЕ БСП, но место под себя съедает тоже изрядно.
Фотографии в базе НЕ храним
Перестроение индекса и обновление статистики по всем таблицам каждый день - это на самом деле глупости. Современные MSSQL сервера итак неплохо ведут статистику.
Это надо делать при значительных порциях данных заливаемых в конкретную таблицу и сильно изменяющих значения в ключевых полях индексов. И как правило это какое-то не обычное наполнение данными.
И да, а еще одна глупость, это держать такую базу в simple режиме, надо full. Но это не имеет отношения к вопросу в (0).
у меня 55 Гб 7.7 база, уже тяжеловато в работе..
вот чешу репу как ее бы почикать )
пока удаление того что можно безболезненно и относительно быстро удалить сокращает до 47 Гб, хочется ужаться примерно до половины.
(17) У нас база 540 гиг , УПП на обычных формах.
Работаем с 01.07.2012 года.
В год ~ 550 000 Документов реализации + сопутствующие.
(27) Время реакции изрядно улучшится.
Кроме того, общий объём бэкапов с фулл моделью будет меньше. При этом дифф бэкапы могут и не понадобиться.
(27)
А мы ведь бекапы делаем не для того чтобы места занимать по меньше, а для того чтобы при попадании американской крылатой ракеты томогавк в серверную мы потеряли как можно меньше данных. Вот автор темы потеряет данные максимум за весь день. А я потеряю за 15 минут.
Про диф. бекапы, не совсем понял (может имеете в виду бекапы лога?), что а мешает их делать чтобы место сэкономить?
(33)на сервер в другую серверную. Мы исходим из того, что две ракеты ракеты тамогавк одновременно по нам это дорого даже для америки.
(34) Ну у меня локальные Бэкапы + копии на QNAP в другом кабинете, и плюс я приезжаю раз в пару дней с внешним USB диском и копирую туда, диск храню дома.
Паранойя.
(35)ну у нас тоже ходят слухи админы бекапы еще куда-то в обака подливают, но это уже на случай затяжной ядерной войны и это не моя зона ответственности. :)
Тема сжатия баз данных 1С в настоящий момент довольно часто обсуждается. Достоинства сжатия известны – уменьшение размера базы данных, уменьшение нагрузки на дисковую подсистему и некоторое ускорение выполнения тяжелых операций чтения/записи. Из недостатков – небольшое увеличение нагрузки на процессоры сервера СУБД за счет расхода ресурсов на компрессию/декомпрессию данных. Но при использовании в качестве MSSQL и DB2 (за Oracle и PostgreSQL не скажу, т.к. не знаю) есть один «подводный камень» - при выполнении реструктуризации происходит декомпрессия новых таблиц и индексов. Происходить это может как при выполнении обновления конфигурации с изменением структуры метаданных, так и при выполнении тестирования и исправления ИБ (реиндексация пересоздает только индексы, а реструктуризация – и таблицы, и индексы). «Проблема» кроется в том, что признак сжатия устанавливается индивидуально для каждой таблицы и индекса.
Решение проблемы для MS SQL Server.
Создание DDL-триггера для операции создания таблицы:
CREATE TRIGGER [data_compression]
ON ALL SERVER
AFTER CREATE_TABLE
AS
--Текст триггера
Создание DDL-триггера для операции создания индекса:
CREATE TRIGGER [index_compression]
ON ALL SERVER
AFTER CREATE_INDEX
AS
--Текст триггера
Для установки признака сжатия страниц для таблицы необходимо выполнить код:
ALTER TABLE [имя базы].[имя схемы].[имя таблицы] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
Для установки признака сжатия страниц для индекса необходимо выполнить код:
ALTER INDEX [имя индекса] ON [имя базы].[имя схемы].[имя таблицы] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
В общем-то, уже этого достаточно, чтобы создать работоспособный механизм, который не позволит платформе 1С создать новую таблицу или индекс без сжатия, но мне захотелось большей гибкости в настройках :).
Ниже приведу решение в том виде, как оно используется у нас. Это позволило автоматически применить сжатие для всех баз данных на сервере.
Создана служебная база CompressionSetting с двумя таблицами:
1) Databases - для хранения списка баз, которые сжимать НЕ требуется.
CREATE TABLE [dbo].[Databases](
[name] [nvarchar](100) NULL ,
[active] [int] NULL
) ON [PRIMARY]
Из ~200 баз на сервере я внес в эту таблицу только одну – tempdb:
2) Trace – для мониторинга работы DDL-триггеров
CREATE TABLE [dbo].[trace](
[text] [nvarchar]( max ) NULL ,
[DatabaseName] [nvarchar]( max ) NULL ,
[DateTime] [datetime] NULL
) ON [PRIMARY]
Далее создал 2 DDL-триггера:
Сразу после создания триггеров никаких изменений в размере баз, конечно, не произойдет. Для сжатия уже имеющихся баз можно воспользоваться следующими методами:
1) Написать скрипт для поочередного перебора таблиц и индексов и установки для них признака сжатия. Мне этот вариант не понравился, т.к. на сжатие больших таблиц требуется очень много времени, база при этом раздувается, а потом очень долго уменьшается через SHRINK DATABASE.
2) Выполнить полную реструктуризацию через «Тестирование и исправление» . По времени быстрее, но база также раздувается и ее потом придется уменьшать через SHRINK DATABASE.
3) Самый оптимальный вариант, на мой взгляд – пересоздать базу через dt-файл. При этом готовая база изначально будет минимального размера, а время загрузки базы со сжатием мало отличается от загрузки в обычном режиме.
Относительно допустимости применения подобного метода сжатия я задавал вопрос в партнерской конференции, на что был в итоге получен следующий комментарий Сергея Нуралиева:
«…Можно считать позицией то, что мы считаем, что использование этих возможностей должно быть или запрещено или разрешено, но с адекватной поддержкой (методологической или программной). Вариант использования их без надлежащей поддержки мы считаем неправильным, так как он приводит к проблемам в администрировании.»
Таким образом, не думаю, что предлагаемый мной подход должен использоваться массово.
Администратор, решившийся на это, должен четко понимать что он делает и какие последствия могут быть.
В
Шринк (shrink) - это сжатие файлов данных для уменьшения занимаемого ими пространства на диске. Выполняется за счет перемещения страниц данных с конца файла в свободное пространство в начало файла. После этого страницы в конце файла становятся неиспользованными и это пространство может быть возвращено файловой системе.
Стоит различать сжатие файла данных (shrink) со сжатием строк и страниц для таблиц и индексов.
Также есть шринк лога транзакций, но он также не совсем относится к теме статьи.
В стародавние времена считалось нормальным выполнять шринк базы данных. Некоторые администраторы даже настраивали регламентные процедуры для "шринкования" вне рабочего времени. К счастью, такое уже редко где можно увидеть, но случаи еще есть.
Почему шринк не стоит делать регулярно на рабочем окружении? Вот основные причины:
- Сама по себе операция сжатия файла данных очень ресурсоемка и на больших базах может выполняться десятки часов, а то и больше. Также во время ее выполнения пользователи могут, и скорее всего будут, ощущать замедление работы системы.
- Значительное снижение эффективности работы индексов. Шринк переносит страницы в начало файла, так он может перенести и индекс. Причем часть его страниц может быть в самом начале файла, другая где-то по середине, а третья вообще разбросана где попало. В итоге фрагментация индекса будет под 99.9%, что значительно снижает производительность. Индексы попросту не могут быть использованы должным образом. Спасти может только перестроение или реорганизация (иногда), но это снова может увеличит размер файла данных. Есть отличная статья об этом от Brent Ozar.
- Появление дополнительных задержек при увеличении файла данных. После шринка база занимает минимальный размер, но если информационная система жива, то новые данные в нее будут поступать снова и снова. Новые данные = нужно больше места. Каждое увеличение файла данных потребует дополнительных ресурсов. Оптимизировать можно уменьшив количество таких операций за счет большего шага автоувеличения файла в настройках базы данных. О влиянии на производительность можно узнать вооооот здесь.
Постойте! Но статья же о быстром шринке! Если сам по себе он так плох, то неужели статью можно уже закончить?
Конечно, нет! Бывают ситуации, когда шринк для базы целесообразен. Замете, целесообразен, но не обязателен!
Вот несколько кейсов, когда его стоит использовать:
- В базе данных произошли серьезные изменения. Например, вы удалили какой-либо исторический регистр из базы 1С, который занимал 100 ГБ. И если эти 100 ГБ важны и их нужно освободить на диске, то от шринка не уйти.
- Вы применили сжатие страниц для таблиц и индексов, что уменьшило размер занимаемых данных на 70%! А файл базы данных на диске не изменился! Снова шринк! Но опять же, если место и правда нужно под что-то освободить, ведь рано или поздно данные в базе смогут занять его снова.
- Вы готовите тестовую базу и удаляете из нее данные, чтобы она не весила 1ТБ. Без шринка тоже никуда, но вопрос производительности может быть неактуальным. Ведь это тестовая среда.
Вообщем, смысл думаю уже понятен.
Классический путь
Стандартный способ выполнить шринк файлов базы данных - воспользоваться такими командами как "SHRINKDATABASE" или "SHRINKFILE". Вот примеры.
Разница между ними заключается лишь в том, что "SHRINKDATABASE" сжимает все файлы данных и журналы транзакций для указанной базы, а "SHRINKFILE" применяет изменения только для указанного файла.
На практике использовать шринк всей базы данных практически никогда не приходится. Да что говорить, если сам шринк - это последнее дело, которое нужно делать, то необходимость применять его для всей базы вообще редкость. В случае если без него не обойтись, то лучше применять его для конкретного файла с помощью второй инструкции.
Также для каждой операции могут быть указаны дополнительные параметры, о который вы можете узнать по ссылкам:"SHRINKDATABASE" или "SHRINKFILE".
Выше мы уже говорили, что сам по себе шринк является болезненной операцией для базы данных как с точки зрения производительности, так и с позиции времени обслуживания. У Вас не должно остаться сомнений по поводу того, что стандартный способ ужать файлы очень неоптимальный и его регулярное применение не имеет никакого смысла.
Ах да, ни в коем случае не ставьте в настройках базы данных включенной опцию "Auto Shrink".
Все еще сомневаетесь? Прочитайте перевод статьи от разработчика Microsoft, который имел прямое отношение к алгоритму сжатия файлов.
Быстрее, выше, сильнее
Есть и другой способ сжать файл данных, при этом он будет быстрее, т.к. стандартные операции шринка будут использоваться по минимуму.
Речь идет о переносе данных из одной файловой группы в другую, при котором есть два пути:
- Старую файловую группу, из которой данные перемещены, после этого удаляют.
- Если исходную файловую группу удалить нельзя (например, если это группа "PRIMARY"), то ее можно обработать стандартной операцией шринка. В любом случае это будет быстрее, чем использовать стандартное сжатие на исходном файле.
На сколько перемещение данных в новую файловую группу быстрее, чем стандартный шринк? Трудно сказать, т.к это зависит от дисковой подсистемы, где находятся файловые группы, а также от объема BLOB-данных в базе. Как показала практика, чем больше таких данных хранится, тем медленнее выполняется шринк, тем быстрее будет работать сжатие через файловые группы.
На моем опыте использование этого подхода позволяло ускорить сжатие файла данных от 3 до 8 раз, да и последствий для производительности куда меньше.
Как это сделать для баз 1С? Допустим, у нас есть некоторая база "bsl" на инстансе SQL Server и мы решили ее сжать через файловые группы. Т.к. файловая группа по умолчанию только одна, то нужно добавить еще одну дополнительную и файл данных для нее.
Теперь с помощью этих скриптов мы можем переместить все таблицы, индексы и даже BLOB-данные в эту файловую группу вот так.
Скрипт не универсальный, по крайней мере в текущей его версии, и не может перенести таблицы, в которых, например, только одно поле с BLOB-типом. Для баз 1С это таблица "DBSchema" с описанием структуры базы данных, которую автоматически в новую файловую группу переместить нельзя. Для этого нужно выполнить немного ручной работы:
Так как файловую группу "PRIMARY" удалить нельзя, то мы можем ее сжать через стандартный шринк. Это уже будет работать гораздо быстрее, чем выполнение этой операции до переноса данных.
В итоге все данные перемещены в новую файловую группу, что можно проверить с помощью этого скрипта.
По ссылке выше в своей статье Paul S. Randal этот способ рекомендовал использовать вместо стандартного сжатия данных. Так почему бы не прислушаться? Если бы исходную файловую группу можно было бы удалить (если это не "PRIMARY"), то можно было бы сделать следующее.
В этом случае файл на диске, на котором ранее располагалась исходная файловая группа, был бы сначала освобожден от всех данных, в потом удален. "PRIMARY" удалять нельзя, т.к. она содержит различную служебную информацию о базе, которую также и переместить в другую файловую группу нельзя.
Конечно, у этого способа есть минус - он требует больший объем свободного места на диске, т.к. при перемещении данных исходный файл сразу не уменьшается в размере. Но по сравнению с остальными плюсами и недостатками стандартного шринка - это может быть несущественным.
База в базу
Можно пойти дальше и переносить данные не в новые файловые группы, а в новую базу данных. Довольно радикальный способ, но работоспособный. Можно выделить несколько основных способов перемещения таблиц и индексов между базами:
- Использовать Bulk-операции, как, например, описано здесь.
- Штатный мастер импорта и экспорта данных
- Использовать утилиту "sqlpackage.exe", входящую в состав SQL Server Data Tools.
- Сгенерировать скрипт с помощью SQL Server Managment Studio
- Использовать операцию "INSERT INTO".
Подробнее на этом способе останавливаться не будем. За более подробной информацией и развернутыми примерами Вы можете обратиться к отличной статье о шести различных способах передачи данных между базами для SQL Server.
Вы все еще шринкуете?
На этом все. Думаю, в публикации не открыл особо ничего нового для сообщества, но может хотя бы скрипты пригодятся. Главное помнить - шринку нет места в продакшене! Хватит шринковать! :)
При использовании MS SQL появляется проблема, когда размеры расположенных баз данных на физическом носителе увеличиваются до огромных объемов.
Одно из решений — это покупка нового жесткого диска с большим объемом памяти. Но тот же самый MS SQL Server предлагает более экономичное решение (бесплатное) — свои собственные функции (как сжатие). Ниже представлены четыре основных метода по решению данной проблемы.
Шаг 1: Правая кнопка мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database)
Шаг 2: Нажимаем на «ОК»
Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).
Метод 2: Использование Transact SQL Command
Шаг 1: Открываем наш SQL Server Management Studio
Шаг 2: Подключаемся к необходимой Базе данных
Шаг 3: Нажимаем на «Создать запрос» (New Query)
Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)
Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.
Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:
Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину,
Не применяет сохранение данных, если значения являются 0 и NULL
Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.
Осуществим сжатие по строкам.
Алгоритмы действия данного сжатия заключается в том, что система проходит по всей таблице. Если видит повторяющиеся значения, то вместо копирования этих данных, система создает ссылки на них. Аналогично осуществляется с общими префиксами.
Данное сжатие позволяет максимизировать кол-во строк, которые хранятся на странице,
Повторы данных заменяются ссылками, если происходит сжатие по префиксу.
Пример: используем ту же самую таблицу на 14 500 строк.
Осуществим сжатие по страницам.
Результат: занимаемое пространство данными уменьшилось до 2МВ.
Различия между сжатием на уровне страниц и строк
Если кратко резюмировать выше описанные способы, то главное различие между 3 и 4 способом – это данные которые используются в самой базе данных.
Если вам известно, что БД использует огромное количество повторяющихся значений, то лучше использовать «Сжатие на уровне страниц» (Метод 4), т.к. система хранит ссылки на эти значения, а не дублирует данные. В остальных случаях лучше использовать «Сжатие на уровне рядов» (Метод 3). Первые 2 метода используются по желанию.
Негативные факторы при использовании сжатия:
Частое сжатие Базы Данных не рекомендуется, т.к. сжатие приводит к фрагментации таблиц.
Размер базы данных никаким образом нельзя сделать меньше, чем минимальный размер этой БД. Пример: если базу данных создали с размером 5 МВ и она увеличилась до 50 МВ, то ее можно сжать только до изначального созданного размера в 5МВ (даже с пустыми столбцами и строками).
Чтобы достичь наибольшего эффекта от сжатия, ее нужно применять после операций, которые после своего применения создают большое количество неиспользуемого пространства в БД (удаление таблиц).
Сжатие таблицы в MS SQL позволяет существенно сэкономить дисковое пространство. Помимо экономии места, повышается производительность запросов, т.к. уменьшается количество обрабатываемых строк. При правильном выборе метода, мы можем увидеть значительное освобождение места для записи новых данных. Таблица на 14 500 строк это доказала (уменьшение размера в 2 и в 5 раз).
Подсистема работает в конфигурациях 8.2 и 8.3 в толстом, тонком и WEB -клиенте (обычные и управляемые формы). Сейчас сложилась парадоксальная ситуация. Есть конфигурации, которые используют обычные формы, есть которые работают на управляемых формах, а также есть те, которые работают и так, и так. Наша подсистема позволяет работать в любом режиме, причем если будет запущена в толстом клиенте, то будет интерфейс и формы толстого клиента, если в тонком или WEB, то на управляемых формах. При этом практически не будет никакой разницы с точки зрения функциональности.
Регистрируются изменения для следующих видов объектов: константы, справочники, документы, планы видов характеристик, планы счетов, планы видов расчета, бизнес-процессы, задачи и регистры сведений.
Для хранения истории изменений объектов используется внешняя информационная база 1 C «Хранитель журнала регистрации». Был выбран именно механизм внешнего хранения изменений, т.к. он не влияет на размер основной информационной базы 1 C , а работа напрямую с внешней базой данный позволяет быстро производить чтение и запись событий.
Алгоритм работы подсистемы такой: при произведении изменения объекта, полный образ изменённого объекта попадает в так называемый «кэш журнала регистрации» – справочник в подсистеме, в который попадают все данные по измененным объектам. Затем, фоновое задание запускает перенос данных из кэша во внешнюю базу данных хранителя, данные просто переносятся из кэша. Далее в информационной базе хранителя происходит определение изменений. Что это значит? Приведем пример. Допустим пользователь «А» создал номенклатуру, заполнил ее и сохранил. Затем пользователь «Б» через время открыл ее и изменил в ней один реквизит, после чего также сохранил. При этом наша подсистема позволит Вам увидеть, как то, что пользователь «Б» изменил без показа всех остальных реквизитов, табличных частей и т.д., так и полные образы объектов. Это позволяет точно сказать, что было изменено пользователем, а что нет. Удобно, не правда ли?
При просмотре истории данные по изменениям подгружаются из внешней базы хранителя и из кэша. Причем данные, которые находятся в кэше в журнале «подсвечиваются» темно-красным цветом , данные которые находятся в базе хранителя и не определены изменения отображаются светло-серым цветом , а данные которые обработаны обычным черным цветом. Как уже было сказано ранее, данные в кэше содержат полный образ объекта на момент изменения объекта (содержат все реквизиты, все табличные части, все предопределённые реквизиты), а в хранителе хранятся те же полные образы, но уже с определением изменений, т.е. как было и как стало.
После переноса «сжатых» данных из кэша в базу данных хранителя, изменения в кэше удаляются. Таким образом, общий размер информационной базы остается практически неизменным.
Читайте также:
- Регистрация конфигурации в центре лицензирования не выполнена 1с
- Какая система пожаротушения в paint store на судне
- На какие типы новостей можно подписаться в рассылке сайта информационной системы 1с итс
- Эксель двойной клик по ячейке не работает
- Приказ по унифицированной форме т 1 в программах фирмы 1с можно распечатать из формы документа