CentOS 7 VM

Mariadb 10.1.12

max_connections, as well as some other variables, are auto-sized in a not-very-obvious-way based on the max number of open files which your server was run with. Try to increase it. You can pass it to mysqld_safe via --open-files-limit=count, but of course the hard limit should allow the value you are trying to set.

Sorry to feedback later

I checked my host

[root@pmo-cloud01 nova]# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 514557
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 514557
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

Seemed open files number is 1024

Is that too small?

Yes, that's too small to have max_connections=500 as you want to. It's adjusted automatically to the maximum number that server can get with this max number of open files (214, as you see). Check the hard limit, it's probably higher, in this case you'll be able to increase the value for the session running mysqld. If the hard limit is equally low, you'll need to have your machine reconfigured.

Thanks your info!

I will change my hard limit and try again!

To be clear – to make it work for mysqld, you need to increase the soft limit; but you can only do it if your hard limit is high enough.

I checked my hard limit with -H

seemed it is 4096

as my soft limit is only 1024, I guess I can try to change it to 2048 first and see if settings worked

thanks!

@Elena Stepanova

I did not know how to reopen this issue.

But just one more question, seemed I changed soft limit to higher one, but still max_connection in /etc/my.cnf did not work

ulimt -S -a

[root@test11061 ~]# ulimit -S -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 31146
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 16384
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 31146
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

Could you guide me how to change it permanent?

Thanks!

Please run SELECT @@open_files_limit on your MariaDB server, and compare the result with the limit you think you've set in the system.
If @@open_files_limit is lower than that, most likely you set the limit not for the user which the server is running under (usually mysql user), or not system-wide.
For instructions how to do it for a particular user and make it permanent, please check CentOS documentation.

If @@open_files_limit on your running MariaDB server is indeed 16384, but you are not getting the expected value for max_connections, please attach the output of SHOW GLOBAL VARIABLES.

Yeah, your are right

Seemed my open file limits did not take effect

MariaDB [(none)]> SELECT @@open_files_limit;
--------------------

--------------------

--------------------
1 row in set (0.00 sec)

Strange, I did update /etc/security/limits.conf like below
cat /etc/security/limits.conf
...
root soft nofile 16384
root hard nofile 16384

I guess MariaDB did not use this way to update system-wide parameters in CentOS 7 as systemd used
Checked mariadb.service file again found below info

1. It's not recommended to modify this file in-place, because it will be
2. overwritten during package upgrades. If you want to customize, the
3. best way is to create a file "/etc/systemd/system/mariadb.service",
4. containing
5. .include /usr/lib/systemd/system/mariadb.service
6. ...make your changes here...
7. or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
8. which doesn't need to include ".include" call and which will be parsed
9. after the file mariadb.service itself is parsed.
   #
10. For more info about custom unit files, see systemd.unit(5) or
11. http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

1. For example, if you want to increase mysql's open-files-limit to 10000,
2. you need to increase systemd's LimitNOFILE setting, so create a file named
3. "/etc/systemd/system/mariadb.service.d/limits.conf" containing:
4. [Service]
5. LimitNOFILE=10000

1. Note: /usr/lib/... is recommended in the .include line though /lib/...
2. still works.
3. Don't forget to reload systemd daemon after you change unit configuration:
4. root> systemctl --system daemon-reload

1. Use [mysqld.INSTANCENAME] as sections in my.cnf to configure this instance.

Seemed we need create one directory under /etc/systemd/system/mariadb.server with file limits.conf

I did so and restart maridb service

Magic things happened.

MariaDB [(none)]> SELECT @@open_files_limit
-> ;
--------------------

--------------------

--------------------
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT @@max_connections;
-------------------

-------------------

-------------------
1 row in set (0.00 sec)

It worked.

Thanks!