FAQ keeps MySQL sessions open

[BSOD2600]

I'm using 1.6.5 of the FAQ with MySQL 5.0.22, for pc-linux-gnu (i486) using readline 5.1. It seems the FAQ creates a new MySQL session for every user and then letting it sleep. This of course results in hundreds of open and idle connections after a while.

Also, it seems the faqsessions table keeps growing and growing with no option to prune it.

[matteo]

Hi,

I'm using 1.6.5 of the FAQ with MySQL 5.0.22, for pc-linux-gnu (i486) using readline 5.1. It seems the FAQ creates a new MySQL session for every user and then letting it sleep. This of course results in hundreds of open and idle connections after a while.

we change it in the last release due to several users issues about persistent connections: we'll move the code to be more intelligent when choosing the type of connection, when permitted i.e. the mysqli extension do not provide explicit support to persistent connections.

In the mean while you could fix it simply imposing the use of a persistent connection. Locate, backup and open the PATH_TO_PMFINSTALL/inc/mysql.php and move the line below, from:

Code: Select all

		$this->conn = mysql_connect($host, $user, $passwd);

to:

Code: Select all

		$this->conn = mysql_pconnect($host, $user, $passwd);

and you must check into your PATH_TO_PMFINSTALL/inc/data.php if you're using this type of DB (mysql) among the supported:

Code: Select all

$DB["type"] = 'mysql';

Also, it seems the faqsessions table keeps growing and growing with no option to prune it.

You could delete sessions per month in the admin page: this deletion button will both empty the related records in the DB and delete the files in the data/ folder.

[tommo]

I think this is connected to the thread above and I need a bit of clarification..

I've been running 1.6.2 and my host pointed out a problem with mysql sessions not closing which were causing problems with server load.

I have now updated to 1.6.5 will this problem now be solved or do i need to also make the changes mentioned above???

[Thorsten]

Hi,

yes, we fixed that.

bye
Thorsten

[GargoyleMT]

In the mean while you could fix it simply imposing the use of a persistent connection. Locate, backup and open the PATH_TO_PMFINSTALL/inc/mysql.php and move the line below, from:

Code: Select all

		$this->conn = mysql_connect($host, $user, $passwd);

to:

Code: Select all

		$this->conn = mysql_pconnect($host, $user, $passwd);

BSOD was posting the question for me, I admin the pages he's concerned about.

I have that line changed, as well as the patch to 1.6.6 applied, and 99 of my machine's 100 mysql connections are used (and idle) phpmyfaq sessions. I don't think this is quite right. Please let me know what I should try to free up some of those sessions.

[matteo]

Hi,

I have that line changed, as well as the patch to 1.6.6 applied, and 99 of my machine's 100 mysql connections are used (and idle) phpmyfaq sessions. I don't think this is quite right. Please let me know what I should try to free up some of those sessions.

we'll try to replicate the issue in order to find out what's wrong in PMF code. In the mean time here are some few questions:

1. Is your PMF a public server (I'm thinking to some kind of DOS)?
2. Did you perform any customization on PMF code?
3. Do you have any difference between using mysql_connect versus mysql_pconnect (my expectation is that mysql_pconnect should be the worst compared to your opened and not ever closed connections)?

Regards,
Matteo

[GargoyleMT]

Is your PMF a public server (I'm thinking to some kind of DOS)?

It is a public server, and it is experiencing an attack of some sort, but all evidence points to non-HTTP packets arriving on port 80. The FAQ is installed in a subdirectory, so I'm not sure how it could be affected.

Did you perform any customization on PMF code?

I have been upgrading by patch and then running the upgrade script. I just did a diff to the virgin 1.6.6 code, and the only changed file is index.tpl (to add tracking code and to hide the feedback options -- people were using it instead of the our phpbb forum).

Do you have any difference between using mysql_connect versus mysql_pconnect (my expectation is that mysql_pconnect should be the worst compared to your opened and not ever closed connections)?

I changed back to mysql_connect, but I'll have to get someone with root access to see if the changes had any effect.

[matteo]

Hi,

[I changed back to mysql_connect, but I'll have to get someone with root access to see if the changes had any effect.

OK, I'll wait for your feedback thinking also how to replicate your issue and some other questions/tests if the issue will be strictly related to your installation i.e. if I'll not able to replicate an "abuse" of idle MySQL connections on my servers.
Could you post what are the parameters in your MySQL server you are looking at to find out this connections overload?
I'm supposing you're watching at these running values:

Code: Select all

SHOW STATUS LIKE '%connection%'

Code: Select all

SHOW STATUS LIKE 'Thread%'

comparing them with some of these values from your MySQL server configuration:

Code: Select all

SHOW VARIABLES LIKE '%thread%'

Code: Select all

SHOW VARIABLES LIKE '%connections%'

Code: Select all

SHOW VARIABLES LIKE '%timeout%'

Regards,
Matteo

[matteo]

Hi,
I'm working on replicating your issue bombing one of my test servers (the slowest) and I'm currently not able to saturate the whole of the 100 (default value) MySQL connections:

Code: Select all

SHOW PROCESSLIST

returns very few (5) idle connections (and you can reduce them in average by tuning wait_timeout value) only when using persistent connections (mysql_pconnect).
I'll do more tests on a more powerfull test server in the next days.

Regards,
Matteo

[GargoyleMT]

Could you post what are the parameters in your MySQL server you are looking at to find out this connections overload?

Based on the output of that (particularly, the thread status), it looks like the software is behaving well, since the only connection shown is my local one, even after I visit the FAQ.

So, all is right. Or at least I can't find a clue to help you replicate the issue as we saw before we made that above change to pconnect.


Is there a way to keep PMF from updating the faqsessions column in the table? It gets rather large, and for me, new entries to it are not being shown on the session admin page... Before I manually cleared the table when these troubles started, it only showed up to July of this year.

[matteo]

Hi,

Based on the output of that (particularly, the thread status), it looks like the software is behaving well, since the only connection shown is my local one, even after I visit the FAQ.

So, all is right. Or at least I can't find a clue to help you replicate the issue as we saw before we made that above change to pconnect.

Perfect! Stay with mysql_connect and, optionally, move wait_timeout to about 30sec.

Is there a way to keep PMF from updating the faqsessions column in the table? It gets rather large, and for me, new entries to it are not being shown on the session admin page... Before I manually cleared the table when these troubles started, it only showed up to July of this year.

Read here how to correctly disable users sessions (faqsessions table). Let me know if PMF keep on not tracking your users sessions.

Ciao,
Matteo