Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL

  • ... when the DB software is on localhost?
  • ... when the DB software is on another machine?
share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

Think about the amount of memory being allocated per DB Connection. What things must be allocated? According to MySQL 5.0 Certification Study Guide, page 357:

The server maintains several buffers for each client connection. One is used as a communications buffer for exchanging information with the client. Other buffers are maintained per client for reading tables and performing join and sort operations.

What settings govern the per-connection buffers?

It takes time to allocate and deallocate these buffers when a connection comes into being. Don't forget to multiple the sum of those values by max_connections. As a side note, please refrain from using mysql_pconnect as PHP and MySQL's persistent connections do not get along well. Here are two informative links on this topic:

In heavy-read, heavy-write environment, such as OLTP, this would be expensive in terms of RAM usage and possible inhibition due to swapping in the OS. On a low-write, low_read website, I would not worry as much.

share|improve this answer
    
Ah, this is what I was looking for Thanks a lot again. :) –  ThinkingMonkey Apr 24 '12 at 18:57
    
Can an allocation of an existing unused connection be made to a request of newer connection? Is this advisable? –  ThinkingMonkey Apr 24 '12 at 19:02
    
I just added that to my answer. It is best not to use persistent connections for MySQL although possible. –  RolandoMySQLDBA Apr 24 '12 at 19:03
    
Thank you...... –  ThinkingMonkey Apr 24 '12 at 19:05
    
@RolandoMySQLDBA: Awesome as always..!! –  Abdul Manaf May 1 '12 at 12:54
add comment

The dilemma between re-using an object and tearing it down and building it up again (both of which have advantages and disadvantages) can often be addressed with a compromise: cache the object, but for a limited time (i.e. with expiry). If the object is frequently accessed then it keeps being reused. But if it is not used for some time, then an expiry mechanism disposes of it, forcing it to be recreated when it is needed once again.

A system can have a global hook for these kinds of caches which is invoked when memory is low, which triggers all of them to drop recently unused objects.

share|improve this answer
    
Interesting perspective. I think of this in terms of two things: PHP Code (how rigorous the code has to be in the construction/destruction of Connections) and the OS (because no matter how good PHP is coded, any TIME_WAIT from netstat on a MySQL Connection that was terminated long ago gets in the way of new DB Connections being made). Nevertheless, +1 for this !!! –  RolandoMySQLDBA Apr 30 '12 at 18:39
add comment

I'm not sure it matters "how much more costly" it is. It is certainly more costly than re-using the same connection. What you'll observe will depend on whether you are correctly using connection pooling, how saturated your pool is, the available resources on the box, etc.

In general, if you are performing a loop to do some interaction with the database, you are going to be far better off re-using the same active connection than opening and closing within the loop (an anti-pattern that I see quite often).

share|improve this answer
    
Thanks for the answer.. What you have mentioned I do understand. Could you specify some data on what happens when a connection is opened? –  ThinkingMonkey Apr 24 '12 at 18:50
1  
I think Rolando has give some great details on the specifics. My answer is just to think about it at a higher level. If I need some groceries, I don't go to the store, get the milk, come home, go back to the store, get the bread, come home... –  Aaron Bertrand Apr 24 '12 at 19:04
    
+1 for your bird's eye view of the DB environment as well as one of the biggest abuses of a DB Connection as mentioned in your second paragraph. –  RolandoMySQLDBA Apr 24 '12 at 19:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.