TantorLabs 23 мая в 17:29

Работа с длинными строками в PostgreSQL

Средний

9 мин

3.3K

Блог компании Тантор ЛабсPostgreSQL*Базы данных*1С*

Обзор

В полях типов text и bytea могут храниться данные до 1 Гб. Если размер строки в процессе обработки превышает 1 Гб, возникают ошибки нехватки памяти. Обнаруживают такие ошибки обычно при выгрузке данных утилитой pg_dump, которая преобразует бинарные данные в текстовый вид.

Когда строки таблицы выгружаются командой COPY TO (вообще, когда происходит обработка данных любыми командами), выделяется память под строковый буфер, причем выделяется он динамически в процессе обработки каждой строки, а не при запуске серверного процесса. Максимальный размер буфера задан в stringinfo.c константой MaxAllocSize, которая равна 0x3fffffff, что равно 1 Гб – 1 байт (1 073 741 823 байт).

По умолчанию команда COPY выгружает значения полей в текстовом формате. В этом формате для символов наподобие перехода на новую строку, табуляции, забоя используются спецпоследовательности \r, \t, \b, которые в текстовом виде занимают два байта. Поэтому, если поле содержит такие спецсимволы, при выгрузке в текстовом виде его размер может превысить 1 Гб. Вот пример:

create table t (c1 text);
insert into t (c1) VALUES (repeat(E'a\n', 357913941));
COPY t TO '/tmp/test';

При выполнении команды COPY будет выдаваться ошибка:

ERROR:  out of memory
HINT:  Cannot enlarge string buffer containing 1073741822 bytes by 1 more bytes.

Размер поля – треть гигабайта. При выгрузке в текстовом виде содержимое поля заменяются на последовательность символов: a\na\na\na\n и размер поля увеличится в три раза до 1073741823 байт, что на 1 байт превышает максимальный размер буфера строк (1 Гб – 1 байт). Такую строку можно выгрузить, используя формат binary:

postgres=# COPY t TO '/tmp/test' WITH BINARY;
COPY 1

Утилита pg_dump не имеет опции для выгрузки данных с WITH BINARY, и это создаёт неудобство: поля большого размера приходится выгружать отдельно и исключать их из выгрузки утилитой pg_dump.

При выгрузке поля bytea также выгружаются в текстовом виде, но размер полей этого типа увеличивается побайтно и выдаётся ошибка о том, что нельзя выделить 1 байт памяти или больше:

ERROR:  out of memory
DETAIL:  Cannot enlarge string buffer containing 1073741822 bytes by 1 more bytes.

Для выгрузки таких полей или строк также можно использовать бинарный формат: COPY .. TO .. WITH BINARY;

Выгрузка длинных строк утилитой pg_dump

Создадим базу данных testdb и таблицу с длинной строкой:

create database testdb;
\c testdb \\
drop table if exists t;
create table t (c1 text storage external, c2 text storage external);
insert into t (c1) VALUES (repeat('a', 1024*1024*512));
update t set c2 = c1;

Параметр storage external используется, чтобы поля в TOAST не сжимались. Один повторяющийся символ сжимается хорошо, и TOAST займёт 12 Мб. На результаты команд это не влияет, но само по себе ближе к практике.

Попробуем выгрузить базу данных утилитой pg_dump:

postgres@tantor:~$ pg_dump -c -C -d testdb -f testdb.sql 
pg_dump: error: Dumping the contents of table "t" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR:  out of memory
DETAIL:  Cannot enlarge string buffer containing 536870913 bytes by 536870912 more bytes.
pg_dump: detail: Command was: COPY public.t (c1, c2) TO stdout;

Возникла ошибка невозможности выгрузить содержимое таблицы из-за превышения размера памяти под string buffer. Сначала память была выделена под поле столбца c1 размером 536 870 913 байт (512 Мб + 1 байт). При выделении памяти под поле столбца c2 возникла ошибка, поскольку суммарный размер превысил ограничение на 2 байта.

Такие поля придётся выгружать по частям: по столбцам или фильтруя строки и выгружая проблемные строки отдельно по полям. Размер поля любого типа не может превышать размер строкового буфера, поэтому в таблицах не может быть полей, размер которых превышал бы 1 Гб (https://docs.tantorlabs.ru/tdb/ru/16_8/se/limits.html). Данные из одного столбца всегда можно выгрузить в бинарном виде.

Варианты ошибок, связанных со строковым буфером

Ошибки, связанные со строковым буфером, могут варьироваться. Вот несколько примеров:

1) Ошибка "invalid memory alloc request size", причина которой – попытка выделить блок памяти, превышающий установленное макросом MaxAllocSize ограничение в 1 Гб – 1. Ошибка с текстом "invalid memory alloc request size" вызывается проверкой на превышение этого ограничения.

postgres=# create table a as select repeat('a', 1024 * 1024 * 1024 - 5) a;
ERROR:  invalid memory alloc request size 1073741887

2) Ошибка "array size exceeds the maximum allowed" при работе с массивами.

3) В текстовых функциях типа lpad(..), repeat(..) используются проверки, которые выдают менее пугающую ошибку: "requested length too large"

postgres=# select repeat('x', 1024 * 1024 * 1024);
ERROR:  requested length too large

4) Ошибка "invalid DSA memory alloc request size", возникающая при проверках превышения ограничений при выделении памяти типа Dynamic Shared Memory (DSA), которая используется при распараллеливании команд.

Если размер памяти, который указан после текста ошибки, превышает 2 Гб, то это означает, что размер памяти, который должен быть выделен, рассчитан неверно, и это может указывать на наличие повреждения записей в блоках данных. Также расчет может быть выполнен неверно из-за ошибок в библиотеках расширений, загруженных в память процесса.

Параметр конфигурации enable_large_allocations

Для решения проблемы выгрузки-загрузки строк, превышающих 1 Гб, в СУБД Tantor Postgres имеется параметр enable_large_allocations, который увеличивает размер строкового буфера до 2 Гб. Параметр можно установить на любом уровне, а также параметром утилит pg_dump, pg_dumpall.

Пример установки параметра на уровне функции:

CREATE OR REPLACE FUNCTION buff() 
  RETURNS int 
  LANGUAGE plpgsql 
  SET enable_large_allocations = true
AS $function$ 
  BEGIN  
    drop table if exists a;  
    create table a as select repeat('a', 1024 * 1024 * 1024 - 5) a;  
    RETURN (select length(a) from a); 
    END;
$function$
;    
      buff 
------------ 
 1073741819
(1 row)

Команда создания таблицы выполнилась без ошибки. Без параметра команда выдаст ошибку.

Пример использования параметра утилитой pg_dump

Выгрузим содержимое базы данных с использованием параметра --enable-large-allocations:

postgres@tantor:~$ pg_dump -c -d testdb -f testdb.sql --enable-large-allocations

Данные были выгружены в файл размером чуть больше 1 Гб:

postgres@tantor:~$ ls -al testdb.sql
-rw-r--r-- 1 postgres postgres 1073742755 testdb.sql

Проверим, что полученный файл с дампом выполняется без ошибок:

postgres=# \i testdb.sql
...
SET
CREATE TABLE
ALTER TABLE
COPY 1
testdb=# show enable_large_allocations; 
 enable_large_allocations
-------------------------- 
 on
(1 row)

Команда COPY успешно загрузила строку. В примере параметр явно не устанавливался, однако данные загрузились, и на уровне сессии параметр был enable_large_allocations, значение которого было установлено утилитой pg_dump и сохранено в файле дампа:

postgres@tantor:~$ head -n 20 testdb.sql
--
-- PostgreSQL database dump
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET enable_large_allocations TO on;

Характеристики параметра enable_large_allocations

testdb=# select * from pg_settings where name like '%large%' \gx-
[ RECORD 1 ]---+--------------------------------------------
name            | enable_large_allocations
setting         | on
unit            | 
category        | Resource Usage / Memory
short_desc      | Sets whether to use large memory buffers - greater than 1 Gb, up to 2 Gb
extra_desc      | 
context         | superuser
vartype         | bool
source          | session
min_val         | 
max_val         | 
enumvals        | 
boot_val        | off
reset_val       | off
sourcefile      | 
sourceline      | 
pending_restart | f

Использование параметра увеличивает максимальный размер строкового буфера с 1 до 2 Гб. Строковый буфер не ограничен параметрами конфигурации, в том числе work_mem.

Процесс может выделить память вплоть до максимального размера строкового буфера, при этом в операционной системе может запрашиваться двойной размер от необходимого.

Объем выделяемой памяти

Проверим, что при выполнении скрипта дампа "\i testdb.sql" выделяется объем памяти 4 Гб, а не 2 Гб. Перед выполнением скрипта можно запустить терминал с командой free для наблюдения за свободной памятью в Linux и выполнить скрипт повторно:

postgres@tantor:~$ echo 1 | sudo tee /proc/sys/vm/drop_caches
1
postgres@tantor:~$ while :; do free -h; ps -C postgres -o vsz,rss,pss,cmd --sort -pss | head -2;  sleep 2; done

               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       1.0Gi       5.8Gi       193Mi       1.4Gi       6.7Gi
   VSZ   RSS   PSS CMD
234028 153280 50482 postgres: postgres postgres [local] DROP TABLE waiting

               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       1.3Gi       5.8Gi       193Mi       1.0Gi       6.4Gi
   VSZ   RSS   PSS CMD
758320 461504 359247 postgres: postgres postgres [local] COPY

               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       2.0Gi       4.5Gi       193Mi       1.7Gi       5.8Gi
   VSZ   RSS   PSS CMD
1282608 1169728 1067691 postgres: postgres postgres [local] COPY

               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       3.2Gi       3.2Gi       193Mi       1.8Gi       4.6Gi
   VSZ   RSS   PSS CMD
4952632 2459200 2355475 postgres: postgres postgres [local] COPY

               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       5.1Gi       1.3Gi       193Mi       1.9Gi       2.7Gi
   VSZ   RSS   PSS CMD
6525504 4347456 4246215 postgres: postgres postgres [local] COPY

               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       5.1Gi       184Mi       193Mi       3.0Gi       2.7Gi
   VSZ   RSS   PSS CMD
6525504 4347456 4246193 postgres: postgres postgres [local] COPY

               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       1.1Gi       4.2Gi       193Mi       3.0Gi       6.7Gi
   VSZ   RSS   PSS CMD
234028 153184 51869 postgres: postgres postgres [local] idle
^C

При работе команды COPY в скрипте дампа выделяется память размером 4 Гб, с 1 Гб до ~5 Гб. Команда сброса страничного кэша приведена для удобства, чтобы при повторных выполнениях скрипта размер страничного кэша Linux оставался таким же.

При этом, размер таблицы (с TOAST) составляет немногим более 1 Гб:

postgres=# \dt+ public.t
                                   List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+------+-------+----------+-------------+---------------+---------+-------------
 public | t    | table | postgres | permanent   | heap          | 1063 MB | 
(1 row)
В каком контексте выделена память? Можно получить PID серверного процесса функцией pg_backend_pid() и вывести в журнал список контекстов памяти функцией pg_log_backend_memory_contexts(PID) во время работы команды:

LOG:  logging memory contexts of PID 94931
LOG:  level: 0; TopMemoryContext: 110000 total in 6 blocks; 10320 free (28 chunks); 99680 used
LOG:  level: 1; TopTransactionContext: 8192 total in 1 blocks; 7544 free (0 chunks); 648 used
...
LOG:  level: 1; TopPortalContext: 8192 total in 1 blocks; 7664 free (0 chunks); 528 used
LOG:  level: 2; PortalContext: 1073770368 total in 6 blocks; 448 free (1 chunks); 1073769920 used: <unnamed>
LOG:  level: 3; ExecutorState: 8192 total in 1 blocks; 7200 free (0 chunks); 992 used
LOG:  level: 4; ExprContext: 1073774704 total in 5 blocks; 32352 free (27 chunks); 1073742352 used
LOG:  level: 3; COPY: 4295073944 total in 6 blocks; 32040 free (15 chunks); 4295041904 used
...
LOG:  Grand total: 6445717424 bytes in 296 blocks; 1365376 free (344 chunks); 6444352048 used

В каком контексте выделена память? Можно получить PID серверного процесса функцией pg_backend_pid() и вывести в журнал список контекстов памяти функцией pg_log_backend_memory_contexts(PID) во время работы команды:

LOG:  logging memory contexts of PID 94931
LOG:  level: 0; TopMemoryContext: 110000 total in 6 blocks; 10320 free (28 chunks); 99680 used
LOG:  level: 1; TopTransactionContext: 8192 total in 1 blocks; 7544 free (0 chunks); 648 used
...
LOG:  level: 1; TopPortalContext: 8192 total in 1 blocks; 7664 free (0 chunks); 528 used
LOG:  level: 2; PortalContext: 1073770368 total in 6 blocks; 448 free (1 chunks); 1073769920 used: <unnamed>
LOG:  level: 3; ExecutorState: 8192 total in 1 blocks; 7200 free (0 chunks); 992 used
LOG:  level: 4; ExprContext: 1073774704 total in 5 blocks; 32352 free (27 chunks); 1073742352 used
LOG:  level: 3; COPY: 4295073944 total in 6 blocks; 32040 free (15 chunks); 4295041904 used
...
LOG:  Grand total: 6445717424 bytes in 296 blocks; 1365376 free (344 chunks); 6444352048 used

Память выделяется в в контексте портала. Размер памяти, выделенной командой COPY, 4 295 073 944 байт в 6 блоках. Всего виртуальной памяти выделено 6 Гб. Выделенная память не зависит от числа строк и размера файла дампа, поскольку команда COPY обрабатывает данные построчно.

Сколько памяти выделится при выполнении команды SELECT, а не COPY? Выполним команду:

postgres=# \o x.txt
postgres=# select * from t;

В окне терминала:

free -h
               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       3.1Gi       1.3Gi       193Mi       3.9Gi       4.7Gi

ps -C psql -o vsz,rss,pss,cmd --sort -pss
   VSZ   RSS   PSS CMD
2123176 2107276 2100639 psql
ps -C postgres -o vsz,rss,pss,cmd --sort -pss
   VSZ   RSS   PSS CMD
234028 153664 52056 postgres: postgres postgres [local] idle

Утилитой psql было выделено ~2 Гб памяти. Серверный процесс выделил немного, RSS=153664 байт.

При выполнении SELECT с обычным размером буфера будет выдана ошибка:

postgres=# select * from public.t;
ERROR:  out of memory
DETAIL:  Cannot enlarge string buffer containing 536870922 bytes by 536870912 more bytes.

при этом в контекстах памяти ExecutorState и printtup будет выделено по ~1Гб памяти:

LOG:  logging memory contexts of PID 104038
LOG:  level: 0; TopMemoryContext: 97664 total in 5 blocks; 10704 free (10 chunks); 86960 used
LOG:  level: 1; TopTransactionContext: 8192 total in 1 blocks; 7752 free (0 chunks); 440 used
...
LOG:  level: 1; TopPortalContext: 8192 total in 1 blocks; 7664 free (0 chunks); 528 used
LOG:  level: 2; PortalContext: 1024 total in 1 blocks; 608 free (0 chunks); 416 used: <unnamed>
LOG:  level: 3; ExecutorState: 1073766448 total in 3 blocks; 13808 free (3 chunks); 1073752640 used
LOG:  level: 4; printtup: 1073777512 total in 4 blocks; 7016 free (2 chunks); 1073770496 used
LOG:  Grand total: 2148781008 bytes in 198 blocks; 328192 free (151 chunks); 2148452816 used

Заключение

В PostgreSQL по умолчанию нельзя работать со строками, размер которых превышает 1 Гб, и в данной статье мы рассмотрели ошибки, которые возникают при выгрузке таблиц с длинными полями. Также описано использование параметра конфигурации enable_large_allocations в СУБД Tantor Postgres.

Хабы: