Re: Out of Memory errors are frustrating as heck!

Gunther <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <[email protected]> 
After applying Tomas' corrected patch 0001, and routing HJDEBUG messages 
to stderr:

integrator=# set enable_nestloop to off;
SET
integrator=# explain analyze select * from reports.v_BusinessOperation;
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 16 to 32
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 32 to 64
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 64 to 128
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 128 to 256
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 256 to 512
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 512 to 1024
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 1024 to 2048
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 2048 to 4096
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 4096 to 8192
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 8192 to 16384
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 16384 to 32768
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 32768 to 65536
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 65536 to 131072
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 131072 to 262144
WARNING:  ExecHashIncreaseNumBatches: increasing number of batches from 262144 to 524288
ERROR:  out of memory
DETAIL:  Failed on request of size 32800 in memory context "HashBatchContext".

Now

TopMemoryContext: 4347672 total in 9 blocks; 41688 free (18 chunks); 4305984 used
   HandleParallelMessages: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
   TableSpace cache: 8192 total in 1 blocks; 2096 free (0 chunks); 6096 used
   Type information cache: 24352 total in 2 blocks; 2624 free (0 chunks); 21728 used
   pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 416 free (0 chunks); 7776 used
   TopTransactionContext: 8192 total in 1 blocks; 5416 free (2 chunks); 2776 used
   RowDescriptionContext: 8192 total in 1 blocks; 6896 free (0 chunks); 1296 used
   MessageContext: 32768 total in 3 blocks; 13488 free (10 chunks); 19280 used
   Operator class cache: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
   smgr relation table: 32768 total in 3 blocks; 16832 free (8 chunks); 15936 used
   TransactionAbortContext: 32768 total in 1 blocks; 32512 free (0 chunks); 256 used
   Portal hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
   TopPortalContext: 8192 total in 1 blocks; 7664 free (0 chunks); 528 used
     PortalHoldContext: 24632 total in 2 blocks; 7392 free (0 chunks); 17240 used
     PortalContext: 1482752 total in 184 blocks; 11216 free (8 chunks); 1471536 used:
       ExecutorState: 2449896 total in 16 blocks; 1795000 free (3158 chunks); 654896 used
         HashBatchFiles: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashTableContext: 16384 total in 2 blocks; 3008 free (6 chunks); 13376 used
           HashBatchContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashBatchFiles: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashTableContext: 8192 total in 1 blocks; 7320 free (0 chunks); 872 used
           HashBatchContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashBatchFiles: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashTableContext: 8192 total in 1 blocks; 7320 free (0 chunks); 872 used
           HashBatchContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashBatchFiles: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashTableContext: 8192 total in 1 blocks; 7752 free (0 chunks); 440 used
           HashBatchContext: 90288 total in 4 blocks; 16072 free (6 chunks); 74216 used
         HashBatchFiles: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashTableContext: 8192 total in 1 blocks; 7624 free (0 chunks); 568 used
           HashBatchContext: 90288 total in 4 blocks; 16072 free (6 chunks); 74216 used
         TupleSort main: 1073512 total in 11 blocks; 246792 free (39 chunks); 826720 used
         TupleSort main: 286912 total in 8 blocks; 246792 free (39 chunks); 40120 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         HashBatchFiles: 2242545904 total in 266270 blocks; 3996232 free (14164 chunks); 2238549672 used
         HashTableContext: 8192 total in 1 blocks; 7624 free (5 chunks); 568 used
           HashBatchContext: 168165080 total in 5118 blocks; 7936 free (0 chunks); 168157144 used
         TupleSort main: 452880 total in 8 blocks; 126248 free (27 chunks); 326632 used
           Caller tuples: 1048576 total in 8 blocks; 21608 free (14 chunks); 1026968 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
         ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
   Relcache by OID: 16384 total in 2 blocks; 3512 free (2 chunks); 12872 used
   CacheMemoryContext: 1101328 total in 14 blocks; 288672 free (1 chunks); 812656 used
     index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_2619_index
     index info: 2048 total in 2 blocks; 968 free (1 chunks); 1080 used: entity_id_fkidx
...
     index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_attribute_relid_attnum_index
     index info: 2048 total in 2 blocks; 952 free (1 chunks); 1096 used: pg_class_oid_index
   WAL record construction: 49768 total in 2 blocks; 6368 free (0 chunks); 43400 used
   PrivateRefCount: 8192 total in 1 blocks; 2624 free (0 chunks); 5568 used
   MdSmgr: 8192 total in 1 blocks; 6560 free (1 chunks); 1632 used
   LOCALLOCK hash: 16384 total in 2 blocks; 4600 free (2 chunks); 11784 used
   Timezones: 104120 total in 2 blocks; 2624 free (0 chunks); 101496 used
   ErrorContext: 8192 total in 1 blocks; 7936 free (5 chunks); 256 used
Grand total: 2424300520 bytes in 271910 blocks; 7332360 free (17596 chunks); 2416968160 used
2019-04-21 19:50:21.338 UTC [6974] ERROR:  out of memory
2019-04-21 19:50:21.338 UTC [6974] DETAIL:  Failed on request of size 32800 in memory context "HashBatchContext".
2019-04-21 19:50:21.338 UTC [6974] STATEMENT:  explain analyze select * from reports.v_BusinessOperation;

Next I'll apply Tomas' corrected 0002 patch on top of this and see ...

-Gunther
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.