MariaDB table_open_cache value doesn't change

where in /etc/my.cnf being set ? within [mysqld] or [mariadb-10.1] groups ?

openvz virtualisation or non-openvz ?

centos 6.x ? or 7.x ?

output for

Code (Text):

mysqladmin var | grep table_open_cache | tr -s ' '

 

Interesting on openvz centos 7.3 with mariadb 10.1.20. It's systemd being difficult LOL

Code (Text):

grep table_open_cach /etc/my.cnf
table_open_cache = 14854

Code (Text):

mysqladmin var | grep table_open_cache | tr -s ' '
| table_open_cache | 3941 |

Code (Text):

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

and for mysql user

Code (Text):

su - mysql -c 'ulimit -aHS' -s '/bin/bash'
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 256892
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 262144
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 8192
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Open file limits inn systemd files

Code (Text):

 systemctl status mariadb
* mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           `-migrated-from-my.cnf-settings.conf, protecthome.conf
   Active: active (running) since Mon 2016-12-26 15:32:16 UTC; 5min ago
  Process: 29093 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 28827 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 28824 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 29070 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           `-29070 /usr/sbin/mysqld

Code (Text):

cat /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf 
# converted using /usr/bin/mariadb-service-convert
#

[Service]

LimitNOFILE=8192

what happens if you remove /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf

Code (Text):

rm /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
systemctl daemon-reload
systemctl restart mariadb

Code (Text):

mysqladmin var | grep table_open_cache | tr -s ' '
| table_open_cache | 8027 |

what happens if create custom /etc/systemd/system/mariadb.service.d/openfileslimit.conf and add to it

Code (Text):

[Service]
LimitNOFILE=65536

simple command line way of setting it up

Code (Text):

echo "[Service]" > /etc/systemd/system/mariadb.service.d/openfileslimit.conf
echo "LimitNOFILE=65536" >> /etc/systemd/system/mariadb.service.d/openfileslimit.conf
cat /etc/systemd/system/mariadb.service.d/openfileslimit.conf
systemctl daemon-reload
systemctl restart mariadb

Yup as suspected the MariaDB 10.1 auto setup /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf file overrode the system open file descriptor limit. Raising the limit to 65536 allowed /etc/my.cnf set table_open_cache to be raised to 14854.

Code (Text):

mysqladmin var | grep table_open_cache | tr -s ' '                                  
| table_open_cache | 14854 |

Guess need to update 123.09beta01 to account for this

 

Fixed in 123.09beta01 update Beta Branch - raise MariaDB open files artificial limit for systemd CentOS 7 systems

Update and run centmin.sh once after update to deploy fix

 

once you run updated centmin.sh in 123.09beta01 the auto fix function restarts mariadb mysql server as well