M Salehi M Salehi - 13 days ago 5
MySQL Question

cannot increase open-files-limit in mariadb 10 on centos7

I searched about the topic subject and tested options, but I still cant increase the open-files-limit on my mariadb server that is used as remote database server for cpanel/whm server.
here is s good reference
http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit

I increased it in

/etc/my.cnf

open-files-limit=65550


here is some results

#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) 63471
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 65535
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) 65535
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

#ulimit -Hn -Sn
open files (-n) 65535
open files (-n) 65535

cat /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=65500

cat /usr/lib/systemd/system/mariadb.service

[Unit]
Description=MariaDB database server
After=syslog.target
After=network.target

[Service]
Type=simple
User=mysql
Group=mysql
LimitNOFILE=infinity
LimitMEMLOCK=infinity


and still in mysql I get

show global variables like 'open%';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| open_files_limit | 1024 |

+------------------+-------+

1 row in set (0.00 sec)

and im getting following error over and over after restart

60108 16:30:02 [ERROR] mysqld: Can't open file: './username_wp/wp_users.frm' (errno: 24)
160108 16:30:02 [ERROR] Error in accept: Too many open files
160108 16:30:04 [ERROR] Error in accept: Too many open files
160108 16:30:06 [ERROR] Error in accept: Too many open files
160108 16:30:11 [ERROR] mysqld: Can't open file: './username_db/strikes.frm' (errno: 24)


and here is the startup log of mariadb

[Warning] Could not increase number of max_open_files to more than 1024 (request: 132107)

Answer

finally i find solution but without any reasonable cause ! i was running MariaDB 5.5 and all settings where fine but the soft limit did not goes more than 1024 ! i was thinking my mariadb is 10.0 as i freshly installed it

after i find out that its 5.5 i tried to upgrade it to v10.0 ! and then BOOM ! problem solved without any extra action or setting ( kernel setting allows maximum files limit as described in reference link )

i hope it help other peoples have same problem ( but after try all other required settings )

Comments