[nick2price]

Hi guys, quick question. In our system, we have a lot of php files, and all of them need to be connected to the database. At the moment, at the beginning of each file, we have something like

mysql_connect($sqlhost, $sqluser, $sqlpass) or die(mysql_error());
mysql_select_db($sqldb) or die(mysql_error());

Is this normal making a connection in every class? I know in java we can set up a connection pool which improves efficieny, is there something similar in php? Also, if there is something like a connection pool available, does it make a big difference in performance?

Cheers

Nick

[KingCuddles]

Would *_pconnect do what you want? There is a bit more information here.

Obviously they suggest not using mysql_*, but I assume you are working with legacy code here.

[nick2price]

Looks interesting, but at the same time quite dangerous. I am basically trying to improve the overall efficiency of our system, really by just making small changes here and there. The problem is, I am not an expert in php, so I dont really know the ways connections are normally made. To me, it seems like it could be costly making a direct connection in each class with the code I showed above. I dont know the in's and out's of the code I showed above, but I assume it is making a connection object each time. In java, if I had a lot of classes connecting to the same database, I would have one connection, and once I finished with it, I would place it in the pool. In doing this, I am basically reusing one connection over and over, rather than making a new connection each time. If its normal to do the above in large applications, then thats kool. My main question is really whether thats ok, or should I be doing things differently? Really, I am interested in what you guys would normally do when making a lot of connections to the same database?

Cheers

[KingCuddles]

Personally I use a persistent connection in PDO, for exactly the same reasons. I care about performance! As per the manual:

Quote

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

However to be perfectly honest I am no expert, and am sure one of the forums PHP Gurus will be along soon to help you out!

This may be worth a quick read: What are the disadvantages of using persistent connection in PDO.

If performace is also your goal, it may be worth using some form of profiler such as Xdebug or xhprof. Again StackOverflow has a nice answer. Eric Hogue's 'Profiling a PHP Application' is another good read.

Also spotted e_i_pi has covered MySQL profiling rather superbly on your previous thread! - Added this link as others may find that thread useful.

This post has been edited by KingCuddles: 14 September 2012 - 04:00 AM

[Atli]

Like the SO thread you pointed to points out, KingCuddles, there can be issues involved with using persistent connections. Unless you're actually having performance issues relating to your database connections, I would avoid using them. On most servers you will see little or no benefit from using them in any case, so you may as well stay clear of them unless they are needed.

nick2price, on 14 September 2012 - 08:53 AM, said:

Is this normal making a connection in every class?

No. It's in fact quite abnormal.

PHP applications are all single-threaded, without any sort of complexity that would require you to have multiple connections open. Given that you take care not to leave transactions open, or multiple result sets half used (which is not an issue with the old MySQL API, as it's not capable of using either), then your PHP application should only ever have to have a single database connection per request. Opening multiple connections is basically a waste of resources.

There are many different ways to achieve this, but what I've found it simplest to use a Singleton class to manage the database connection. It just connects once, and lets every part of your code access that single connection.

[dallbee]

Good question, and some good answers.

Quote

On most servers you will see little or no benefit from using them in any case, so you may as well stay clear of them unless they are needed.

In my experience, using persistent connections has always been a significant optimization. The difference becomes greater as you reach higher loads.

Quote

There are many different ways to achieve this, but what I've found it simplest to use a Singleton class to manage the database connection

Atli's advice here is sound. Try making a database class that is instantiated just once, and does its connection in its constructor. You'll find this is easy to implement and will clean up your code substantially.

As far as speed goes, how large do you plan on this application being?