Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Scott Marlowe
On Mon, Feb 9, 2009 at 1:58 PM, Matt Magoffin wrote: I wonder if this is the problem, or part of it. This part of the explain analyze on down, there's 1.4M rows, when the planner seems to expect the number of rows to be chopped down quite a bit more when it goes from the bitmap index scan to the

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Scott Marlowe
On Mon, Feb 9, 2009 at 3:57 PM, Matt Magoffin wrote: >> No, explain analyze for the query that wouldn't execute before but now >> does, with, I assume, a large work_mem. I'd like to see how it >> differes from the one with smaller work_mem. > > Ah, I pasted that in an earlier email, sent February

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> No, explain analyze for the query that wouldn't execute before but now > does, with, I assume, a large work_mem. I'd like to see how it > differes from the one with smaller work_mem. Ah, I pasted that in an earlier email, sent February 10, 2009 9:58:00 AM GMT+13:00... that plan was the one usin

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Scott Marlowe
On Mon, Feb 9, 2009 at 2:40 PM, Matt Magoffin wrote: >>> Yes... and indeed changing vm.overcommit_ratio to 80 does allow that >>> previously-failing query to execute successfully. Do you think this is >>> also what caused the out-of-memory error we saw today just when a >>> transaction was initiat

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Tom Lane
Stephen Frost writes: > * Matt Magoffin (postgresql@msqr.us) wrote: >> Yes... and indeed changing vm.overcommit_ratio to 80 does allow that >> previously-failing query to execute successfully. Do you think this is >> also what caused the out-of-memory error we saw today just when a >> transact

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
>> Yes... and indeed changing vm.overcommit_ratio to 80 does allow that >> previously-failing query to execute successfully. Do you think this is >> also what caused the out-of-memory error we saw today just when a >> transaction was initiated? > > Curious, what's the explain analyze look like for

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Scott Marlowe
On Mon, Feb 9, 2009 at 1:57 PM, Stephen Frost wrote: > * Scott Marlowe (scott.marl...@gmail.com) wrote: >> I think that you're fixing a symptom, but ignoring the cause. >> Twiddling VM parameters may help out, but this problem of too much >> memory allocated is the real issue, so yeah, you're just

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Scott Marlowe
On Mon, Feb 9, 2009 at 2:01 PM, Matt Magoffin wrote: >> I don't think changing work_mem down is actually going to reduce the >> memory allocated without changing the plan to something less optimal. >> In the end, all of this is putting off the inevitable, if you get enough >> PGs going and enough

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > Yes... and indeed changing vm.overcommit_ratio to 80 does allow that > previously-failing query to execute successfully. Do you think this is > also what caused the out-of-memory error we saw today just when a > transaction was initiated? Almost ce

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > Also, by adjusting this, would I possibly just be delaying the problem we > currently have (i.e. over time, we start to run out of memory)? I just > wonder why the system is reaching this limit at all... do you feel it is > quite normal for a system

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> I don't think changing work_mem down is actually going to reduce the > memory allocated without changing the plan to something less optimal. > In the end, all of this is putting off the inevitable, if you get enough > PGs going and enough requests and whatnot, you're going to start running > out

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> I'd do both. But only after I'd reduced work_mem. Given that > reducing work_mem removed the problem, it looks to me like pgsql is > requesting several large blocks of ram, then only using a small port > of them. But overcommit set to 2 means that the OS will not allow an > overcommit of memor

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Stephen Frost
* Scott Marlowe (scott.marl...@gmail.com) wrote: > I think that you're fixing a symptom, but ignoring the cause. > Twiddling VM parameters may help out, but this problem of too much > memory allocated is the real issue, so yeah, you're just putting off > the inevitable. I don't think changing work

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Stephen Frost
* Scott Marlowe (scott.marl...@gmail.com) wrote: > I'd do both. But only after I'd reduced work_mem. Given that > reducing work_mem removed the problem, it looks to me like pgsql is > requesting several large blocks of ram, then only using a small port > of them. But overcommit set to 2 means th

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> with 100 concurrent postgres connections, if they all did something > requiring large amounts of work_mem, you could allocate 100 * 125MB (I > believe thats what you said it was set to?) which is like 12GB :-O > > in fact a single query thats doing multiple sorts of large datasets for > a messy

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Scott Marlowe
On Mon, Feb 9, 2009 at 1:32 PM, Matt Magoffin wrote: >> I suspect this may be it... Apparently, while you're only using about >> 2G, you've got 10G or so of outstanding commitments, and Linux is >> refusing to allocate more. >> >> You probably want to up your overcommit_ratio, esp. in light of th

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Scott Marlowe
On Mon, Feb 9, 2009 at 1:25 PM, Matt Magoffin wrote: >> * Matt Magoffin (postgresql@msqr.us) wrote: >>> [r...@170226-db7 ~]# cat /proc/meminfo >>> CommitLimit: 10312588 kB >>> Committed_AS: 9760756 kB >> >> I suspect this may be it... Apparently, while you're only using about >> 2G, you've

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > Thanks for the advice. Should we have more than 2GB of swap available? I > thought the goal for a Postgres system was to avoid swap use at all cost? > Would it be better for us to add more swap, or adjust this > overcommit_ratio as you discuss? You

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> I suspect this may be it... Apparently, while you're only using about > 2G, you've got 10G or so of outstanding commitments, and Linux is > refusing to allocate more. > > You probably want to up your overcommit_ratio, esp. in light of the fact > that you've only got 2G of swap on this box. I'd

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> * Matt Magoffin (postgresql@msqr.us) wrote: >> [r...@170226-db7 ~]# cat /proc/meminfo >> CommitLimit: 10312588 kB >> Committed_AS: 9760756 kB > > I suspect this may be it... Apparently, while you're only using about > 2G, you've got 10G or so of outstanding commitments, and Linux is > refu

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> Which is a longwinded way of saying "that doesn't seem to match the > symptoms, but ..." If you're not dead certain that your kernel is > configured to allow *well* north of 7000 open files, you might consider > cutting max_files_per_process in half at your next restart. I think it is, looking

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > [r...@170226-db7 ~]# cat /proc/meminfo > CommitLimit: 10312588 kB > Committed_AS: 9760756 kB I suspect this may be it... Apparently, while you're only using about 2G, you've got 10G or so of outstanding commitments, and Linux is refusing to allo

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> Err, yea, overcommit_memory=2, a small amount of swap space, and a low > overcommit_ratio could cause this to happen... The default > ratio is 50 though, which should mean, on this system, there is about > 10G available for user processes, but his usage shows only a bit over 2G > being used outs

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> So did the backend crashed on this one, or just produced 'out of > memory ' message ? No crash, just the error message. -- m@ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > OTOH ... wait a minute. Have you maybe got the system configured to > start denying memory requests before it gets into significant swapping? > We typically suggest setting vm.overcommit_memory=2 on Linux, but > I'm not sure whether that results in the kern

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Tom Lane
"Matt Magoffin" writes: > I just noticed something: the "open files" limit lists 1024, which is the > default for this system. A quick count of open data files currently in use > by Postgres returns almost 7000, though. In principle, any problem with that ought to result in explicit error message

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Scott Marlowe
On Mon, Feb 9, 2009 at 2:17 AM, John R Pierce wrote: > Matt Magoffin wrote: >> >> We have 100+ postgres processes running, so for an individual process, >> could the 1024 file limit be doing anything to this query? Or would I see >> an explicit error message regarding this condition? >> >> > > wit

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread John R Pierce
Matt Magoffin wrote: We have 100+ postgres processes running, so for an individual process, could the 1024 file limit be doing anything to this query? Or would I see an explicit error message regarding this condition? with 100 concurrent postgres connections, if they all did something req

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Grzegorz Jaśkiewicz
On Mon, Feb 9, 2009 at 8:53 AM, Matt Magoffin wrote: >>> We have 100+ postgres processes running, so for an individual process, >>> could the 1024 file limit be doing anything to this query? Or would I >>> see >>> an explicit error message regarding this condition? >> >> you would get one of "Open

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
>> We have 100+ postgres processes running, so for an individual process, >> could the 1024 file limit be doing anything to this query? Or would I >> see >> an explicit error message regarding this condition? > > you would get one of "Open files rlimit 1024 reached for uid " in > syslog (which

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Grzegorz Jaśkiewicz
On Mon, Feb 9, 2009 at 8:23 AM, Matt Magoffin wrote: > I just noticed something: the "open files" limit lists 1024, which is the > default for this system. A quick count of open data files currently in use > by Postgres returns almost 7000, though. > > [r...@170226-db7 ~]# lsof -u postgres |egrep

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> [r...@170226-db7 ~]# su -l postgres -c "ulimit -a" > core file size (blocks, -c) 0 > data seg size (kbytes, -d) unlimited > max nice(-e) 0 > file size (blocks, -f) unlimited > pending signals (-i) 139264 > max locked memory

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> erp, that stinks. Must be on an older kernel? I've got it under (a > Debian-built) 2.6.26. I can't recall if there's another way to get > limit info for an active process.. Could use Tom's suggestion of > echo'ing ulimit -a out to a file somewhere during database start-up. Yes, this is a RHE

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Matt Magoffin
> Yeah, I remember we found a few xml-related leaks based on your reports. > However, there's not anything here to suggest that this query is > straining the capabilities of a 64-bit system with lots o RAM. It seems > certain you're hitting some artificial process-size limit, and the only > one I

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-09 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > There is no /prod//limits file, but here are erp, that stinks. Must be on an older kernel? I've got it under (a Debian-built) 2.6.26. I can't recall if there's another way to get limit info for an active process.. Could use Tom's suggestion of

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Matt Magoffin
> Agreed, that ulimit isn't reflecting any such limit, but is that really > the same environment the postmaster gets started in? I wouldn't trust > a system startup script to be launched in the same environment that a > login shell gets. You might try adding > ulimit -a >/tmp/something > to

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Matt Magoffin
> How about cat /proc//limits for the postmaster? > And maybe: > status > stat > maps > > Though I'm kinda grasping at straws here, to be honest. I've had PG up > and running through >16G of memory at a time before. There is no /prod//limits file, but here are status: Name: postmaster State:

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Tom Lane
"Matt Magoffin" writes: > We've had nagging memory-related issues with 8.3 that manifest themselves > like memory leaks... some posts I've made in the past have led to some > leaks getting fixed... but I've not been able to track down more specific > causes. It's just that over time Postgres seems

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Hmm ... a gig here, a gig there, pretty soon you're talking about real > memory? He's got several sorts and hashes that are each taking over > 100MB according to the memory context dump, so it seems impossible that > it all fits into a strict 32-bit address

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > > I think it must be compiled 64-bit, or he'd not be able to get > > shared_buffers that high to start with. However, it's possible that the > > postmaster's been started under a ulimit setting that constrains each > > backend to just a few hundred

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Tom Lane
"Matt Magoffin" writes: > Here's the output of ulimit -a by the "postgres" user the database is > running under: > ... > I think this means it does not have an artificial memory limit imposed, Agreed, that ulimit isn't reflecting any such limit, but is that really the same environment the postmas

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Matt Magoffin
> Hmm ... a gig here, a gig there, pretty soon you're talking about real > memory? He's got several sorts and hashes that are each taking over > 100MB according to the memory context dump, so it seems impossible that > it all fits into a strict 32-bit address space. There's surely no harm > in do

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Tom Lane
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> I think it must be compiled 64-bit, or he'd not be able to get >> shared_buffers that high to start with. > I'm not so sure.. He has it as '4000MB', which would leave 96M free, > which doesn't seem that far from where his query is

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Matt Magoffin
> I think it must be compiled 64-bit, or he'd not be able to get > shared_buffers that high to start with. However, it's possible that the > postmaster's been started under a ulimit setting that constrains each > backend to just a few hundred meg of per-process memory. Here's the output of ulimit

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Matt Magoffin
>> total used free sharedbuffers >> cached >> Mem: 16432296 16273964 158332 0 173536 >> 14321340 >> -/+ buffers/cache:1779088 14653208 >> Swap: 20964405602095880 > > That certainly looks fine.. And you've got 14G or so

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > Uhh.. I saw that your system was 64-bit, but is your PG process > > compiled as 64bit? Maybe you're hitting an artificial 32-bit limit, > > which isn't exactly helped by your shared_buffers being set up so high > > to begin with?

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Tom Lane
> * Matt Magoffin (postgresql@msqr.us) wrote: >> Just running top, it does appear to chew through a fair amount of memory. >> Here's a snapshot from top of the postgres processing running this query >> from just before it ran out of memory: >> >> PID USER PR NI VIRT RES SHR S %CPU %ME

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > > Does the result from 'free' look reasonable on this box? > > I think so: > > total used free sharedbuffers cached > Mem: 16432296 16273964 158332 0 173536 14321340 > -/+ buffers/cach

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Matt Magoffin
>> PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) >> 4.1.2 20070626 (Red Hat 4.1.2-14) > > Does the result from 'free' look reasonable on this box? I think so: total used free sharedbuffers cached Mem: 16432296 16273964 1583

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > Yep, we've got 16GB to work with here. I should have also mentioned the > architecture in my original post, sorry. SELECT version() returns this: > > PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) > 4.1.2 20070626 (Red Hat

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Matt Magoffin
> erm.. How much memory do you have in the system? This system has 16GB of RAM, and Postgres is basically the only service running on the box. >> shared_buffers = 4000MB > > I hope you've got a fair bit more than 4G of memory if you're going to > use 4G for shared buffers... Once that memory is

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Stephen Frost
* Matt Magoffin (postgresql@msqr.us) wrote: > We've been having persistent out-of-memory errors occur in our production > 8.3 deployment, which is now running 8.3.5. I'm not sure the query here is > the cause of the problem, but this is our most-recent example which > triggered an out-of-memory

[GENERAL] Out of memory on SELECT in 8.3.5

2009-02-08 Thread Matt Magoffin
We've been having persistent out-of-memory errors occur in our production 8.3 deployment, which is now running 8.3.5. I'm not sure the query here is the cause of the problem, but this is our most-recent example which triggered an out-of-memory error for us. Perhaps our configuration needs tweaking