search

tosdr / edit.tosdr.org

👍👎 A new web app to rate services
https://edit.tosdr.org
GNU Affero General Public License v3.0
212 stars 37 forks source link

Database Connection Limit #971

Closed JustinBack closed 3 years ago

JustinBack commented 3 years ago

Saw this occurring multiple times today,

The connection limit of heroku is being reached and thus new connections are blocked which result in 500 server errors at Phoenix and beta.tosdr.org

JustinBack commented 3 years ago

I have killed all connections for now so that unused connections get cleaned up

heroku pg:killall -a edit-tosdr-org
JustinBack commented 3 years ago

The issue lies in Phoenix not killing any unused database connections

usename application_name backend_start query wait_event backend_type
biwrhbbtkoxeky /app/vendor/bundle/ruby/2.6.0/bin/puma 2020-12-20 05:43:41.476716+00 SELECT "cases".* FROM "cases" ClientRead client backend
biwrhbbtkoxeky /app/vendor/bundle/ruby/2.6.0/bin/puma 2020-12-20 05:43:42.216318+00 SELECT 1 AS one FROM "case_comments" WHERE "case_comments"."case_id" = $1 LIMIT $2 ClientRead client backend
biwrhbbtkoxeky psql non-interactive 2020-12-20 05:49:43.297141+00 select usename, application_name, backend_start, query, wait_event, backend_type from pg_stat_activity WHERE usename = 'biwrhbbtkoxeky'; client backend

Per query above the Select Cases has stayed up for 7 minutes so far

michielbdejong commented 3 years ago

Is it possible to upgrade the Database to the heroku-postgresql:standard-0 plan? This has a connection limit of 120 and should be enough for now.

Possible yes, but that would cost the project an extra 41 dollars per month, and I don't think the issue here is that 20 connections is not enough for us. The root problem is probably that connections are not being closed by edit-tosdr-org.

Wouldn't we just be paying more and still hit the 120 connections limit?

Our current monthly hosting costs are:

Total: 1+0+6+25+9+7+15=63 USD

This is not counting the new server we will need for the tosdr.org rewrite in PHP.

JustinBack commented 3 years ago

Ah I see, didn't expect a "Standard" plan to be that costly.

JustinBack commented 3 years ago

As per E-Mail reply

Maybe there is a setting in ruby to turn off persistent database connections?

At least there is in PHP and its off by default

(new PDO(null, null, null, array(PDO::ATTR_PERSISTENT => true));)

I have checked if it may be my application creating unnecessary connections however this problem occurs only at specific times so can it be a cron pooling connections?

Below are some timestamps where the errors occur:

2020-12-19 21:16:30 2020-12-19 23:20:00 2020-12-20 00:07:39 -> From here on only errors until 2020-12-20 06:42:38 where I killed all connections

So I think a cron running after 12 AM is pooling up connections

Traceback:

#0 (OMITTED)/crisp/pixelcatproductions/class/crisp/api/Phoenix.php(31): crisp\core\Postgres->__construct()
#1 (OMITTED)/crisp/pixelcatproductions/class/crisp/api/Phoenix.php(630): crisp\api\Phoenix::initPGDB()
#2 (OMITTED)/crisp/themes/crisp/includes/frontpage.php(7): crisp\api\Phoenix::getServicePG('182')
#3 (OMITTED)/crisp/pixelcatproductions/class/crisp/core/Template.php(50): require('(OMITTED)/...')
#4 (OMITTED)/crisp/pixelcatproductions/class/crisp/core/Templates.php(32): crisp\core\Template->__construct(Object(Twig\Environment), 'index', 'frontpage')
#5 (OMITTED)/crisp/pixelcatproductions/crisp.php(130): crisp\core\Templates::load(Object(Twig\Environment), 'index', 'frontpage')
#6 (OMITTED)/crisp/index.php(20): require_once('(OMITTED)/...')
#7 {main}
JustinBack commented 3 years ago

Update: Found the cause, it is the tosback crawler cron not cleaning up connections

 2265 ?        Ss     0:00 /bin/sh -c bash /home/tosdr/cron.sh
 2266 ?        S      0:00 bash /home/tosdr/cron.sh
 7143 ?        Ss     0:00 /bin/sh -c bash /home/tosdr/cron.sh
 7144 ?        S      0:00 bash /home/tosdr/cron.sh
24329 ?        Ss     0:00 /bin/sh -c bash /home/tosdr/cron.sh
24330 ?        S      0:00 bash /home/tosdr/cron.sh
29286 ?        Ss     0:00 /bin/sh -c bash /home/tosdr/cron.sh
29287 ?        S      0:00 bash /home/tosdr/cron.sh
usename application_name backend_start query wait_event backend_type
biwrhbbtkoxeky /app/vendor/bundle/ruby/2.6.0/bin/puma 2020-12-20 08:52:33.508949+00 SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 ClientRead client backend
biwrhbbtkoxeky 2020-12-20 06:16:08.840075+00 SELECT p."id", p."quoteText", p."quoteStart", p."quoteEnd", p."status" FROM points p INNER JOIN documents d ON p.document_id=d.id WHERE d.id = $1::int AND (p."status" = 'approved' OR p."status" = 'pending' OR p."status" = 'approved-not-found' OR p."status" = 'pending-not-found') ClientRead client backend
biwrhbbtkoxeky 2020-12-20 08:16:07.004612+00 SELECT p."id", p."quoteText", p."quoteStart", p."quoteEnd", p."status" FROM points p INNER JOIN documents d ON p.document_id=d.id WHERE d.id = $1::int AND (p."status" = 'approved' OR p."status" = 'pending' OR p."status" = 'approved-not-found' OR p."status" = 'pending-not-found') ClientRead client backend
biwrhbbtkoxeky 2020-12-20 07:16:08.036001+00 SELECT p."id", p."quoteText", p."quoteStart", p."quoteEnd", p."status" FROM points p INNER JOIN documents d ON p.document_id=d.id WHERE d.id = $1::int AND (p."status" = 'approved' OR p."status" = 'pending' OR p."status" = 'approved-not-found' OR p."status" = 'pending-not-found') ClientRead client backend
biwrhbbtkoxeky psql non-interactive 2020-12-20 09:13:05.098376+00 select usename, application_name, backend_start, query, wait_event, backend_type from pg_stat_activity WHERE usename = 'biwrhbbtkoxeky'; client backend
JustinBack commented 3 years ago

Disabled cron for now.