[dancer-users] High CPU usage on DB

Mon Dec 12 09:06:16 GMT 2016

Hello,

This conversation makes me wonder about how the database connections works
behind Dancer.

Please, suppose we have a simple application with only two routes

/route1
 -> call Package->new() to create the structure (and the database handler)
 -> do queries
 -> do some stuff
 -> return value

/route2
 -> call Package->new() to create the structure (and the database handler)
 -> do queries
 -> do some stuff
 -> return value

Do we need to insert Package->end() to for example close the database
connections?
Is it any difference if we are using the Plugin or make the DB connections
directly? Cache?

Thanks to all for any light.

2016-12-10 22:28 GMT+01:00 Zahir Lalani <ZahirLalani at oliver.agency>:

> Thx All
>
> No transactions - its mainly selects but I will add in Racke's suggestion
> of finish and see what that does.
>
> As for Starman - so far not had any issues - but we have set it to limit
> the max requests so that it gets cleaned every so often. I do want to try
> out uwsgi - have read very good things - thx for the heads up
>
> Z
>
> > -----Original Message-----
> > From: dancer-users [mailto:dancer-users-bounces at dancer.pm] On Behalf
> > Of Hermann Calabria
> > Sent: 10 December 2016 21:21
> > To: Perl Dancer users mailing list <dancer-users at dancer.pm>
> > Subject: Re: [dancer-users] High CPU usage on DB
> >
> > If you're using Dancer::Plugin::Database (and I suspect D2 has the same
> > issue), you're using transactions, and a crash occurs mid-transaction,
> note
> > that there is no automatic rollback() on die().  I documented this issue
> here,
> > and bigpresh (the plugin author) was kind enough to offer some
> > suggestions:
> >
> > https://github.com/bigpresh/Dancer-Plugin-Database/issues/76
> >
> > With enough of these, you end up with a bunch of unfinished transactions,
> > and the obvious problems eventually start occurring -- memory leaks, high
> > cpu usage, hung processes, etc.
> >
> > Unrelated, but may be helpful: We gave up on Starman. Just could not get
> it
> > stable in a high-traffic production environment. We switched to uwsgi
> (with
> > nginx) and haven't looked back since. uwsgi is fast, compact, has tons of
> > useful options.  It is used by Python stacks, so it is well-supported and
> > updated, including commercial support if you need it.
> >
> > Good luck on your project!
> >
> > -----Original Message-----
> > From: Zahir Lalani
> > Sent: Saturday, December 10, 2016 1:52 AM
> > To: Perl Dancer users mailing list
> > Subject: Re: [dancer-users] High CPU usage on DB
> >
> > Hi Warren
> >
> > We are looking into the processes more - we are also looking at all the
> fine
> > tuning of PG parameters to make sure none of those are having an impact
> >
> > On the Dancer side - maybe I need to check that we are doing things
> > correctly. We use Dancer2::Plugin::Database in the following way.
> >
> > In each module that performs DB requests, we 'use' the above package
> >
> > In a typical DB layer request we will do:
> >
> > Package mmm;
> > use Dancer2 appname => 'APP';
> > Use Dancer2::Plugin::Database
> >
> > .........
> > $sth = database->prepare("select xxxx from yyy(?)"); $sth->execute($id);
> >
> > $result = $sth->fetchrow_hashref();
> >
> > return $result;
> > ............
> >
> > There is no explicit closing of connections.
> >
> > We use starman to run our app, and it looks like the number of starman
> > workers matches exactly the number of open connections to the DB - so it
> > seems that the plugin holds one open connection per instance.
> >
> > Does the above all sound the correct use of the plugin?
> >
> > Z
> >
> > > -----Original Message-----
> > > From: dancer-users [mailto:dancer-users-bounces at dancer.pm] On Behalf
> > > Of Warren Young
> > > Sent: 08 December 2016 17:33
> > > To: Perl Dancer users mailing list <dancer-users at dancer.pm>
> > > Subject: Re: [dancer-users] High CPU usage on DB
> > >
> > > On Dec 8, 2016, at 10:27 AM, Zahir Lalani <ZahirLalani at oliver.agency>
> > > wrote:
> > > >
> > > > We use the Dancer database module to create a connection
> > >
> > > Are you caching those connections or re-opening a fresh one for every
> > > request?
> > >
> > > And if the latter, are you *closing* the connection before returning
> > > the queried data to the client?  Are you sure, evidenced by monitoring
> > > the OS’s open file handles counter?
> > >
> > > You don’t say which OS this is, but if it’s Linux:
> > >
> > >     ls /proc/$(pidof my-dancer-program)/fd | wc -l
> > >
> > > > so in this case we have 20 connections to the DB
> > >
> > > So you believe.  Double-check.
> > >
> > > > a restart of Starman cures the problem
> > >
> > > That sure sounds like a leaked resource of some kind.  I have you
> > > chasing DB file handles.  You say it isn’t leaked memory.  If not
> > > either of those, then it could be web sockets or any similar sort of
> > > thing.
> > >
> > > _______________________________________________
> > > dancer-users mailing list
> > > dancer-users at dancer.pm
> > > http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
> > _______________________________________________
> > dancer-users mailing list
> > dancer-users at dancer.pm
> > http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
> >
> > _______________________________________________
> > dancer-users mailing list
> > dancer-users at dancer.pm
> > http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
> _______________________________________________
> dancer-users mailing list
> dancer-users at dancer.pm
> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.preshweb.co.uk/pipermail/dancer-users/attachments/20161212/304b576e/attachment.html>