Re: Memory exhaustion due to temporary tables? - Mailing list pgsql-general
On Monday, December 10, 2018, 5:50:22 PM EST, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Is the error message spelling really exactly "Cannot allocate memory"?
Yes - but I think the message is forwarded from Linux. Here is an example:
2018-12-08 00:00:00.070 EST,,,32506,,5bc71a25.7efa,25643,,2018-10-17 07:16:53 EDT,,0,LOG,00000,"could not fork autovacuum worker process: Cannot allocate memory",,,,,,,,,""
Should have been more thoughtful in what I chose to mention. Here is one that may be more familiar, and directly related to the issue I am encountering:
2018-12-08 00:00:03.642 EST,"schema_name","db_name",24949,"10.12.113.56:50138",5bfed8cd.6175,17637,"SELECT",2018-11-28 13:05:01 EST,24/223502,0,ERROR,53200,"out of memory","Failed on request of size 24.",,,,"SQL statement ""CREATE TEMPORARY TABLE table_name ON COMMIT DROP AS
> Also, as mentioned upthread, it'd be interesting to see if there's
A hopefully-helpful short excerpt from the top:
> a memory context dump showing up in your server log.
There are many memory context dumps, and they are long (well over 100 lines apiece). To me these are unfamiliar and hard to read, but I tried to pick one that referenced all the same tables used in the function. I put one such dump (188 lines) here:
TopMemoryContext: 174216 total in 7 blocks; 12520 free (27 chunks); 161696 used
pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 632 free (0 chunks); 7560 used
TopTransactionContext: 24576 total in 2 blocks; 7288 free (3 chunks); 17288 used
Combo CIDs: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
ExecutorState: 8192 total in 1 blocks; 7400 free (0 chunks); 792 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Exec: 2088960 total in 8 blocks; 865976 free (7 chunks); 1222984 used
ExecutorState: 262216 total in 6 blocks; 94344 free (11 chunks); 167872 used
HashTableContext: 8192 total in 1 blocks; 7584 free (0 chunks); 608 used
HashBatchContext: 197104 total in 6 blocks; 0 free (0 chunks); 197104 used
HashTableContext: 8192 total in 1 blocks; 7968 free (0 chunks); 224 used
HashBatchContext: 49240 total in 2 blocks; 8136 free (0 chunks); 41104 used
HashTableContext: 8192 total in 1 blocks; 7584 free (0 chunks); 608 used
HashBatchContext: 197104 total in 6 blocks; 0 free (0 chunks); 197104 used
pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 632 free (0 chunks); 7560 used
TopTransactionContext: 24576 total in 2 blocks; 7288 free (3 chunks); 17288 used
Combo CIDs: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
ExecutorState: 8192 total in 1 blocks; 7400 free (0 chunks); 792 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Exec: 2088960 total in 8 blocks; 865976 free (7 chunks); 1222984 used
ExecutorState: 262216 total in 6 blocks; 94344 free (11 chunks); 167872 used
HashTableContext: 8192 total in 1 blocks; 7584 free (0 chunks); 608 used
HashBatchContext: 197104 total in 6 blocks; 0 free (0 chunks); 197104 used
HashTableContext: 8192 total in 1 blocks; 7968 free (0 chunks); 224 used
HashBatchContext: 49240 total in 2 blocks; 8136 free (0 chunks); 41104 used
HashTableContext: 8192 total in 1 blocks; 7584 free (0 chunks); 608 used
HashBatchContext: 197104 total in 6 blocks; 0 free (0 chunks); 197104 used
Anything I can do to zero in on helpful information, I'm all ears.
Thanks so much!
Tom
pgsql-general by date: