Postgres Pro
Downloads
Home   >   mailing lists

Thread: Vacuum problem on large table

Vacuum problem on large table

From
mordicus
Date:
This table have 1005540 tuples

Index on issn field.

CREATE TABLE "record_blob" (
        "issn" character varying(9) NOT NULL,
        "data" varchar(10000)
);

vacuum verbose analyse record_blob;
NOTICE:  --Relation record_blob--
NOTICE:  Pages 62437: Changed 0, reaped 0, Empty 0, New 0; Tup 1005540: Vac
0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 80, MaxLen 2032; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 4.57s/0.37u sec.
NOTICE:  --Relation pg_toast_44260091--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 2: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 63, MaxLen 2034; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_toast_44260091_idx: Pages 2; Tuples 2. CPU 0.00s/0.00u
sec.
NOTICE:  Analyzing...
ERROR:  Memory exhausted in AllocSetAlloc(875769886)


In pglog i have :
TopMemoryContext: 57392 total in 4 blocks; 1968 free (4 chunks); 55424 used
TopTransactionContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DeferredTriggerXact: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
TransactionCommandContext: 57344 total in 3 blocks; 44968 free (24 chunks);
12376 used
QueryContext: 8192 total in 1 blocks; 7496 free (1 chunks); 696 used
Vacuum: 8192 total in 1 blocks; 8152 free (0 chunks); 40 used
DeferredTriggerSession: 8192 total in 1 blocks; 8176 free (0 chunks); 16
used
CacheMemoryContext: 516096 total in 6 blocks; 213592 free (94 chunks);
302504 used
pg_user: 5120 total in 5 blocks; 912 free (0 chunks); 4208 used
PortalMemory: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
MdSmgr: 8192 total in 1 blocks; 6120 free (0 chunks); 2072 used
DynaHash: 24576 total in 2 blocks; 6392 free (2 chunks); 18184 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ERROR:  Memory exhausted in AllocSetAlloc(875769886)

Help ! :)

Re: Vacuum problem on large table

From
Tom Lane
Date:
mordicus <mordicus@free.fr> writes:
> vacuum verbose analyse record_blob;
> NOTICE:  --Relation record_blob--
> NOTICE:  Pages 62437: Changed 0, reaped 0, Empty 0, New 0; Tup 1005540: Vac
> 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 80, MaxLen 2032; Re-using:
> Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 4.57s/0.37u sec.
> NOTICE:  --Relation pg_toast_44260091--
> NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 2: Vac 0,
> Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 63, MaxLen 2034; Re-using:
> Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
> NOTICE:  Index pg_toast_44260091_idx: Pages 2; Tuples 2. CPU 0.00s/0.00u
> sec.
> NOTICE:  Analyzing...
> ERROR:  Memory exhausted in AllocSetAlloc(875769886)

This looks like corrupt data --- specifically, a varlena value with a
ridiculous length word --- in your table.  Hard to say more with so
little information.

            regards, tom lane