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!