FAQ keeps MySQL sessions open

In this board you can talk about general questions about phpMyFAQ

Moderator: Thorsten

Post Reply
BSOD2600
Posts: 36
Joined: Thu Nov 10, 2005 4:50 am
Location: USA

FAQ keeps MySQL sessions open

Post by BSOD2600 » Tue Oct 17, 2006 11:12 pm

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.
| phpmyfaq 1.6.3 |
| MySQL 4.1.18 | PHP 4.4.2 | Apache |

matteo
Posts: 572
Joined: Sun Nov 20, 2005 6:53 pm
Location: Italy

Re: FAQ keeps MySQL sessions open

Post by matteo » Wed Oct 18, 2006 9:08 am

Hi,
BSOD2600 wrote: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';
BSOD2600 wrote: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.
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist

tommo
Posts: 36
Joined: Mon Aug 01, 2005 4:48 pm

Post by tommo » Thu Oct 26, 2006 12:07 am

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
Posts: 15025
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Post by Thorsten » Thu Oct 26, 2006 6:33 am

Hi,

yes, we fixed that.

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist

GargoyleMT
Posts: 3
Joined: Mon Nov 06, 2006 2:22 am

Re: FAQ keeps MySQL sessions open

Post by GargoyleMT » Mon Nov 06, 2006 2:26 am

matteo wrote: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
Posts: 572
Joined: Sun Nov 20, 2005 6:53 pm
Location: Italy

Re: FAQ keeps MySQL sessions open

Post by matteo » Mon Nov 06, 2006 11:18 pm

Hi,
GargoyleMT wrote: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
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist

GargoyleMT
Posts: 3
Joined: Mon Nov 06, 2006 2:22 am

Re: FAQ keeps MySQL sessions open

Post by GargoyleMT » Tue Nov 07, 2006 2:24 pm

matteo wrote: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.
matteo wrote: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).
matteo wrote: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
Posts: 572
Joined: Sun Nov 20, 2005 6:53 pm
Location: Italy

Re: FAQ keeps MySQL sessions open

Post by matteo » Tue Nov 07, 2006 9:14 pm

Hi,
GargoyleMT wrote:[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
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist

matteo
Posts: 572
Joined: Sun Nov 20, 2005 6:53 pm
Location: Italy

Post by matteo » Wed Nov 08, 2006 12:10 am

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
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist

GargoyleMT
Posts: 3
Joined: Mon Nov 06, 2006 2:22 am

Re: FAQ keeps MySQL sessions open

Post by GargoyleMT » Thu Nov 09, 2006 2:04 pm

matteo wrote: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
Posts: 572
Joined: Sun Nov 20, 2005 6:53 pm
Location: Italy

Re: FAQ keeps MySQL sessions open

Post by matteo » Thu Nov 09, 2006 4:56 pm

Hi,
GargoyleMT wrote: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.

GargoyleMT wrote: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
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist

Post Reply