FAQ keeps MySQL sessions open
Moderator: Thorsten
FAQ keeps MySQL sessions open
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.
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 |
| MySQL 4.1.18 | PHP 4.4.2 | Apache |
Re: FAQ keeps MySQL sessions open
Hi,
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:to:
and you must check into your PATH_TO_PMFINSTALL/inc/data.php if you're using this type of DB (mysql) among the supported:
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.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.
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);
Code: Select all
$this->conn = mysql_pconnect($host, $user, $passwd);
Code: Select all
$DB["type"] = 'mysql';
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.BSOD2600 wrote:Also, it seems the faqsessions table keeps growing and growing with no option to prune it.
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist
Amazon.co.uk Wishlist
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???
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???
-
- Posts: 3
- Joined: Mon Nov 06, 2006 2:22 am
Re: FAQ keeps MySQL sessions open
BSOD was posting the question for me, I admin the pages he's concerned about.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:to:Code: Select all
$this->conn = mysql_connect($host, $user, $passwd);
Code: Select all
$this->conn = mysql_pconnect($host, $user, $passwd);
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.
Re: FAQ keeps MySQL sessions open
Hi,
Matteo
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: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.
- Is your PMF a public server (I'm thinking to some kind of DOS)?
- Did you perform any customization on PMF code?
- 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)?
Matteo
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist
Amazon.co.uk Wishlist
-
- Posts: 3
- Joined: Mon Nov 06, 2006 2:22 am
Re: FAQ keeps MySQL sessions open
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:Is your PMF a public server (I'm thinking to some kind of DOS)?
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:Did you perform any customization on PMF code?
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 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)?
Re: FAQ keeps MySQL sessions open
Hi,
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:
comparing them with some of these values from your MySQL server configuration:
Regards,
Matteo
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.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.
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%'
Code: Select all
SHOW VARIABLES LIKE '%thread%'
Code: Select all
SHOW VARIABLES LIKE '%connections%'
Code: Select all
SHOW VARIABLES LIKE '%timeout%'
Matteo
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist
Amazon.co.uk Wishlist
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: 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
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
I'll do more tests on a more powerfull test server in the next days.
Regards,
Matteo
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist
Amazon.co.uk Wishlist
-
- Posts: 3
- Joined: Mon Nov 06, 2006 2:22 am
Re: FAQ keeps MySQL sessions open
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.matteo wrote:Could you post what are the parameters in your MySQL server you are looking at to find out this connections overload?
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.
Re: FAQ keeps MySQL sessions open
Hi,
Ciao,
Matteo
Perfect! Stay with mysql_connect and, optionally, move wait_timeout to about 30sec.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.
Read here how to correctly disable users sessions (faqsessions table). Let me know if PMF keep on not tracking your users sessions.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.
Ciao,
Matteo
phpMyFAQ QA / Developer
Amazon.co.uk Wishlist
Amazon.co.uk Wishlist