Re: "Out of memory" errors.. - Mailing list pgsql-general

My responses below yours. Thanks so much for bearing with me..



On 8/13/07, Gregory Stark <stark@enterprisedb.com> wrote:
> "Lim Berger" <straightfwd007@gmail.com> writes:
>
> > On 8/13/07, Gregory Stark <stark@enterprisedb.com> wrote:
> >> "Lim Berger" <straightfwd007@gmail.com> writes:
> >>
> >> > Hi
> >> >
> >> > I am getting the following error while running queries such as "vacuum
> >> > analyze TABLE", even on small tables with a piddly 35,000 rows!
> >> >
> >> > The error message:
> >> > --
> >> > ERROR:  out of memory
> >> > DETAIL:  Failed on request of size 67108860.
> >> > --
> >> >
> >> > My postgresql.conf is below. I am on a Dual Core server with 4GB or
> >> > RAM, which runs MySQL as well (key_buffer for which is at around
> >> > 800M).
> >>
> >> What version of Postgres is this?
> >
> > =# select version();
> >                                                 version
> > ----------------------------------------
> >  PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
> > 3.4.6 20060404 (Red Hat 3.4.6-3)
> > (1 row)
>
> Hm, this is quite odd.
>



> Could you give more information? You're getting this
> on lots of different tables?


Yes, all of them. The example I gave is the smallest table with about
35,000 rows.

Btw, before anything else, the tweaking comments here --
http://www.powerpostgresql.com/PerfList/  ..suggest that for
autovacuum I need to recompile and have settings like "-D -v 400 -V
0.4 -a 100 -A 0.3".

Where do I do this? I don't have any such setting in my
postgresql.conf! How should I find out if autovacuum is set up and
functional, and at what points it enters and vacuums up?


> Could you give more examples? And do you get it on anything
> other than vacuum analyze?


More examples of "vacuum analyze" on other tables? Every table gives
the same error. ANALYZE alone works ok. SELECTing works ok. Multiple
UPDATEing in a transaction block works ok. So does INSERT. What other
examples could I furnish? Please help me help you help me :)


> What does the schema look like?
>


You mean the entire tables definition? Below it is. It's a simple
schema, because I am sharing the workload between MySQL and PGSQL for
now, slowly switching to PGSQL.


                       List of relations
 Schema |          Name           |   Type   |      Owner
--------+-------------------------+----------+-----------------
 public | program                 | table    | MYUSERID
 public | program_id_seq          | sequence | MYUSERID
 public | program_subscribers     | table    | MYUSERID
 public | mini                    | table    | MYUSERID
 public | users                   | table    | MYUSERID
(5 rows)


PROGRAM table has 35,000 rows
PROGRAM_SUBSCRIBERS has 10,000
MINI has about 3 million
USERS has about 200.

On this small DB, I am not sure why there is memory outage. Just one
thing -- the "MINI" table has **huge** concurrent usage, about 10,000
accesses per minute, and it has only three columns so it is a bit of a
caching table.

It does have an index though, that is used in our queries very simply
and effeciently, because this concurrent use is all with an "=" query
on the indexed column. Very small, fast queries.

In MYSQL, this used to be very fast due to their "query cache", but
there is no equivalent in PGSQL inside the DB, not outside of the
usual filesystem anyway, so I am not sure if PGSQL is holding up to
the concurrent usage.

To test this ignorant hypothesis of whether PGSQL was buckling under
huge concurrent pressure, I restarted the postgresql process, and the
memory problem is still there, so I doubt the memory outage is caused
by huge concurrent access.

PGSQL (seems to) return the results very fast as well.



> Do you have any hash indexes? (there was a bug fixed
> in 8.2.4 with them)


Nope. Very simple tables actually, all with one BTREE index each. The
complex stuff is still in MySQL and totally separate from this. There
are five tables in MYSQL with compound indexes on 2 to 5 columns. But
that is besides the point for now.


> Do you have anything else unusual like tsearch2 or
> custom C modules loaded?


Not that I know of, unless they are included by default. How can I
check? If they are not smooshed in, then no, I don't have them
installed.


> Has anything unusual happened to this machine such as a
> server crash or power failure?


Hmm, not really. But I did reboot it last week when it buckled under a
MYSQL REPAIR TABLE issue. I wonder how that could be related to PGSQL
though? Same machine and all? It wasn't a very dramatic crash or
anything.



> Is anything else failing? Can you run a good memory tester like
> memtest86? Could you check your dmesg log to see if there are any system
> problems?



Wow, this is all a bit technical for me. I went to the memtest86 site,
and downloaded their binary. Now I will try to do the untarring and
make/makeinstall stuff, but their site is very sparse on info.

But here is my memory check info from the server:

---
MemTotal:      4148844 kB
MemFree:        793052 kB
Buffers:        130280 kB
Cached:        2333716 kB
SwapCached:       7304 kB
Active:        2551448 kB
Inactive:       711836 kB
HighTotal:     3276160 kB
HighFree:       237184 kB
LowTotal:       872684 kB
LowFree:        555868 kB
SwapTotal:     2096440 kB
SwapFree:      2084700 kB
Dirty:            1996 kB
Writeback:           0 kB
Mapped:         836816 kB
Slab:            65140 kB
CommitLimit:   4170860 kB
Committed_AS:  2531972 kB
PageTables:       9284 kB
VmallocTotal:   106488 kB
VmallocUsed:      3284 kB
VmallocChunk:   102504 kB
HugePages_Total:     0
HugePages_Free:      0
Hugepagesize:     2048 kB
---


The dmesg output shows me this blabber, which I have no idea where to
begin decoding. It'd be great if you could point me in the right
direction?


-----BEGIN----
Mem-info:
DMA per-cpu:
cpu 0 hot: low 2, high 6, batch 1
cpu 0 cold: low 0, high 2, batch 1
cpu 1 hot: low 2, high 6, batch 1
cpu 1 cold: low 0, high 2, batch 1
cpu 2 hot: low 2, high 6, batch 1
cpu 2 cold: low 0, high 2, batch 1
cpu 3 hot: low 2, high 6, batch 1
cpu 3 cold: low 0, high 2, batch 1

Normal per-cpu:
cpu 0 hot: low 32, high 96, batch 16
cpu 0 cold: low 0, high 32, batch 16
cpu 1 hot: low 32, high 96, batch 16
cpu 1 cold: low 0, high 32, batch 16
cpu 2 hot: low 32, high 96, batch 16
cpu 2 cold: low 0, high 32, batch 16
cpu 3 hot: low 32, high 96, batch 16
cpu 3 cold: low 0, high 32, batch 16

HighMem per-cpu:
cpu 0 hot: low 32, high 96, batch 16
cpu 0 cold: low 0, high 32, batch 16
cpu 1 hot: low 32, high 96, batch 16
cpu 1 cold: low 0, high 32, batch 16
cpu 2 hot: low 32, high 96, batch 16
cpu 2 cold: low 0, high 32, batch 16
cpu 3 hot: low 32, high 96, batch 16
cpu 3 cold: low 0, high 32, batch 16

Free pages:      417404kB (404864kB HighMem)
Active:458596 inactive:451436 dirty:145735 writeback:48813 unstable:0
free:104351 slab:15369 mapped:411925 pagetables:2938
DMA free:12540kB min:16kB low:32kB high:48kB active:0kB inactive:0kB
present:16384kB pages_scanned:297 all_unreclaimable? yes
protections[]: 0 0 0
Normal free:0kB min:928kB low:1856kB high:2784kB active:271116kB
inactive:511380kB present:901120kB pages_scanned:1749
all_unreclaimable? no
protections[]: 0 0 0
HighMem free:404864kB min:512kB low:1024kB high:1536kB
active:1563284kB inactive:1293200kB present:4063232kB pages_scanned:0
all_unreclaimable? no
protections[]: 0 0 0
DMA: 1*4kB 3*8kB 4*16kB 3*32kB 3*64kB 1*128kB 1*256kB 1*512kB 1*1024kB
1*2048kB 2*4096kB = 12540kB
Normal: 0*4kB 0*8kB 0*16kB 0*32kB 0*64kB 0*128kB 0*256kB 0*512kB
0*1024kB 0*2048kB 0*4096kB = 0kB
HighMem: 14382*4kB 24921*8kB 5608*16kB 1802*32kB 1*64kB 0*128kB
0*256kB 1*512kB 0*1024kB 0*2048kB 0*4096kB = 404864kB
Swap cache: add 190137, delete 125938, find 44796/57030, race 0+17
0 bounce buffer pages
Free swap:       1817480kB
1245184 pages of RAM
819040 pages of HIGHMEM
207973 reserved pages
469280 pages shared
65337 pages swap cached
-----END-----



Thanks for bearing with me!
LB