Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Имеется вот такая табличка:
create table TBL (
   x                    smallint                       not null,
   y                    smallint                       not null,
   z                    smallint                       not null,
   s                    int                             default 0 not null,
   c                    smallint                       not null,
   d                    datetime                     default GETDATE() not null,
   i                     image                          null,
   constraint PK_TBL primary key (x, y, z)
)
go

create index IDX_TBL on TBL (
x ASC,
y ASC,
z ASC,
s ASC
)
go


В неё влиты 25299 записей.
ПК кластерный.
Проца sp_spaceused выдаёт
reserved = 349464 KB - забиваем
data = 348792 KB - собственно "кластер"
index_size = 360 KB - хвост индекса

В поле s хранится реальный размер i в байтах. Прямая сумма по нему даёт 319641871. То есть в среднем 12634 байта на картинку. Такая вот куча маленьких картиночек. Хотя есть экземпляры и больше 100k, то есть они как бы с точки зрения мировой революции и маленькие, но всё же уже не совсем крошечные.

А теперь о грустном. Складываем (data+index_size) и вычитаем 319641871 - получаем 37889777 байт. Если поделить на 25299 - получим что хранение одной такой картиночки обходится в лишних 1497 байт, что превышает 10% от среднего значения размера картиночки. Понятно что в случае хранения этого безобразия в файловой системе, потери в размере были бы больше. Однако вопрос касается минимизирования этих потерь. Потому что по соседним похожим таблицам суммарным размером в десятки-сотни гигов набегают уже не просто какие-то жалкие мегабайты. Операции - 99% insert и select, а вот update и delete - только одиночные, и то они бывают реже, чем случается 29 февраля в Афганистане.

Есть что-то типа "размера страницы", чтобы подкрутить его в сторону уменьшения с 2k до 1k?
Могу сравнить с некоторыми другими СУБД (после "подкрутить") ровно на этих же данных - есть где примерно также, а есть где заметно меньше, например на PostgreSQL строго меньше 1k выходит.

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86)
6 дек 12, 00:04    [13584939]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Сергей Васкецов
create index IDX_TBL on TBL (
x ASC,
y ASC,
z ASC,
s ASC
)

ПК кластерный.


В поле s хранится реальный размер i в байтах.

И что будет, когда у Вас размер картинки поменяется, например, сжатие? Поле s поменяется - начнется перестройка кластерного индекса со всеми "прелестями" данного процесса? Почему в первичном ключе - размер картинки, что обозначают x, y, z?
Сергей Васкецов
превышает 10% от среднего значения размера картиночки...Потому что по соседним похожим таблицам суммарным размером в десятки-сотни гигов набегают уже не просто какие-то жалкие мегабайты...

Вообще не вижу логики. Взяли статистику одной таблицы и механически перенесли на другую. "Буксир водоизмещением x тонн тратит y тонн топлива на 1 морскую милю. Атомный авианосец водоизмещением z*x тонн тратит y*z тонн топлива на 1 морскую милю" - вот как-то так.
6 дек 12, 00:13    [13584965]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
Сергей Васкецов,

Проверьте, включена ли опция TEXT IN ROW
6 дек 12, 00:13    [13584966]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Andy_OLAP
Поле s поменяется - начнется перестройка кластерного индекса со всеми "прелестями" данного процесса? Почему в первичном ключе - размер картинки

Уважаемый, либо внимательнее, либо спокойнее. Поле s не входит в кластерный ПК.

Andy_OLAP
что обозначают x, y, z?

А вас не волнует вопрос, как нам распустить ОВИР? (Ц) КВН
Достаточно, что там произвольные smallint. Тем более что так оно и есть )))

Andy_OLAP
Взяли статистику одной таблицы и механически перенесли на другую

Данные схожи, инфа 100%, вся "статистика" per row отличается на сотню байт максимум.

alexeyvg
опция TEXT IN ROW

Выключена.
Да и я как бы хотел наоборот "ужать" таблицу, а не впихнуть в неё ещё и блобы до 7000 байт, тем более что 7000 байт - несколько неактуальная величина в рассматриваемом случае.
6 дек 12, 00:25    [13584992]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
Сергей Васкецов
тем более что 7000 байт - несколько неактуальная величина в рассматриваемом случае.
Ну, распределенитя я не знаю, просто предположил, что перерасход как раз из за маленьких картинок.
Сергей Васкецов
Да и я как бы хотел наоборот "ужать" таблицу, а не впихнуть в неё ещё и блобы до 7000 байт
Тогда вопрос то какой??? В таблице у вас тратится по 16 байт на картинку, в виде ссылки на страницу. Сильнее не ужмёте.

Хотя странно, перечитал стартовый пост ещё раз - вопрос вы задаёте не про место для таблицы, а про место для картинок. Неувязочка.
6 дек 12, 00:32    [13585012]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Имеет смысл попробовать заменить image на varbinary(max) + "натянуть" ему 'large value types out of row'=1?
6 дек 12, 00:35    [13585020]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
alexeyvg
вопрос вы задаёте не про место для таблицы, а про место для картинок

Я сравниваю суммарный размер картинок с размером таблицы, в которую они залетели. Разница в кило-полтора на каждую картинку - это не 16 байт на ссылку на хранилище LOB, это как бы не смертельно, но всё же неприятно.
6 дек 12, 00:38    [13585030]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
Сергей Васкецов
Имеет смысл попробовать заменить image на varbinary(max) + "натянуть" ему 'large value types out of row'=1?
Ну так я про это вам и говорю.

Понятно, что это лучше, чем TEXT IN ROW из соображений совместимости, но в принципе то же самое, и реализовано так же.

Сергей Васкецов
Я сравниваю суммарный размер картинок с размером таблицы, в которую они залетели. Разница в кило-полтора на каждую картинку - это не 16 байт на ссылку на хранилище LOB, это как бы не смертельно, но всё же неприятно.
Что то вы совсем запутались :-)

Вы уж определитесь с терминологией, где хранится картинка - в таблице или нет (в принципе верны оба варианта, но вы используете их оба в одном описании проблемы).
6 дек 12, 00:57    [13585085]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Сергей Васкецов
заменить image на varbinary(max) + "натянуть" ему 'large value types out of row'=1?

Попробовал. Перелил эти же данные в новую таблу. Удельный оверхед стал вообще 5 килобайт (((((

alexeyvg
где хранится картинка - в таблице или нет

Я хочу чтобы картинка хранилась НЕ в таблице.
Я ничего не делал для чтого, чтобы image-ы падали в row.
Так что картинки хранятся по идее ВНЕ таблицы, скрипт же выше ничего другого не допускает.
Если конечно мне неизвестна какая-то хитрая тонкость.
6 дек 12, 01:14    [13585113]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
defragmentator
Member

Откуда:
Сообщений: 20504
Сергей Васкецов
в среднем 12634 байта на картинку. Такая вот куча маленьких картиночек.
...
А теперь о грустном. Складываем (data+index_size) и вычитаем 319641871 - получаем 37889777 байт. Если поделить на 25299 - получим что хранение одной такой картиночки обходится в лишних 1497 байт, что превышает 10% от среднего значения размера картиночки.


Непонятно, что плохого или грустного в том, что индекс занимает какое-то место?
Если бы картинки были по 1МБ, то Вам бы было не так грустно, а так это просто издержки производства (то есть хотелок).
Как-то так.
6 дек 12, 10:04    [13585753]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
defragmentator
Непонятно, что плохого или грустного в том, что индекс занимает какое-то место?

ПК кластерный, единственный приличный индекс торчит над ним маленьким хвостиком размером с int, более того в sp_spaceused его размер выдаётся, по сравнению с полутора килобайтами на запись индекс просто ничтожен (если посчитать 360*1024/25299 - будет 14 байт на запись от индекса).

defragmentator
Если бы картинки были по 1МБ, то Вам бы было не так грустно

Ну в общем-то да. Аналогичная грусть наступила бы, если бы гранулярность был соответственно кил этак 100 ))

defragmentator
а так это просто издержки производства

Я вполне согласен на издержки. Равно как на аналогичные по сути издержки, когда эти файлики упали бы в файловую систему. И про цены на HDD я тоже в курсе )).

Мне просто непонятна величина этих "издержек", и можно ли как-нибудь её уменьшить.
Есть конечно мысль, что на @@version имеет место заметная неуменьшаемная гранулярность выделения памяти под такие блобики - и хоть ты тресни, но пока что подтверждения этому я не нашёл.
6 дек 12, 14:27    [13588207]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Причём судя по отсутствию такого оверхеда при тестовых вставках типа
declare @i int, @c int
select @c = count(*) from TBL
select @i = @c+1

while (@i <= (@c+64)) begin
  insert into TBL (x,y,z,c,s,i)
  values (@i,@i,@i,@i,1,0x11)

  select @i=@i+1
end

, в рамках самой таблицы всё отлично, а "беда" именно с блобами.
6 дек 12, 14:41    [13588339]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Сергей Васкецов,

Проведём такой эксперимент.

USE tempdb;
GO
IF OBJECT_ID(N'dbo.Test_Image') IS NOT NULL DROP TABLE dbo.Test_Image;
GO
CREATE TABLE dbo.Test_Image(Id INT IDENTITY PRIMARY KEY, Image_Column IMAGE);
GO
INSERT dbo.Test_Image(Image_Column) VALUES(REPLICATE('1', 4020));
GO 1000
SELECT u.*
FROM sys.partitions p
     INNER JOIN sys.allocation_units u ON u.container_id = p.hobt_id
WHERE p.object_id = object_id('dbo.Test_Image');
GO


В таблице 1000 записей примерно по 4 килобайта каждая, то есть около 4 Мб данных. При этом IN_ROW_DATA занимают 7 страниц, а LOB_DATA — 1001 страницу. То есть 8 Мб. Оверхед сами посчитаете :)

Теперь заменим в скрипте 4020 на 4019. IN_ROW_DATA остаётся равным 7 страниц, а LOB_DATA уменьшается до 513 страниц.

Видимо, SQL Server _сначала_ нарезает image на куски, а _затем_ ищет страницы, в которые эти куски могли бы поместиться. Если кусок никуда не помещается — выделяет ему новую страницу.
6 дек 12, 15:24    [13588686]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Гость333
Если кусок никуда не помещается — выделяет ему новую страницу.

Общая логика, что сервер хапает память не по одному байту, и так очевидна. Вопрос в том, можно ли как-то уменьшить размер такой страницы, или хотя бы при создании чего-нибудь (инстанса, базы, таблицы,... если нельзя по-живому) указать меньшее значение.
6 дек 12, 16:48    [13589412]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Сергей Васкецов
Вопрос в том, можно ли как-то уменьшить размер такой страницы

Размер страницы данных в MSSQL всегда равен 8 кб, и изменить это нельзя никакими настройками.
6 дек 12, 16:56    [13589481]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Гость333
всегда равен 8 кб, и изменить это нельзя никакими настройками.

Благодарю. Именно ответ такого типа я и ждал.
зы. В тексте процы sp_saceused захардкодено 8k, но были смутные надежды...

Теперь продолжение маолезонского балета. Может ли @@version, выделив datapage, забить её хвостами BLOBиков из разных записей полностью под завязку, а не выделять на каждую строку в таблице по своей странице? Ну или другими словами, может ли он такое (цифры условны, просто попробую донести суть вопроса):
1. упал блоб 12k.
2. под него выделили 8k+8k, во второй странице 4k пустых.
3. упал ещё блоб 12k.

далее хочется что-то типа:
4а. под него выделили 8k новых, а хвост 4k сунули первому в конец.
или
4б. его сунули в конец первому, а под остаток 8k выделили новую страницу 8k.

а то сейчас получается что:
4. под него выделили 8k+8k, во второй странице 4k пустых - итого 8k пустых после первых двух блобов.
6 дек 12, 17:10    [13589589]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Glory
Member

Откуда:
Сообщений: 104760
Сергей Васкецов
далее хочется что-то типа:
4а. под него выделили 8k новых, а хвост 4k сунули первому в конец.
или
4б. его сунули в конец первому, а под остаток 8k выделили новую страницу 8k.

Страница ссылаются друг на друга. Место ссыли строго определно.
Все значение блоба выбирается путем выборки цепочки страниц
Поэтому нельзя ничего "сунуть" в часть чужую страницу.
Потому что невозможно будет сослаться на эту часть
6 дек 12, 17:26    [13589704]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Сергей Васкецов,

Вот нашёл красивые картинки, возможно они что-то прояснят :)
ntext, text, and image Data When text in row Is Set to OFF
6 дек 12, 17:41    [13589820]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Гость333
Вот нашёл красивые картинки, возможно они что-то прояснят

Они только запутывают, если читать что там написано, потому что написано что то что я хочу - должно работать:
автор
For example, if an application first writes 1 KB of image data, this is stored as the first 1-KB block of image data for the row. If the application then writes 12 KB of image data, then 7 KB is combined with the first 1-KB block so the first block becomes 8 KB. The remaining 5 KB forms the second block of image data. (The actual capacity of each ntext, text, or image page is 8080 bytes of data.)

Или тут под write имеется в виду операция записи ОДНОГО блоба в ОДНОЙ записи?
автор
Because the blocks of text, ntext, or image data and the root structures can all share space on the same text, ntext, or image pages, SQL Server 7.0 uses less space with small amounts of text, ntext, or image data than earlier versions of SQL Server. For example, if you insert 20 rows that each have 200 bytes of data in a text column, the data and all the root structures can all fit on the same 8-KB page.

Ставить семёрку? ))))))))))

Glory
Поэтому нельзя ничего "сунуть" в часть чужую страницу.
Потому что невозможно будет сослаться на эту часть

Если можно сослаться только на начало страницы - тогда да, грустно ((
6 дек 12, 18:00    [13589970]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8734
Если уж совсем принципиально место под данные, то храните картинки в одном невъебенном поле одной невъебенной записи.... А в записях основной таблицы храните индексы выборки по невъебенному полю (смещение + длинну).

х..ня какая-то, в общем, получится ... Но зато сжатенько...
6 дек 12, 18:03    [13589982]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
SIMPLicity_
х..ня какая-то, в общем, получится ... Но зато сжатенько...

Получится свой Remote Blob Storage с квадратными колёсами
6 дек 12, 18:17    [13590089]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Glory
Member

Откуда:
Сообщений: 104760
FILESTREAM ?
6 дек 12, 18:25    [13590122]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Я как раз пытаюсь уйти от хранения микрокартинок в ФС )).
6 дек 12, 18:29    [13590139]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Glory
Member

Откуда:
Сообщений: 104760
Сергей Васкецов
Я как раз пытаюсь уйти от хранения микрокартинок в ФС )).

Зачем тогда блоб поле ?
Нужно varbinary(8000) тогда
6 дек 12, 18:33    [13590154]     Ответить | Цитировать Сообщить модератору
 Re: Как сэкономить место в узкой табличке с image  [new]
Сергей Васкецов
Member

Откуда:
Сообщений: 20362
Glory
Нужно varbinary(8000) тогда

))))
Чтобы почти любой insert обламывался с кодом odbc SQL_ERROR=-1 и мессагой типа
'22001:8152:[MICROSOFT][SQL SERVER NATIVE CLIENT 10.0][SQL SERVER]Символьные или двоичные данные могут быть усечены.'
?
Я уже пробовал varbinary(max) - ещё хуже получается.
6 дек 12, 20:21    [13590694]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить