all database connections being consumed up and not released with ORM and postgres

[Cage Sarin]

Hi all. After reloading my web app that has a Lucee + Taffy back end approximately 100 times, I get the following error:

psql: FATAL: remaining connection slots are reserved for non-replication superuser connections

This requires me to restart the Lucee server (4.5.2.0.18 final) and then things are fine for a while and then the process repeats!  

I've tried various datasource settings via the administrator without any success. I've tried increasing (as well as decreasing) the max connection limit as well as setting the connection timeout to 0 vs 1 minute vs something very long. When I decrease the max connection limit, I don't get the above error message but rather Lucee just times out (which makes sense as here it is waiting to use a connection).

I'm using Postgresql 9.4 on the same server as Lucee (localhost). I am using a combination of ORM as well as non-ORM SQL statements. I use two data sources.

A "ps auxwww|grep postgres" shows that there are lots of idle connections and these don't go away, even after a long time (see below).

Anyone have any ideas what's happening and how to troubleshoot and/or solve? 

Thanks,

Cage

postgres  8336  0.0  0.2 220396  7828 ?        Ss   02:34   0:00 postgres: username dbname 127.0.0.1(36112) idle                                                                       

postgres  8337  0.0  0.3 220396 10292 ?        Ss   02:34   0:00 postgres: username dbname 127.0.0.1(36113) idle                                                                       

postgres  8339  0.0  0.1 219268  3484 ?        Ss   02:34   0:00 postgres: username dbname 127.0.0.1(36115) idle                                                                       

postgres  8359  0.0  0.1 219268  3484 ?        Ss   02:34   0:00 postgres: username dbname 127.0.0.1(36120) idle                                                                       

postgres  8364  0.0  0.1 219268  3484 ?        Ss   02:34   0:00 postgres: username dbname 127.0.0.1(36121) idle                                                                       

postgres  8410  0.0  1.2 220940 38620 ?        Ss   02:35   0:00 postgres: username dbname 127.0.0.1(36125) idle                                                                       

postgres  8415  0.0  0.3 220220  9736 ?        Ss   02:35   0:00 postgres: username dbname 127.0.0.1(36127) idle                                                                       

postgres  8428  0.0  0.1 219264  3500 ?        Ss   02:35   0:00 postgres: username dbname 127.0.0.1(36130) idle                                                                       

postgres  8446  0.2  2.0 220540 61960 ?        Ss   02:36   0:04 postgres: username dbname 127.0.0.1(36132) idle                                                                       

postgres  8457  0.0  0.1 219264  3508 ?        Ss   02:37   0:00 postgres: username dbname 127.0.0.1(36136) idle                                                                       

postgres  8539  0.3  2.7 220564 83256 ?        Ss   02:42   0:04 postgres: username dbname 127.0.0.1(36145) idle                                                                       

postgres  8628  0.4  2.7 220200 83288 ?        Ss   02:47   0:04 postgres: username dbname 127.0.0.1(36158) idle                                                                       

postgres  8664  0.5  2.7 221480 84672 ?        Ss   02:49   0:04 postgres: username dbname 127.0.0.1(36163) idle                                                                       

postgres  8674  0.0  0.5 220216 17320 ?        Ss   02:49   0:00 postgres: username dbname 127.0.0.1(36165) idle                                                                       

postgres  8677  0.0  0.2 220084  8756 ?        Ss   02:49   0:00 postgres: username dbname 127.0.0.1(36168) idle                                                                       

postgres  8744  0.0  0.1 219264  3512 ?        Ss   02:52   0:00 postgres: username dbname 127.0.0.1(36176) idle                                                                       

postgres  8750  0.6  2.0 220540 62004 ?        Ss   02:52   0:03 postgres: username dbname 127.0.0.1(36178) idle                                                                       

postgres  8752  0.1  0.5 220216 18372 ?        Ss   02:52   0:00 postgres: username dbname 127.0.0.1(36179) idle                                                                       

postgres  8759  0.1  1.5 221516 47096 ?        Ss   02:52   0:00 postgres: username dbname 127.0.0.1(36180) idle                                                                       

postgres  8760  0.6  2.0 220156 63316 ?        Ss   02:52   0:04 postgres: username dbname 127.0.0.1(36181) idle                                                                       

postgres  8785  0.0  0.1 219264  3512 ?        Ss   02:53   0:00 postgres: username dbname 127.0.0.1(36183) idle                                                                       

postgres  8789  0.0  0.3 220220 10172 ?        Ss   02:53   0:00 postgres: username dbname 127.0.0.1(36185) idle               

[Cage Sarin]

Also, I'm using ORM event handling to insert data into a messages table using executeQuery...not sure if that could be a source of problems...

[Julian Halliwell]

Hi Cage

Sounds like it might be related to this bug:

https://luceeserver.atlassian.net/browse/LDEV-119

Basically if you call OrmReload() any existing session connections
become "detached" and fail to time out.

I use MariaDB rather than Postgres, but I discussed the issue and my
workaround here:

http://cfsimplicity.com/98/migrating-from-coldfusion-to-railo-to-lucee

Cheers
Julian.

[Cage Sarin]

I'll double check but I'm pretty sure I'm not doing any ormReload() (except when first starting lucee and the application).

I do do some entityReload()'s though.

Any chance it could be related to using SQL in the ORM event handlers? 

[Jon Clausen]

I uses PostgreSQL (same PGSQL version, same Lucee version) quite a bit and have never had an issue as long as “Maintain Connections” is enabled.

How are you executing the SQL queries (e.g. using ormExecuteQuery() or using the standard query() ).  There may be something in the arguments passed to your query method that’s causing the issue.

Jon

--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/14bd52ad-3192-4402-a5ed-9bd9f68dfc8f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Cage Sarin]

Including examples below. In addition, I use standard entityLoad / entityNew / entitySave ORM functions as well.

example ORM query:

deletedCases = ormExecuteQuery("update table1 set deleted = true,
                                datetimemodified = :datetimemodified
                                where mydate = :mydate
                                    and location = :location
                                    and id not in (:idsProcessed)", {mydate=workDT, location=siteName, idsProcessed=idsProcessed, datetimemodified=Now()});

example SQL query:

public void function postInsert( any entity )
    
{

    var id = this.getID();
    var something = this.getSomething();
    var surgdate = this.getSurgdate();
   
    var sql = "INSERT INTO messages(type,target,creator,value,workdate) VALUES(:type,:target,:creator,:value,:workdate);";
    var params = {type='messagetype1',
                    target='{"id":#id#, "something":#something#}',
                    creator='#Session.cfid#',
                    value="somevalue",
                    workdate={value = surgdate, cfsqltype="CF_SQL_DATE"}};

    var message = queryExecute(
                    sql,
                    params,
                    {datasource="datasourcename"});
}

[Jon Clausen]

Cage,

I ran some tests an found that there was one setting that will append connections if it is changed.  If the connection timeout is changed or set programmatically, it will append new connections and the other ones will hang out there until they expire.  I’m not sure if that value is being set anywhere within the application, but it will produce the problem you’re seeing.

Also, programically creating the DSN repeatedly will orphan connections.  Are you creating the DSN within the application startup in a way that might be re-creating that datasource when you reload your application?   

Other than that, I used both of your query methods and wasn’t able to duplicate the problem  (my standard development timeout is set to 1 minute but changing it didn’t seem to .  

What is your  PostgreSQL driver version ( In the admin under Driver Name/Version)?  That might give a clue as well.

Jon

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/1977ab64-c30e-46f9-838b-89221527f70e%40googlegroups.com.

[Cage Sarin]

I am doing all of the settings via admin and no connection timeout settings or creation of data sources programmatically. 

Driver info is below.

Any chance you'd be able to test with a query in an ORM event handler? Just want to make sure that's not it since the problem started soon after I implemented those methods (or at least I think it did - maybe coincidence!).

Thanks very much. 

Cage 

Database (Name/Version)	PostgreSQL 9.4.1
Driver (Name/Version)	PostgreSQL Native Driver PostgreSQL 8.3 JDBC4 with SSL (build 607) (JDBC 4.0)

[Jon Clausen]

Not seeing the issue when using event handling either.  Sorry I can’t help more.  My connection re-usage is rock solid with every type of query I can think of, aside from the issues I noted before.   

Is there anything you’re doing inside of your pre/post create/update/delete event handlers (or pre/postFlush(), pre/postLoad(), etc ) that might be causing the issue?

Jon

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/eecb10f9-3aed-42df-bd27-4c03333717b4%40googlegroups.com.

[Cage Sarin]

Can't think of anything unusual. Just the SQL queries in event handlers.

And in one persistent CFC, I'm doing some encrypt/decrypt for data (see below) using Lucee encrypt functions.

I am using Taffy with REST API resources in a subfolder of my main web app. It has a Application.cfc that extends the taffy.core.api and in it, I specify some of the same settings as the Application.cfc in my main app (i.e., datasource, eventhandling, etc are specified a second time) but the app name is the same in both Application.cfc's. These settings duplicated include:

this.name = "myappname";

this.ormEnabled = true;
this.ormsettings = {datasource = "datasourcename", cfclocation = "../models", eventhandling = true, logSQL = false};

public void function preInsert( any entity )
{
     this.encryptProperties();
}

public void function preUpdate( any entity )
{
     this.encryptProperties();
}

public void function postLoad( any entity )
{
     this.decryptProperties();
}

public void function encryptProperties()
{
    var props = getMetaData( this ).properties;

    for (var prop in props){
      if (structKeyExists(  prop, "encrypted" ) AND prop.encrypted){
        var n = prop.name;
        if (!isNull(variables[n])){
            variables[ n ] = encrypt( variables[ n ], Application.stcMagic.key, Application.stcMagic.type, Application.stcMagic.block );
        }
      }
    }

}

[Cage Sarin]

I tried one more thing, which is use flushatrequestend = false, and now I'm seeing less of a problem. It appears all the connections are being released. Any thoughts on why this is helping? And are there any unexpected consequences of doing this? I'm pretty sure I'm doing ormFlush() after making my changes to ORM data entities.

[Julian Halliwell]

As I say, I can replicate this at will by calling OrmReload() as
described in LDEV-119. I also use the default 1 minute connection
timeout for datasources and any connections made in the 60 seconds
prior to calling OrmReload() become detached and only die when killed
by the database. To work around this I've had to set my database's
timeout for inactive connections to be much lower than the default -
which is 8 hours!

[Julian Halliwell]

You could also try setting autoManageSession=false. Coupled with
flushAtRequestEnd that will stop Hibernate from interfering in how
sessions are handled. You should then control them yourself by
wrapping changes that need persisting in transaction{} blocks (no need
for OrmFlush()).

I always use these settings and the only time I see the detached
connections are when I call OrmReload().

Julian.

[Bill Tindal]

Hi Jon,

I'm having an issue with maintaining connections with Lucee and Postgres. I intermittently get a 'Connection Failure', next click and everything is fine.

Where is the toggle to 'Maintain Connections' in Lucee or are you referring to Postgres. I can't see any setting for that.

[Jon Clausen]

Hi Bill,

No, sorry, that is the ACF name for the setting. I mis-named it in that reply.  Lucee maintains connections via the “Timeout" setting.  In Lucee, you can also enable the “Validate” setting ( the PostgreSQL driver is JDBC4 ) to validate the connection before using.  Usually, though, that’s not necessary and the validation does add a tiny bit of overhead to each database transaction.

Are you using a proxy or balancer at all between your PostgreSQL server(s) and your Lucee Server(s)?  Intermittent connection failure errors, in my experience, are usually caused by a keepalive “misunderstanding” between Lucee and the PostgreSQL server and I’ve seen the intermittent failures when using a proxy between the two, which is why I ask.  If so, perchance, then I might suggest using PGBouncer instead of a standard proxy balancer.  It’s much more reliable at connection pooling.

You might also want make sure your postgresql.conf `tcp_keepalives_idle` setting is equal to or higher than your server Timeout setting.  If it’s a Windows, machine, you might also want to check your KeepAliveInterval and KeepAliveTime settings in the registry.

HTH,

Jon  

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/edf8364d-73cb-4097-aa81-7c1c4a004790%40googlegroups.com.

[Bill Tindal]

Thanks Jon.

Not using a proxy or balancer between Lucee and the Postgres server.

I'm trying the 'validate' setting now that I understand what it's doing. The overhead added is OK for me at this time as I just want to ensure that this doesn't happen: The connection attempt failed.

PGBouncer is my next port of call once we get some added traffic.

[Bill Tindal]

@Jon

I was wondering if you had a 'default' config for a production datasource that connects to PostgreSQL. I'm interested in comparing notes, I'm still seeing a 'connection failure' every now and then and my PostgreSQL DB guys are wondering why there's only 1 connection coming from the Lucee server.