How costly is opening and closing of a DB connection?

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?

• join_buffer_size
• sort_buffer_size
• read_buffer_size
• read_rnd_buffer_size
• tmp_table_size / max_heap_table_size
• net_buffer_length / max_allowed_packet
• thread_stack

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:

• Open bug on PHP Not Cleanly Closing MySQL Connections on Apache Death.
• Requesting a Persistent ssh Connection.

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.

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).

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.