PHP opening / closing DB connections

dahayeser

I have developed a web application for a company and using PHP, HTML, JavaScript etc. Initially it was used just a team but it has got a nice bit of exposure and now the plan is to roll it out across all groups and possibly even all sites. Obviously this is great but I reckon I now need to look at performance.

There are a lot of AJAX calls in the app and at the moment for every call I open a database connection, retrieve my data, then close a database connection.

Is this inefficient and is there a better solution? If I leave the connection open (persistent connection) and people just 'x' the window rather than logging out via the app logout button (where I could trigger clean up) there would be a build up of unreleased DB connections which would I think would eventually cause the a connection failure due to running out of DB connections..

Is opening and closing on the call the safest and most reliable way of doing this?

Thanks in advance,
Sean

Ziycon

Have a look at caching some of the database data so you don't have to query the DB every time.

CreepingDeath

dahayeser wrote: »

There are a lot of AJAX calls in the app and at the moment for every call I open a database connection, retrieve my data, then close a database connection.

Is this inefficient and is there a better solution?

I don't use PHP but what you need is database connection pooling.

colm_c

For each request to your webserver, you do need to open and close the DB if you're using it, otherwise you'll have a bunch of open connections to the DB which will hog the connection pool as mentioned above. AFAIK these un-used connections get closed if not in use.

To be honest you'll get more bang for buck for performance with simple things:

- appropriate DB indexes
- appropriate fields in your DB, e.g. using a varchar(255) for holding a 50 digit number is a waste.
- rewriting your SQL for performance/speed, and benchmarking it

In your code you could then try different options for doing the same thing in php, here's some PHP performance tips:
http://blog.castsoftware.com/10-tips-to-speed-up-your-php-applications/

From an architecture point of view, you could look at:
- Using an appropriately tuned and configured webserver/php setup
- Using an MVC framework which might help with caching and logical separation of your data
- Using Varnish or some other web accelerator
- Using a noSQL DB for some of your data
- DB slave/master with different servers for read vs write
- load balancing multiple web servers
- etc.