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?
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?
Maximum (theoretically) possible memory usage = Total Server Buffer + (max_connections * Buffer per thread), which is:
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.
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 themax_connections
might not be always the best thing to do.