Discover Centmin Mod today
Register Now

MariaDB table_open_cache value doesn't change

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by pamamolf, Dec 27, 2016.

  1. pamamolf

    pamamolf Well-Known Member

    4,101
    428
    83
    May 31, 2014
    Ratings:
    +838
    Local Time:
    10:35 AM
    Nginx-1.26.x
    MariaDB 10.6.x
    Hi :)

    On MariaDB 10.1.19 i use on mycnf file:


    Code:
    table_open_cache = 14854
    but mysqltuner report it as 7427 and i am getting a warning about it with a recommendation to increase it but it seems it doesn't get the new value after restarting MariaDB :(

    I double check for a possible double entry but there is no second option for it...
     
  2. eva2000

    eva2000 Administrator Staff Member

    55,816
    12,275
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,861
    Local Time:
    5:35 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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 ' '
     
  3. pamamolf

    pamamolf Well-Known Member

    4,101
    428
    83
    May 31, 2014
    Ratings:
    +838
    Local Time:
    10:35 AM
    Nginx-1.26.x
    MariaDB 10.6.x
    within [mysqld]

    Dedicated server

    Centos 7.x

    Code:
    [root@server ~]# mysqladmin var | grep table_open_cache | tr -s ' '
    | table_open_cache | 7427 |
     
  4. eva2000

    eva2000 Administrator Staff Member

    55,816
    12,275
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,861
    Local Time:
    5:35 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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 :)
     
  5. eva2000

    eva2000 Administrator Staff Member

    55,816
    12,275
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,861
    Local Time:
    5:35 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
  6. pamamolf

    pamamolf Well-Known Member

    4,101
    428
    83
    May 31, 2014
    Ratings:
    +838
    Local Time:
    10:35 AM
    Nginx-1.26.x
    MariaDB 10.6.x
    After checking the fix i can see that i do not have to restart it manually correct?
     
    Last edited: Dec 27, 2016
  7. eva2000

    eva2000 Administrator Staff Member

    55,816
    12,275
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,861
    Local Time:
    5:35 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    once you run updated centmin.sh in 123.09beta01 the auto fix function restarts mariadb mysql server as well