This article is half-done without your Comment! *** Please share your thoughts via Comment ***
As a Database Professional, we are listening many times that opening and closing a Database Connection is always costly.
every RDBMS products are doing lots of operation in the background to just an open and close Database Connection.
Every Database Connection requires some amount of Memory and CPU utilization. There is N number of different buffer settings for various 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 single connection is costly and how much different buffer it requires for opening and closing a Database connection.
Please note that I am just listing important buffer parameters, we should change the values basis on our requirements and loads of Database server.
MySQL server maintains several buffers for each database connection used for communication, join and sort operations, other manipulations.
read_buffer_size:
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 the result of nested queries.
join_buffer_size:
When we require getting a faster full join result without adding an index, We should increase the size of join_buffer_size.
The minimum size of the buffer used for plain index scans, range index scan and joins without an index.
sort_buffer_size:
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 the session level and it requires a larger size only for that session.
tmp_table_size:
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 performing advance GROUP BY queries, You should increase the size of tmp_table_size.
max_heap_table_size:
This variable defines the maximum size for user-created MEMORY tables which are required to grow at certain levels.
net_buffer_length:
This is one of the important variables 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, we should change the size of this variable.
max_allowed_packet:
Whenever we require to process the big binary string like BLOB column, we should increase the value of this variable.
thread_stack:
This variable defines the stack size for each connection and when we require to execute the 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 modify require variable based on our requirements.
Be aware before of setting large number max_connections without any estimation.
