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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
