0. Fragster 879 27.10.17 11:50 Сейчас в теме

Сжатие данных в базе данных средствами СУБД

На Инфостарте есть статья https://infostart.ru/public/114634/ про то, как навесить триггеры на создание таблиц, чтобы новые таблицы в БД создавались сжатыми. Для существующих баз предлагается загрузить базу из ДТ или сделать ТиИ с реструктуризацией. Предлагаю вашему вниманию скрипт, который сделает это для существующей БД средствами SQL.

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

Комментарии
Избранное Подписка Сортировка: Древо
1. nvv1970 28.10.17 23:30 Сейчас в теме
Описание способа включения сжатия выглядит как рекомендация. Это так?
При этом не указано, сколько % места это экономит и сколько % нагрузки добавляет.
ИМХО место на дисках стоит условно дешевле, чем процессоры. При этом диски можно докупать разной ценовой категории под разные потребности.
2. nvv1970 29.10.17 01:23 Сейчас в теме
(1) ознакомился с темой подробнее. Вопросы сняты. Крайне заинтригован отзывами. Побежал тестировать сжатие )))
3. Fragster 879 31.10.17 10:55 Сейчас в теме
15. nvv1970 17.12.17 13:15 Сейчас в теме
(3) тесты прекрасны. Сжатие порой существенно - на порядок.
При этом чтение ускоряется в 2-3 раза точно.
А вот с записью все грустно. Замедляется раз в 10.
Для хранения например журналов, истории чего либо, с ассинхронной записью в таблицы - само то.
За использование сжатия и синхронной записи - расстрел на месте)))
16. Fragster 879 19.12.17 13:54 Сейчас в теме
(15) >А вот с записью все грустно. Замедляется раз в 10.
не было дефицита процессора? у меня запись, конечно, замедлилась, но не настолько. Восстановление из .dt в два-три раза дольше стало.
17. nvv1970 25.12.17 14:45 Сейчас в теме
(16) Антон, тесты я точно не в 1с проводил )))
Точно уже не вспомню условия теста, но делал его на своем ноуте (i5, ssd). Операция - insert в таблицу1 и в таблицу2.
Примерно аналогичное замедление(+/-) получил на рабочем сервере.
18. Fragster 879 25.12.17 17:32 Сейчас в теме
(17) а, я думал что как минимум что-то типа перепроведения всех документов за период было.
19. nvv1970 25.12.17 23:01 Сейчас в теме
(18) нееее )))))))) ну это же бред ))))
Влияние размывается на тысячи аспектов. Какой удельный вес замедления записи в общей операции - одному богу известно. Наиболее точная оценка от 10% до 90% ))
20. Fragster 879 26.12.17 11:08 Сейчас в теме
(19) ИМХО мерить синтетикой в наших условиях - не совсем то. Самое правильное - тест центр, правда его настраивать задолбаешься :)
Перепроведение хотя бы к реальности ближе.
21. nvv1970 26.12.17 23:32 Сейчас в теме
(20) вообще не понимаю что вы собираетесь мерить, даже ТЦ. И что вам это даст?
Я попытался измерить технологию. Очень приближенно и усредненно. Технология не имеет никакого отношения к 1с. Однако полученный результат все же зависит от состава данных, не спорю.
Производить какие-то замеры в 1с бессмыслица и дилетантство. Одно поведение - 90% дискового времени, другое - 10%. Куда деть процессорное время, ожидания и т.п. чтобы понять чистое влияние сжатия СУБД?
Важно в принципе понимать это чистое поведение, чтобы оценить области его применения. И возможно ли хоть где-то его в 1с применять.
22. Fragster 879 27.12.17 15:53 Сейчас в теме
(21)
Важно в принципе понимать это чистое поведение

Да. Но, например, на ssd обновление строки в СУБД занимает перезапись всего 4кб сектора, что со сжатием, что без сжатия. Таким образом запись больше упирается в процессор. На hdd немного по другому, и при обновлении одной строки действительно нужно читать и писать намного больше данных. + профиль нагрузки, создаваемый 1с действительно очень разнообразный. Для розницы с десятком касс - один, для финансистов с аналитическими отчетами в центральной управленческой базе, в которую оперативные данные сливаются по обменам - совсем другой. Даже сами алгоритмы проведения разные по соотношению чтения/записи. По этому я и говорю, что нужно моделировать именно на конкретном оборудовании с конкретным профилем нагрузки. Более-менее неплохо с этим справляется как раз тест центр от 1с, но он очень долго настраивается. А синтетические тесты - зло. Вот в моем случае пользователи даже не заметили ничего, да и показатели апдекс не поплыли.
4. dm.donetsk 13.12.17 12:08 Сейчас в теме
Скрипт отработал, но ничего не изменилось, размер базы не изменился. До запуска 19,9гб, после 19,9Гб. В чем может быть пролема? Делал для отдельной таблицы
ALTER TABLE [dbo].[TestTable] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)
Результата тоже нет.
sp_estimate_data_compression_savings расчитывала сжатие, почему его нет после выполнения скрипта?
5. sssss_aaaaa_2011 13.12.17 12:19 Сейчас в теме
(4)Сжатие данных в базе (Сжатие базы) и сжатие файлов базы данных - две большие разницы. И первое не обязывает сервер делать второе.
6. dm.donetsk 13.12.17 12:26 Сейчас в теме
(5) Из описания к статье "основным плюсом идет экономия ввода вывода и места на диске". Я так понимаю сжатие базы необходимо делать отдельно?
7. sssss_aaaaa_2011 13.12.17 12:31 Сейчас в теме
(6)Термин "Сжатие базы" некорректный.
Есть сжатие данных и сжатие файлов.
Первое описано в публикации.
Второе нужно делать только по необходимости. И ни в коем случае не на регулярной основе. И да, отдельно.
8. dm.donetsk 13.12.17 12:33 Сейчас в теме
13. Fragster 879 13.12.17 16:26 Сейчас в теме
(8) добавил шринк в конец скрипта
9. sssss_aaaaa_2011 13.12.17 13:50 Сейчас в теме
(0)То же самое, но без курсоров и кучи переменных:
DECLARE @cmd VARCHAR(MAX)

sel ect @cmd =(
SELECT
'PRINT '''+Table_catalog + '.' + Table_schema + '.' + Table_name+ ''''+CHAR(10) +
'ALT ER TABLE [' + Table_catalog + '].[' + Table_schema + '].[' + Table_name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
+';'+char(10)
as "data()"
FR OM
INFORMATION_SCHEMA.TABLES WH ERE TABLE_TYPE = 'BASE TABLE'
ORDER BY Table_catalog, Table_schema, Table_name
for xml path('')
)

Exec(@cmd)
--Print @cmd

SELECT @cmd =(
SELECT
'PRINT '''+DB_NAME() + '.' + sch.name + '.' + tabl.name + '.' + ind.name + ''''+CHAR(10) +
'ALT ER INDEX [' + ind.name + '] ON [' + DB_NAME() + '].[' + sch.name + '].[' + tabl.name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
+';'+char(10) as "data()"
FR OM
sys.schemas as sch
inner join sys.tables as tabl on sch.schema_id = tabl.schema_id
inner join sys.indexes as ind on tabl.object_id = ind.object_id
ORDER BY sch.name, tabl.name, ind.name
for xml path('')
)

Exec(@cmd)
--Print @cmd

Не забудьте убрать лишние пробелы, которые вставляет движок форума.
10. Fragster 879 13.12.17 16:04 Сейчас в теме
(9) неплохо, не знал, что exec работает не только со строками, но и с таблицами.
11. sssss_aaaaa_2011 13.12.17 16:08 Сейчас в теме
(10)Ну здрасте! И где вы там таблицы нашли? :) А закомментированные Print по вашему мнению тоже таблицы выводят? :)
12. Fragster 879 13.12.17 16:17 Сейчас в теме
(11) ну условно если этот select просто выполнить, то результатом будет таблица
14. sssss_aaaaa_2011 13.12.17 16:41 Сейчас в теме
(12)Вы чем там исполняете? Скрипт генерит 2(две) строки, которые и исполняет. И если ваше средство исполнения запросов даже сообщения от Print выводит как таблицу, то скрипт тут совершенно не при чем.
23. a.ivanov 27.12.17 16:59 Сейчас в теме
вот мой скрипт ))

EXEC sp_MSforeachtable 'ALTER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE)'
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)'
wertep; Fragster; +2 Ответить
24. sssss_aaaaa_2011 27.12.17 17:04 Сейчас в теме
25. EALeXx 28.04.18 08:30 Сейчас в теме
Может кто подскажет при попытке сжатия базы скриптом выдается ошибка:
Внутренняя ошибка. Буфер, предоставленный для считывания значения столбца, слишком мал.
База конечно не маленькая но может кто в курсе как можно победить ошибку
26. a.ivanov 28.04.18 08:49 Сейчас в теме
(25)
Буфер, предоставленный для считывания значения столбца, слишком мал


Большая таблица и мало памяти? По английски есть текст ошибки?
27. EALeXx 28.04.18 08:58 Сейчас в теме
(26) Таблицы большие база размером 250 гиг по английски ошибки нет пишет только на русском
28. a.ivanov 28.04.18 09:08 Сейчас в теме
(27) а про запуск CHECKDB есть в сообщении?
29. EALeXx 28.04.18 09:12 Сейчас в теме
30. a.ivanov 28.04.18 09:13 Сейчас в теме
(29) Ну так это первым делом и надо сделать. А потом уже дальше смотреть.
31. EALeXx 28.04.18 09:17 Сейчас в теме
(30) ну так это я сделал и уже дважды а что толку не каких ошибок не обнаружено и все равно ошибка та же выдается
32. PerlAmutor 35 08.06.18 09:55 Сейчас в теме
Это одна из тех технологий, которую нельзя применять бездумно. Первое что стоит сделать - снять статистику по объектам базы данных, а именно по чтению и записи. Если какая-нибудь таблица меняется чаще чем читается, или поровну, то включать сжатие не следует. Если четко видно, что таблица в основном читается, чем пишется, скажем в 80% случаях, то можно и включить.

Статистика статистикой, но в разрезе 1С мы можем принять решение основываясь на простых фактах: в конфигурации есть объекты, которые никогда не используются, или не будут использоваться. Документы и справочники - обычно не меняются тысячами. А вот некоторые регистры чаще читаются чем пишутся или наоборот. В общем тут нужен по-объектный анализ и включением сжатия применительно к вашим условиям.
33. Fragster 879 08.06.18 11:16 Сейчас в теме
можно воспользоваться запросом
SEL ECT  @@ServerName AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_NAME(ddius.object_id) AS TableName ,
        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)
                                                               AS  Reads ,
        SUM(ddius.user_updates) AS Writes ,
        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups
            + ddius.user_updates) AS [Reads&Writes] ,
        ( SEL ECT    DATEDIFF(s, create_date, GETDATE()) / 86400.0
          FR OM      master.sys.databases
          WHERE     name = 'tempdb'
        ) AS SampleDays ,
        ( SELECT    DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig
          FR OM      master.sys.databases
          WHERE     name = 'tempdb'
        ) AS SampleSeconds
FR OM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.object_id = i.object_id
                                     AND i.index_id = ddius.index_id
WHERE    OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY [Reads&Writes] DESC;

GO
Показать

для определения соотношения чтения/записи, но даже если запись осуществляется в 10 раз чаще, профит от сжатия может быть. Нужно тетсить. Как правило все равно процессора больше, чем диска.
34. PerlAmutor 35 08.06.18 11:33 Сейчас в теме
(33) Т.к. форум "ломает" запрос прикреплю его в виде файла, если не возражаете.

С удивлением обнаружил, что таблица _Reference183 (Справочник.ИдентификаторыОбъектовМетаданных) читается чаще всех после регистра накопления ПрочиеРасходыНезавершенногоПроизводства, и при этом туда 0 записей. RLS похоже.

В целом общая статистика по моей базе говорит, что чтений конечно больше. Причем видно, что конфигурацией читаются объекты, которые в принципе не содержат документов вообще. Тем не менее, табличку она "дергает".
Прикрепленные файлы:
ReadWriteStat.sql
35. МихаилМ 08.06.18 12:29 Сейчас в теме
скрипт не учитывает специфику 1с : некоторые данные сжимаются deflate . смысла сжимать такие таблицы нет.
36. Fragster 879 08.06.18 13:10 Сейчас в теме
(35) посмотреть эффект можно с помощью sp_estimate_data_compression_savings
37. Aule2 23.08.18 19:47 Сейчас в теме
По идее если мы не отбираем таблицы с разными типами индексов и не задействуем параметры типа on line = on\off все выборки списка таблиц и индексов можно заменить парой строк
EXEC sp_MSforeachtable 'ALT ER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE, MAXDOP = N)'
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE, MAXDOP = N)'
А если вы счастливый обладатель 2017 MSSQL то можно указать явно RESUMABLE = ON
И после шринка обязательно запустить обслуживание индексов, т.к. операция сжатия полностью фрагментирует индекс
38. St0rmik 01.11.18 22:23 Сейчас в теме
День добрый, а может подскажете как расчитать оборудование для базы размером в 1.7тб и в 2500 пользователей? потому что как только начинаю делать сжатие так сразу по cpu проседать начинаю
39. shmax 4 21.12.18 13:58 Сейчас в теме
Может кому не лень упростит скрипт при помощи которого смогу сжать данные в конкретной таблице.
40. shmax 4 21.12.18 14:55 Сейчас в теме
Написал вот так, синтаксис прошёл, табличка лопатится. как отлопатит напишу, она большая а тестовый сервак не самый мощный.
ALTER TABLE [upp_3].[dbo].[_AccumRg24455] Rebuild partition = all with (data_compression = page)

Опережая вопросы опишу цель: на сервере начало заканчиваться место, путём анализа было выяснено что много файлов хранится в базе. Для выноса их из базы на хранилку сделана доработка требующая реструктуризации таблицы с файлами, этой сцуко большой . при реструктуризации требуется свободное место равное лучше больше этой таблицы а его нет, вот и хочу пожать 3-5 самых больших таблиц у которых статистика в пользу чтения а не записи и мне хватит места на реструктуризацию, выброс файлов на ружу и освобождение места на SQL диске для данных.
41. shmax 4 21.12.18 17:36 Сейчас в теме
всё гуд. всё ужалось, спасибо всем :-)
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

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

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

Работа от Инфостарт
Санкт-Петербург
Временный (на проект)

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

Руководитель отдела внедрения 1С
Новосибирск
зарплата от 60 000 руб. до 160 000 руб.
Полный день