CODEWITHSUNDEEP
phpmysql
Sree KS
Sree KS

Reputation: 1343

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

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

Upvotes: 9

Views: 12489

Answers (3)

ageric
ageric

Reputation: 1

mysql (and mariadb) consume memory per connection it's capable of accepting. I'm not sure of the exact variables that go into it, but on a "reasonably" configured server it's usually between 1 - 16MiB per connection. Your database server is probably slow because it's swapping like mad.

I'd recommend you run mysqltuner.pl. Here's some output from the when run against our DB cluster, handling about just shy of a million web requests per day (we have max_connections 500 btw):

-------- Performance Metrics -------
[--] Physical Memory     : 503.8G
[--] Max MySQL memory    : 73.9G
[--] Total buffers: 60.2G global + 7.5M per thread (500 max threads)

Upvotes: 0

Ryan Shillington
Ryan Shillington

Reputation: 25147

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.

Upvotes: 5

Ninju
Ninju

Reputation: 2522

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).

Upvotes: 8

Related Questions