qs=> show shared_buffers; shared_buffers ---------------- 12GB (1 line record) qs=> show work_mem; work_mem ---------- 4MB (1 line record) qs=> show maintenance_work_mem; maintenance_work_mem ----------------------- 8GB (1 line record) qs=> CREATE INDEX ACT_IDX_BYTEARRAY_NAME ON qs.ACT_GE_BYTEARRAY USING btree (NAME_); Error: Out of memory Description: Failed when requesting size 9 in memory context "TupleSort main". Background: Parallel Worker Processes
# free -h
total used free shared buff/cache available
Mem: 31G 759M 25G 739M 5.4G 29G
Swap: 4.9G 0B 4.9G
TopMemoryContext: 384728 total in 11 blocks; 59376 free (8 chunks); 325352 used
Collation cache: 8192 total in 1 blocks; 1536 free (0 chunks); 6656 used
TopTransactionContext: 73728 total in 4 blocks; 72608 free (60 chunks); 1120 used
TupleSort main: 5863645240 total in 686 blocks; 19112 free (22 chunks); 5863626128 used
Caller tuples: 234881024 total in 38 blocks; 2781352 free (12 chunks); 232099672 used
Operator class cache: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
smgr relation table: 24576 total in 2 blocks; 8648 free (3 chunks); 15928 used
pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 1408 free (0 chunks); 6784 used
TransactionAbortContext: 32768 total in 1 blocks; 32464 free (0 chunks); 304 used
Portal hash: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
TopPortalContext: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
Relcache by OID: 16384 total in 2 blocks; 2424 free (3 chunks); 13960 used
CacheMemoryContext: 524288 total in 7 blocks; 243304 free (536 chunks); 280984 used
index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: ACT_IDX_BYTEARRAY_NAME
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: pg_constraint_status_conoid_index
index info: 2048 total in 2 blocks; 544 free (1 chunks); 1504 used: pg_constraint_conrelid_contypid_conname_index
index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: pg_collation_oid_index
index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: pg_index_indexrelid_index
index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: pg_opclass_oid_index
index info: 2048 total in 2 blocks; 712 free (3 chunks); 1336 used: pg_trigger_tgrelid_tgname_index
index info: 2048 total in 2 blocks; 712 free (3 chunks); 1336 used: pg_rewrite_rel_rulename_index
index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: pg_class_oid_index
index info: 2048 total in 2 blocks; 632 free (2 chunks); 1416 used: pg_attribute_relid_attnum_index
index info: 3072 total in 2 blocks; 1088 free (2 chunks); 1984 used: pg_amproc_fam_proc_index
index info: 2048 total in 2 blocks; 984 free (1 chunks); 1064 used: pg_authid_oid_index
index info: 2048 total in 2 blocks; 680 free (2 chunks); 1368 used: pg_auth_members_member_role_index
index info: 2048 total in 2 blocks; 656 free (2 chunks); 1392 used: pg_shseclabel_object_index
index info: 2048 total in 2 blocks; 984 free (1 chunks); 1064 used: pg_database_datname_index
index info: 2048 total in 2 blocks; 984 free (1 chunks); 1064 used: pg_database_oid_index
index info: 2048 total in 2 blocks; 984 free (1 chunks); 1064 used: pg_authid_rolname_index
WAL record construction: 49752 total in 2 blocks; 6320 free (0 chunks); 43432 used
SysContextContext: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
Parallel worker: 8192 total in 1 blocks; 7744 free (1 chunks); 448 used
PrivateRefCount: 8192 total in 1 blocks; 2576 free (0 chunks); 5616 used
MdSmgr: 8192 total in 1 blocks; 7792 free (2 chunks); 400 used
SQL IO Stats Context: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
SQL IO Stats hash: 131072 total in 5 blocks; 61312 free (16 chunks); 69760 used
SQL Wait Event Context: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
SQL Wait Event hash: 131072 total in 5 blocks; 61312 free (16 chunks); 69760 used
SQL Time Stats Context: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
SQL Time Stats hash: 131072 total in 5 blocks; 61312 free (16 chunks); 69760 used
LOCALLOCK hash: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
Function Call Graphs: 16384 total in 2 blocks; 4536 free (3 chunks); 11848 used
Function Lines: 262144 total in 6 blocks; 126296 free (20 chunks); 135848 used
PL/pgSQL profiler: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
plsql portal handle cache: 16384 total in 2 blocks; 4536 free (3 chunks); 11848 used
Rendezvous variable hash: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
PLSQL function hash: 8192 total in 1 blocks; 2576 free (0 chunks); 5616 used
PLSQL package memory: 8192 total in 1 blocks; 2576 free (0 chunks); 5616 used
Timezones: 104120 total in 2 blocks; 2576 free (0 chunks); 101544 used
CritSectionContext: 262144 total in 1 blocks; 261840 free (0 chunks); 304 used
ErrorContext: 8192 total in 1 blocks; 7888 free (5 chunks); 304 used
Grand total: 6100869152 bytes in 832 blocks; 3906248 free (745 chunks); 6096962904 used
2023-11-04 16:15:20.576 CST [4103] Error: Out of memory
2023-11-04 16:15:20.576 CST [4103] Details: Requested size 9 failed in memory context "TupleSort main".
2023-11-04 16:15:20.576 CST [4103] Statement: CREATE INDEX ACT_IDX_BYTEARRAY_NAME ON qs.ACT_GE_BYTEARRAY USING btree (NAME_);
2023-11-04 16:15:20.577 CST [4100] Error: Out of memory
2023-11-04 16:15:20.577 CST [4100] Details: Requested size 9 failed in memory context "TupleSort main".
2023-11-04 16:15:20.577 CST [4100] Context: Parallel Worker Process
2023-11-04 16:15:20.577 CST [4100] Statement: CREATE INDEX ACT_IDX_BYTEARRAY_NAME ON qs.ACT_GE_BYTEARRAY USING btree (NAME_);
2023-11-04 16:15:20.577 CST [4102] Fatal error: Connection interrupted due to administrator command
2023-11-04 16:15:20.577 CST [4102] Statement: CREATE INDEX ACT_IDX_BYTEARRAY_NAME ON qs.ACT_GE_BYTEARRAY USING btree (NAME_);
2023-11-04 16:15:20.577 CST [4102] LOG: could not send on message queue: Other process has detached queue
2023-11-04 16:15:20.577 CST [4102] STATEMENT: CREATE INDEX ACT_IDX_BYTEARRAY_NAME ON qs.ACT_GE_BYTEARRAY USING btree (NAME_);
2023-11-04 16:15:20.688 CST [4057] Log: Background worker process "parallel worker" (PID 4103) exited with exit code 1
2023-11-04 16:15:20.692 CST [4057] Log: Background worker process "parallel worker" (PID 4102) exited with exit code 1
An error is reported in the background. The table size is 6G, the memory is sufficient, shared_buffers and maintenance_work_mem have also increased, but the index still prompts “out of memory” when creating an index. Does anyone know what the problem is?
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. PostgreSQL skill treeHomepageOverview 7069 people are learning the system