Why costly is opening and closing of a DB connection?

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 multiply the sum of those values by max_connections.

MySQL caches connections (or threads) using the Thread Cache (thread_cache_size). The max value for it is 100. When the client closes the connection, it is returned to the cache. When a new connection opens, it checks the thread cache. ON a very busy system, opening closing connection can get expensive, especially if you have long-running queries.