How costly is opening and closing of a DB connection?

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?

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.

StackExchange Database Administrators Q#16969, answer score: 14