max_user_connections issue

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
foxed
Registered User
Posts: 10
Joined: Tue Jul 11, 2006 4:18 pm

max_user_connections issue

Post by foxed » Tue Jul 11, 2006 4:28 pm

Hi,

My forum is encountering problems with what seem to be persistant connections.

My host has imposed a 10 connection limit until I can sort this out - so as you can imagine my site is more down than up!

I have looked at /db/mysql4.php and lines 22 to 49 read like:

Code: Select all

if(!defined("SQL_LAYER"))
{

define("SQL_LAYER","mysql4");

class sql_db
{

	var $db_connect_id;
	var $query_result;
	var $row = array();
	var $rowset = array();
	var $num_queries = 0;
	var $in_transaction = 0;

	//
	// Constructor
	//
	function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = false)
	{
		$this->persistency = $persistency;
		$this->user = $sqluser;
		$this->password = $sqlpassword;
		$this->server = $sqlserver;
		$this->dbname = $database;

		$this->db_connect_id = ($this->persistency) ? mysql_pconnect($this->server, $this->user, $this->password) : mysql_connect($this->server, $this->user, $this->password);
From what I can see - persistancy is not turned on. Please correct me if Im wrong.

What I am seeing however is whenever I view ANY topic within the forum it starts adding a process to mysql for every view.

The following is a screenshot of the database in phpmyadmin - showing the current processes. Bearing in mind I have taken this forum offline during testing it, only I am accessing it - no other applications other than phpbb are accessing the database.
I clicked and viewed four topics - each view created a process which was left in SLEEP command - filling a connection slot.

I can assure you with a busy forum I was ploughing through quite a lot of these connections in a very very short space of time!

As far as I knew phpBB closed off its connections once it was finished with them rather than putting them in this SLEEP command?

Any help greatly greatly appreciated!

Image

foxed
Registered User
Posts: 10
Joined: Tue Jul 11, 2006 4:18 pm

Post by foxed » Tue Jul 11, 2006 8:37 pm

Any ideas? :)

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom » Tue Jul 11, 2006 9:14 pm

Hosts who complain about persistent connections on user scripts are, um, well, I'm not going to use the proper words to describe their intelligence here, because it's a family forum. Any host who wants to remove persistent connections from there server need only make a one line change to the php.ini file:

Code: Select all

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = On
becomes:

Code: Select all

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = Off
Then, any program that uses mysql_pconnect will have it magically changed to mysql_connect. No muss, no fuss.

But, these people rarely understand how persistent connections work, so they don't know why or how to tweak them.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

foxed
Registered User
Posts: 10
Joined: Tue Jul 11, 2006 4:18 pm

Post by foxed » Tue Jul 11, 2006 10:12 pm

Um ... thank you for your input.

But is there any way I can actually get around this issue? Switching hosts is not an option.

--

Edit: I feel I should highlight - is there any way that I can get around the issue? I, like the vast majority of Shared Hosting users do not have the ability to modify the php.ini file. A further tragedy is that my host, is not willing to :)

User avatar
Lumpy Burgertushie
Registered User
Posts: 66581
Joined: Mon May 02, 2005 3:11 am
Contact:

Post by Lumpy Burgertushie » Tue Jul 11, 2006 10:29 pm

foxed wrote: Um ... thank you for your input.

But is there any way I can actually get around this issue? Switching hosts is not an option.

--

Edit: I feel I should highlight - is there any way that I can get around the issue? I, like the vast majority of Shared Hosting users do not have the ability to modify the php.ini file. A further tragedy is that my host, is not willing to :)


the answer to that is probably .. no.

if your host won't setup their servers in a reasonable way, then your only choice is to live with whatever they do , or change hosts.

most people do not have this problem.

most hosts, set their servers up in a reasonable fashion.


phpbb should not be causing this problem.

if you have any MODs installed, or other scritpts running that access the database, then you can turn them off or uninstall them or whatever.

but phpbb does not do this.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom » Wed Jul 12, 2006 2:56 pm

You can edit your db/mysql.php and db/mysql4.php scripts to replace the optional use of mysql_pconnect() with just mysql_connect(), which means any MOD that might have switched on persistent connections won't get them, but, ultimately, it's your host that has things screwed up in this case.

Heck, I'll bet US$0.02 that they're running PHP as a CGI application, which means it CAN NOT have pconnections (all connections are closed as soon as the page is sent), and the performance issues are revolving around the overhead of creating/destroying tasks...
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

majjk99
Registered User
Posts: 238
Joined: Sun Dec 07, 2003 10:36 pm
Location: EU

Post by majjk99 » Wed Aug 02, 2006 9:54 am

I'm having problem with my webhost putting a limit on max_user_connections, which give me the error message:

Code: Select all

Warning: mysql_connect(): User myjjkco_myjjk has already more than 'max_user_connections' active connections in /home/myjjkco/public_html/db/mysql4.php on line 48
my /db/mysql4.php has got the following on line 40:

Code: Select all

function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true)
so I suppose I should then just set "$persistency = false". but what about
espicom wrote: You can edit your db/mysql.php and db/mysql4.php scripts to replace the optional use of mysql_pconnect() with just mysql_connect(), which means any MOD that might have switched on persistent connections won't get them


which bit am I upposed to edit, and how?

majjk99
Registered User
Posts: 238
Joined: Sun Dec 07, 2003 10:36 pm
Location: EU

Post by majjk99 » Thu Aug 17, 2006 3:08 pm

majjk99 wrote:
espicom wrote:You can edit your db/mysql.php and db/mysql4.php scripts to replace the optional use of mysql_pconnect() with just mysql_connect(), which means any MOD that might have switched on persistent connections won't get them


which bit am I upposed to edit, and how?


I've once again having problems with max user connections, so what am I supposed to do to cover this area?

Also, my host have set max user connections to 25. On an average board, how many visitors should I be able to handle with that (assuming no MODs)? The reason for asking is that I don't really have that many visitors. I hardly ever have more than 10-20 visitors at a time, so this shouldn't really be a problem, right? So is the likely trouble maker a MOD, or what do you think?

majjk99
Registered User
Posts: 238
Joined: Sun Dec 07, 2003 10:36 pm
Location: EU

Post by majjk99 » Thu Aug 17, 2006 10:11 pm

anyone?

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom » Fri Aug 18, 2006 12:54 am

You can handle 25 simultaneous page requests - each page makes ONE connection to the database, and it ends when the page is sent to the browser. You can server hundreds of people, so long as no more than 25 requests are made at the same time.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

majjk99
Registered User
Posts: 238
Joined: Sun Dec 07, 2003 10:36 pm
Location: EU

Post by majjk99 » Sun Aug 20, 2006 1:40 pm

I understand that I can handle 25 simultaneous page requests, but what does that mean in practice? When should I start to experience problems? When I reach 1000 daily visitors? 5000? 10000? I really have no clue how much of a limit it is to have max user connections = 25...

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom » Mon Aug 21, 2006 3:59 pm

There is no way to positively say where you will hit a problem... other than you can't have the problem with fewer than a dozen users. You could hit it with 13 people online, if they're using a browser that "pre-fetches" pages in the background, or accessing through "intelligent proxies" that do the same sort of thing.

The only way to keep people from seeing a "too many connections" error is to make sure that the web server is configured to allow no more than the number of database connections you are allowed. I.e., if you have 25 database connections available, then Apache should not accept more than 25 simultaneous web connections for your site. A browser will retry a web connection that is "busy", but PHPBB doesn't have a "wait and retry the connection" function built in.

Hmmm... maybe a spot for a MOD...
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Locked

Return to “2.0.x Support Forum”