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С
Самара
зарплата от 50 000 руб. до 100 000 руб.
По совместительству


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

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

Программист 1С
Бобров
зарплата от 100 000 руб. до 150 000 руб.
Временный (на проект)