too many DB connections

[Cecilia Vela Gurovic]

From forum post https://mahara.org/interaction/forum/topic.php?id=8654&offset=0&limit=10#post34439

By what is reported in this forum post, it looks like the amount of connections by DB username is over 50 even when it's only one mahara account logged in on the site.

We have to look into why this is happening, if we are opening connections but never closing them.

By a quick search I could find that we are calling
$db = ADONewConnection($CFG->dbtype);
from init.php everytime, there are no checks for conditions so we always create a new connection
but we are not calling the Disconnect function from ADOConnection to kill the connection.

The forum post resports this for mysqli but we should check if we have the same problem with postgres as well

[Kristina Hoeppner (kris-hoeppner)]

More investigation is needed for this. Pushing it out to 21.04 to do as we are getting too close to the release of 20.10 to make major changes in this area.

[Doris Tam (doristam)]

We call $db = ADONewConnection($CFG->dbtype); at evey page refresh, however looking closer, ADOdb helps us with creating a persistent connection if out $CFG->dbpersist is true.

I was unable to replicate this on a Postgres DB.

 $db = ADONewConnection($CFG->dbtype);
    if (empty($CFG->dbhost)) {
        $CFG->dbhost = '';
    }
    // The ADODB connection function doesn't have a separate port argument, but the
    // postgres, mysql, and mysqli drivers all support a $this->dbport field.
    if (!empty($CFG->dbport)) {
        $db->port = $CFG->dbport;
    }
    if (!empty($CFG->dbpersist)) { // Use persistent connection (default)
        $dbconnected = $db->PConnect($CFG->dbhost,$CFG->dbuser,$CFG->dbpass,$CFG->dbname);
    }
    else { // Use single connection
        $dbconnected = $db->Connect($CFG->dbhost,$CFG->dbuser,$CFG->dbpass,$CFG->dbname);
    }

ADOdb's PConnect uses the new connection to persist the current one, whereas their Connect doesn't.

References:
https://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:adonewconnection
https://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:connect - simply establishes a connection

Other checks I would make is to make sure the config is set:
- $CFG->dbtype is set to mysqli and that the extension_loaded is true
- $CFG->dbpersist is not set to true. If it is not set, it will create a new connection each time.

However, one thing I am unsure of in the following code is where we set $CFG->dbpersist is set. When I debug this, it skips the persist function, so I must be creating a new connections each time locally too.

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/11851

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/11851
Committed: https://git.mahara.org/mahara/mahara/commit/f76a305746e648f21183d23c6cf0514887fba900
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit f76a305746e648f21183d23c6cf0514887fba900
Author: Doris Tam <email address hidden>
Date: Thu Jul 8 13:57:08 2021 +1200

Bug 1887321: Update old database connection comments to match current Mahara logic

Change-Id: Icf94aa4bb0567655ddd6a83843f6dbef7b14113e

[Doris Tam (doristam)]

After investigating this bug on both MySQL and Postgres, I didn't find my connections were hanging or staying active.

I found that there is a config setting $cfg->dbpersist that when set to true, our init.php file calls the ADONewConnection's PConnect().

There are two functions in the init.php to create database connections: ADOdb’s PConnect() and Connect().
    • Pconnect() uses a new connection and closes the old database connection on refresh.
    • Connect() persists the database connection and may lead to having unnecessary active connections.

On Postgres, I called the following query and found only one connection, no matter how many times I refreshed my page.

SELECT pid, datname ,application_name
    ,client_hostname ,client_port, backend_start
    ,query_start ,query,state
FROM pg_stat_activity
WHERE state = 'active';

On MySQL, I called the following query and got two connections maximum on numerous page refreshes.
`show status where variable_name = 'threads_connected'; `
`select id, user, host, db, command, time, state, info from information_schema.processlist \G`

There are two connections. One is asleep and one is active because I ran a query
(threads is number of active connections)

*************************** connection one ***************************
     id: 213
   user: root
   host: localhost
     db: mahara-master
command: Query
   time: 0
  state: executing
   info: select id,user,host,db,command,time,state,info from information_schema.processlist
*************************** connection two ***************************
     id: 131
   user: root
   host: localhost
     db: mahara-master
command: Sleep
   time: 1689
  state:
   info: NULL
2 rows in set (0.01 sec)

mysql> show status where variable_name = 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 2 |
+-------------------+-------+

I tried changing the mysql> SET GLOBAL max_connections = 100;
I learned that max_connctions applies only to 'threads_connected' (only active connections), not the total number of connections.

I eventually got the error that you mentioned by setting my max_connections=1;

There are a few checks to make when using MySQL to have Mahara running smoothly:
    • In your Mahara config.php file: $CFG->dbpersist make sure this is set to false or not set at all.
    • In your database config: max_connections is not set too low.
    • If you have a load balancer, check that it does not restrict connections itself.
    • In your Mahara config.php file: $CFG->dbtype is set to mysqli and that the extension_loaded is true.

References:
https://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:adonewconnection
https://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:connect