Re: Out of Memory - 8.2.4

On Aug 24, 2007, at 10:09 AM, Jeff Amiel wrote:

> "PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by
> GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath)"
>
>
> Week-old install....still tuning and tweaking this
> thing.
>
> Over last 2 days, have spotted 10 "Out of Memory"
> errors in postgres logs (never saw before with same
> app/usage patterns on tuned hardware/postgres under
> FreeBSD)
>
> Aug 22 18:08:24 db-1 postgres[16452]: [ID 748848
> local0.warning] [6-1] 2007-08-22 18:08:24 CDT ERROR:
> out of memory.
> Aug 22 18:08:24 db-1 postgres[16452]: [ID 748848
> local0.warning] [6-2] 2007-08-22 18:08:24 CDT
> DETAIL: Failed on request of size 536870910.
>
> What I found interesting is that It's ALWAYS the same
> size....536870910
>
> I am running autovacuum and slony.....but I see
> nothing in the logs anywhere near the "out of memory"
> errors related to either (autovacuum used to under
> 8.0.X log INFO messages every time it vacuumed which
> came in handy...I assume it doesn't so this any more?)
>
>
> The events are fairly spread out...and cannot (by
> looking at app logs and rest of DB logs) correlate to
> any specific query or activity.
>
> Any help would be appreciated
>
> Box is a Sun X4600 with 8 dual-core processors and 32
> gig of ram.
>
> # su - pgsql
> Sun Microsystems Inc. SunOS 5.10 Generic
> January 2005
> -bash-3.00$ ulimit -a
> core file size (blocks, -c) unlimited
> data seg size (kbytes, -d) unlimited
> file size (blocks, -f) unlimited
> open files (-n) 256
> pipe size (512 bytes, -p) 10
> stack size (kbytes, -s) 10240
> cpu time (seconds, -t) unlimited
> max user processes (-u) 16357
> virtual memory (kbytes, -v) unlimited
>
> shared_buffers = 3GB # min 128kB or
> max_connections*16kB
> temp_buffers = 1000 # min 800kB
> was 8MB
> max_prepared_transactions = 450 # can be 0 or
> more
> work_mem = 100MB # min
> 64kB
> maintenance_work_mem = 512MB # min 1MB
> #max_stack_depth = 2MB # min 100kB
> max_fsm_pages = 208000 # min
> max_fsm_relations*16, 6 bytes each
> max_fsm_relations = 10000 # min 100, ~70
> bytes each
> #max_files_per_process = 1000 # min 25
> #shared_preload_libraries = '' # (change
> requires restart)
> fsync = on # turns forced
> synchronization on or off
> wal_sync_method = fdatasync # the default
> is the first option
> full_page_writes = off # recover from
> partial page writes
> wal_buffers = 2300 # min 32kB
> commit_delay = 10 # range
> 0-100000, in microseconds
> #commit_siblings = 5 # range 1-1000
> checkpoint_segments = 128 # in logfile
> segments, min 1, 16MB each
> checkpoint_timeout = 5min # range 30s-1h
> checkpoint_warning = 99s # 0 is off

A few weeks ago I got the same error on the same server. In fact,
the only difference is our memory where you have 32GB and I have 16GB
and you have 512MB maintenance_work_mem and I have 256MB. I point
out the maintenance work memory setting as that is pretty much
exactly what the request size that your error pointed out as was mine
(yours/2). However, that was the only time I've seen this. Below is
the full context of the error report in my log. I see that there is
an Autovacuum context as well as references to a toast table so,
something to do with autovacuum?

TopMemoryContext: 14466424 total in 1758 blocks; 7160792 free (12578
chunks); 7305632 used
TopTransactionContext: 8192 total in 1 blocks; 7688 free (10 chunks);
504 used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks);
3320 used
Autovacuum context: 16769024 total in 11 blocks; 6959320 free (11
chunks); 9809704 used
smgr relation table: 24576 total in 2 blocks; 11952 free (4 chunks);
12624 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
chunks); 16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 256 free (0 chunks); 7936 used
CacheMemoryContext: 1183288 total in 20 blocks; 889824 free (4094
chunks); 293464 used
pg_toast_356294_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_index_indrelid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672 used
pg_namespace_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672 used
pg_authid_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_operator_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 352 free (0
chunks); 672 used
pg_class_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 216 free (0 chunks);
808 used
Per-database table: 57344 total in 3 blocks; 37592 free (13 chunks);
19752 used
Per-database table: 57344 total in 3 blocks; 37592 free (13 chunks);
19752 used
Per-database table: 57344 total in 3 blocks; 37592 free (13 chunks);
19752 used
Per-database table: 57344 total in 3 blocks; 37592 free (13 chunks);
19752 used
Per-database table: 24576 total in 2 blocks; 13040 free (5 chunks);
11536 used
Per-database table: 100671512 total in 22 blocks; 5803552 free (91
chunks); 94867960 used
Databases hash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
MdSmgr: 8192 total in 1 blocks; 7936 free (226 chunks); 256 used
LOCALLOCK hash: 24576 total in 2 blocks; 10000 free (4 chunks); 14576
used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
Postmaster: 24576 total in 2 blocks; 20264 free (155 chunks); 4312 used
ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used
2007-08-08 20:21:05 CDT 3716 :ERROR: out of memory
2007-08-08 20:21:05 CDT 3716 :DETAIL: Failed on request of size
268435452.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com