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 know
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 RHEL 5
[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
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 you should
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
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 outside
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 at
* 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
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
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 join
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 memory to
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 of
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 that one?
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 using
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
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
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 158332
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 which should
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 -a
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
How about cat /proc/pid/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/pid/limits file, but here are
status:
Name: postmaster
State: S
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 the
Huh, I was overthinking the problem. xml_out has a memory leak :-(
More than one in fact: a large leak (equal to size of output) in
the executor context (hence query lifespan) and a small one in
LibxmlContext (hence transaction lifespan).
Try the patch here:
These numbers don't even have any demonstrable connection to Postgres,
let alone to an xpath-related memory leak. You're going to need to come
up with a concrete test case if you want anyone to investigate.
regards, tom lane
As I said in the start of this thread, this
Gregory Stark [EMAIL PROTECTED] writes:
That's just a special case of what would be expected to happen with
memory
allocation anyways though. Few allocators return memory to the OS
anyways.
Well, that does happen on Linux for instance. Since Matt knew in his
original report that the
Gregory Stark [EMAIL PROTECTED] writes:
That's just a special case of what would be expected to happen with
memory
allocation anyways though. Few allocators return memory to the OS
anyways.
Well, that does happen on Linux for instance. Since Matt knew in his
original report that the xpath
OK, I'll try to come up with something. Do you have a recommended way of
capturing the amount memory being used by Postgres related to this? I
was
thinking I would have a plpgsql function that loops a large number of
times, calling a few xpath() calls,
Yeah, that's what I'd try first.
I looked through the libxml2 sources a little bit but couldn't
immediately find the problem. I'm fairly confident though that
this could be reproduced outside Postgres, by replicating the sequence
of libxml2 calls we make in xpath(). The next step should probably be
to build a reproducer
I'm able to duplicate the memory leak in this function with the current
Fedora 8 libxml2 (2.6.32). The leak is definitely inside libxml2
itself, because the bloat shows up here:
I think this should fix it.
Kris Jurka
It looks like xml.c source has changed considerably since 8.3 (looking
I think this should fix it.
Kris Jurka
It looks like xml.c source has changed considerably since 8.3 (looking at
revision 1.68.2.2 from the 8.3.3. release). Do you know where/if this
patch would apply to the 8.3 branch?
I diff'ed 1.74 and 1.68.2.2, and I'm guessing this new line could be
This part seems to match the bug though --- the leak is approximately
the same size as all the text returned by xpath() within the current
transaction.
So there may be a second issue remaining to be found. Can you put
together a test case for the long-term small leak?
Probably the right thing for you to do now is just to install the known
fix, and keep an eye on your server for awhile to see if you still see
any indication of the long-term leak behavior.
Certainly, that is my plan. Once I can get the patch rolled out to these
systems, I should be able to
I've been using Postgres 8.3 in a production environment for some time
now, and have noticed a difference in long-term non-shared memory use by
Postgres since upgrading the system from 8.1. After initially making the
switch to 8.3 and making only one schema change (changed a text column to
xml)
Ugh. Sounds like small memory leak inside libxml2 --- probably not
going to be easy to find. Can you put together a self-contained test
case?
OK, I'll try to come up with something. Do you have a recommended way of
capturing the amount memory being used by Postgres related to this? I was
I was trying to extract XML fragments within a pl/pgsql function by nested
xpath() function calls, and found that when namespaces are used in the
XML, only the first xpath() call is able to correctly use namespaces.
First here is an example that works, when no namespaces are used:
BEGIN;
CREATE
But it's true that it's possible for a slow expression to make the recheck
very expensive. The planner doesn't have a very good understanding of how
to
tell whether the expression is likely to be slow.
The case I ran into is thing like WHERE x = ANY $1::integer[] which
become
very slow for
Hello, I'm trying to find a way to use a text[] index lookup using an
xpath() function in 8.3, but I suspect this situation is not specific to
8.3 or this exact query style. The query plan looks like
Bitmap Heap Scan on lead (cost=37.39..7365.22 rows=2206 width=8)
Recheck Cond:
The problem for me is, the Recheck Cond is then on the xpath() function
used by the function-based index. My understanding is that then the
database must actually call the xpath() function again on all matches
from
the index lookup.
This is mistaken. It only happens if there are so many
Hello, I'm using 8.3b4 and keep experiencing server crash when I execute
various queries using XML functions. The crash backtraces look like this:
---
Program received signal EXC_BAD_ACCESS, Could not access memory.
Reason: KERN_INVALID_ADDRESS at address: 0x3f847ae1
0x004b140c in
This was reported before,
http://archives.postgresql.org/pgsql-general/2007-12/msg00716.php
but neither he nor you have provided anything approximating a
reproducible test case. The interactions with libxml are messy
enough that I'm not even going to think about fixing this without
a test
Not sure --- when are you thinking of, and what was the context?
I don't usually keep sample data unless the issue still seems open.
I was referring to a dump I provided a link to you called
pg83-leads-sanitized.db which was around 20 Dec, with email subject Re:
[GENERAL] 8.3b2 XPath-based
Matt Magoffin [EMAIL PROTECTED] writes:
Hello, I'm trying to write a query to return an XML document like
root foo=bar
range range=x count=123
range range=y count=345
...
/root
Something like this:
regression=# select xmlelement(name root, xmlagg(x)) from
regression-# (select
Matt Magoffin [EMAIL PROTECTED] writes:
Thanks very much, that helps. Now I'm wondering if it's also possible to
then fill in another nested element level in the XML output, from the
rows
that are aggregated into the range count.
Something involving xmlagg in the sub-query, perhaps
Hello, I'm trying to write a query to return an XML document like
root foo=bar
range range=x count=123
range range=y count=345
...
/root
I started with
select xmlelement(name range, xmlattributes(m.range, count(s.id) as count))
from mb_sale s
inner join mb_lead m on m.sale_id = s.id
where
So what is the state-of-the-art in the Postgresql world if I _do_ want
synchronous replication? 2-phase commit from the client application? Any
success/horror stories about doing it in Java?
For Java, you could check out Sequoia (http://sequoia.continuent.org/) or
their commercial version
AFAICT that's exactly what it does.
regression=# select xpath('//[EMAIL PROTECTED]mykey]/text()',
'valueABCfoo
key=mykeyXYZ/foo/valuefoo key=mykeyRST/foofooDEF/foo');
xpath
---
{XYZ,RST}
(1 row)
regression=#
Of course this is of type xml[], but you can cast to text[] and
Matt Magoffin [EMAIL PROTECTED] writes:
2) Even if I could have an xpath() result return an array with multiple
values, like {value1,value2,value3} I wasn't able to define a GIN index
against the xml[] type. Should this be possible?
Dunno about your other questions, but the answer
Hello, I currently have a table in Postgres 8.1 with a text column that
contains XML. I use the xml2 module to define several XPath-based function
indices on that column, and this has worked very well.
I'm trying not to evaluate the native XML support in 8.3b2. I dumped this
table from 8.1, then
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] (Tom Lane) wrote:
Matt Magoffin [EMAIL PROTECTED] writes:
So I explored with a SELECT statement, thinking there was some specific
XML document causing the crash. I could consistently execute this
statement to get a crash:
select
On Mon, Nov 19, 2007 at 10:02:20AM +1300, Matt Magoffin wrote:
Sorry if I left any relavent details out. I've been looking at this for
a while so many things are probably obvious only to me. Could you hint
at which additional details you think would be useful here?
What's being asked
Matt Magoffin [EMAIL PROTECTED] writes:
I understand. I cannot make the SELECT query nor the ADD INDEX command
break on an empty database. I cannot share this database data, either.
So try to make a test case using dummy data, or with suitably obscured
versions of your real data.
Also
Matt Magoffin [EMAIL PROTECTED] writes:
(gdb) bt
#0 0x8820 in __memcpy ()
#1 0x004d9098 in xmlBufferAdd ()
#2 0x004e0dc4 in xmlParserInputBufferCreateMem ()
#3 0x004ced98 in xmlCtxtReadMemory ()
#4 0x0026ea0c in xpath (fcinfo=0x37) at xml.c:3183
#5 0x001095bc
Matt Magoffin [EMAIL PROTECTED] writes:
(gdb) bt
#0 0x8820 in __memcpy ()
#1 0x004d9098 in xmlBufferAdd ()
#2 0x004e0dc4 in xmlParserInputBufferCreateMem ()
#3 0x004ced98 in xmlCtxtReadMemory ()
#4 0x0026ea0c in xpath (fcinfo=0x37) at xml.c:3183
#5 0x001095bc
(gdb) bt
#0 0x8820 in __memcpy ()
#1 0x004d9098 in xmlBufferAdd ()
#2 0x004e0dc4 in xmlParserInputBufferCreateMem ()
#3 0x004ced98 in xmlCtxtReadMemory ()
#4 0x0026ea0c in xpath (fcinfo=0x37) at xml.c:3183
#5 0x001095bc in ExecMakeFunctionResultNoSets (fcache=0x10563fc,
Matt Magoffin [EMAIL PROTECTED] writes:
I took the latest snapshot from /dev, but I still get the same crash:
Drat :-(. Please try a bit harder at generating a self-contained
test case. Given where the crash is happening, I suspect it may be
only weakly if at all dependent on your input
I've working with XML in Postgres 8.3 and am trying to find a way to
create a text-based index from an XPath that returns multiple nodes. For
example, if I have an XPath like
/[EMAIL PROTECTED]mykey]/text()
which might return a few text nodes like
value1
value2
value3
I'd like 3 index values
ELID or TG_RELNAME inside your trigger.
On Tue, 20 Mar 2001, Matt Magoffin wrote:
Is there any way to make use of the tableoid either as an argument to
the
function or as a reference within the function (in plpgsql)? For
example,
I'd like to either
CREATE TRIGGER set_default_value BEFOR
CUTE PROCEDURE set_value("type", 0);
CREATE TRIGGER set_default_value BEFORE INSERT
ON bar FOR EACH ROW
EXECUTE PROCEDURE set_value("type", 1);
The function set_value has to be written in C language (plpgsql lang
doesn't
allow parameter passing for trigger functions).
Has
oding="utf-8"?>
mercedes
http://www.mail-archive.com/mercedes@okiebenz.com
mercedes @ okiebenz
Sun, 14 Sep 2008 21:59:21 GMT
Sun, 14 Sep 2008 21:59:21 GMT
http://blogs.law.harvard.edu/tech/rss
MHonArc RSS 2.0 RCFile
themailarchive@gmail.com (The Mail Archive)
59 matches
Mail list logo
