[dancer-users] High CPU usage on DB

[Stefan Hornburg (Racke)]

On 12/10/2016 10:52 AM, Zahir Lalani wrote:
> 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

You can try to use $sth->finish before "return $result." to release resources.

Regards
        Racke

> 
>> -----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
> 


-- 
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration.