Сравнение методов и работа с большими данными

Сравнение методов и работа с большими данными#

Сравнение методов#

Метод

Тип доступа

Возвращает

Учитывает изменения текущей сессии

Использует кеш

Когда использовать:

OQuery

объектный

объекты / Rop

да

да, но с особенностями: загруженные объекты помещаются в кеш, но условие where применяется к данным в БД, а не в кеше

стандартные выборки объектов, бизнес-логика

ElExpOQuery

объектный

объекты / Rop

да

да, частично

динамические запросы, когда тип объекта заранее неизвестен

largeInQuery

объектный

объекты / Rop

да

зависит от вызываемого механизма

выборки по очень большому списку значений IN

TxIndex.byKey

индекс

список Rop

да

да

частый доступ по одному полю с учетом локальных изменений

TxIndex.refreshByKey

индекс

список Rop

да

обновляет состояние

когда нужно принудительно перечитать данные по ключу

load

объектный

Rop

да

да

загрузка объекта по идентификатору

byParent

объектный

список Rop

да

да

чтение дочерних записей, если данные уже могли быть загружены

refreshByParent

объектный

список Rop

да

обновляет состояние

принудительное перечитывание коллекции по мастеру

ASQL

реляционный

строки результата

нет

нет

простые SQL-запросы на чтение

ATSQL

реляционный

результат SQL-операции

нет

нет

изменение данных, блокировки, транзакционные SQL-запросы

ASelect

реляционный

типизированный табличный результат

нет

нет

сложные SQL-выборки с большим количеством колонок

PreparedStatement / JDBC

низкоуровневый реляционный

зависит от реализации

нет

нет

batch insert/update и специализированные низкоуровневые сценарии

Работа с большими данными#

Postgresql не поддерживает bulk insert\select\update, более того, коммит закроет любой открытый курсор. Это делает невозможной обработку больших объемов данных частями на сервере, так как очень длинные транзакции перегружают wal и в конечном счете сильно снижают общую производительность сервера. Поэтому необходимо выполнять партиционирование на сервере приложения.

Замеры производительности показывают, что наиболее производительным средством перегонки данных является sql команда postgresql «copy».

Сравнительная таблица производительности:

Операция 100000 записей

Приблизительное время(ms) на сервере

Вставка записей одним sql в базе

214

Общее время запроса\вставки через copy

728

Вставка записей одним sql в базе при наличии индексов

2785

Общее время вставки через массовый flush при наличии индексов из сервера в базу

19562

Общее время вставки через flush по одной записи при наличии индексов из сервера в базу

156710

Запрос данных через ResultSet

396

Запрос данных через команду copy

330

Примечание

На десктопе разница между вставкой через copy и вставкой прямым sql может достигать различий в 10 раз. Финальные тесты производительности нужно замерять исключительно на серверном оборудовании.

Вставка через flush по одной записи может работать в 1000 раз медленнее, особенно это актуально для данных с аудитом.

Вставка через batch на чистом sql сравнима по порядку с copy, хоть и медленнее.

Массовая вставка на чистом sql должна быть раза в 4 быстрее за счет отсутствия затрат на кеш.

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

Массовая вставка#

Через команду copy.

Массовый update\delete#

  • Сохранение данных в файл через команду copy

  • Для каждой пачки:

    • Начало транзакции;

    • Копирование данных в gtt;

    • Выполнение dml операции;

    • Завершение транзакции.

Внимание

Транзакция не должна завершаться до конца обработки пачки, иначе это приведет к потере данных в gtt(при завершении транзакции сессия отпускается)

Размер транзакции#

PostgreSQL по своей архитектуре менее зависим от длины транзакции (смотри наполнение базы данных). Таким образом нет потребности в дроблении на мелкие транзакции.

Слишком длинные транзакции негативно влияют на базу, происходят следующие негативные эффекты:

  • блокировка данных

  • блокировка сборщика мусора, что может привести к падению производительности запросов на dead row и к распуханию файлов

Таким образом на сервисных операциях, если они не превышают несколько гигабайтов(зависит от базы), разбиением транзакций можно пренебречь.

Желательно, чтобы оперативные транзакции не занимали больше 10 минут (зависит от частоты транзакций и нагрузки на базу) и не вызывали блокировок.

Содержание