Mysql: MySQL max_connections limit at 214 connections

[kaumini]

I set the max_connections to 1000 using a custom my.cnf

But MySQL conatainer limits max_connections to 214. Gives following warning during runtime.

MySQL v5.7

How to overcome the limit?

[wglambert]

I can't reproduce any issue with setting that variable

Mounting a my.cnf

$ docker run -d --rm -v "$PWD"/my.cnf:/etc/mysql/my.cnf --name mysql -e MYSQL_ROOT_PASSWORD=pass mysql:5.7 
d3baf1c33106bf470af6e4f4d84f3d638eda959519cc5a7b634b7f73f8aa6b12

$ grep -i connections <(docker logs -f mysql 2>&1)
2019-09-30T17:13:02.685056Z 0 [Note] mysqld: ready for connections.
2019-09-30T17:13:25.703700Z 0 [Note] mysqld: ready for connections.
^C

$ docker exec -it mysql bash

root@d3baf1c33106:/# cat /etc/mysql/my.cnf                                                                                             
!includedir /etc/mysql/conf.d/                                                                                                         
!includedir /etc/mysql/mysql.conf.d/                                                                                                   
[mysqld]                                                                                                                               
max_connections=1000

root@d3baf1c33106:/# mysql -uroot -ppass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set (0.01 sec)

Using --max_connections=1000

$ docker run -d --rm --name mysql -e MYSQL_ROOT_PASSWORD=pass mysql:5.7 --max_connections=1000
b7f512aac8431f8258ddbe660f41f8f506d27b261c4a3755eb992705c747b2c5

$ grep "mysqld: ready" <(docker logs -f mysql 2>&1)
2019-09-30T16:51:38.795734Z 0 [Note] mysqld: ready for connections.
2019-09-30T16:52:06.074452Z 0 [Note] mysqld: ready for connections.
^C

$ docker exec -it mysql mysql -uroot -ppass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set (0.04 sec)

Ulimit doesn't seem to affect setting the value

$ docker exec -it mysql bash
root@b7f512aac843:/# ulimit -Hn && ulimit -Sn
1048576
1048576

root@b7f512aac843:/# mysql -uroot -ppass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "open_files_limit";
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| open_files_limit | 1048576 |
+------------------+---------+
1 row in set (0.01 sec)

$ docker run -d --rm --ulimit nofile=500:500 --name mysql -e MYSQL_ROOT_PASSWORD=pass mysql:5.7 --max_connections=1000
c3f25b912652d995b0e7525f929594bde82496e83ed01b054981da3fe7599edf

$ grep -i connections <(docker logs -f mysql 2>&1)
2019-09-30T17:08:00.029149Z 0 [Note] mysqld: ready for connections.
2019-09-30T17:08:26.009172Z 0 [Note] mysqld: ready for connections.
^C

$ docker exec -it mysql bash

root@c3f25b912652:/# ulimit -Hn && ulimit -Sn
500
500

root@c3f25b912652:/# mysql -uroot -ppass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "open_files_limit";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 500   |
+------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_open_files_limit

[KalanaDananjaya]

I tried the same steps using docker-compose and I am getting the same issue.

version: '3.7'
services:

  mysql:
    container_name: mysql
    image: mysql:5.7.24
    ports:
      - "3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - "./mysql/scripts/setup.sql:/docker-entrypoint-initdb.d/setup.sql"
      - "./mysql/scripts/my.cnf:/etc/mysql/my.cnf"
    command: [--ssl=0]
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-uroot", "-proot"]
      interval: 10s
      timeout: 10s
      retries: 5
      start_period: 30s

This is the my.cnf file

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]                                                                                                                               
max_connections=2213

Getting the warning Changed limits: max_connections: 214 (requested 2213) in the terminal

[yosifkit]

/**
Adjust @c open_files_limit
  Computation is  based on:
 - @c max_connections,
 - @c table_cache_size,
 - the platform max open file limit.
*/

MySQL uses max_connections and table_cache_size (i.e. table_open_cache) to determine if it'll fit within the current max_open_files (i.e. ulimit -n) and then adjusts to try and fit.

The ulimit needs to be at least the max of these three numbers:

• 10 + max_connections + table_cache_size * 2
• max_connections * 5
• open_files_limit ? open_files_limit : 5000

So if you are trying to increase the max_connections, then you need to make sure that the ulimit in the container is large enough. (docker run and compose file)

ulimits:
  nofile:
    soft: 20000
    hard: 40000

---

Here is the code from each MySQL branch that does the calculation for open_files_limit, see also the adjust_max_connections and adjust_table_cache_size in the same file.

• mysql 5.6: https://github.com/mysql/mysql-server/blob/895e70b7b8fadb409cd10687e853bfbcdb1a2913/sql/mysqld.cc#L6912-L6962
• mysql 5.7: https://github.com/mysql/mysql-server/blob/817fcb883a8457fcf507156bb1a2052e887acc4c/sql/mysqld.cc#L5530-L5574
• mysql 8 (: https://github.com/mysql/mysql-server/blob/77306a41670543e103f94fa0db77a71d2b9f88b2/sql/mysqld.cc#L7372-L7411

---

FYI the "default" 214 comes from a ulimit of 1024:

// from adjust_max_connections
// TABLE_OPEN_CACHE_MIN is defined as 400
  limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;
  // ie, 1024 - 10 - 400 * 2 => 214
  if (limit < max_connections) {
    max_connections = limit;
  }

[feeh27]

I tried the same steps using docker-compose and I am getting the same issue.

version: '3.7'
services:

  mysql:
    container_name: mysql
    image: mysql:5.7.24
    ports:
      - "3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - "./mysql/scripts/setup.sql:/docker-entrypoint-initdb.d/setup.sql"
      - "./mysql/scripts/my.cnf:/etc/mysql/my.cnf"
    command: [--ssl=0]
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-uroot", "-proot"]
      interval: 10s
      timeout: 10s
      retries: 5
      start_period: 30s

This is the my.cnf file

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]                                                                                                                               
max_connections=2213

Getting the warning Changed limits: max_connections: 214 (requested 2213) in the terminal

I Solve in Docker compose with snippet bellow:

command: >
    bash -c "mysqld --user=root --max_connections=2213"

[yosifkit]

In docker-compose, you'll want to use something similar to the below examples so that the docker-entrypoint.sh script will properly initialize the database with any of the environment variables described on the Docker Hub. Using bash as the first argument of your command will cause the entrypoint to skip all of its logic.

command: ["--max_connections=2213"]

# mysqld is not necessary as it will be implied if the command is just flags to mysqld
command: 
  - mysqld
  - --max_connections=2213

[Adiii717]

ulimit + my.cnf worked for me

[mysqld]
local-infile=0
datadir=/var/lib/mysql
user=mysql
symbolic-links=0

max_connections = 100

and ulimit in docker-compose

     ulimits:
       nofile:
         soft: "65536"
         hard: "65536"

Docker-compose for those who still seeing an issue

version: '3.3'

services:
   db:
     image: mysql:5.7
     container_name: main-db
     ulimits:
       nofile:
         soft: "65536"
         hard: "65536"
     volumes:
       - ./my.cnf:/etc/mysql/my.cnf
     restart: always
     environment:
       MYSQL_ROOT_PASSWORD: password
       MYSQL_DATABASE: maindb
       MYSQL_USER: maindb
       MYSQL_PASSWORD: password
     ports:
       -  "3306:3306"