mysql connections bottle neck via PHP

Question

Currently we are restoring MySQL connections in a $_SESSION in order to save round trip time to set up connection to MySQL,

but the problem is that the number of connections exceed the limit quickly,

so what's your guys' solution?

Do you create a new connection each time,and close that connection after using,or like me,restore it?

Answer 1

I setup a new connection on each new page call. I do use the 1 connection throughout the page, and close it when the page is sent to the browser.

I suspect you're optimizing too early and causing yourself unneeded pain.

Answer 2

PHP really lacks in the area of connection pooling, a very common concept in other languages/frameworks.

You can get pretty far with using mysql_pconnect . Storing a connection in a session sounds like a really bad thing though, you'd chew up way too much resources.

Answer 3

Storing a connection in a session just should not work. Don't do that. I'd be surprised if it actually reused that connection on subsequent visits, since it involves networks access, and you can't store a network connection in a file! At best, it reconnects upon the next page view, which doesn't gain you anything. At worst, you have a separate connection for every request that gets held open and not closed properly.

edit - some more thoughts

Furthermore, in the name of optimization, why even bother? Have you actually profiled your code and found this to be the real bottleneck? Just write the code in the most simple and clear way, and then worry about optimization when you discover a real problem that has been measured. Unless you have a very large site, the database connections are not the source of any bottlenecks.

Answer 4

Further to DGM's comment, resource types (like a MySQL connection reference) are not restored with a session. The contents of $_SESSION are serialized using serialize() and stored with the session ID, to be restored when the session is started again.

The documentation for serialize states:

string serialize( mixed $value )

value -- The value to be serialized. serialize() handles all types, except the resource type.

This means that you're actually reconnecting with every request. Using mysql_pconnect() as suggested by noselasd would probably alleviate this. Be sure to heed the notes on its manual page, particularly the fact that this requires PHP to run as a module.

Answer 5

a database connection object is a resource, meaning it's something magical from outside the php world (afaik a c struct or object). you can't serialize a resource, and storing a variable in the session means serializing it (the same thing serialize(); does).

php has a "nothing shared" nature, meaning it shares no resources with other requests. 2 requests may both be from user A, or one user A and one user B, no way to know. every request is encapsulated, and the only very limited "workaround" to make a logical connection between two requests are sessions. everything else must be grabbed from the outside (databases, files, memcached), so it's not php's problem anymore.

because of that, sharing database resources between requests was never meant to be. for every request his own mysql_connect. i assume this was the main reason making mysql popular with the php community in the first place. mysql connects a lot faster than other rdbms, because it had less features (no transactions, no views, no triggers, no nothing), so it was a good fit.

mysql_pconnect did connection pooling (but on a different level), but it wasn't a magic wand either. nowadays you should use PDO anyway (and PDO doesn't support it afaik).

if your application is to slow, the problems lie elsewhere.

my advice:

• whatever you think you do ... you're not sharing connections, so have a look at that. in fact, your application may be slow because of your trick (if it creates the connection for every query). remove that feature and test again.
• check your queries (EXPLAIN SELECT * FROM foo;)
• get your indices right
• use caching (eg. memcached)

The most common performance problem I see is people who think there's a most-common performance problem that they should be looking for, instead of measuring to find out what their actual performance problem actually is.
- cary millsap

Answer 6

as stated in other answers, php cannot store a resource in text, just cannot be done.

if you are using php 5.3 and mysqli, you can now use persistent connections by prefixing the host param when connecting with "p://".

using the mysql extension, use mysql_pconnect.

you could set up a mysql proxy maybe http://forge.mysql.com/wiki/MySQL_Proxy