Re: [GENERAL] Out of memory
Hi Scott, It was the work_mem that was set too high. I reduced it to 32mb and the function executed. Just so I understand this. Every time a sort is performed within a function, the sort memory is allocated, and then it not released until the function completes? Rather then deallocating the memory after each sort operation has completed. Thanks, Jeremy From: Scott Marlowe [scott.marl...@gmail.com] Sent: Friday, 25 March 2011 5:04 p.m. To: Jeremy Palmer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Out of memory On Thu, Mar 24, 2011 at 9:23 PM, Jeremy Palmer wrote: > I’ve been getting database out of memory failures with some queries which > deal with a reasonable amount of data. > > I was wondering what I should be looking at to stop this from happening. > > The typical messages I been getting are like this: > http://pastebin.com/Jxfu3nYm > The OS is: > > Linux TSTLHAPP01 2.6.32-29-server #58-Ubuntu SMP Fri Feb 11 21:06:51 UTC > 2011 x86_64 GNU/Linux. > > It’s a running on VMWare and, has 2 CPU’s and 8GB of RAM. This VM is > dedicated to PostgreSQL. The main OS parameters I have tuned are: > > work_mem = 200MB That's a really big work_mem. I have mainline db servers with 128G of ram that have work_mem set to 16M and that is still considered a little high in my book. If you drop work_mem down to 1MB does the out of memory go away? work_mem is how much memory EACH sort can use on its own, if you have a plpgsql procedure that keeps running query after query, it could use a LOT of memory really fast. __ This message contains information, which is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank you. __ -- 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] Deadlock in libpq
On Fri, Mar 25, 2011 at 21:21, Merlin Moncure wrote: > On Fri, Mar 25, 2011 at 3:26 AM, Erik Hesselink wrote: >>> hm, ISTM (I don't know haskell) that the hdbc driver isn't doing any >>> type of synchronization at all unless it is using a non thread safe >>> libpq...and in that case it uses a global mutex. That doesn't look >>> correct -- the hdbc driver should be locking around the PGconn always, >>> and globally if you're stuck with a non thread safe libpq. >> >> No, that is not the case. If libpq is not thread safe, the library >> uses a global lock. If it is thread safe, it uses a single lock per >> connection. This lock is created on connect, and locked before >> executing a statement. So it seems the library is doing the correct >> things. >> >> (And yes, libpq is thread safe, I just checked). > > hm, I'm stumped. Are you sure nothing else is using the crypto > library? There is an unlikely but possible case that you initialized > crypto locks over somebody else. No, I'm not sure at all. We have quite a few dependencies, since Haskell libraries are very small. I've looked through all available Haskell libraries and none that we use seem to depend on libcrypto or libssl. However, it is possible that we transitively depend on libraries that do. It is also possible that we depend on libraries that use a C library that uses libcrypto or libssl (like we do with libpq). It will take some time to analyze the full dependency tree. > *something* is happening here, but I have no idea what. I'm very > skeptical it's a locking issue in libpq itself, because there is so > little going on beyond counting the connections. Let me ask you this: > how much does your connection count range over time? would it be > possible to reserve a connection that stays open all the time and see > if the issue re-occurs? (a wild stab in the dark, but I'm curious of > ssl re-initialization is causing your problem if you always have at > least one connection open, it wont re-initialize). My tests were with two clients. Each client performs database queries sequentially, and a background thread is also opening database connections, so that makes 0 to 3 concurrent connections. I will test with one connection always open; that should be very simple to set up. I've also set up a connection pooler (pgbouncer) on our live server, and turned off SSL. This seems to prevent/hide the problem there at least, but I will continue to investigate on a different server. Thanks for all the help so far, -- Erik Hesselink http://silkapp.com -- 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
Hi. Your idea is cool - i think this feature ought to be added to TODO. Sorted rows should be materialized when memory is exhaused, and memory reused. 2011/3/26, Jeremy Palmer : > Hi Scott, > > It was the work_mem that was set too high. I reduced it to 32mb and the > function executed. > > Just so I understand this. Every time a sort is performed within a function, > the sort memory is allocated, and then it not released until the function > completes? Rather then deallocating the memory after each sort operation has > completed. > > Thanks, > Jeremy > > > From: Scott Marlowe [scott.marl...@gmail.com] > Sent: Friday, 25 March 2011 5:04 p.m. > To: Jeremy Palmer > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Out of memory > > On Thu, Mar 24, 2011 at 9:23 PM, Jeremy Palmer wrote: >> I’ve been getting database out of memory failures with some queries which >> deal with a reasonable amount of data. >> >> I was wondering what I should be looking at to stop this from happening. >> >> The typical messages I been getting are like this: >> http://pastebin.com/Jxfu3nYm >> The OS is: >> >> Linux TSTLHAPP01 2.6.32-29-server #58-Ubuntu SMP Fri Feb 11 21:06:51 UTC >> 2011 x86_64 GNU/Linux. >> >> It’s a running on VMWare and, has 2 CPU’s and 8GB of RAM. This VM is >> dedicated to PostgreSQL. The main OS parameters I have tuned are: >> >> work_mem = 200MB > > That's a really big work_mem. I have mainline db servers with 128G of > ram that have work_mem set to 16M and that is still considered a > little high in my book. If you drop work_mem down to 1MB does the out > of memory go away? work_mem is how much memory EACH sort can use on > its own, if you have a plpgsql procedure that keeps running query > after query, it could use a LOT of memory really fast. > __ > > This message contains information, which is confidential and may be subject > to legal privilege. > If you are not the intended recipient, you must not peruse, use, > disseminate, distribute or copy this message. > If you have received this message in error, please notify us immediately > (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. > LINZ accepts no responsibility for changes to this email, or for any > attachments, after its transmission from LINZ. > > Thank you. > __ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Non-storable data type
Hello, writing an extension library, I have a type only used to perform efficient in-place aggregation, but absolutely not to be used as a data type into a table (it contains pointers, so it would be a guaranteed crash). Is there a way to mark the type as non-storable? Thanks, -- Daniele -- 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] Non-storable data type
Daniele Varrazzo writes: > Hello, > writing an extension library, I have a type only used to perform > efficient in-place aggregation, but absolutely not to be used as a > data type into a table (it contains pointers, so it would be a > guaranteed crash). > Is there a way to mark the type as non-storable? Can you avoid making it a type at all? I think there are existing examples of aggregates that just declare their state value as INTERNAL. regards, tom lane -- 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] Non-storable data type
On Sat, Mar 26, 2011 at 10:35 PM, Tom Lane wrote: > Daniele Varrazzo writes: >> Hello, >> writing an extension library, I have a type only used to perform >> efficient in-place aggregation, but absolutely not to be used as a >> data type into a table (it contains pointers, so it would be a >> guaranteed crash). > >> Is there a way to mark the type as non-storable? > > Can you avoid making it a type at all? I think there are existing > examples of aggregates that just declare their state value as INTERNAL. I found no reference about this in the docs but yes, for instance intagg is one of these. However using it has not been straightforward: using the aggregate defined with stype=internal I got the error "cannot accept a value of type internal". I've found the error is raised by internal_in: that's because the aggregate has an initcond defined - which however is only a dummy to work around the error received in the agg definition in case it is omitted: "must not omit initial value when transition function is strict and transition type is not compatible with input type" I've made "internal" working by redefining the transition function as non strict: this obviously has made its code a little bit more complex, but it's probably better than having the internal type exposed to the sql. Thank you. -- Daniele -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
