Overhead & Impact of Opening & Closing MySQL Connections

[n1concepts]

Can someone provide some stats and/or details regarding the impact of opening & closing 'multiple' MySQL connections - in particular within the same script that requires multiple queries and/or inserts or updates to satisfy objective?

 

I'm curious to see a benchmark of stats comparing - executing Open/Close oppose to one 'persistent' connection to complete the entire query / insert operation then closing the connection per request.

 

Note: the thought of opening & closing the connection - for each - was to allow an infux of request (that's constantly hitting the db) to have ample time to process each respective query or insert/update. I know this flawed thinking but before making the neccessary edits, wanted to research to see stats - if any available - and review actual facts on why, 'multiple connections' bad way to handle processing the data.

 

BTW: the script is handling several thousands hits per hour (and each of those request require five to seven queries (searches) and three to four inserts so imagine the load placed on db - opening and closing connections along w/executing each process.

 

Any info appreciated - thx!

[n1concepts]

FYI: I found afew articles on the subject:

 

http://www.php.net/manual/en/features.persistent-connections.php

http://stackoverflow.com/questions/9736188/mysql-persistent-connection-vs-connection-pooling

 

To that, I don't have a problem w/max connections as I'm closing them and the average - even with thousands of hits to the db - is only 78 which is good considering the high volume of traffic.

 

Still, welcome comments if anyone have good insight.

[mac_gyver]

Can someone provide some stats and/or details regarding the impact of opening & closing 'multiple' MySQL connections

 

 

this would be something that you can benchmark yourself, using the database library you happen to be using, which you haven't mentioned.

 

each time you open a database connection, requires some overhead (the brief list of tasks performed was mentioned in your last thread on this subject.) if you are doing this x times during the execution of any one script, all the processing time taken after making the first database connection is wasted. it should be noted that closing a database connection also has the overhead of shutting down the instance of the database client on the php side and for the database server to actually shut down the process it started to service the connection the server side.

 

i think you are misunderstanding how interrupt driven, multitasking operating systems work and how services/servers work. just because you have a database connection open, doesn't mean you are 'tying' up the database server for the length of time that connection is open. it's not your job to minimize the amount of time you are connected to the database server, its your job to minimize the amount of processing cycles you are using. the database server only performs an action when it has received a set of data packets that form a complete command/query from the client (php in this case.) this communication is handled using interrupts. there can be any number of concurrent open database connections (limited by the - max_connections setting) from any number of different clients.

 

the total amount of data packets and the total number of commands/queries determines the load on the database server. doing anything that increases this load is undesirable, such as opening and closing a database connection for every query.

[mac_gyver]

concerning using a persistent connection to 'help' your current code. a persistent connection is only persistent when php is running as a server module (this is not common, php is most commonly running as a CGI application) and for only some versions of php/php's underlying database client library. in all other cases, trying to use a persistent connection only results in a regular connection.

[n1concepts]

Thx for your reply but I think you misunderstood my question.

Appreciate response however - i got it worked out.... thx!