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
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
> 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
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
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
>> 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
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
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
* 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
* 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
> 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
> 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
* 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
* 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
> 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
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
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
* 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
> 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
> * 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
> 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
* 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
> 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
> 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
* 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
"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
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
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
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
>> 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
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
> [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
> 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
> 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
* 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
> 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
> 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:
"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
* 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
* 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
"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
> 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
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
> 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
>> 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
* 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?
> * 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
* 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
>> 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
* 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
> 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
* 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
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
53 matches
Mail list logo