Apache, MySQL, Pers. Connections Question from Pro

[anon_login_001]

Not sure if this is Apache Related, MySQL Related, or PHP - but I'm in need of advice!

 

I have an (PHP) app that uses persistent (and even with non-persistent) connections to a MySQL database.

 

In doing some much-needed performance tuning, I notice something peculiar:

 

I open open several tabs (IE7, FF) in quick succession, which go to pages that I know run SELECT queries that take quite a few seconds each --

 

While watching the MySQL Administrator, or other monitoring tool, I notice that of ~10 available connections (sleeping) it will only use one... it will finish, and either use the same or a different connection -- but never more than one at a time.

 

I would think this is normal if it was a single page loading, as a persistent connection would be used throughout the script - however when opening multiple tabs/browser windows, shouldn't there be multiple database connections used?

 

I have thouroughly checked the MySQL settings to ensure that it is not a problem limiting connections -- when other clients do the same thing as me from a different computer, the same happens for them -- we each get ONE connection, which processes the queries from multiple tables one at a time.

 

Another strange occurence seems to be that although the queries appear to be happening in order,  ALL of the browser tabs load at the same time. None show any content until ALL are finished, and the last query has finished.

 

Does this sound like an Apache issue, or does PHP/Apache recognize somehow that all of my connections are coming from my client and will not open up a seperate MySQL connection?

 

I'm just trying to find out where to look next... Apache, MySQL, PHP?

 

Any ideas?

[.josh]

did you try opening and specifying different connections?

 

page1.php

$conn1 = mysql_connect(...);
$result = mysql_query('...',$conn1);

 

page2.php

$conn2 = mysql_connect(...);
$result = mysql_query('...',$conn2);

 

etc...

 

[anon_login_001]

I haven't specifically tried that, on account of the app is using a somewhat hacked version of (what at least appears to be, by name) the PEAR Database abstraction class...

 

Even with multiple connections defined, shouldn't seperate page requests (even from the same client) be opening their own connections? 

 

I'll look into dynamically naming the connections, but in the mean time I'm still open to any other ideas.

[.josh]

I really don't specifically know but it seems more efficient to me for the server to have one connection stream handle the requests and queries, especially if it's from the same client.  It even seems more efficient if it's different clients making requests/queries through the same script or to the same database. 

 

I think that's where specifying the connection comes in; so you can specifically tell it to use a different stream.  But again, I'm just kinda guessing, based on what seems more logical/efficient.

[anon_login_001]

A single connection is more effecient for all the reasons that persistent connections were even invented.

 

I'm just trying to find out why I load a page in a new tab/browser window, which is a new Request to Apache, a new instance of PHP, etc, and yet all of those queries are being routed through one pipe. Which is fine, except for when a SELECT up front is taking 20+ seconds, and *all of my other pages* are blocked until *all of the queries* are finished -- from every tab.

 

I'm trying to figure out if Apache is doing this, on account of it sees all requests from my IP as a single client ... but even then, why does PHP (opening connections to the database) not behave as a NEW CLIENT to the database, since these are technically different scripts running, requested from Apache at different times (although very close together).

 

Ah, well... I do appreciate the help!

[.josh]

Maybe it's the database.  Maybe When page1 sends a query, the database locks everything else out until that query is executed, in order to prevent other queries from somehow messing up that query, so basically each query has to "stand in line" and wait it's turn?

 

Haha I dunno. I mean, I know you can specifically tell the db to do that, but idk if that's a default setting or if your db does have that turned on or somethin'.  Just another shot in the dark.

[anon_login_001]

All shots are welcome at this point. 

 

I'm stumped.