As a Database Proffesional, we are listening many times that opening and closing a Database Connection is always costly.
Each and every RDBMS products are doing lots of operation to open and close Database Connection.
Each and every Database Connection require some amount of Memory and CPU utilization. There are N number of different buffer settings for different RDBMS products.
There are also different approaches to manage database connections like: Connection pooling, Persistent connections.
In this post, I am showing that how MySQL one single connection is costly and how much different buffer, it requires to allocate and deallocate for opening and closing a Database connection.
Please note that, I am just listing important buffer parameters, we should change the value of its base on our requirements and loads of Database server.
MySQL server maintains several buffers for each database connection basically use for communication, join and sort operations, other manipulation.
We should increase the size of this buffer, when each connection requires sequential scan for MyISAM tables.
For other storage engines, it also uses for indexing, bulk insert into partitions and caching result of nested queries.
When we require to get a faster full join result without adding an index, We should increase the size of join_buffer_size.
The minimum size of the buffer uses for plain index scans, range index scan and joins without an index.
When we require a faster sort operation, we should increase the size of sort_buffer_size.
This buffer is not specific to any storage engine and it applies in a general manner of optimization.
The best way to increase at session level and it requires a larger size only for that session.
This buffer size is used for internal in-memory temporary tables and this variable does not apply to user created MEMORY tables.
If you require to perform advance GROUP BY queries, You should increase the size of tmp_table_size.
This variable defines the maximum size for user created MEMORY tables which is required to grow at certain levels.
This is one of the important variable because each client database connection associated with a connection buffer and result buffer. A net_buffer_length variable defines the size for this both buffer and dynamically enlarged up to max_allowed_packet bytes as needed.
Whenever we have less memory, only we should change the size of this variable.
Whenever we require to process big binary string like BLOB column, we should increase the value of this variable.
This variable defines the stack size for each connection and when we require to execute complex SQL Statements, we should increase the size of this variable.
Thus all variables require to allocate and deallocate for each and every database connection and now we can multiply the values of this variable with max_connections.
This is very important to know about this all and we should modified require variable based on our requirements.
Be aware before to set large number max_connections without any kind of estimation.