Site icon Database Tutorials

What you should know before changing MySQL max_connections variable

Mikael HOUNDEGNON

 

Have you tried connecting to your MySQL server only to get the annoying error, “too many connections” ? This means that the number of available connections are in use by other clients on the server. And it can be quite annoying to get this error especially when you have an urgent need to access your MySQL environment.

Luckily, you can easily fix this with the max_connections setting in your environment. Before you get started, there are a few things to know about the max_connections setting.

MySQL uses memory in 2 ways :

Generally speaking global memory is allocated once when the server starts and per connection memory allocations are for each connection. In order to change the max_connections is critical to not exceed your Total RAM Memory. The following formula can help to calculate the memory allocation in MySQL :
 

Main Global Buffers Variables that impact  Memory order by importance :

1- innodb_buffer_pool_size :  the size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system

2- innodb_log_buffer_size : The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value changed from 8MB to 16MB with the introduction of 32KB and 64KB innodb_page_size values. A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O.

3- key_buffer_size : Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks;  The maximum allowable setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms.  Using a value that is 25% of total memory on a machine that mainly runs  MySQL is quite common. However, if you make the value too large your system might start to page and become extremely slow

4- table_open_cache : The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table  cache by checking the Opened_tables status variable. If the value of Opened_tables is constantly increasing and you don’t do FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable

5- thread_cache_size : threads the server should cache for reuse.

6- query_cache_size : The amount of memory allocated for caching query results. Default value is 0, which disables the query cache. Allowable values are multiples of 1024; other values are rounded down to the nearest multiple. Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0.

 

Main Sessions Variables that impact Memory order by importance :

1- tmp_table_size : Maximum size of internal in-memory temporary tables. (The actual limit is determined as the smaller of max_heap_table_size and tmp_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk “MyISAM table”.

2- read_buffer_size : Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.  The maximum allowable setting is 2GB though we do not normally recommend having it higher then 8M.

3- read_rnd_buffer_size : When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can  improve ORDER BY performance by a lot. The maximum allowable setting is 2GB though we do not normally recommend having it higher then 8M.

4- sort_buffer_size : Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.

5- bulk_insert_buffer_size : MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT … SELECT, INSERT … VALUES (…), (…), …, and LOAD DATA INFILE when adding  data to non-empty tables. Limits the size of the cache tree in bytes per thread.

6- join_buffer_size : size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. the best way to get fast joins is to add indexes.

7- max_allowed_packet : maximum size of one packet or any generated/intermediate string. The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. value by default is small, to catch large (possibly incorrect) packets. You must increase this value if you are using large BLOB columns or long strings.  It should be as big as the largest BLOB you want to use.

8 – When use MyISAM engine following variable must be included in the RAM allocation per connection as well : myisam_max_sort_file_size and myisam_sort_buffer_size

 

Example of MySQL Total Memory allocation for 500 Connections you can change this number to see max memory allocation for MySQL : 

 

Once you’re comfortable with the value of max_connections for your environment you can use SET command below :

Exit mobile version