Pros vs Cons of Opening/closing MySQL Connections

[n1concepts]

Hi,

 

Need some input on the pros and cons of having 'multiple' MySQL connections - opening and closing (per query) which required to process the entire <php script> operation - oppose to opening 'one' connection and running that same script, THEN closing that connection before queuing the next one for processing.

 

Setup: Have a php script that reads and captures inbound (appended) variables from URL's and peforms several checks (queries and inserts) to ensure:

 

1. data sent in the link is valid and if so, decide where to forward the requestor based on those variables - read from that (inbound) URL.

Note: there are seven - individaul - MySQL queries along with four INSERTs that take place for each process.

 

To add, there are - between - 1500 to 8500 HTTP hits to that script (sending in this information) per hour and that count will gradually grow (expect times ten within the next year).

---

 

Current performance time - opening and closing the db connection (for every <individual> query and/or insert) - is working just fine and processing the information w/little to now latency (average four seconds total which includes the actual redirect to external sites - the DB portion taking one to two seconds on average).

 

Here's that current db connection: (example of course):

function execute($query) {
$db_handle = mysqli_connect("1.2.3.4","db_user1","pass123","db_name");
 $res=mysqli_query($db_handle,$query);
 mysqli_close($db_handle);
 return $res;
}

Note: when the 'execute()' function is called - in the actual scripts - it immediately close that db connection right after running that specific query or insert. Thus, multiple connections happening per process - oppose to just opening the db (once) at beginning of script then forcing close at very end of script once the entire set of operations complete within the script.

 

The (original) thinking behind this logic was to - attempt - try and keep 'queue' time on incoming requests to a minimum by processing bits of each request - in hopes to avoid a backlog (waiting to be processed).

<?php
    
function execute($query) {
// $db_handle = mysqli_connect("1.2.3.4","db_user123","pass123","db_name");
 $res=mysqli_query($db_handle,$query);
// mysqli_close($db_handle);
 return $res;
}
    
?>

In writing this - to explain - i just answered my own question (LOL!!!): this method still creating a queue b/c in processing portions of any one request - being there are several queries and inserts required to complete that entire request - still creates the buffering I want to avoid.

 

Anyway, I'm thinking I need to change the db connection string (move to static 'include' file) to just establish that connection - comment out 'mysqli_close ()' in the above function and execute that close function at the very end of the actual scripts (themselves).

 

Objective: establish just ONE db connection for each record being processing oppose to seven or eight connections for same queued entry.

I see this is the logical approach but appreciate insight to get other's thoughts on this being the setup is (somewhat) high volume of traffic in short bursts.

 

FYI: batch processing the entries - to take load off the db by saving entries to a file for later import to db - not possible b/c each record logged must be unique to all 'previous' records entered - thus, every NEW record is checked against the existing records so reason 'batch' processing not an option - every record (on initial hit to script) must be read, processed, and logged to the db - all in real-time.

 

Again, everything works - currently - based on the current 'db connection' function shown above but i'm looking to optimize this (reduce lag time if better to establish just one db connection oppose to multiple to process each individual request/record).

 

I know it's extra load on db but curious if this best due to high volume of requests coming in 24/7.

Again: the question is, 'is it best to establish just one db connection and hold it open until the entire script completes?' or ' stay w/the current "Open/Close" method due to high volume?"

What I'm looking for is input from those that have (actually) dealt w/this type scenario - I konw the 'book' answer but need real experience on issues encountered and experiences on why one choice made over the other - thx!

 

Let me know your thoughts.

[kicken]

Connecting to the DB server takes a bit of time as there is a fair amount that happens there (create connection, do authentication, allocate resource, etc). Trying to do that several times within a script is just going to waste time and resources. Open the connection once, then do everything you need to do. If desired, you can delay the opening of the connection until you are sure you need it. That way you can do a number of validations and possibly reject bad calls w/o needlessly connecting to the database.

 

Another way to reduce the overhead of connecting to the database may be to uses persistent connections. In this way you'd open the connection with a p: prefix, but not explicitly close it. PHP will maintain the connection and re-use it next time a script that needs it is run. How well this works will depend on how your PHP setup is configured. If your using an Apache module or FastCGI it should work OK, you'd have one connection per PHP process.

Edited July 7, 2013 by kicken

[n1concepts]

Got chat - that's for that insight (I was looking into persistent connections) but was concerned if holding any particular connection open - beig there are countless queries and INSERT/UPDATES hitting same script (database) nonstop - would create a problem.

 

I'll beta test this to confirm but sounds like that will reduce load, easily, by 25% if not more...

 

Thanks for reply - helps alot (appreciate it!)