Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm running a dedicated MySQL server on an m1.large EC2 instance, which has 7.5 GB of RAM.

What might be a good number to set my max_connections value to?

share|improve this question

Maximum (theoretically) possible memory usage = Total Server Buffer + (max_connections * Buffer per thread), which is:

key_buffer_size + tmp_table_size + max_heap_table_size + max_heap_table_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size + (max_connections * (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size))

What will be a good number depends on these components. How will you adjust these components depends on what your DB does.

You might set max_connections to 100 and other parameters to higher values than usual and every thread will be fast because of large buffers, you might set it to 800, and other parameters to a lower values than usual and DB might become slow for every connection because it will read data from the disk for global cache.

So, everything depends on your DB, how much data will it process at time and how will you optimize your configuration for it.

You have to go over all parameters listed here to know what are optimal values for your DB. Maybe, your application and DB architecture is so bad, that you have too large joins and join_buffer_size is very large, or maybe you require sorting, so every thread may eat more and more ram, but it doesn't mean that all threads will do so at the same time. This is theoretical maximum. Average, optimal values may vary. You might have 10000000 requests that require just a 8MB RAM per thread to work entirely with buffer, but one thread might require 10GB of RAM to operate with buffer. It might not be optimal to change parameters to satisfy that one large query. So, depends :)

Let your application run, collect stats and optimize DB accordingly, not vice-versa.

share|improve this answer

The answer depends on the expected workload: the expected number of concurrent connections and the expected time of a connection. The default value is set to 100. For a good and dedicated machine, you can increase it to some value like: 200, 500, or even more! You can set it to some value and watch your database connections. When you feel there is a need, you can increase it more. Of course, the default value might be small and should be increased.

The max_connections is not the only parameter to look at. There are other things that need to be done to achieve a better performance (like query optimization). Increasing the max_connections might not be always the best thing to do.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.