Hi All Posting this to the Dancer list as there is possibly a connection. We have a fairly complex query we run from our web app and it normally runs just fine. However, we have noticed that after while the system slows down and we see the postgres server on 100% CPU. It is not out of memory and not thrashing the disk. We have 2 app servers each with 10 workers. We use the Dancer database module to create a connection, and so in this case we have 20 connections to the DB. When we see the performance issue, a restart of Starman cures the problem. We never have to do anything in the DB, just dropping those Starman connections is enough to resolve. I don't understand enough about how the DBI connections work - does the above ring any bells for anyone? Z
On Dec 8, 2016, at 10:27 AM, Zahir Lalani <ZahirLalani@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.
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@dancer.pm] On Behalf Of Warren Young Sent: 08 December 2016 17:33 To: Perl Dancer users mailing list <dancer-users@dancer.pm> Subject: Re: [dancer-users] High CPU usage on DB
On Dec 8, 2016, at 10:27 AM, Zahir Lalani <ZahirLalani@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@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
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@dancer.pm] On Behalf Of Warren Young Sent: 08 December 2016 17:33 To: Perl Dancer users mailing list <dancer-users@dancer.pm> Subject: Re: [dancer-users] High CPU usage on DB
On Dec 8, 2016, at 10:27 AM, Zahir Lalani <ZahirLalani@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@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
-- Ecommerce and Linux consulting + Perl and web application programming. Debian and Sympa administration.
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@dancer.pm] On Behalf Of Warren Young Sent: 08 December 2016 17:33 To: Perl Dancer users mailing list <dancer-users@dancer.pm> Subject: Re: [dancer-users] High CPU usage on DB
On Dec 8, 2016, at 10:27 AM, Zahir Lalani <ZahirLalani@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@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
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@dancer.pm] On Behalf Of Hermann Calabria Sent: 10 December 2016 21:21 To: Perl Dancer users mailing list <dancer-users@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@dancer.pm] On Behalf Of Warren Young Sent: 08 December 2016 17:33 To: Perl Dancer users mailing list <dancer-users@dancer.pm> Subject: Re: [dancer-users] High CPU usage on DB
On Dec 8, 2016, at 10:27 AM, Zahir Lalani <ZahirLalani@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@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
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@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@dancer.pm] On Behalf Of Hermann Calabria Sent: 10 December 2016 21:21 To: Perl Dancer users mailing list <dancer-users@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@dancer.pm] On Behalf Of Warren Young Sent: 08 December 2016 17:33 To: Perl Dancer users mailing list <dancer-users@dancer.pm> Subject: Re: [dancer-users] High CPU usage on DB
On Dec 8, 2016, at 10:27 AM, Zahir Lalani <ZahirLalani@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@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Zahir> We have a fairly complex query we run from our web app and it Zahir> normally runs just fine. However, we have noticed that after Zahir> while the system slows down and we see the postgres server on Zahir> 100% CPU. It is not out of memory and not thrashing the Zahir> disk. We have 2 app servers each with 10 workers. We use the Zahir> Dancer database module to create a connection, and so in this Zahir> case we have 20 connections to the DB. When we see the Zahir> performance issue, a restart of Starman cures the problem. We Zahir> never have to do anything in the DB, just dropping those Zahir> Starman connections is enough to resolve. What happens if you just run the same query using a perl DBI module directly against your DB? Or run 10-20 of them in succession, but never actually exiting? Or can you run a cut down version of your app and recreate the problem using the apache load testing tool while you watch the system more closely? Does the memory used by the starman process go up when this happens? How about the PGsql process(es)? Do they show increased memory use? You could even do an 'strace' on your PG process(es) to see what they're doing and if they're looping through a bunch of poll/select calls or something. As others have said, it sounds like you've got an open resource that's not being closed properly. John
participants (6)
-
Hermann Calabria -
John Stoffel -
Juan José 'Peco' San Martín -
Stefan Hornburg (Racke) -
Warren Young -
Zahir Lalani