Re: ERROR: Out of memory - when connecting to database - Mailing list pgsql-general

Replaying to my own mail. Maybe we've found the root cause:

In one database there was a table with 200k records where each record
contained 15kB bytea field. Auto-ANALYZE was running on that table
continuously (with statistics target 500). When we avoid the
auto-ANALYZE via UPDATE table set bytea_column = null; CLUSTER table;
the problem with ERROR: out of memory went away.

Could it be that the failed connections were issued by autovacuum?

Thanks,

Kuba

Dne 8.11.2010 19:19, Jakub Ouhrabka napsal(a):
> Hi,
>
> we have several instances of following error in server log:
>
> 2010-11-08 18:44:18 CET 5177 1 @ ERROR: out of memory
> 2010-11-08 18:44:18 CET 5177 2 @ DETAIL: Failed on request of size 16384.
>
> It's always the first log message from the backend. We're trying to
> trace it down. Whether it's always connection attempt to the same
> database or not - I don't know at the moment.
>
> Sometimes the error message is preceded by memory stats which are below
> in the email.
>
> Other relevant data:
> Linux, PostgreSQL 8.2.10
> RAM 28GB
>
> max_connections = 2048
>
> shared_buffers = 2048MB
>
> temp_buffers = 32MB
> max_prepared_transactions = 0
>
> max_fsm_pages = 10000000
> max_fsm_relations = 100000
>
>
> There are cca 1200 concurrent database connections (active backends). I
> know it's too much, we're trying to reduce the number but it's not that
> easy because of large number of databases and heavy use of listen/notify
> so connection pooler doesn't help...
>
> What can cause this error? What parameter should be raised?
>
> Thanks,
>
> Kuba
>
> Messages preceding ERROR: out ouf memory message
>
> TopMemoryContext: 581920 total in 32 blocks; 13760 free (15 chunks);
> 568160 used
> TopTransactionContext: 85077936 total in 20 blocks; 4523352 free (9
> chunks); 80554584 used
> Analyze Index: 1358288 total in 3 blocks; 7408 free (0 chunks); 1350880
> used
> ExecutorState: 8192 total in 1 blocks; 4928 free (0 chunks); 3264 used
> ExprContext: 13664019952 total in 564677 blocks; 968292944 free (117296
> chunks); 12695727008 used
> Analyze Column: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
> Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks);
> 12688 used
> Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
> used
> Autovacuum context: 57344 total in 3 blocks; 35624 free (13 chunks);
> 21720 used
> CFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
> MbProcContext: 17408 total in 2 blocks; 17184 free (8 chunks); 224 used
> smgr relation table: 24576 total in 2 blocks; 5648 free (4 chunks);
> 18928 used
> TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks);
> 32 used
> Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
> PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> Relcache by OID: 24576 total in 2 blocks; 14912 free (3 chunks); 9664 used
> CacheMemoryContext: 800552 total in 19 blocks; 162536 free (2 chunks);
> 638016 used
> pg_toast_1152341368_index: 2048 total in 1 blocks; 680 free (0 chunks);
> 1368 used
> index_name1: 2048 total in 1 blocks; 512 free (0 chunks); 1536 used
> index_name2: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
> ...
> Per-database table: 253952 total in 5 blocks; 120064 free (21 chunks);
> 133888 used
> Per-database table: 253952 total in 5 blocks; 138544 free (22 chunks);
> 115408 used
> Per-database table: 253952 total in 5 blocks; 138544 free (22 chunks);
> 115408 used
> Per-database table: 57344 total in 3 blocks; 34352 free (11 chunks);
> 22992 used
> ...
> Databases hash: 122880 total in 4 blocks; 66912 free (13 chunks); 55968
> used
> MdSmgr: 8192 total in 1 blocks; 1664 free (0 chunks); 6528 used
> LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
> Rendezvous variable hash: 8192 total in 1 blocks; 1680 free (0 chunks);
> 6512 used
> PLpgSQL function cache: 8192 total in 1 blocks; 3744 free (0 chunks);
> 4448 used
> Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used
> Postmaster: 253952 total in 5 blocks; 65400 free (8 chunks); 188552 used
> ErrorContext: 8192 total in 1 blocks; 8160 free (3 chunks); 32 used