mahara-contributors team mailing list archive

[Doris Tam <1887321@xxxxxxxxxxxxxxxxxx>]

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

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: mahara-contributors
https://bugs.launchpad.net/bugs/1887321

Title:
  too many DB connections

Status in Mahara:
  Fix Committed

Bug description:
  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

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1887321/+subscriptions