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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
