Large value of max connections in mysql will affect the site performance?

Question

My website is having 5000+ users daily. So I changed mysql max connection in server as follows

      SET global max_connections = 1000000

and I edited my.conf file also. Now I feel like my site is loading very slow . Will this affect the site loading time ? Please clarify my doubt . Thanks in advance

Answer 1

Before you increase the connections limit, you will want to ensure that the machine on which the database is hosted can handle the additional workload. The maximum number of connections that can be supported depends on the following variables:

• The available RAM – The system will need to have enough RAM to handle the additional workload.

• The thread library quality of the platform - This will vary based
  on the platform. For example, Windows can be limited by the Posix
  compatibility layer it uses (though the limit no longer applies to
  MySQL v5.5 and up). However, there remains memoray usage concerns
  depending on the architecture (x86 vs. x64) and how much memory can
  be consumed per application process.

• The required response time - Increasing the number could increase
  the amount of time to respond to request. This should be tested to
  ensure it meets your needs before going into production.

• The amount of RAM used per connection - Again, RAM is important,
  so you will need to know if the RAM used per connection will overload the system or not.
• The workload required for each connection - The workload will also
  factor in to what system resources are needed to handle the
  additional connections.

Another issue to consider is that you may also need to increase the open files limit–This may be necessary so that enough handles are available.

Instead of worrying about these settings on your own system, you could opt to use a service like Morpheus, which offers databases as a service on the cloud. With Morpheus, you can easily and quickly set up your choice of several databases (including MySQL, MongoDB, Redis, and Elasticsearch).

Answer 2

We have a DB that sees 30 connections being used almost every day. We recently lowered the number of max_connections from 400 to 120 and noticed the database has a lot more memory available. This surprised us. It seems that MySQL hogs memory expecting to possibly get more connections.

tl;dr I'd set it as close as possible to the maximum you'd expect.