mahara-contributors team mailing list archive
-
mahara-contributors team
-
Mailing list archive
-
Message #62616
[Bug 1887321] Re: too many DB connections
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
References