Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Catalog · How the catalog works

Random project

blob_opt

Оптимизация данных в полях типа BLOB

View on GitHub

Anyone may press Interesting, signed in or not. It helps others find this project.

Member rating

No ratings yet.

Sign in to rate this project.

README

Оптимизация данных в полях типа BLOB

В Firebird BLOB-ы могут быть двух типов: сегментированные (segmented) и потоковые (stream).

Сегментированные BLOB

Единицей хранения сегментированных BLOB является сегмент. Сегмент это порция данных которой был записан BLOB. В таких BLOB-ах дополнительно хранятся границы сегментов. Максимальный размер сегмента составляет 65535 Байт.

Сегменты могут быть разного размера, например если вы записываете 80 Кбайт данных, то такой BLOB может содержать два сегмента 50Кбайт и 30КБайт, или три 30КБайт, 30КБайт и 20 КБайт, или любое другое количество и размер сегментов. Всё зависит от того какими порциями вы записывали BLOB.

Важно

Необязательное предложение SEGMENT SIZE которое указывается при объявлении BLOB полей, никак не влияет на размер сегментов, только если приложение не использует эти сведения из метаданных, для определения размера сегментов которыми должен быть записан BLOB.

При чтении сегментированных BLOB, за один API вызов isc_get_segment (IBlob::getSegment) будет прочитано указанное количество байт, но не более чем размер сегмента, который был записан. Таким образом, даже если вы пытаетесь прочесть 1 Мбайт BLOB порциями по 65535 Байт, но который был записан сегментами по 1 Кбайт, то вы вам потребуется 1024 (1 Мбайт / 1 Кбайт) вызова isc_get_segment, чтобы прочитать такой BLOB целиком.

Сегментированные BLOB хорошо подходят для хранения структур данных фиксированного размера. Однако при хранении данных вроде произвольного текста или изображений возникают дополнительные издержки из-за хранения границ сегментов. Кроме того, дополнительные издержки могут возникнуть при чтении сегментированных BLOB, если они записаны сегментами маленького размера. Отсюда совет: если вы не сохраняете структуры фиксированного размера, всегда пишите BLOB максимальным размером сегмента. Если необходимо сохранять структуры фиксированного размера, то пишите BLOB сегментами размерами этой структуры, это упростит их считывание.

Агрегатная функция LIST всегда создаёт временный сегментированный BLOB с сегментами размером равными суммарному размеру аргументов, если конечно вы не объединяете BLOB-ы.

Потоковые BLOB

Потоковые BLOB представляют собой просто поток байтов. В отличие от сегментированных BLOB они не сохраняют размер порций, которыми были записаны. Это обозначает что вы можете считывать такие BLOB любыми порциями, не зависимо от того какими порциями вы записывали их. То есть если вы записали 1 МБайт BLOB порциями по 1 КБайт, а читаете порциями по 50 КБайт, то вам потребуется приблизительно 20 (1 Мбайт / 50 Кбайт) вызовов isc_get_segment, чтобы прочитать такой BLOB целиком.

Потоковые BLOB имеют ещё одно преимущество перед сегментированными — они поддерживают позиционирование с помощью функции isc_seek_blob (IBLOB::seek). Однако учтите, что такое позиционирование работает только для BLOB размер которых не превышает 2 ГБайт.

Возможные проблемы приложений при работе с сегментированными BLOB

Некоторые приложения написаны так что они записывают поля типа BLOB сегментами с небольшим размером.

В наихудшем случае компоненты доступа могут ориентироваться на метаданные, где зачастую записано что-то вроде

sql
PIC  BLOB SUB_TYPE 0 SEGMENT SIZE 80

В таком случае такие компоненты доступа могут записывать сегментами по 80 байт. Это может негативно отразиться на скорости чтения таких BLOB полей клиентским приложением.

Советы по улучшению производительности при работе с BLOB

  • По возможности используйте потоковые BLOB
  • Производите запись BLOB как можно большими кусками (максимальный размер 65535 байт)
  • Производите чтение BLOB как можно большими кусками (максимальный размер 65535 байт)
  • Не вызывайте isc_blob_info, если в этом нет реальной необходимости, поскольку вызов этой API функции требует дополнительные сетевые пакеты.

Вы можете проанализировать каким образом ваши данные хранятся в BLOB полях с помощью утилиты blob_opt. Если BLOB является сегментированным, количество сегментов больше 1, и размер таких сегментов мал, вы можете оптимизировать хранение ваших данных в BLOB полях. Для этого необходимо прочитать BLOB и перезаписать его либо как потоковый, либо с максимальным размером сегмента.

Анализ BLOB полей с помощью графической утилиты blob_opt

Важно

Утилите blob_opt требуется установленная 64-разрядная библиотека fbclient.dll. Если библиотека не установлена, то поместите её в каталог с исполняемым файлом.

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

Утилита может работать в двух режимах:

  • строить запросы в автоматическом режиме (установлена галочка "Autobuild SQL query")
  • использовать запросы из заранее подготовленных файлов с расширением .sql

Утилита доступна как в варианте с графическим интерфейсом, так и в консольном варианте.

Для работы утилиты в режиме автоматического построения запросов обязательно требуется заполнить следующие поля:

  • Имя таблицы
  • Имя BLOB поля
  • Имя ключевого поля. Если не заполнено используется псевдо-поле RDB$DB_KEY

Кроме того вы можете ограничить количество обрабатываемых строк с помощью ограничителя "Limit rows", и задать условие фильтрации в "Where filter".

Для работы утилиты с заранее подготовленным запросом, необходимо выбрать файл SELECT запроса в "Select SQL Filename". При этом необходимо обязательно указать имя BLOB поля и имя ключевого поля.

В файле SELECT запроса должен быть запрос, который читает данные из искомой таблицы. Этот запрос должен выбирать два поля: поле первичного ключа и поле типа BLOB. Если в таблице нет первичного ключа или он является составным, то вы можете воспользоваться служебным полем RDB$DB_KEY.

Пример содержимого файла select.sql:

sql
select 
  id,
  pic
from t
where pic is not null

Вы можете использовать любые фильтры которые ограничат размер выборки. Например, можно использовать функцию OCTETS_LENGTH для отсечки совсем маленьких BLOB.

После настройки приложения вы можете сохранить их нажав кнопку "Save settings". В этом случае при следующем старте приложения будут загружены сохранённые параметры.

Для анализа BLOB полей необходимо нажать кнопку "Analyze". Результат анализа будет выведен в Memo поле и сохранён в файл журнала. Галочка "Reading time in statistic" позволяет узнать время чтения данных из BLOB поля. Чтение по возможности идёт максимально большими кусками, то есть по 65535 байт.

Пример статистики при анализе BLOB полей:

pascal
Key paperid=1; ReadTime:    0,197 ms;
NumSegments: 1; MaxSegmentSize: 574; TotalSize: 574; BlobType: Segmented;

Key paperid=2; ReadTime:    0,247 ms;
NumSegments: 1; MaxSegmentSize: 55080; TotalSize: 55080; BlobType: Segmented;

Key paperid=3; ReadTime:    0,221 ms;
NumSegments: 1; MaxSegmentSize: 46416; TotalSize: 46416; BlobType: Segmented;

Key paperid=4; ReadTime:    9,294 ms;
NumSegments: 55; MaxSegmentSize: 65535; TotalSize: 3562695; BlobType: Segmented;
...

Анализ BLOB полей с помощью консольной утилиты BlobOpt

Для получения справки по ключам консольной утилиты наберите команду

pascal
BlobOpt --help

После чего будет выведена следующая справка:

pascal
Usage: d:\IBase\blob_opt\console\BlobOpt.exe <options>

Commands:
  -h or --help - help for usage blobopt util
  -a or --analyze - analyze blob field
  -o or --optimize - optimize blob field

Common options:
  -d <database> or --database=<database> - database connection string
  --user=<username> - database user name
  --password=<password> - database password
  --charset=<charset> - connection character set
  --role=<role> - database role name
  --table=<tablename> - the table in which the blob is analyzed or optimized. Used only if sqlSelectFile or sqlModifyFile options are not specified
  --keyfield=<keyfield> - name of the key field, if not specified, then rdb$db_key is used
  --blobfield=<blobfield> - name of the blob field
  --filter=<filter> - WHERE filter for auto build select sql query. Not used when sqlSelectFile is not specified
  --rows=<rows> - limiter ROWS for auto build select sql query. Not used when sqlSelectFile is not specified
  --sqlSelectFile=<filename> - name of the file containing the select query to analyze or optimize the BLOB field

Options for analyze:
  --readstat - blob field read statistics

Options for optimize:
  --blobType=<blobtype> - convert ot segmented or stream blob
  --maxSegmentSize=<segement_size> - maximum segment size for segmented blobs
  --sqlModifyFile=<filename> - name of the file containing the update query to optimize the BLOB field

Пример использования консольной утилиты для анализа BLOB полей

pascal
BlobOpt --database=localhost/3052:f:\fbdata\2.5\NEWDEMO.FDB --user=SYSDBA --password=masterkey --table=wnppaper --blobfield=paper --analyze --readstat --rows=10 > log.txt

В отличие от графической утилиты, консольный вариант существует также для операционных систем семейства Linux.

Оптимизация BLOB полей с помощью утилиты BlobOpt

Данная функциональность только в консольной версии.

Поддерживается два режима оптимизации:

  • преобразование сегментированных BLOB полей в потоковые
  • преобразование потоковых BLOB полей в сегментированные, или перезапись сегментированных BLOB полей с заданным максимальным размером сегмента.

Во втором случае я рекомендую задавать размер сегмента как можно больше, но не выше 65535.

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

В файле sqlModifyFile должен быть указан UPDATE запрос, который обновляет данные в искомой таблицы. Этот запрос должен обновлять BLOB поле, и осуществлять фильтрацию по полю первичного ключа. Если в таблице нет первичного ключа или он является составным, то вы можете воспользоваться служебным полем RDB$DB_KEY.

Пример содержимого файла для ключа sqlModifyFile

sql
update wnppaper
set paper = :paper
where paperid = :paperid

Пример команды для оптимизации BLOB поля

pascal
BlobOpt --database=localhost/3052:f:\fbdata\2.5\NEWDEMO.FDB --user=SYSDBA --password=masterkey --table=wnppaper --blobfield=paper --optimize --rows=10 --blobType=segmented --segmentSize=32000

Как пишут/читают популярные компоненты доступа

IBX2 for Lazarus

По умолчанию читает и пишет BLOB размером сегмента от 8 Kбайт до 65535 байт, в зависимости от используемых методов.

  • TIBBlobStream пишет блоб по 65535 байт, если это возможно
  • однако при использовании IBLob.LoadFromStream и IBlob.LoadFromFile порция данных почему-то снижается до 8 Кбайт.
  • IBLob.Write можно писать любыми порциями вплоть до 65535 байт

Поддерживает потоковые BLOB. Можно легко перейти на уровень объектного API и писать/читать BLOB с любым размером сегмента не превышающим максимального.

IBX for Delphi

По умолчанию читает и пишет BLOB размером сегмента 16Kб. Поддерживает потоковые BLOB. Можно легко перейти на уровень ISC API и писать/читать BLOB с любым размером сегмента не превышающим максимального.

FireDac

По возможности читает и пишет порциями по 65535 байт.

PHP pdo_firebird

По возможности читает и пишет порциями по 65535 байт. При чтении BLOB всегда запрашивает isc_blob_info чтобы узнать размер BLOB. Это как минимум генерирует один лишний сетевой пакет. На самом деле запрашивать длину BLOB для того, чтобы прочитать его полностью, нет никакой необходимости.

Firebird ODBC

По умолчанию BLOB читает и пишет BLOB размером сегмента 16Kб.

ADO .NET Provider

Довольно неожиданно, но ADO .NET Provider по умолчанию пишет блобы сегментами равными размеру сетевого пакета. Это параметр FbConnection.PacketSize. Этот же параметр можно задать в строке подключения. Для этого параметра указан следующий комментарий

The size (in bytes) of network packets. PacketSize may be in the range 512-32767 bytes.

По умолчанию размер сетевого пакета равен 8192 байт.

JDBC Jaybird

По умолчанию пишет BLOB сегментами размером 16 Кб. Этот размер можно переопределить непосредственно перед использованием FBBlob. Поддерживается использование потоковых BLOB.

Ссылки для скачивания утилиты blob_opt

Исполняемые файлы можно скачать по ссылкам